我在PostgreSQL 10.5中有一个表trips
:
id start_date end_date
----------------------------
1 02/01/2019 02/03/2019
2 02/02/2019 02/03/2019
3 02/06/2019 02/07/2019
4 02/06/2019 02/14/2019
5 02/06/2019 02/06/2019
我要计算行程中与给定周重叠的天数。表中的行程具有包含界限。周从星期一开始,星期日结束。预期结果为:
week_of days_utilized
------------------------
01/28/19 5
02/04/19 8
02/11/19 4
对于日历参考:
Monday 01/28/19 - Sunday 02/03/19
Monday 02/04/19 - Sunday 02/10/19
Monday 02/11/19 - Sunday 02/17/19
我知道如何用我使用的编程语言来写这个,但是我更喜欢用Postgres来写,我不清楚从哪里开始...
2条答案
按热度按时间qeeaahzv1#
您似乎想要
generate_series()
、join
和group by
。要计算涵盖的周数:编辑:
我知道你想把日子都安排好了。这是一个稍微多一点的工作:
注意:这并没有返回你得到的确切结果。我认为这些是正确的。
rseugnpd2#
考虑一下range types。使用范围运算符可以使计算更简单更清晰。我使用了下面的重叠运算符
&&
和交集运算符*
。如果表很大,可以使用函数式GiST或SP-GiST索引来支持它,以使查询更快。例如:然后,您的查询可以使用此索引:
默认情况下,
date_range
包含一个 * 包含 * 下限和一个 * 不包含 * 上限。* 您的 * 范围 * 包含 * 上限和下限,因此使用以下内容创建daterange
:daterange(start_date, end_date, '[]')
。函数upper()
仍然返回互斥上限。因此表达式upper(overlap) - lower(overlap)
正确地计算了天数。我将
generate_series()
与timestamp
输入一起使用是有原因的:相关:
OVERLAPS
运算符: