SQL实现同列指定窗口差值计算、parity字段生成及st列透视

原有代码问题 #

之前编写的SQL无法满足需求，核心错误有两点：

select date, sid, category, st, st_status, %discount, lag(%discount) over (partition by date, sid, st order by date) as lag_discount
from table1

• 分区逻辑错误：将st加入分区键，导致不同st值的记录被拆分到独立窗口，无法关联到同组下st='mg'的基准数据
• 函数选择错误：lag()只能取同窗口内上一行的数值，无法固定提取st='mg'的基准行，且同分组下date值完全一致，按date排序无实际意义

正确实现代码 #

生成parity字段 #

先通过条件窗口函数提取每个date+sid分组下st='mg'的基准折扣、基准库存状态，再按规则计算parity字段：

注意：%discount带特殊字符，不同数据库需要加对应转义符，比如MySQL用`%discount`、PostgreSQL用"%discount"

WITH base AS (
    SELECT
        date,
        sid,
        category,
        st,
        st_status,
        `%discount`,
        -- 提取同组mg记录的基准折扣
        MAX(CASE WHEN st = 'mg' THEN `%discount` END) OVER (PARTITION BY date, sid) AS mg_discount,
        -- 提取同组mg记录的基准库存状态
        MAX(CASE WHEN st = 'mg' THEN st_status END) OVER (PARTITION BY date, sid) AS mg_status
    FROM table1
)
SELECT
    date,
    sid,
    category,
    st,
    st_status,
    `%discount`,
    CASE
        -- 非mg记录：折扣值存在时按差值规则判定
        WHEN st != 'mg' AND `%discount` IS NOT NULL AND `%discount` - mg_discount > 0 THEN 'lead'
        WHEN st != 'mg' AND `%discount` IS NOT NULL AND `%discount` - mg_discount < 0 THEN 'lag'
        -- 非mg记录：折扣值为空时按库存状态规则判定
        WHEN st != 'mg' AND `%discount` IS NULL AND st_status = 'out_of_stock' AND mg_status = 'in_stock' THEN 'lead'
        WHEN st != 'mg' AND `%discount` IS NULL AND st_status = 'in_stock' AND mg_status = 'out_of_stock' THEN 'lag'
        -- mg记录自身parity按规则1判定，无匹配则为空
        WHEN st = 'mg' AND st_status = 'in_stock' AND NOT EXISTS (
            SELECT 1 FROM base sub 
            WHERE sub.date = base.date AND sub.sid = base.sid 
            AND sub.st != 'mg' AND sub.st_status = 'in_stock'
        ) THEN 'lead'
        ELSE NULL
    END AS parity
FROM base;

按date、sid维度透视st列 #

基于上面的计算结果，用条件聚合实现透视，把不同st值的属性展开为列：

SELECT
    date,
    sid,
    category,
    -- az维度字段
    MAX(CASE WHEN st = 'az' THEN st_status END) AS az_st_status,
    MAX(CASE WHEN st = 'az' THEN `%discount` END) AS az_discount,
    MAX(CASE WHEN st = 'az' THEN parity END) AS az_parity,
    -- ph维度字段
    MAX(CASE WHEN st = 'ph' THEN st_status END) AS ph_st_status,
    MAX(CASE WHEN st = 'ph' THEN `%discount` END) AS ph_discount,
    MAX(CASE WHEN st = 'ph' THEN parity END) AS ph_parity,
    -- mg维度字段
    MAX(CASE WHEN st = 'mg' THEN st_status END) AS mg_st_status,
    MAX(CASE WHEN st = 'mg' THEN `%discount` END) AS mg_discount,
    MAX(CASE WHEN st = 'mg' THEN parity END) AS mg_parity
FROM (
    -- 此处替换为上面计算parity的SQL代码
    WITH base AS (
        SELECT
            date,
            sid,
            category,
            st,
            st_status,
            `%discount`,
            MAX(CASE WHEN st = 'mg' THEN `%discount` END) OVER (PARTITION BY date, sid) AS mg_discount,
            MAX(CASE WHEN st = 'mg' THEN st_status END) OVER (PARTITION BY date, sid) AS mg_status
        FROM table1
    )
    SELECT
        date,
        sid,
        category,
        st,
        st_status,
        `%discount`,
        CASE
            WHEN st != 'mg' AND `%discount` IS NOT NULL AND `%discount` - mg_discount > 0 THEN 'lead'
            WHEN st != 'mg' AND `%discount` IS NOT NULL AND `%discount` - mg_discount < 0 THEN 'lag'
            WHEN st != 'mg' AND `%discount` IS NULL AND st_status = 'out_of_stock' AND mg_status = 'in_stock' THEN 'lead'
            WHEN st != 'mg' AND `%discount` IS NULL AND st_status = 'in_stock' AND mg_status = 'out_of_stock' THEN 'lag'
            ELSE NULL
        END AS parity
    FROM base
) t
GROUP BY date, sid, category;

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