如何编写SQL实现2021年Q3工单按账号、周维度统计的透视表

核心修改逻辑 #

• 替换原按天生成日期标签的逻辑，改为计算工单创建日对应周的首日（按需求取周日为周起始），统一输出MM/DD格式作为透视列
• 修正原分组逻辑，删除C.CASEID、C."CREATED DATE"两个冗余分组字段，避免单条工单重复统计
• 简化透视列列表，仅保留2021年第三季度所有周的首日值

修改后完整代码 #

SELECT * FROM 
(
    SELECT
        A.ACCOUNTID AS "Account ID",
        A.NAME AS "Account Name",
        -- 计算当前日期所属周的周日（周首日），格式化为MM/DD
        TO_CHAR(
            DATEADD('day', 1, DATE_TRUNC('week', DATEADD('day', -1, C."CREATED DATE"))),
            'MM/DD'
        ) AS WEEK,
        COUNT(DISTINCT C.CASEID) AS TicketCount
    FROM PROD_DB_SOURCE.SALESFORCE.ACCOUNT A
    LEFT JOIN PROD_DB_SOURCE.SALESFORCE."CASE" C
        ON A.ACCOUNTID = C.ACCOUNTID
    LEFT JOIN PROD_DB_BUSINESS.SUPPORT.HEADCOUNT HC
        ON C.OWNERID = HC.CRMUSERID
    WHERE HC.LEVEL = 'Analyst'
        AND C."CREATED DATE" != C."CLOSED DATE"
        AND C."CREATED DATE" BETWEEN '2021-07-01' AND '2021-09-30'
    GROUP BY A.ACCOUNTID, A.NAME, WEEK
    ORDER BY A.NAME ASC
) A
PIVOT
(
    SUM(TicketCount) -- 按周聚合求和
    FOR WEEK IN (
        '07/04', '07/11', '07/18', '07/25',
        '08/01', '08/08', '08/15', '08/22', '08/29',
        '09/05', '09/12', '09/19', '09/26'
    )
) AS PIV

注意事项 #

• 上述日期计算逻辑适配周日为周起始的规则，符合你给出的7月11日当周统计11日-17日数据的要求
• 如果你使用的数据库DATE_TRUNC周默认起始为周日，可直接简化为DATE_TRUNC('week', C."CREATED DATE")计算周首日
• 原代码中LEFT JOIN HC后加了HC.LEVEL = 'Analyst'的过滤条件，实际执行时会转为INNER JOIN，不会返回无对应分析师的账号数据，若需保留这部分账号可将该条件移至LEFT JOIN的ON子句中

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