SQL按公司分组生成月度价格涨跌变动标记列

核心实现思路 #

你之前写的首月记录判断逻辑可以直接复用，只需要搭配LAG()窗口函数，按公司分区、按月份排序后取上一条记录的价格，做差值判断即可完成三类状态标记：

• 同公司按月份排序后第一条记录标记为ORIGINAL
• 当前价格大于上月价格标记为UP（上涨）
• 当前价格小于上月价格标记为DOWN（下跌）
• 其余情况标记为SAME（持平）

完整可运行代码 #

注意：如果你的原始日期字段精确到日、且同一个公司同一个自然月存在多条价格记录，需要先做一层子查询按公司+月度维度聚合出单月唯一价格（比如取当月最后一次交易价、月平均价），避免窗口函数取值错乱。

SELECT
  Company,
  Price,
  Date,
  CASE
    WHEN ROW_NUMBER() OVER (PARTITION BY Company ORDER BY DATE_TRUNC('month', Date)) = 1 
      THEN 'ORIGINAL'
    WHEN Price > LAG(Price) OVER (PARTITION BY Company ORDER BY DATE_TRUNC('month', Date)) 
      THEN 'UP'
    WHEN Price < LAG(Price) OVER (PARTITION BY Company ORDER BY DATE_TRUNC('month', Date)) 
      THEN 'DOWN'
    ELSE 'SAME'
  END AS price_monthly_status
FROM your_table_name;

不同数据库日期适配说明 #

代码里的DATE_TRUNC('month', Date)是用来把日期截断到当月第一天、统一月度排序粒度的函数，不同数据库可以替换成对应写法：

• PostgreSQL/Redshift/BigQuery：直接用DATE_TRUNC('month', Date)
• MySQL：替换为DATE_FORMAT(Date, '%Y-%m-01')
• SQL Server：替换为DATEFROMPARTS(YEAR(Date), MONTH(Date), 1)
• Oracle：替换为TRUNC(Date, 'MONTH')

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