Postgresql:创建一个日期序列，在日期范围查询中使用它

Question

我不太擅长 SQL，但到目前为止，我在一个项目上取得了很好的进展。现在我完全卡住了。

我正在尝试计算具有每种状态的公寓数量。我每天都需要这些信息，以便我可以随着时间的推移对它进行趋势分析。我有这样的数据:

table: y_unit_status

unit | date_occurred | start_date | end_date   | status
1    | 2017-01-01    | 2017-01-01 | 2017-01-05 | Occupied No Notice
1    | 2017-01-06    | 2017-01-06 | 2017-01-31 | Occupied Notice
1    | 2017-02-01    | 2017-02-01 |            | Vacant
2    | 2017-01-01    | 2017-01-01 |            | Occupied No Notice

我想得到如下所示的输出:

date       | occupied_no_notice | occupied_notice | vacant
2017-01-01 | 2                  | 0               | 0
...
2017-01-10 | 1                  | 1               | 0
...
2017-02-01 | 1                  | 0               | 1

或者，这种方法可行:

date       | status             | count
2017-01-01 | occupied no notice | 2
2017-01-01 | occupied notice    | 0

date_occurred:单位状态改变的日期开始日期:与发生日期相同end_date: status 停止为 x 变为 y 的日期。

我正在提取卧室数量和特性 ID，因此与选项 1(如果重要的话)相比，第二种方法一次选择一种状态的计数会产生相对较多的行数。

我找到了很多引用资料，这些引用资料使我接近了我正在寻找的东西，但我总是以一种滚动的、累积的计数结束。

这是我的查询，它生成一列日期和计数，它们会随着时间的推移而累积，而不是反射(reflect)特定日期的计数快照。您可以看到我对另一个表的引用，我在其中提取了一个属性 ID。表架构是属性 -> 单位 -> 单位状态。

WITH t AS(
    SELECT i::date from generate_series('2016-06-29', '2017-08-03', '1 day'::interval) i
    )

SELECT t.i as date,
u.hproperty,
count(us.hmy) as count --us.hmy is the id
FROM t
LEFT OUTER JOIN y_unit_status us ON t.i BETWEEN us.dtstart AND 
us.dtend
INNER JOIN y_unit u ON u.hmy = us.hunit -- to get property id
WHERE us.sstatus = 'Occupied No Notice'
AND t.i >= us.dtstart
AND t.i <= us.dtend
AND u.hproperty = '1'
GROUP BY t.i, u.hproperty
ORDER BY t.i
limit 1500

我还尝试了一个 FOR 循环，遍历日期以确定日期介于开始和结束之间但我的逻辑不起作用的情况。感谢您的任何见解!

Answer 1

您走在正确的轨道上，但您需要处理 NULL end_date 中的值.如果那些意味着status假设在未来某处发生变化(但不确定何时会发生变化)，containment operators ( @> and <@ )对于 daterange type非常适合您(因为范围可以是“无界的”):

with params as (
  select date '2017-01-01' date_from,
         date '2017-02-02' date_to
)
select     date_from + d, status, count(unit)
from       params
cross join generate_series(0, date_to - date_from) d
left join  y_unit_status on daterange(start_date, end_date, '[]') @> date_from + d
group by   1, 2

要实现第一个变体，可以使用条件聚合:

with params as (
  select date '2017-01-01' date_from,
         date '2017-02-02' date_to
)
select     date_from + d,
           count(unit) filter (where status = 'Occupied No Notice') occupied_no_notice,
           count(unit) filter (where status = 'Occupied Notice') occupied_notice,
           count(unit) filter (where status = 'Vacant') vacant
from       params
cross join generate_series(0, date_to - date_from) d
left join  y_unit_status on daterange(start_date, end_date, '[]') @> date_from + d
group by   1

注释:

• syntax filter (where <predicate>) 是 9.4+ 的新功能。在此之前，您可以使用 CASE (以及大多数聚合函数不包含 NULL 值的事实)来模拟它。
• 您甚至可以索引表达式 daterange(start_date, end_date, '[]') (使用 gist )以获得更好的性能。

http://rextester.com/HWKDE34743