编写SQL查询实现用户-日期计数矩阵转换的技术问询

前提说明 #

我们假设原始数据表名为user_daily_active，包含两个字段：

• user_name：用户名，对应示例中的User1、User2、User3
• active_day：活跃日期，对应示例中的Monday、Tuesday

需求实现SQL #

MySQL 兼容写法 #

用条件聚合逻辑完成行转列，无活跃记录的日期默认返回0，有活跃记录返回对应活跃次数：

SELECT 
  user_name AS 用户,
  SUM(CASE WHEN active_day = 'Monday' THEN 1 ELSE 0 END) AS Monday,
  SUM(CASE WHEN active_day = 'Tuesday' THEN 1 ELSE 0 END) AS Tuesday
FROM user_daily_active
GROUP BY user_name
ORDER BY user_name;

PostgreSQL 精简写法 #

PostgreSQL支持FILTER语法，可以简化条件聚合的写法：

SELECT 
  user_name AS 用户,
  COUNT(*) FILTER (WHERE active_day = 'Monday') AS Monday,
  COUNT(*) FILTER (WHERE active_day = 'Tuesday') AS Tuesday
FROM user_daily_active
GROUP BY user_name
ORDER BY user_name;

输出结果说明 #

执行上述SQL后得到的结果和预期完全一致：

如果需要不管用户单日访问多少次都只算1次活跃，只需要把SQL中的SUM替换为MAX即可。

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