sql - 日历的 Postgres 查询

Question

我正在尝试编写一个查询，以从一个简单日历应用程序的事件查询中检索数据。表结构如下:

table name: events

Column   |  Type      
---------+-----------
 id      | integer
 start   | timestamp
 end     | timestamp

表里面的数据

id| start               | end
--+---------------------+--------------------
1 | 2017-09-01 12:00:00 | 2017-09-01 12:00:00
2 | 2017-09-03 10:00:00 | 2017-09-03 12:00:00
3 | 2017-09-08 12:00:00 | 2017-09-11 12:00:00
4 | 2017-09-11 12:00:00 | 2017-09-11 12:00:00

预期的结果是

 date      | event.id
-----------+---------
2017-09-01 | 1
2017-09-03 | 2
2017-09-08 | 3
2017-09-09 | 3
2017-09-10 | 3
2017-09-11 | 3
2017-09-11 | 4

如您所见，仅检索有事件的日期(不仅是开始和结束，还有中间的日期)，没有事件的日期根本不会被检索。在第二步中，我希望能够限制不同天数的数量，例如“获得 4 天的事件”可能超过 4 行。

现在我只能使用以下查询来检索基于开始日期的事件:

SELECT start::date, id FROM events WHERE events.start::date >= '2017-09-01' LIMIT 3

我想我已经考虑过 DENSE_RANK 和 generate_series，但到目前为止我还没有找到一种方法来填补开始和结束之间的空白，但在没有数据的日子里没有。

简而言之:我想要得到的是:获得接下来的X天有事件的地方。有事件的日期是开始 <= 日期 >= 结束

的一天

有什么想法吗？

编辑

感谢 Tim，我现在有了以下查询(修改为使用 generate_series 而不是表并使用 dense_rank 添加了限制):

select date, id FROM (
SELECT 
    DENSE_RANK() OVER (ORDER BY t1.date) as rank,
    t1.date, 
    events.id 
FROM
    generate_series([DATE]::date, [DATE]::date + interval '365 day', '1 day') as t1
INNER JOIN 
    events 
    ON t1.date BETWEEN events.start::date AND events."end"::date
) as t
WHERE rank <= [LIMIT]

这真的很好用，尽管我不能 100% 确定这种限制对性能的影响

Answer 1

我认为您确实需要一个日历表来涵盖您的数据可能出现的所有日期范围。在下面的第一个 CTE 中，我生成了一个涵盖 2017 年 9 月的表格。然后我们需要做的就是根据给定日期出现在给定范围内的标准，将此日历表与事件表进行内部联接。

WITH cte AS (
    SELECT CAST('2017-09-01' AS DATE) + (n || ' day')::INTERVAL AS date
    FROM generate_series(0, 29) n
)

SELECT
    t1.date,
    t2.id
FROM cte t1
INNER JOIN events t2
    ON t1.date BETWEEN CAST(t2.start AS DATE) AND CAST(t2.end AS DATE);

输出:

    date                id
1   01.09.2017 00:00:00 1
2   03.09.2017 00:00:00 2
3   08.09.2017 00:00:00 3
4   09.09.2017 00:00:00 3
5   10.09.2017 00:00:00 3
6   11.09.2017 00:00:00 3
7   11.09.2017 00:00:00 4

此处演示:

Rextester