请求重写SQL语句：按周六作为周末统计呼叫总量并指定列名

要实现以周六作为周末节点分组统计呼叫总量，核心是把每个呼叫日期映射到对应的周末（周六），再按周末日期和坐席ID分组求和。以下是针对主流数据库的具体实现方案：

核心思路 #

1. 将dIntervalStart转换为日期类型后，计算出该日期所属周的周六（即周末结束日期）。
2. 按坐席ID（cName）和计算出的周末日期分组，统计nInternToExternCalls的总和。
3. 保留原查询的日期范围过滤，并指定要求的列名。另外原SQL中的DISTINCT是多余的，GROUP BY已经会自动完成分组去重，所以可以去掉。

MySQL 实现 #

SELECT
    cName AS "Agent ID",
    -- 计算当前日期对应的周六：WEEKDAY返回0=周一，5=周六
    DATE_ADD(CAST(dIntervalStart AS DATE), INTERVAL (5 - WEEKDAY(CAST(dIntervalStart AS DATE))) DAY) AS "Week-Ending",
    SUM(nInternToExternCalls) AS "Total Calls"
FROM QueueStats
WHERE CAST(dIntervalStart AS DATE) BETWEEN '2019-12-29' AND '2020-12-26'
GROUP BY cName, `Week-Ending`
ORDER BY cName, `Week-Ending`;

说明： #

WEEKDAY(日期)返回0（周一）到6（周日），用5 - WEEKDAY(...)得到当前日期到周六的间隔天数，叠加后就能得到本周六的日期。如果当前日期本身是周六，计算结果就是它自己，符合分组要求。

PostgreSQL 实现 #

SELECT
    cName AS "Agent ID",
    -- 计算当前日期对应的周六：EXTRACT(DOW FROM 日期)返回0=周日，6=周六
    (CAST(dIntervalStart AS DATE) + 
        CASE EXTRACT(DOW FROM CAST(dIntervalStart AS DATE))
            WHEN 0 THEN INTERVAL '6 days'  -- 周日到周六需加6天
            WHEN 1 THEN INTERVAL '5 days'  -- 周一到周六需加5天
            WHEN 2 THEN INTERVAL '4 days'
            WHEN 3 THEN INTERVAL '3 days'
            WHEN 4 THEN INTERVAL '2 days'
            WHEN 5 THEN INTERVAL '1 day'
            WHEN 6 THEN INTERVAL '0 days'  -- 本身是周六，无需调整
        END
    )::DATE AS "Week-Ending",
    SUM(nInternToExternCalls) AS "Total Calls"
FROM QueueStats
WHERE CAST(dIntervalStart AS DATE) BETWEEN '2019-12-29' AND '2020-12-26'
GROUP BY cName, "Week-Ending"
ORDER BY cName, "Week-Ending";

SQL Server 实现 #

SELECT
    cName AS "Agent ID",
    -- 计算当前日期对应的周六：DATEPART(WEEKDAY, 日期)默认1=周日，7=周六
    DATEADD(DAY, 
        CASE DATEPART(WEEKDAY, CAST(dIntervalStart AS DATE))
            WHEN 1 THEN 6  -- 周日→周六：+6天
            WHEN 2 THEN 5  -- 周一→周六：+5天
            WHEN 3 THEN 4
            WHEN 4 THEN 3
            WHEN 5 THEN 2
            WHEN 6 THEN 1
            WHEN 7 THEN 0  -- 本身是周六，无需调整
        END, 
        CAST(dIntervalStart AS DATE)
    ) AS "Week-Ending",
    SUM(nInternToExternCalls) AS "Total Calls"
FROM QueueStats
WHERE CAST(dIntervalStart AS DATE) BETWEEN '2019-12-29' AND '2020-12-26'
GROUP BY cName, 
         DATEADD(DAY, 
            CASE DATEPART(WEEKDAY, CAST(dIntervalStart AS DATE))
                WHEN 1 THEN 6
                WHEN 2 THEN 5
                WHEN 3 THEN 4
                WHEN 4 THEN 3
                WHEN 5 THEN 2
                WHEN 6 THEN 1
                WHEN 7 THEN 0
            END, 
            CAST(dIntervalStart AS DATE)
        )
ORDER BY cName, "Week-Ending";

说明： #

SQL Server的DATEPART(WEEKDAY)返回值依赖于SET DATEFIRST设置，上述代码默认DATEFIRST 7（周日为一周第一天），如果你的环境是DATEFIRST 1（周一为第一天），需要调整CASE里的对应逻辑。

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