按国家和城市计算价格涨幅的SQL查询方法咨询

我来帮你搞定这个价格涨幅统计的问题！你之前写的SQL只做了基础分组，但没处理交易日期的先后关系，也没关联最新和上一次的价格，所以得借助窗口函数来实现需求。

核心思路 #

要计算每个国家+城市的价格涨幅，关键是先拿到每个组（国家+城市）的最新交易价格和上一次交易的价格，再套用你的涨幅公式计算。这里用LAG()窗口函数可以直接获取同一分组内的上一行价格，再配合ROW_NUMBER()筛选出最新的交易记录，就能得到你要的结果。

完整SQL查询 #

假设你的表tbl包含Country、City、Price和交易日期字段（比如叫TransactionDate，如果字段名不同请自行替换），SQL语句如下：

WITH ranked_transactions AS (
    SELECT
        Country,
        City,
        Price,
        TransactionDate,
        -- 获取同一国家+城市的上一次交易价格
        LAG(Price) OVER (PARTITION BY Country, City ORDER BY TransactionDate) AS PreviousPrice,
        -- 按交易日期倒序排序，标记最新的记录为1
        ROW_NUMBER() OVER (PARTITION BY Country, City ORDER BY TransactionDate DESC) AS rn
    FROM tbl
)
SELECT
    Country,
    City,
    -- 计算涨幅百分比，处理无历史交易的情况
    CASE
        WHEN PreviousPrice IS NULL THEN NULL  -- 如果只有1次交易，返回NULL；也可以改成0，按需调整
        ELSE ROUND(((Price - PreviousPrice) / PreviousPrice) * 100, 2)
    END AS Percentage
FROM ranked_transactions
WHERE rn = 1  -- 只保留每个组的最新交易记录
ORDER BY Country, City;

关键部分解释 #

1. CTEranked_transactions：

   • PARTITION BY Country, City：把数据按国家和城市分组，确保只在同一组内对比价格。
   • LAG(Price) OVER (...)：获取当前行的上一行（按交易日期正序）的价格，也就是上一次交易的价格。
   • ROW_NUMBER() OVER (...)：给每个组内的交易按日期倒序编号，最新的交易编号为1。

2. 主查询：

   • 筛选rn = 1的记录，只保留每个国家+城市的最新交易。
   • 用CASE处理没有历史交易的情况（比如某个城市只有1次交易，PreviousPrice为NULL），避免计算错误。
   • ROUND(..., 2)：把涨幅百分比保留两位小数，你可以根据需求调整精度。

特殊情况处理 #

如果存在同一天多次交易的场景，可以在ORDER BY TransactionDate后面加上更精确的时间字段（比如TransactionTime），确保能准确获取最新的交易记录。

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