SQL如何对拼接存储的Pre、Post营收列求和计算互动净收益

核心逻辑 #

先将Pre、Post列中用分号拼接的多组数值拆分为独立元素，分别按InteractionID分组求和，再通过Post求和值 - Pre求和值得到净收益Net。

不同SQL方言实现 #

PostgreSQL #

SELECT 
    InteractionID,
    "Customer ID",
    (SELECT SUM(CAST(elem AS INT)) FROM UNNEST(STRING_TO_ARRAY(Pre, ';')) AS elem) AS Pre,
    (SELECT SUM(CAST(elem AS INT)) FROM UNNEST(STRING_TO_ARRAY(Post, ';')) AS elem) AS Post,
    (SELECT SUM(CAST(elem AS INT)) FROM UNNEST(STRING_TO_ARRAY(Post, ';')) AS elem) 
    - (SELECT SUM(CAST(elem AS INT)) FROM UNNEST(STRING_TO_ARRAY(Pre, ';')) AS elem) AS Net
FROM your_table_name;

MySQL 8.0+ #

WITH split_pre AS (
    SELECT 
        t.InteractionID,
        SUM(CAST(j.val AS UNSIGNED)) AS pre_sum
    FROM your_table_name t
    JOIN JSON_TABLE(
        CONCAT('["', REPLACE(t.Pre, ';', '","'), '"]'),
        '$[*]' COLUMNS (val VARCHAR(255) PATH '$')
    ) j
    GROUP BY t.InteractionID
),
split_post AS (
    SELECT 
        t.InteractionID,
        SUM(CAST(j.val AS UNSIGNED)) AS post_sum
    FROM your_table_name t
    JOIN JSON_TABLE(
        CONCAT('["', REPLACE(t.Post, ';', '","'), '"]'),
        '$[*]' COLUMNS (val VARCHAR(255) PATH '$')
    ) j
    GROUP BY t.InteractionID
)
SELECT 
    t.InteractionID,
    t.`Customer ID`,
    sp.pre_sum AS Pre,
    spo.post_sum AS Post,
    spo.post_sum - sp.pre_sum AS Net
FROM your_table_name t
JOIN split_pre sp ON t.InteractionID = sp.InteractionID
JOIN split_post spo ON t.InteractionID = spo.InteractionID;

Hive/Spark SQL（大数据场景常用） #

SELECT 
    InteractionID,
    `Customer ID`,
    pre_sum AS Pre,
    post_sum AS Post,
    post_sum - pre_sum AS Net
FROM (
    SELECT 
        InteractionID,
        `Customer ID`,
        SUM(CAST(pre_elem AS INT)) OVER(PARTITION BY InteractionID) AS pre_sum,
        SUM(CAST(post_elem AS INT)) OVER(PARTITION BY InteractionID) AS post_sum,
        ROW_NUMBER() OVER(PARTITION BY InteractionID ORDER BY InteractionID) AS rn
    FROM your_table_name
    LATERAL VIEW EXPLODE(SPLIT(Pre, ';')) pre_table AS pre_elem
    LATERAL VIEW EXPLODE(SPLIT(Post, ';')) post_table AS post_elem
) t
WHERE rn = 1;

注意事项 #

• 以上实现默认InteractionID为表的唯一主键，若主键规则不同可调整分组/分区字段
• 若数值存在小数，可将CAST的目标类型替换为DECIMAL或FLOAT适配精度
• 可提前通过COALESCE处理Pre/Post的空值，避免求和结果异常

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