MySQL 5.5.34按周获取每日TOP2租户通话汇总数据方法

你需要按日期（对应每周的每一天）统计各租户的通话总量，并筛选出每日TOP2的租户。原查询已经完成了每日租户的求和，但缺少排名筛选逻辑，下面提供两种适配不同场景的实现方案：

方法1：使用窗口函数（MySQL 8.0及以上版本） #

这是最简洁高效的实现方式，推荐优先使用：

WITH daily_tenant_totals AS (
    SELECT
        TENANT_NAME,
        SUM(MAX_CALLS) AS total_calls,
        DATE(TIME_STAMP) AS stat_date,
        DAYNAME(TIME_STAMP) AS day_name
    FROM TENANT_LIC_DISTRIBUTION
    GROUP BY TENANT_NAME, DATE(TIME_STAMP), DAYNAME(TIME_STAMP)
),
ranked_tenants AS (
    SELECT
        *,
        -- 若要保留并列TOP2的租户，将ROW_NUMBER()替换为RANK()
        ROW_NUMBER() OVER (PARTITION BY stat_date ORDER BY total_calls DESC) AS tenant_rank
    FROM daily_tenant_totals
)
SELECT
    TENANT_NAME,
    total_calls,
    stat_date,
    day_name
FROM ranked_tenants
WHERE tenant_rank <= 2
ORDER BY stat_date, tenant_rank;

关键说明： #

• daily_tenant_totals 计算每个租户在完整日期（而非仅天数）的总通话量，避免跨年同一天的数据混淆
• ROW_NUMBER() 会给每个日期内的租户按通话量降序生成唯一排名，若存在并列第二的情况只会保留一条；如果需要显示所有并列的TOP2租户，换成RANK()即可
• 最终筛选排名≤2的记录，按日期和排名排序展示

方法2：适配MySQL 5.x版本（无窗口函数支持） #

如果你的MySQL版本低于8.0，无法使用窗口函数，可以用关联子查询实现：

SELECT
    t1.TENANT_NAME,
    t1.total_calls,
    t1.stat_date,
    t1.day_name
FROM (
    SELECT
        TENANT_NAME,
        SUM(MAX_CALLS) AS total_calls,
        DATE(TIME_STAMP) AS stat_date,
        DAYNAME(TIME_STAMP) AS day_name
    FROM TENANT_LIC_DISTRIBUTION
    GROUP BY TENANT_NAME, DATE(TIME_STAMP), DAYNAME(TIME_STAMP)
) t1
WHERE (
    SELECT COUNT(*)
    FROM (
        SELECT
            SUM(MAX_CALLS) AS total_calls,
            DATE(TIME_STAMP) AS stat_date
        FROM TENANT_LIC_DISTRIBUTION
        GROUP BY TENANT_NAME, DATE(TIME_STAMP)
    ) t2
    WHERE t2.stat_date = t1.stat_date AND t2.total_calls >= t1.total_calls
) <= 2
ORDER BY t1.stat_date, t1.total_calls DESC;

关键说明： #

• 子查询t1先计算每日各租户的总通话量
• 内层关联子查询统计同一日期内，通话量大于等于当前租户的记录数，这个数值就是当前租户的排名
• 筛选排名≤2的记录，实现TOP2效果

重要提示 #

• 原查询中用day(TIME_STAMP)分组存在逻辑漏洞：仅提取日期中的天数（如7），会导致不同年份的12月7号被合并到同一组，必须改用DATE(TIME_STAMP)获取完整日期（如2022-12-07）来确保分组准确性
• 如果需要限定统计某一周的数据，可在所有查询的FROM子句后添加WHERE TIME_STAMP BETWEEN '开始日期' AND '结束日期'，比如WHERE TIME_STAMP BETWEEN '2022-12-05' AND '2022-12-11'

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