如何在连续几天的“连续变化”中向行添加运行计数

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

但我不知道如何将dense_rank()技巧与其他窗口功能结合使用以获取正确的结果。

Answer 1

在此表上构建（不使用SQL关键字“ 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从另一个减去adate得到一个integer。由于您一直在寻找连续的日子，因此下一行将增加 一
。如果row_number()从中减去，则整个条纹最终将归入同一组（grp）pid。然后，很容易按组分配数字。

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()完成工作。