分组查询中仅统计唯一组合首次出现的计数实现方案
实现方案
你可以先给每一组(user_id, event_id, message_id)标记首次出现的日期,统计时仅当当前分组日期和该组合首次日期一致时,才计入唯一计数。
以下是支持窗口函数的主流SQL(MySQL8.0+/PostgreSQL/Spark SQL/Hive等)通用写法:
WITH message_first_log AS ( SELECT *, -- 计算每个(user_id,event_id,message_id)组合第一次出现的日期 MIN(date) OVER (PARTITION BY user_id, event_id, message_id) AS first_appear_date FROM events ) SELECT date, user_id, event_id, COUNT(*) AS count, -- 仅首次出现的message_id计入统计,非首次的返回NULL会被COUNT自动忽略 COUNT(DISTINCT IF(date = first_appear_date, message_id, NULL)) AS count_unique FROM message_first_log GROUP BY user_id, event_id, date ORDER BY date, user_id, event_id;
如果使用不支持CTE的版本,可以替换为子查询写法:
SELECT date, user_id, event_id, COUNT(*) AS count, COUNT(DISTINCT IF(date = first_appear_date, message_id, NULL)) AS count_unique FROM ( SELECT *, MIN(date) OVER (PARTITION BY user_id, event_id, message_id) AS first_appear_date FROM events ) t GROUP BY user_id, event_id, date ORDER BY date, user_id, event_id;
如果使用不支持窗口函数的极低版本SQL,可以用关联子查询实现:
SELECT e.date, e.user_id, e.event_id, COUNT(*) AS count, COUNT(DISTINCT IF(e.date = m.first_date, e.message_id, NULL)) AS count_unique FROM events e LEFT JOIN ( SELECT user_id, event_id, message_id, MIN(date) AS first_date FROM events GROUP BY user_id, event_id, message_id ) m ON e.user_id = m.user_id AND e.event_id = m.event_id AND e.message_id = m.message_id GROUP BY e.user_id, e.event_id, e.date ORDER BY e.date, e.user_id, e.event_id;
以上写法运行后得到的结果和你预期的完全一致。
内容的提问来源于stack exchange,提问作者ozahorulia
相关产品推荐
相关产品推荐

