DB2 SQL实现客户最高占比服务标记（含并列场景）

需求背景 #

现有DB2表包含customer_id及各类账单占比字段，需新增Preferred Service列，自动标记客户占比最高的服务；需支持超200万条动态数据自动计算，无需手动操作；若存在多服务占比并列最高，需合并显示为Electric Bill and Water Bill这类格式。

解决方案 #

1. 核心逻辑实现

通过CTE拆分服务占比数据，找出每个客户的最高占比，再用LISTAGG函数合并符合条件的服务名称。以下是完整SQL示例（假设表名为customer_bills，包含electric_bill_pct、water_bill_pct、gas_bill_pct三类占比字段）：

WITH service_pcts AS (
    -- 拆分所有服务及其占比为行数据
    SELECT 
        customer_id,
        'Electric Bill' AS service_name,
        electric_bill_pct AS pct
    FROM customer_bills
    UNION ALL
    SELECT 
        customer_id,
        'Water Bill' AS service_name,
        water_bill_pct AS pct
    FROM customer_bills
    UNION ALL
    SELECT 
        customer_id,
        'Gas Bill' AS service_name,
        gas_bill_pct AS pct
    FROM customer_bills
),
max_pct_per_customer AS (
    -- 计算每个客户的最高占比数值
    SELECT 
        customer_id,
        MAX(pct) AS highest_pct
    FROM service_pcts
    GROUP BY customer_id
)
SELECT 
    cb.customer_id,
    cb.electric_bill_pct,
    cb.water_bill_pct,
    cb.gas_bill_pct,
    -- 合并所有占比等于最高值的服务名称
    LISTAGG(sp.service_name, ' and ') WITHIN GROUP (ORDER BY sp.service_name) AS preferred_service
FROM customer_bills cb
JOIN service_pcts sp ON cb.customer_id = sp.customer_id
JOIN max_pct_per_customer mp 
    ON sp.customer_id = mp.customer_id 
    AND sp.pct = mp.highest_pct
GROUP BY cb.customer_id, cb.electric_bill_pct, cb.water_bill_pct, cb.gas_bill_pct;

2. 实现自动计算（无需手动操作）

将上述逻辑封装为视图，每次查询视图时会自动基于最新数据计算Preferred Service列，完全适配动态数据场景：

CREATE VIEW customer_bills_with_preferred AS
WITH service_pcts AS (
    SELECT 
        customer_id,
        'Electric Bill' AS service_name,
        electric_bill_pct AS pct
    FROM customer_bills
    UNION ALL
    SELECT 
        customer_id,
        'Water Bill' AS service_name,
        water_bill_pct AS pct
    FROM customer_bills
    UNION ALL
    SELECT 
        customer_id,
        'Gas Bill' AS service_name,
        gas_bill_pct AS pct
    FROM customer_bills
),
max_pct_per_customer AS (
    SELECT 
        customer_id,
        MAX(pct) AS highest_pct
    FROM service_pcts
    GROUP BY customer_id
)
SELECT 
    cb.customer_id,
    cb.electric_bill_pct,
    cb.water_bill_pct,
    cb.gas_bill_pct,
    LISTAGG(sp.service_name, ' and ') WITHIN GROUP (ORDER BY sp.service_name) AS preferred_service
FROM customer_bills cb
JOIN service_pcts sp ON cb.customer_id = sp.customer_id
JOIN max_pct_per_customer mp 
    ON sp.customer_id = mp.customer_id 
    AND sp.pct = mp.highest_pct
GROUP BY cb.customer_id, cb.electric_bill_pct, cb.water_bill_pct, cb.gas_bill_pct;

之后直接查询视图即可获取带Preferred Service的最新数据：

SELECT * FROM customer_bills_with_preferred;

3. 性能优化建议（针对200万+数据）

• 给customer_id字段创建主键或唯一索引，大幅提升关联查询效率
• 若占比字段存在NULL值，需用COALESCE(pct, 0)替换原占比字段，避免MAX函数忽略NULL
• 对于频繁更新的表，可考虑按customer_id创建分区表，降低查询时的数据扫描范围
• 若LISTAGG性能瓶颈明显，可提前将服务名称与占比的映射关系存入辅助表，简化拆分逻辑

内容的提问来源于stack exchange，提问作者Mwai.John