postgreSQL 查询：创建一个表，其中行按月/年划分，并计算值的数量，其中'01/month/year'位于两个日期列之间

Question

这是我在这里的第一个问题。

我在创建一个复杂的查询以根据月份/年份的第一天是否位于两个日期列之间对值进行分组时遇到了问题。

这是我的表格的一个例子：

2020年07月31日202020202020202020202020202020202020202020202020年11月7日

USER_ID	START_DATE	END_DATE
A	2020年7月3日
A	2020年6月5日	20200年7月3日
A	2020年5月8日	20200年6月5日
A	2020年4月10日	20200年5月8日
B	2020年2月13日
B	2020年1月16日
C	2020年5月22日
C	2020年4月24日
D	2020年9月25日	202010年10月23日
D	2020年8月28日
D	2020年7月31日
D	2020年7月3日
D	2020年6月5日
E	2020年11月25日	20200年12月23日
E	2020年10月28日
E	2020年9月30日
F	2020年2月14日		2020年3月13日
F	2020年1月17日		2020年2月14日
F	2019年12月20日	2020年1月17日
F	2019年11月22日	2020年12月
G	2020年7月11日
G	2020年10月10日

试试这个：

WITH m AS
( SELECT generate_series(min(date_trunc('month', start_date)), max(end_date), '1 month') :: date AS month
FROM my_table AS t
)
SELECT to_char(m.month, 'YYYY') AS year
, to_char(m.month, 'MM') AS month
, count(DISTINCT t.user_id) AS "count(distinct user_id)"
FROM my_table AS t
RIGHT JOIN m
ON daterange(t.start_date, t.end_date) @> m.month
GROUP BY m.month
ORDER BY m.month

第一查询CCD_ 1计算覆盖my_table的start_date和end_date的月份列表。

第二个查询将my_table与生成的表"m"连接起来，以选择其间隔daterange(start_date, end_date)包含月1日的所有用户(请参阅手册(。

然后，按m.month对行进行分组，并使用count(DISTINCT user_id)聚合函数计算每月不同user_id的数量(请参阅手册(。

最后，"m"0子句允许选择my_table中没有相应user_id的月份(请参阅手册(。

请参阅dbfiddle中的测试结果。