postgresql - 识别先前出现的分区值的窗口函数查询

Question

我有一个用户的每日数据，如下所示:

the_date   | status
2015-12-01 | active
2015-12-02 | active
2015-12-03 | inactive
2015-12-04 | inactive
2015-12-05 | inactive
2015-12-06 | active
2015-12-07 | active

我想添加一个新列 days_in_current_status 来汇总此用户处于当前状态的天数，但是将两个独立的“事件”链视为两个不同的状态，例如结果看起来像这样:

the_date   | status   | days_in_current_status
2015-12-01 | active   | 1
2015-12-02 | active   | 2
2015-12-03 | inactive | 1
2015-12-04 | inactive | 2
2015-12-05 | inactive | 3
2015-12-06 | active   | 1
2015-12-07 | active   | 2

我该怎么做？

SELECT ROW_NUMBER() OVER (PARTITION BY status ORDER BY the_date) 对我来说不够，因为它将 2015-12-06 行标记为 4，将其下方标记为 5。它'如果我可以添加一个将最后 2 个 status 值重述为“active2”的列，d 工作。

Answer 1

如果您确实有每日值，那么您可以使用递归 CTE:

WITH RECURSIVE stat (the_date, status, days_in_current_status) AS (
  SELECT min(the_date), status, 1
  FROM mytable
  GROUP BY status
  UNION
  SELECT t.the_date, t.status,
         CASE WHEN t.status = s.status THEN s.days_in_current_status + 1 ELSE 1 END
  FROM mytable t
  JOIN stat s ON s.the_date + 1 = t.the_date
)
SELECT * FROM stat
ORDER BY the_date;

结果:

psql (9.5.0)
Type "help" for help.

test=# WITH RECURSIVE stat (the_date, status, days_in_current_status) AS (
test(#   SELECT min(the_date), status, 1
test(#   FROM mytable
test(#   GROUP BY status
test(#   UNION
test(#   SELECT t.the_date, t.status,
test(#          CASE WHEN t.status = s.status THEN s.days_in_current_status + 1 ELSE 1 END
test(#   FROM mytable t
test(#   JOIN stat s ON s.the_date + 1 = t.the_date
test(# )
test-# SELECT * FROM stat
test-# ORDER BY the_date;
  the_date  |  status  | days_in_current_status
------------+----------+------------------------
 2015-12-01 | active   |                      1
 2015-12-02 | active   |                      2
 2015-12-03 | inactive |                      1
 2015-12-04 | inactive |                      2
 2015-12-05 | inactive |                      3
 2015-12-06 | active   |                      1
 2015-12-07 | active   |                      2
(7 rows)

如果您的日期有间隔，您可以在感兴趣的日期范围内执行 generate_series()