使用RankedCombination实现按行计算成本差值的SQL查询需求

数据集 #

原始数据（注意Cost字段带美元符号，需转换为数值才能计算）：

-- 示例表结构及数据
CREATE TABLE sales (
    City VARCHAR(50),
    Brand VARCHAR(10),
    Cost VARCHAR(10),
    Date DATE
);

INSERT INTO sales VALUES
('Nashville', 'A', '$100', '2023-11-14'),
('Nashville', 'A', '$125', '2023-11-15');

目标结果 #

获取每个City+Brand组的最新日期、最新成本与上一成本的差值。

实现SQL #

WITH ranked_sales AS (
    SELECT
        City,
        Brand,
        -- 去除美元符号并转换为数值类型
        CAST(REPLACE(Cost, '$', '') AS DECIMAL(10,2)) AS num_cost,
        Date,
        -- 按城市+品牌分区，日期倒序排名，最新记录排第1
        ROW_NUMBER() OVER (PARTITION BY City, Brand ORDER BY Date DESC) AS rn,
        -- 获取同组内上一条记录的成本值
        LAG(CAST(REPLACE(Cost, '$', '') AS DECIMAL(10,2))) OVER (PARTITION BY City, Brand ORDER BY Date) AS prev_cost
    FROM sales
)
SELECT
    City,
    Brand,
    -- 计算差值并还原美元符号格式
    CONCAT('$', num_cost - prev_cost) AS Difference,
    Date AS "Latest Date"
FROM ranked_sales
-- 仅保留每组最新的记录
WHERE rn = 1;

逻辑说明 #

1. CTE ranked_sales：
   • 用PARTITION BY City, Brand将数据按城市和品牌分组，确保每组内独立计算。
   • ROW_NUMBER() OVER (...)给每组内的记录按日期倒序排名，最新的记录排名为1。
   • LAG(...)函数抓取同组内上一条（更早日期）的成本值，用于计算差值。
   • 先将带美元符号的Cost转换为数值类型，避免字符串无法参与算术运算。
2. 主查询：
   • 筛选排名为1的记录（即每组最新的那条），计算最新成本与上一成本的差值后，再格式化为带美元符号的字符串。
   • 输出需求指定的字段：城市、品牌、差值、最新日期。

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