带日期维度的SQL注册量求和查询语法错误排查

语法错误核心原因 #

你遇到的语法错误是因为别名使用了HTML转义字符"，SQL引擎无法识别这种格式的引号，需要替换为数据库兼容的引号格式。此外原SQL的排名逻辑不符合“筛选前5产品”的需求，同时DATE_TRUNC的周起始日期匹配也存在逻辑问题。

修正后的SQL代码 #

WITH weekly_tickets AS (
    SELECT 
        product AS produto,
        category AS categoria,
        reason AS motivo,
        DATE_TRUNC('week', created_date) AS semana,
        COUNT(daily_ticket) AS qtde_registros
    FROM tb_atendimento
    WHERE created_date >= DATE '2022-05-17' AND created_date <= DATE '2022-06-16'
    GROUP BY product, category, reason, semana
),
product_total AS (
    -- 统计每个产品的总注册量，用于筛选前5产品
    SELECT 
        produto,
        SUM(qtde_registros) AS total_registros
    FROM weekly_tickets
    GROUP BY produto
),
top5_products AS (
    SELECT produto
    FROM product_total
    ORDER BY total_registros DESC
    LIMIT 5 -- 取总注册量排名前5的产品
)
SELECT 
    wt.produto,
    wt.categoria,
    wt.motivo,
    SUM(CASE WHEN wt.semana = DATE_TRUNC('week', DATE '2022-05-17') THEN wt.qtde_registros ELSE 0 END) AS "2022-05-17",
    SUM(CASE WHEN wt.semana = DATE_TRUNC('week', DATE '2022-05-24') THEN wt.qtde_registros ELSE 0 END) AS "2022-05-24",
    SUM(CASE WHEN wt.semana = DATE_TRUNC('week', DATE '2022-05-31') THEN wt.qtde_registros ELSE 0 END) AS "2022-05-31",
    SUM(CASE WHEN wt.semana = DATE_TRUNC('week', DATE '2022-06-07') THEN wt.qtde_registros ELSE 0 END) AS "2022-06-07"
FROM weekly_tickets wt
JOIN top5_products tp ON wt.produto = tp.produto
GROUP BY wt.produto, wt.categoria, wt.motivo
ORDER BY wt.produto;

关键修正点 #

• 引号格式修正：将别名中的"替换为SQL标准双引号（若使用MySQL可换成反引号`；SQL Server可换成方括号[]）。
• 排名逻辑修正：新增product_total和top5_products两个CTE，先统计每个产品的总注册量，再筛选总注册量前5的产品，确保是产品维度的前5，而非产品下的单一行维度前5。
• 周日期匹配修正：使用DATE_TRUNC('week', DATE '2022-05-17')获取对应周的起始日期，保证和weekly_tickets中semana的格式完全一致，避免因数据库周起始设置差异导致的匹配失败。

适配说明 #

• 若你的数据库不支持LIMIT（如SQL Server），可将top5_products中的LIMIT 5替换为TOP 5。
• 不同数据库的周处理语法有差异：比如MySQL可用DATE_FORMAT(created_date, '%X-%V')或STR_TO_DATE(CONCAT(YEAR(created_date), '-', WEEK(created_date), '-1'), '%X-%V-%w')来获取周起始日期，需根据实际使用的数据库调整。

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