如何修改SQL查询以计算每日所有id的upload操作平均次数

云原生阿岚

2026-10-3

解决方法

实现思路

要得到目标结果需要两次聚合计算：

第一次聚合：按day和id分组，统计每个用户每日的upload操作次数
第二次聚合：按day分组，对当日所有用户的操作次数求平均值

调整后的SQL语句

CTE写法（适配支持通用表表达式的SQL环境）

WITH user_daily_action AS (
    -- 统计每个id每天的upload操作次数
    SELECT 
        id,
        date_diff("day", create_date, date) as day,
        COUNT(*) as action_count
    FROM "my_database"
    WHERE action_type = 'upload' -- 过滤仅保留upload类型操作
    GROUP BY id, date_diff("day", create_date, date)
)
-- 按天计算所有用户操作次数的平均值
SELECT 
    day,
    AVG(action_count) as avg_num_action
FROM user_daily_action
GROUP BY day
ORDER BY day;

子查询写法（兼容所有SQL环境）

SELECT 
    day,
    AVG(action_count) as avg_num_action
FROM (
    SELECT 
        id,
        date_diff("day", create_date, date) as day,
        COUNT(*) as action_count
    FROM "my_database"
    WHERE action_type = 'upload'
    GROUP BY id, date_diff("day", create_date, date)
) t
GROUP BY day
ORDER BY day;