如何精简分类统计SQL并修正条件聚合查询结果错误

你的合并写法结果不对，核心是两处逻辑偏差：

• 给时长求和的判断多加了vpno IS NOT NULL条件。原查询里SUM(spent_time)只要求item_category匹配、pid非空，和vpno是否为空无关，所有符合条件的行的时长都要计入总和；只有COUNT(vpno)本身会自动跳过vpno为空的行。你把vpno非空的判断加进SUM的条件里，直接漏算了vpno为空的有效行的时长数据。
• 只写了A类的聚合逻辑，未补全B类的统计规则，自然拿不到和原两段查询一致的全量结果。

下面的写法只需要执行一次窗口计算，无需重复扫描表，返回结果和你原来两段独立查询完全等价，且兼容绝大多数SQL引擎：

SELECT 
    id,
    -- 统计A类指标
    SUM(CASE WHEN item_category = 'A' AND pid IS NOT NULL THEN spent_time END) AS spent_time_01,
    COUNT(CASE WHEN item_category = 'A' AND pid IS NOT NULL THEN vpno END) AS spend_time_cnt_01,
    -- 统计B类指标
    SUM(CASE WHEN item_category = 'B' AND pid IS NOT NULL THEN spent_time END) AS spent_time_02,
    COUNT(CASE WHEN item_category = 'B' AND pid IS NOT NULL THEN vpno END) AS spend_time_cnt_02
FROM (
    SELECT 
        *,
        LEAD(create_ts) OVER (PARTITION BY id ORDER BY CAST(vpno AS INT)) AS lead_ts,
        DATEDIFF('second', create_ts::timestamp, lead_ts::timestamp) AS spent_time
    FROM table_1
) t
GROUP BY id
-- 如果只需要同时存在A、B两类数据的id，放开下面HAVING的注释即可
-- HAVING spend_time_cnt_01 > 0 AND spend_time_cnt_02 > 0
;

原写法中直接用关键字LEAD作为列别名容易触发语法错误，这里调整为lead_ts，计算逻辑完全不变。

• 内层子查询和你原查询的窗口计算逻辑完全一致，没有提前加过滤条件，保证LEAD函数的分区、排序规则和原逻辑无偏差，不会出现相邻行匹配错误的问题。
• 条件聚合严格对齐原查询的过滤规则：SUM部分仅判断分类和pid非空条件，和原查询WHERE逻辑完全匹配；COUNT部分仅在符合分类、pid非空条件时传入vpno字段，自动忽略vpno为空的行，和原查询COUNT(vpno)的计算规则完全一致。
• 如果你使用的引擎支持COUNT_IF函数，也可以将COUNT部分替换为COUNT_IF(item_category = 'A' AND pid IS NOT NULL AND vpno IS NOT NULL) AS spend_time_cnt_01的写法，注意COUNT_IF是统计满足条件的行数，必须额外加上vpno非空判断才能和原逻辑对齐。
• 不加HAVING子句时，没有对应分类数据的id会在对应分类的指标字段返回NULL，和你将两个原查询结果按id全连接的表现完全一致。

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