无需CASE语句实现按Guid统计各Event出现次数的SQL查询需求

问题描述 #

现有一张包含Guid、event、timestamp三列的表，数据如下：

Guid | event | timestamp
1236 | page-view | 1234
1236 | product-view | 1235
1236 | page-view | 1237
2025 | add-to-cart | null
2025 | purchase | null

由于event类型持续增加（目前约200种），需要编写不使用CASE语句的SQL查询，将各event转为列并统计对应Guid下的出现次数，期望输出如下：

Guid | page-view | product-view | add-to-cart | purchase 
1236 | 2 | 1 | 0 | 0 
2025 | 0 | 0 | 1 | 1 

解决方案 #

因为event类型动态变化，静态SQL无法适配，必须使用动态SQL生成查询语句，同时避免使用CASE语句。以下是主流数据库的实现方式：

1. PostgreSQL

利用PostgreSQL原生的FILTER子句替代CASE逻辑，结合动态SQL自动生成列：

-- 生成动态查询语句
WITH event_list AS (
    SELECT DISTINCT event FROM your_table_name
)
SELECT
    'SELECT Guid, ' || 
    string_agg(
        'SUM(1) FILTER (WHERE event = ''' || event || ''') AS "' || event || '"',
        ', '
    ) || 
    ' FROM your_table_name GROUP BY Guid ORDER BY Guid;'
INTO @dynamic_sql
FROM event_list;

-- 执行动态语句
EXECUTE @dynamic_sql;

2. MySQL

使用IF函数替代CASE（MySQL原生无FILTER，IF为官方支持的替代方式，未使用CASE语句），结合动态SQL拼接列：

-- 拼接列统计表达式
SET @sql = NULL;
SELECT
    GROUP_CONCAT(DISTINCT
        CONCAT(
            'SUM(IF(event = ''',
            event,
            ''', 1, 0)) AS `',
            event,
            '`'
        )
    ) INTO @sql
FROM your_table_name;

-- 生成完整查询语句
SET @sql = CONCAT('SELECT Guid, ', @sql, ' FROM your_table_name GROUP BY Guid');

-- 执行动态语句
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

3. SQL Server

使用PIVOT运算符实现转置（PIVOT无需显式编写CASE），结合动态SQL自动识别所有event列：

DECLARE @cols AS NVARCHAR(MAX),
        @query AS NVARCHAR(MAX);

-- 获取所有不重复的event列名
SELECT @cols = STUFF((SELECT ',' + QUOTENAME(event)
                    FROM your_table_name
                    GROUP BY event
                    ORDER BY event
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)'),1,1,'')

-- 生成PIVOT查询语句
SET @query = N'SELECT Guid, ' + @cols + N' 
            FROM 
            (
                SELECT Guid, event
                FROM your_table_name
            ) x
            PIVOT 
            (
                COUNT(event)
                FOR event IN (' + @cols + N')
            ) p '

-- 执行动态查询
EXEC sp_executesql @query;

注意事项 #

• 需将代码中的your_table_name替换为实际表名
• 动态SQL执行需要对应数据库的权限支持
• 新增event类型时，查询会自动适配，无需修改代码

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