SQL技术求助：定义指定客户/产品列表并计算营收占比

核心问题修正说明 #

你的代码存在以下语法和逻辑问题：

• 特殊客户/产品列表的定义不符合SQL语法，不能直接使用数组写法
• 查询中引用了未定义的customers_with_usage_attribution变量
• 缺少总营收、特定产品营收的聚合计算，以及百分比的逻辑实现
• 未处理总营收为0时的除法报错问题

完整可执行代码（通用SQL版本） #

-- 步骤1、2：定义特殊客户和产品列表
WITH special_lists AS (
    SELECT 
        ARRAY['1','2','3','4','5'] AS special_customers,
        ARRAY['a','b','c','d','e'] AS special_products
),
-- 步骤3：计算所需指标
results AS (
    SELECT 
        t.customer_id,
        t.revenue_month,
        -- 所有产品的总营收
        SUM(t.total_revenue) AS total_all_products_revenue,
        -- 仅特殊产品的总营收：通过条件判断过滤后求和
        SUM(CASE WHEN t.products = ANY((SELECT special_products FROM special_lists)) THEN t.total_revenue ELSE 0 END) AS total_special_products_revenue,
        -- 特殊产品营收占比：处理除数为0的情况，避免报错
        ROUND(
            CASE 
                WHEN SUM(t.total_revenue) = 0 THEN 0 
                ELSE SUM(CASE WHEN t.products = ANY((SELECT special_products FROM special_lists)) THEN t.total_revenue ELSE 0 END) * 100.0 / SUM(t.total_revenue) 
            END,
            2
        ) AS special_product_revenue_percentage
    FROM tablesource t
    CROSS JOIN special_lists sl
    WHERE t.total_revenue != 0
      AND t.revenue_month >= '2021-08-01'
      -- 筛选特殊客户
      AND t.customer_id = ANY(sl.special_customers)
    GROUP BY t.customer_id, t.revenue_month
    ORDER BY t.customer_id, t.revenue_month
)
SELECT * FROM results;

代码细节解释 #

• 特殊列表定义：用CTE（WITH子句）创建special_lists，存储特殊客户和产品数组，方便后续查询复用
• 总营收计算：直接通过SUM(total_revenue)汇总所有产品的营收
• 特殊产品营收：用CASE WHEN判断当前产品是否属于特殊列表，符合条件则计入求和，否则计0
• 占比计算：用CASE分支处理总营收为0的场景（避免除以0报错），通过ROUND保留2位小数，乘以100转换为百分比格式
• 客户筛选：用customer_id = ANY(sl.special_customers)匹配特殊客户列表，替代原代码中未定义的变量

MySQL兼容版本（不支持ARRAY语法时使用） #

WITH results AS (
    SELECT 
        t.customer_id,
        t.revenue_month,
        SUM(t.total_revenue) AS total_all_products_revenue,
        SUM(CASE WHEN t.products IN ('a','b','c','d','e') THEN t.total_revenue ELSE 0 END) AS total_special_products_revenue,
        ROUND(
            CASE 
                WHEN SUM(t.total_revenue) = 0 THEN 0 
                ELSE SUM(CASE WHEN t.products IN ('a','b','c','d','e') THEN t.total_revenue ELSE 0 END) * 100.0 / SUM(t.total_revenue) 
            END,
            2
        ) AS special_product_revenue_percentage
    FROM tablesource t
    WHERE t.total_revenue != 0
      AND t.revenue_month >= '2021-08-01'
      AND t.customer_id IN ('1','2','3','4','5')
    GROUP BY t.customer_id, t.revenue_month
    ORDER BY t.customer_id, t.revenue_month
)
SELECT * FROM results;

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