You need to enable JavaScript to run this app. 分组查询中仅统计唯一组合首次出现的计数实现方案
优惠活动
大模型
产品
解决方案
定价
更多

分组查询中仅统计唯一组合首次出现的计数实现方案

实现方案

你可以先给每一组(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

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.10.09 19:06:03