postgresql - postgresql - 如何在聚合表和连接表输出中显示空值？

Question

我有2个表如下：

select * from tokens;
 id |      datefield      | category | amount 
----+---------------------+----------+--------
  1 | 2019-01-01 00:00:00 |        1 |      3
  4 | 2019-02-01 00:00:00 |        1 |      3
  2 | 2019-01-15 00:00:00 |        2 |      1
  5 | 2019-01-25 00:00:00 |        3 |      2
  3 | 2019-02-17 00:00:00 |        2 |      1
  6 | 2019-03-16 00:00:00 |        1 |      5
  7 | 2019-03-11 00:00:00 |        3 |      6

select * from category;
 id |   name    
----+-----------
  1 | fiction
  2 | sports
  3 | news

现在我想加入这些表格，并在表格中显示每个类别每月的总金额，如下所示：

  name   | sum | monthfield 
---------+--------+------------
 fiction |   3    | 2019-01
 sports  |   1    | 2019-01
 news    |   2    | 2019-01
 fiction |   3    | 2019-02
 sports  |   1    | 2019-02
 news    |   NULL | 2019-02
 fiction |   5    | 2019-03
 sports  |   NULL | 2019-03
 news    |   6    | 2019-03

我已经编写了以下查询，但是我无法获得具有 NULL 值的行，如上面所需的输出所示：

select
  c.name, sum(t.amount), to_char(t.datefield, 'YYYY-MM') as monthfield
from
  category c
FULL OUTER JOIN
  tokens t on c.id = t.category
group by
  (c.name, c.id, monthfield)
order by
  (to_char(t.datefield, 'YYYY-MM'),c.id)
  ;

  name   | sum | monthfield 
---------+-----+------------
 fiction |   3 | 2019-01
 sports  |   1 | 2019-01
 news    |   2 | 2019-01
 fiction |   3 | 2019-02
 sports  |   1 | 2019-02
 fiction |   5 | 2019-03
 news    |   6 | 2019-03

如果您能帮助我解决查询，我将不胜感激。

标签： postgresql

Answer 1

假设tokens每月至少有一个值，您可以执行以下操作：

select 
    c.name,
    sum(t.amount) sum_amount,
    to_char(m.monthfield, 'yyyy-mm') monthfield
from 
    category c
    cross join (select distinct date_trunc('month', datefield) monthfield from tokens) m
    left join tokens t
        on t.category = c.id
        and t.datefield >= m.monthfield
        and t.datefield < m.monthfield+ interval '1 month'
group by c.id, c.name, to_char(m.monthfield, 'yyyy-mm')
order by monthfield, c.id

category该查询在表和 中的所有可用月份之间生成笛卡尔积tokens。然后，表格tokens被带入left join类别和日期范围。最后，数据按类别和月份汇总。

DB Fiddle 上的演示：

姓名 | 总金额 | 月场
:-------- | ---------: | :---------
小说 | 3 | 2019-01   
运动 | 1 | 2019-01   
新闻 | 2 | 2019-01   
小说 | 3 | 2019-02   
运动 | 1 | 2019-02   
新闻 |       空| 2019-02   
小说 | 5 | 2019-03   
运动 |       空| 2019-03   
新闻 | 6 | 2019-03