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

Question

感谢麦克建议添加create/insert语句。

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:找到到现在为止连续的天数
• 返回最新“连续”数据的行

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

Answer 1

在此表上构建(不使用SQL关键字“日期”作为列名)：

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()，那么整个条纹最终都在同一组(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()。Basic row_number()完成了这项工作。