sql - 如何在连续几天的 'streak' 中向行添加运行计数

Question

感谢Mike获取添加创建/插入语句的建议。

create table test (
  pid integer not null,
  date date not null,
  primary key (pid, date)
);

insert into test values
  (1,'2014-10-1')
, (1,'2014-10-2')
, (1,'2014-10-3')
, (1,'2014-10-5')
, (1,'2014-10-7')
, (2,'2014-10-1')
, (2,'2014-10-2')
, (2,'2014-10-3')
, (2,'2014-10-5')
, (2,'2014-10-7');

我想添加一个新列，即“当前连续天数”所以结果看起来像:

pid    | date      | in_streak
-------|-----------|----------
1      | 2014-10-1 | 1
1      | 2014-10-2 | 2
1      | 2014-10-3 | 3
1      | 2014-10-5 | 1
1      | 2014-10-7 | 1
2      | 2014-10-2 | 1
2      | 2014-10-3 | 2
2      | 2014-10-4 | 3
2      | 2014-10-6 | 1

我一直在尝试使用来自

的答案

• PostgreSQL: find number of consecutive days up until now
• Return rows of the latest 'streak' of data

但我不知道如何将 dense_rank() 技巧与其他窗口函数一起使用以获得正确的结果。

Answer 1

在此表上构建(不使用 SQL keyword "date" 作为列名。):

CREATE TABLE tbl(
  pid int
, the_date date
, PRIMARY KEY (pid, the_date)
);

查询:

SELECT pid, the_date
     , row_number() OVER (PARTITION BY pid, grp ORDER BY the_date) AS in_streak
FROM  (
   SELECT *
        , the_date - '2000-01-01'::date
        - row_number() OVER (PARTITION BY pid ORDER BY the_date) AS grp
   FROM   tbl
) sub
ORDER  BY pid, the_date;

从另一个 date 中减去一个 date 得到一个 integer。由于您正在寻找连续的天数，因此接下来的每一行都会比 一个 大。如果我们从中减去 row_number()，则整个连胜将在每个 pid 的同一组 (grp) 中结束。然后很容易分配每组的数量。

grp是用两次减法计算的，应该是最快的。一个同样快速的替代方案可能是:

the_date - row_number() OVER (PARTITION BY pid ORDER BY the_date) * interval '1d' AS grp

一次乘法，一次减法。字符串连接和类型转换更昂贵。使用 EXPLAIN ANALYZE 进行测试。

不要忘记在两个步骤中另外按pid 进行分区，否则您会不经意地混合应该分开的组。

使用子查询，因为它通常比 CTE 快.这里没有什么是普通子查询做不到的。

既然你提到了它:dense_rank() 显然 不是 这里是必需的。基本row_number()完成工作。