时间戳

首页 > 解决方案 > 是否可以计算不同月份具有相同 ID 的项目数（例如跨聚合月份的交叉点）？

sql - 是否可以计算不同月份具有相同 ID 的项目数（例如跨聚合月份的交叉点）？

问题描述

我很难说出我的问题。我想按月分组，然后每个月，找出 N 和 N-1 个月之间有多少帐户是相同的。所以，具体来说，我不是在比较聚合（如总计数），我想以某种方式计算intersect月份之间的值。

例如，这张表：

date          id    

2020-01-01    123
2020-01-01    234
2020-01-01    345
2020-02-01    123
2020-02-01    345
2020-03-01    123
2020-03-01    456

我正在尝试产生如下结果：

date          total   same 
2020-01-01    3       null -- no previous month with which to compare
2020-02-01    2       2
2020-03-01    2       1

这可能吗？

标签： sqlpostgresql

解决方案

使用lag()和条件聚合：

select date_trunc('month', date) as yyyymm, count(*) as total,
       count(*) filter (where date_trunc('month', date) = date_trunc('month', prev_date) as same_as_prev_month
from (select t.*,
             lag(date) over (partition by id) as prev_date
      from t
     ) t
group by yyyymm
order by yyyymm;

注意：这假设 id 每月最多有一行。如果行可以重复，请使用count(distinct)：

select date_trunc('month', date) as yyyymm,
       count(distinct id) as total,
       count(distinct id) filter (where date_trunc('month', date) = date_trunc('month', prev_date) as same_as_prev_month

推荐阅读