02 | 波峰波谷问题

原创已于 2025-06-24 14:30:47 修改 · 688 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#大数据 #数据仓库 #数据分析 #sql #面试

于 2025-06-24 14:17:35 首次发布

SQL面试真题详解专栏收录该内容

8 篇文章 ¥9.90 ¥99.00

订阅专栏

超级会员免费看

一、问题定义与业务场景

目标：从股票每日收盘价中识别价格波峰（Peak）和波谷（Trough）的日期及价格。
定义：

波峰：当日价格严格高于前一天且高于后一天。
波谷：当日价格严格低于前一天且低于后一天。

二、数据准备与建表语句

2.1 建表语句

CREATE TABLE t_stock (
    ts_code VARCHAR(20) COMMENT '股票代码',  -- 如 '000001.SZ'
    trade_date DATE COMMENT '交易日期',      -- 日期类型避免字符串比较
    close_price FLOAT COMMENT '收盘价',            -- 浮点型存储价格
    PRIMARY KEY (ts_code, trade_date)       -- 复合主键防重复
) COMMENT '股票交易日行情表';

字段设计规范：

股票代码：VARCHAR(20)，兼容交易所格式（如深交所代码后缀.SZ）。
交易日期：DATE类型（非VARCHAR），避免日期格式混乱，支持日期计算。
收盘价：FLOAT足够存储价格，若需高精度可改用DECIMAL(10,2)。
主键：(ts_code, trade_date) 复合主键，防止同一股票同日重复记录。

2.2 样例数据

INSERT INTO t_stock (ts_code, trade_date, close_price) VALUES
('000001.SZ', '2025-06-04', 16.66),  -- 不参与计算
('000001.SZ', '2025-06-05', 17.15),  -- 波峰
('000001.SZ', '2025-06-06', 17.12),  -- 波谷
('000001.SZ', '2025-06-07', 17.20),  -- 波峰
('000001.SZ', '2025-06-10', 17.19),  -- 波谷
('000001.SZ', '2025-06-11', 17.41);  -- 不参与计算

数据特点：

同一股票多日连续价格，需按日期排序。
边缘日期（首日/末日）无前驱或后继，不参与波峰波谷计算。

三、SQL解决方案（窗口函数法）

3.1 核心思路

通过 lag() 函数获取上一天的收盘价；通过 lead() 函数获取下一天的收盘价
过滤出满足波峰波谷条件的记录

3.2 完整代码

SELECT
  ts_code,
  trade_date,
  close_price,
  price_type 
FROM
  (
  SELECT
    ts_code,
    trade_date,
    close_price,
    CASE WHEN close_price > prev_close_price AND close_price > next_close_price THEN '波峰' 
        WHEN close_price < prev_close_price AND close_price < next_close_price THEN '波谷' 
    END AS price_type  
  FROM
    (
    SELECT
      ts_code,
      trade_date,
      close_price,
      LAG( close_price, 1 ) OVER ( PARTITION BY ts_code ORDER BY trade_date ) AS prev_close_price,
      LEAD( close_price, 1 ) OVER ( PARTITION BY ts_code ORDER BY trade_date ) AS next_close_price 
    FROM
      t_stock 
    ) t1 
  ) t2 
WHERE
  price_type IN ( '波峰', '波谷' );

计算结果：

3.3 执行步骤解析

步骤	关键操作	作用
1. 子查询	`LAG(close_price,1)`	取前一日价格
	`LEAD(close_price,1)`	取后一日价格
2. 主查询	比较`close_price > prev_close_price AND close_price > next_close_price`	识别波峰
	比较`close_price < prev_close_price AND close_price < next_close_price`	识别波谷
3. 过滤	`WHERE`条件	排除非波峰波谷

四、扩展优化

1. 边缘情况处理

首尾日期：天然无法满足双比较条件，SQL自动排除。
价格相等：严格不等条件（>/<）会跳过相等情况（如三日价格持平）。

2. 性能优化

索引设计（加速窗口函数）：

CREATE INDEX idx_stock_code_date ON t_stock(ts_code, trade_date);

分区表：按股票代码分区，减少全表扫描。

五、总结

核心方法：LAG()/LEAD()窗口函数是波峰波谷问题的标准解法，避免自连接的低效操作。
- lag()函数：LAG(col, n, DEFAULT) 用于统计窗口内往上第 n 行。参数 1 为列名，参数 2 为往上第 n 行（可选，默认为1），参数 3 为默认值（当往上第 n 行为 NULL 时，取默认值，如不指定，则为NULL）
- lead()函数：LEAD(col, n, DEFAULT) 用于统计窗口内往下第 n 行。参数 1 为列名，参数 2 为往下第 n 行（可选，默认为1），参数 3 为默认值（当往下第 n 行为 NULL 时，取默认值，如不指定，则为NULL）