PostgreSQL如何按最小id判定会话结果并统计成功失败会话数

原SQL的问题 #

1. 语法错误：SELECT子句中同时使用COUNT(DISTINCT(session_id))和min(id)时缺少逗号分隔；
2. 字符串常量未加单引号：result = success应写成result = 'success'；
3. 逻辑错误：直接过滤result无法确保只统计每个会话**第一条记录（最小id）**的结果，会把会话中所有对应result的记录都纳入计算，不符合需求。

方法一：分组取最小id后关联统计 #

先通过分组找到每个会话的最小id，再关联原表获取该id对应的result，最后按result分组统计会话数：

SELECT 
    result,
    COUNT(session_id) AS session_count
FROM (
    SELECT session_id, MIN(id) AS min_id
    FROM logs
    GROUP BY session_id
) AS session_first_ids
JOIN logs ON logs.id = session_first_ids.min_id
GROUP BY result;

执行结果：

方法二：使用窗口函数标记第一条记录 #

用ROW_NUMBER()窗口函数给每个会话的记录按id排序，标记出第一条记录（行号为1），过滤后再统计：

SELECT 
    result,
    COUNT(session_id) AS session_count
FROM (
    SELECT 
        session_id,
        result,
        ROW_NUMBER() OVER (PARTITION BY session_id ORDER BY id ASC) AS row_num
    FROM logs
) AS ranked_logs
WHERE row_num = 1
GROUP BY result;

此方法与方法一逻辑一致，可得到相同的统计结果。

单独统计成功/失败会话数 #

如果需要分别查询两类会话的数量，可使用以下语句：

查询成功会话数 #

SELECT COUNT(*) AS success_session_count
FROM (
    SELECT session_id, MIN(id) AS min_id
    FROM logs
    GROUP BY session_id
) AS session_first_ids
JOIN logs ON logs.id = session_first_ids.min_id
WHERE logs.result = 'success';

查询失败会话数 #

SELECT COUNT(*) AS fail_session_count
FROM (
    SELECT session_id, MIN(id) AS min_id
    FROM logs
    GROUP BY session_id
) AS session_first_ids
JOIN logs ON logs.id = session_first_ids.min_id
WHERE logs.result = 'fail';

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