如何用SQL统计首次出现-1后Status_measure>-1的次数并生成新列

需求回顾 #

针对每个Name，新增Value列，值为首次出现Status_measure=-1之后，Status_measure大于-1的记录总数，且所有行的Value都显示该总数。

原语句问题分析 #

你使用的count(status_measure>-1) over (partition by name order by seconds)是累计窗口计数，会从当前分区的第一行开始，统计到当前行为止符合status_measure>-1的行数，因此每行的Value会逐步递增，无法得到首次-1之后的全局统计数。

方案一：子查询+CTE #

WITH first_neg_time AS (
    -- 获取每个Name首次出现-1的Seconds
    SELECT 
        Name,
        MIN(Seconds) AS first_neg_seconds
    FROM your_table
    WHERE Status_measure = -1
    GROUP BY Name
)
SELECT
    -- 生成自增Id
    ROW_NUMBER() OVER (PARTITION BY t.Name ORDER BY t.Seconds) AS Id,
    t.Name,
    t.Seconds,
    t.Status_measure,
    -- 统计首次-1之后Status_measure>-1的总数
    (SELECT COUNT(*) 
     FROM your_table t2
     WHERE t2.Name = t.Name
       AND t2.Seconds > fnt.first_neg_seconds
       AND t2.Status_measure > -1) AS Value
FROM your_table t
JOIN first_neg_time fnt ON t.Name = fnt.Name;

方案二：纯窗口函数实现 #

SELECT
    ROW_NUMBER() OVER (PARTITION BY Name ORDER BY Seconds) AS Id,
    Name,
    Seconds,
    Status_measure,
    -- 统计当前Name下符合条件的总行数
    SUM(CASE 
        WHEN Seconds > first_neg_seconds AND Status_measure > -1 THEN 1 
        ELSE 0 
    END) OVER (PARTITION BY Name) AS Value
FROM (
    -- 标记每个Name首次出现-1的时间点
    SELECT
        *,
        MIN(CASE WHEN Status_measure = -1 THEN Seconds END) OVER (PARTITION BY Name) AS first_neg_seconds
    FROM your_table
) t;

说明 #

两种方案逻辑一致：

1. 先定位每个Name首次出现Status_measure=-1的时间点
2. 基于该时间点，统计后续所有Status_measure>-1的记录数
3. 将统计数作为当前Name所有行的Value值

内容的提问来源于stack exchange，提问作者Chinti