Postgres/Redshift按pid统计最近5行状态为3的记录数

嘿，我来帮你搞定这个统计需求～ 你原来的SQL问题出在ORDER BY id desc LIMIT 5的作用范围上——它是对整个分组后的结果集取前5条，而不是给每个pid分组单独取最近5行，这肯定得不到你想要的结果。

正确的解法思路 #

我们需要先给每个pid下的记录按id降序分配行号，筛选出每个分组内行号≤5的记录（也就是最近5条），再对这些记录进行统计。这里用窗口函数ROW_NUMBER()就能轻松实现：

WITH ranked_data AS (
    SELECT 
        pid, 
        status,
        -- 按pid分组，每个组内按id降序排，给行编号
        ROW_NUMBER() OVER (PARTITION BY pid ORDER BY id DESC) AS rn
    FROM A
)
SELECT 
    pid,
    -- 统计status=3的记录数，没有则返回0
    COUNT(CASE WHEN status = 3 THEN 1 END) AS is_3
FROM ranked_data
-- 只保留每个pid的最近5行
WHERE rn <= 5
GROUP BY pid
ORDER BY pid;

结果验证 #

运行这段SQL后，会得到你期望的结果：

• 对于pid=x，最近5行是id7、2、1，其中只有id1的status=3，所以统计数为1；
• 对于pid=y，最近5行是id6、4、3，没有status=3的记录，统计数为0；
• 对于pid=z，最近5行是id8、5，两者status都是3，统计数为2。

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