如何避免JOIN子句影响SQL LAG()函数以计算除息日股价涨跌幅

问题背景 #

需要获取公司除息日（dividend exdate）当天的股价涨跌幅百分比，现有两张表：

• 股价表prices_table：包含每日交易日的股价数据
• 股息表dividends：仅包含除息日记录

原脚本使用LAG()函数，但因JOIN仅保留除息日数据，导致LAG()取到的是上一个除息日的股价，而非除息日前一交易日的股价，无法得到预期结果。

表结构示例 #

股价表prices_table

股息表dividends

原脚本问题 #

select 
 a.ticker
, a.date
, price as current_price
, lag(price, 1) over(order by date asc) as previous_price
,   round(((price - (lag(price, 1) over(order by date asc))) / 
(lag(price, 1) over(order by date asc)) * 100),2) as pct_chg
from price a
join dividends b on a.ticker = b.ticker
and (a.date = b.exdate)
where a.ticker = 'AAPL-US'
order by a.date desc

原脚本先通过JOIN筛选出除息日的股价数据，再用LAG()，此时窗口仅包含除息日记录，所以取到的是上一个除息日的股价，而非除息日前一交易日的正常股价。

解决方案 #

核心思路是先给所有交易日的股价按股票分组计算前一交易日的股价，再关联股息表筛选出除息日的记录。

修改后的SQL脚本 #

SELECT
  p.ticker,
  p.date AS exdate,
  p.prices AS current_price,
  p.previous_price,
  ROUND(((p.prices - p.previous_price) / p.previous_price * 100), 2) AS pct_chg
FROM (
  SELECT
    ticker,
    date,
    prices,
    -- 按股票分组，按日期升序取前一交易日的股价
    LAG(prices, 1) OVER (PARTITION BY ticker ORDER BY date ASC) AS previous_price
  FROM prices_table
) p
-- 关联股息表，筛选出除息日的记录
JOIN dividends d ON p.ticker = d.ticker AND p.date = d.exdate
WHERE p.ticker = 'AAPL-US'
ORDER BY p.date DESC;

脚本说明 #

1. 子查询中先对prices_table的所有数据按ticker分组，按date升序使用LAG()，这样每个交易日都能获取到前一交易日的股价，而非仅除息日之间的股价。
2. 外层查询关联dividends表，筛选出除息日的记录，此时previous_price就是除息日前一交易日的股价，计算涨跌幅即可得到正确结果。

预期结果 #

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