PostgreSQL查询：统计各组件最新连续'?'扫描结果数量

我不是数据库专家，现有一个大型PostgreSQL表，表结构及示例数据如下：

CREATE TABLE t
    (ID int, componentid varchar(30), scanresult varchar(30), eventdatetime timestamp)
;
    
INSERT INTO t
    (ID, componentid, scanresult, eventdatetime)
VALUES
    (1, 'A', '123', '2018-02-20 00:00:00'),
    (2, 'A', '345','2018-02-20 00:00:01'),
    (3, 'A', '?','2018-02-20 00:00:02'),
    (4, 'A', '54','2018-02-21 00:00:01'),
    (5, 'A', '?','2018-02-21 00:00:02'),
    (6, 'B', '?','2018-02-21 00:00:03'),
    (7, 'B', '457645','2018-02-22 00:00:01'),
    (8, 'B', '?','2018-02-22 00:00:02'),
    (9, 'B', '56465','2018-02-22 00:00:03'),
    (10, 'C', '?','2018-02-23 00:00:01'),
    (11, 'C', '234234','2018-02-21 00:00:03'),
    (12, 'C', '33','2018-02-22 00:00:01'),
    (13, 'C', '55','2018-02-22 00:00:02'),
    (14, 'C', '?','2018-02-22 00:00:03'),
    (15, 'C', '?','2018-02-23 00:00:01'),
    (16, 'C', '?','2018-02-23 00:00:01'),
    (17, 'C', '?','2018-02-23 00:00:02'),
    (18, 'C', '?','2018-02-23 00:00:03');
;

需求 #

按componentid分组统计scanresult为'?'的记录数量，但仅统计最新的连续出现的'?'。预期结果：

• 组件A：1，因为最后一条扫描结果为'?'
• 组件B：0，因为最后一条扫描结果不是'?'
• 组件C：5，因为最后5条扫描结果连续为'?'（此前的'?'后有正常结果，不纳入统计）

解决方案 #

利用PostgreSQL窗口函数实现，核心逻辑是先按组件分组并按时间倒序排列，标记非'?'记录后通过累计分组区分最后一段连续的'?'，最终统计这段的数量：

WITH ranked_data AS (
    SELECT
        componentid,
        scanresult,
        -- 按组件分组，时间倒序排序
        ROW_NUMBER() OVER (PARTITION BY componentid ORDER BY eventdatetime DESC) AS rn,
        -- 标记非'?'的记录，用于后续分组
        CASE WHEN scanresult != '?' THEN 1 ELSE 0 END AS is_non_question
    FROM t
),
grouped_data AS (
    SELECT
        componentid,
        scanresult,
        -- 累计求和，非'?'的记录会触发分组变化
        SUM(is_non_question) OVER (PARTITION BY componentid ORDER BY rn) AS grp
    FROM ranked_data
)
SELECT
    componentid AS "组件",
    -- 只统计grp=0的记录（即最后连续的'?'），没有则为0
    COUNT(CASE WHEN grp = 0 AND scanresult = '?' THEN 1 END) AS "最新连续'?'数量"
FROM grouped_data
GROUP BY componentid
ORDER BY componentid;

结果说明 #

• 组件A：倒序后第一条记录是'?'，没有触发分组变化，grp=0，统计数量为1
• 组件B：倒序后第一条是正常结果，grp从1开始，没有grp=0的'?'记录，数量为0
• 组件C：倒序后前5条都是'?'，直到第6条出现正常结果才触发分组变化，前5条grp=0，统计数量为5

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