如何创建动态PIVOT视图？解决水果不同步调价的列转行问题

问题核心 #

直接使用PIVOT会因为各水果的价格生效区间独立，导致生成的记录分散，同一日期区间内的水果价格无法聚合到同一行。需要先统一生成每个市场的完整日期区间，再匹配对应区间内的各水果价格。

具体步骤 #

1. 提取所有关键日期并处理空值 #

先收集每个市场所有的Start Date和非空的End Date，同时将空的End Date替换为当前日期（或业务定义的“当前生效截止日”），作为后续生成区间的基础。

WITH AllDates AS (
    SELECT 
        MARKET,
        START_DATE AS DATE_VALUE
    FROM TableA
    UNION
    SELECT 
        MARKET,
        ISNULL(END_DATE, GETDATE()) AS DATE_VALUE
    FROM TableA
),

2. 生成每个市场的连续日期区间 #

对每个市场的关键日期排序，生成相邻日期组成的区间，每个区间对应一个统一的生效时间段：

MarketDateRanges AS (
    SELECT 
        MARKET,
        DATE_VALUE AS START_DATE,
        LEAD(DATE_VALUE) OVER (PARTITION BY MARKET ORDER BY DATE_VALUE) AS END_DATE
    FROM AllDates
)

3. 匹配区间内的水果价格 #

将生成的日期区间与原表关联，找到每个区间内各水果生效的价格（利用日期重叠条件：原表的Start Date <= 区间Start Date，且原表的End Date >= 区间End Date 或者原表End Date为空）：

MarketFruitPrices AS (
    SELECT 
        r.MARKET,
        r.START_DATE,
        CASE WHEN r.END_DATE = GETDATE() THEN NULL ELSE r.END_DATE END AS END_DATE,
        a.FRUIT,
        a.[PRICE/kg]
    FROM MarketDateRanges r
    LEFT JOIN TableA a 
        ON r.MARKET = a.MARKET
        AND a.START_DATE <= r.START_DATE
        AND (a.END_DATE >= r.END_DATE OR a.END_DATE IS NULL)
    WHERE r.END_DATE IS NOT NULL
)

4. 最终透视转换 #

对上述结果使用PIVOT，即可得到所有水果价格在同一日期区间内聚合的目标表：

SELECT 
    MARKET,
    START_DATE,
    END_DATE,
    Apple AS Apple_Price,
    Banana AS Banana_Price,
    Strawberry AS Strawberry_Price
FROM MarketFruitPrices
PIVOT (
    MAX([PRICE/kg])
    FOR FRUIT IN (Apple, Banana, Strawberry)
) AS PivotTable
ORDER BY MARKET, START_DATE;

补充说明 #

• 若使用不支持PIVOT的数据库（如MySQL），可通过GROUP BY结合条件聚合实现相同逻辑：

SELECT 
    MARKET,
    START_DATE,
    END_DATE,
    MAX(CASE WHEN FRUIT = 'Apple' THEN [PRICE/kg] END) AS Apple_Price,
    MAX(CASE WHEN FRUIT = 'Banana' THEN [PRICE/kg] END) AS Banana_Price,
    MAX(CASE WHEN FRUIT = 'Strawberry' THEN [PRICE/kg] END) AS Strawberry_Price
FROM MarketFruitPrices
GROUP BY MARKET, START_DATE, END_DATE
ORDER BY MARKET, START_DATE;

• 若业务中“当前生效”的逻辑不是当前日期，可替换GETDATE()为对应的业务日期值。

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