SQL查询如何按日期间隔差值分组并聚合统计对应总数量

你当前的查询是按t2.action_date和t1.CATEGORY_ID两个字段分组，所以输出的是每个分组维度对应的统计值。要实现按diff_date聚合汇总，只需要在外层套一层聚合查询即可，同时注意修正你原SQL里的两处语法问题：

• 缺失FROM t1声明，查询没有指定主表
• 关联product表时使用了未定义的别名prod，需要统一别名

修改后的查询语句 #

SELECT SUM(count) AS Count, diff_date
FROM (
    -- 内层为你原有的分组逻辑，先得到每个action_date+分类维度的统计结果
    SELECT 
        COUNT(t1.customerID) AS count, 
        DATEDIFF(day, t2.action_date, MIN(t1.ordered_date)) AS diff_date 
    FROM t1
    INNER JOIN t2
        ON t1.EMAIL = t2.EMAIL 
        AND t1.CATEGORY_ID = t2.CATEGORY_ID  
    INNER JOIN product
        ON product.CATEGORY_ID = t1.CATEGORY_ID
    WHERE t2.action_date <= t1.ordered_date
    GROUP BY t2.action_date, t1.CATEGORY_ID
) AS group_result
-- 外层按日期差分组求和
GROUP BY diff_date
ORDER BY diff_date ASC;

逻辑说明 #

1. 内层查询保留你原本的业务逻辑，计算出每一组「操作日期+品类ID」对应的客户数、两个日期的差值
2. 外层查询将内层的结果作为临时表，按diff_date分组，对客户数执行求和汇总，就能得到你预期的按日期差聚合的统计结果

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