时间戳

首页 > 解决方案 > 使用生成的日系列连接表

sql - 使用生成的日系列连接表

问题描述

我有这两张桌子：

SELECT sp.page_id, u.date_registered 
FROM users u 
  JOIN solved_pages sp ON u.username = sp.solver;

SELECT date_begin::date, (date_begin + '31 day'::interval)::date as date_end
FROM generate_series(timestamp '2020-01-01', timestamp '2021-01-01', interval '1 day') AS date_begin;

我想实现某种加入。我想计算所有 page_id，date_registered 在每行的 date_begin 和 date_end 之间。对此有什么建议吗？提前致谢 ：）

表用户

username | date_registered
------------------------------
user1    | 2020-04-01 20:00:00
user2    | 2020-04-07 21:00:00
user3    | 2020-12-01 14:00:00

表solved_pa​​ges

solver   | page_id
------------------------------
user1    | page1
user1    | page2
user1    | page3
user2    | page1
user2    | page2
user3    | page1

我只想要一个页面的结果表（比如说page1）：

date_begin  | date_end   | no_solvers
-------------------------------------
2020-01-01  | 2020-02-01 | 0
-------------------------------------
2020-02-01  | 2020-03-01 | 0
--------------------------------------
................
2020-04-01  | 2020-05-01 | 2   -> because user1 and user2 has registered in that period and both solved page1

标签： sqlpostgresqlpostgresql-9.5

解决方案

这看起来像：

With Registrations as
(
SELECT sp.page_id, u.date_registered 
FROM users u 
  JOIN solved_pages sp ON u.username = sp.solver
and sp.page_id=‘page1’
), TimeSeries as
(
SELECT date_begin::date, (date_begin + '31 day'::interval)::date as date_end
FROM generate_series(timestamp '2020-01-01', timestamp '2021-01-01', interval '1 day') AS date_begin;
)
Select a.date_begin, a.date_end, b.sum(case when b.page_id is null then 0 else 1 end) as no_solvers
from TimeSeries a 
Left join Registrations b
on b.date_registered between a.date_begin and a.date_end
Group by a.date_begin, a.date_end

推荐阅读