使用Oracle SQL分析函数实现行转列及价格Delta计算

方案一：自连接实现 #

通过将表与自身关联，匹配同一客户、产品的本年（2022）和上年（2021）数据，同时筛选出2022年有交易的记录：

SELECT
    cy.Date AS CY,
    cy.Price AS Price_CY,
    py.Date AS PY,
    py.Price AS Price_PY,
    cy.Customer,
    cy.Product,
    ROUND(cy.Price / py.Price, 2) AS Price_Delta
FROM
    your_table cy
LEFT JOIN
    your_table py ON cy.Customer = py.Customer 
                  AND cy.Product = py.Product 
                  AND py.Date = cy.Date - 1
WHERE
    cy.Date = 2022
    AND py.Date IS NOT NULL;

逻辑说明 #

• 主表cy筛选2022年交易记录，确保只保留本年有数据的客户
• 通过LEFT JOIN关联上年（cy.Date -1）的同客户同产品数据
• 用py.Date IS NOT NULL过滤掉上年无对应记录的情况
• 直接计算本年价格除以上年价格得到Price_Delta

方案二：窗口函数（LAG）实现 #

利用LAG()窗口函数直接获取同一客户、产品的上年价格，一步完成数据转换：

WITH yearly_data AS (
    SELECT
        Date,
        Price,
        Customer,
        Product,
        LAG(Price, 1) OVER (PARTITION BY Customer, Product ORDER BY Date) AS Price_PY,
        LAG(Date, 1) OVER (PARTITION BY Customer, Product ORDER BY Date) AS PY
    FROM
        your_table
)
SELECT
    Date AS CY,
    Price AS Price_CY,
    PY,
    Price_PY,
    Customer,
    Product,
    ROUND(Price / Price_PY, 2) AS Price_Delta
FROM
    yearly_data
WHERE
    Date = 2022
    AND Price_PY IS NOT NULL;

逻辑说明 #

• 先用CTEyearly_data，通过LAG()函数按客户、产品分组，按年份排序，获取上一年的价格和年份
• 筛选出2022年的记录，同时排除上年无对应数据的情况
• 计算价格差值比例得到Price_Delta

内容的提问来源于stack exchange，提问作者Es-Dot