SQL如何基于记录创建时间将is_registered值按小时桶分组统计总量

核心逻辑 #

• 从creation_date字段提取独立的日期维度作为第一分组依据
• 提取creation_date的小时值，拼接为要求的xx:00-xx+1:00格式时间桶作为第二分组依据
• 对is_registered字段聚合计算得到每个分组的总量

MySQL语法示例 #

SELECT
    DATE(creation_date) AS 统计日期,
    CONCAT(LPAD(HOUR(creation_date), 2, '0'), ':00-', LPAD(HOUR(creation_date)+1, 2, '0'), ':00') AS 小时时间桶,
    SUM(is_registered) AS 统计总量
FROM 
    替换为你的表名
GROUP BY
    统计日期,
    HOUR(creation_date)
ORDER BY
    统计日期 ASC,
    HOUR(creation_date) ASC;

其他数据库适配修改点 #

• PostgreSQL：将DATE(creation_date)改为creation_date::date，HOUR(creation_date)改为EXTRACT(HOUR FROM creation_date)
• Hive/Spark SQL：将DATE(creation_date)改为to_date(creation_date)，HOUR(creation_date)改为hour(creation_date)

注意：如果is_registered是布尔类型可直接求和，若需要统计该时间桶内所有记录总量（不区分是否注册），将聚合函数替换为COUNT(*)即可
若需要补全无数据的空时间桶，需要先生成连续的时间维度序列，再左关联原统计结果即可

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