时间戳

首页 > 解决方案 > 按日历月聚合日期数据

sql - 按日历月聚合日期数据

问题描述

假设我有一些与用户注册系统相关的数据，以及当他们禁用帐户时（为简单起见，我刚刚写了年/月）：

name  | created   | deactivated
------|-----------|--------------
Dan   | 2018 / 2  | 2018 / 5
Mike  | 2018 / 4  | 2018 / 7 
Dave  | 2018 / 5  | NULL

我将如何编写一个查询，将这些数据转换为基于日历月的聚合形式，可以追溯到年初，例如：

month     | created  | deactivated
----------|----------|--------------
2018 / 7  | 0        | 1
2018 / 6  | 0        | 0
2018 / 5  | 1        | 1
2018 / 4  | 1        | 0
2018 / 3  | 0        | 0
2018 / 2  | 1        | 0
2018 / 1  | 0        | 0

我已经能够为单个列提出类似的内容：

SELECT
  extract(year from created) || ' / ' || extract(month from created) as month,
  count('month')
FROM accounts
GROUP BY month
ORDER BY month;

但是后来我陷入了如何根据另一个字段添加另一列的问题（我在 中使用created）GROUP BY。这个查询也会省略不包含任何数据的月份，并且不会回到2018 / 1没有数据的时候。

标签： sqlpostgresql

解决方案

让我假设这month真的是一个约会。如果是这样，请使用generate_series()生成日期，然后left join进行group by计算：

select gs.mon, sum(created) as created, sum(deactivated) as deactivated
from generate_series('2018-01-01'::date, '2018-07-01'::date, interval '1 month') gs(mon) left join
     ((select created as dte, 1 as created, 0 as deactivated 
       from accounts
      ) union all
      (select deactivated, 0 as created, 1 as deactivated 
       from accounts
      )
     ) a
     on date_trunc('month', a.dte) = gs.mon
group by gs.mon;

推荐阅读