SQLite中转置查询结果按性别统计event_type列数值的实现方法

SQLite 3.39.0以下版本不支持LATERAL JOIN与PostgreSQL风格的FILTER聚合语法，可使用UNION ALL列转行+条件聚合的通用方案实现需求，兼容所有SQLite版本：

SELECT 
    event_name,
    SUM(CASE WHEN sex = 'm' THEN 1 ELSE 0 END) AS m,
    SUM(CASE WHEN sex = 'f' THEN 1 ELSE 0 END) AS f
FROM (
    SELECT 'event_type_1' AS event_name, sex, event_type_1 AS val FROM d
    UNION ALL
    SELECT 'event_type_2' AS event_name, sex, event_type_2 AS val FROM d
) t
WHERE val = 1
GROUP BY event_name;

上述逻辑和你之前PostgreSQL版本的实现逻辑完全等价：内层通过UNION ALL将原表的两个event列拆分为两行数据，每行对应一个事件类型的取值与关联的性别字段，过滤掉事件未触发（val=0）的记录后，外层通过条件聚合统计不同性别下各事件的触发次数。

如果你使用的是SQLite 3.39.0及以上版本，SQLite已原生支持FILTER语法，可以简化聚合部分的写法：

SELECT 
    event_name,
    COUNT(*) FILTER (WHERE sex = 'm') AS m,
    COUNT(*) FILTER (WHERE sex = 'f') AS f
FROM (
    SELECT 'event_type_1' AS event_name, sex, event_type_1 AS val FROM d
    UNION ALL
    SELECT 'event_type_2' AS event_name, sex, event_type_2 AS val FROM d
) t
WHERE val = 1
GROUP BY event_name;

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