sql - 如何计算某个值之前有多少行？

Question

如果我有下表:

CREATE TABLE test (
       c_id INTEGER,
       ref_date DATE,
       success BOOLEAN
);

INSERT INTO test
VALUES
(8, '2016-01-01', FALSE),
(8, '2016-01-04', TRUE),
(8, '2016-01-07', FALSE),
(8, '2016-01-09', FALSE),
(8, '2016-01-15', TRUE),
(9, '2016-01-05', TRUE)
;

我想构建一个返回以下内容的查询:

8, '2016-01-01', FALSE,
8, '2016-01-04', TRUE, 2
8, '2016-01-07', FALSE,
8, '2016-01-09', FALSE,
8, '2016-01-15', TRUE, 3
9, '2016-01-05', TRUE, 1

我想做的是，对于每个 c_id，按日期排序，然后为成功列中的每个 TRUE 值计算自该 c_id 的第一行或最后一个 TRUE 值以来的行数。因此，在示例输出中，第二行有一个二，因为当对 ref_dates 进行排序时，第一个 TRUE 值是自该 c_id 的组开始以来的第二行。倒数第二行有一个 3，因为它是 TRUE，第三行是自最后一个 TRUE 值以来。

我正在计算获得成功推荐需要多少次推荐。

我最初认为我可以使用 lag() 窗口函数构建一个查询，但是没有办法将前面的绑定(bind)设置为成功属性值为 TRUE 的最后一行。

如何计算某个值之前有多少行？

我正在运行 PostgreSQL 9.5。

Answer 1

我觉得这可以进一步简化，但是这个查询可以使用:

• lag 标记作为不同逻辑分组开始的行
• 累积 sum 用唯一的分组 id 标记同一逻辑分组的所有行
• count 按逻辑分组 id 划分

查询:

with grp_start_cte as (
  select *,
         coalesce(lag(success) over (partition by c_id order by ref_date), true) as is_grp_start
    from test
), grp_cte as (
  select *,
         sum(case when is_grp_start then 1 else 0 end) over (partition by c_id order by ref_date) as grp_id
    from grp_start_cte
)
select c_id, ref_date, success,
       case when success then count(*) over (partition by c_id, grp_id) end as cnt
  from grp_cte
 order by c_id, ref_date