经典sql题（三）查找股票价格波峰波谷

了解价格的波动情况对市场决策至关重要，本文通过 SQL 查询，演示如何从一个价格数据表中提取价格波动信息，识别出波峰和波谷。我们假设有一个名为 test 的表，包含每个时间点的价格数据。

示例数据

假设我们的 test 表有以下数据：

id	ds	price
1	2023-10-01 08:00:00	100
1	2023-10-01 09:00:00	110
1	2023-10-02 10:00:00	105
1	2023-10-03 11:00:00	115
2	2023-10-01 10:00:00	200
2	2023-10-02 12:00:00	190
2	2023-10-03 14:00:00	195
2	2023-10-04 15:00:00	210
3	2023-10-01 16:00:00	300

查询步骤

第一步：计算前后价格

我们将使用窗口函数 LAG 和 LEAD 来获取每个价格点的前后价格。

SELECT 
    id,
    ds,
    price,
    LAG(price, 1) OVER (PARTITION BY id ORDER BY ds) AS lag_price,
    LEAD(price, 1) OVER (PARTITION BY id ORDER BY ds) AS lead_price
FROM 
    test;

结果（中间表）：

id	ds	price	lag_price	lead_price
1	2023-10-01 08:00:00	100	NULL	110
1	2023-10-01 09:00:00	110	100	105
1	2023-10-02 10:00:00	105	110	115
1	2023-10-03 11:00:00	115	105	NULL
2	2023-10-01 10:00:00	200	NULL	190
2	2023-10-02 12:00:00	190	200	195
2	2023-10-03 14:00:00	195	190	210
2	2023-10-04 15:00:00	210	195	NULL
3	2023-10-01 16:00:00	300	NULL	NULL

第二步：识别波峰与波谷

在获取前后价格的基础上，我们接着使用 CASE 语句来标记波峰和波谷。

SELECT 
    id,
    ds,
    price,
    CASE 
        WHEN price > lag_price AND price > lead_price THEN '波峰'
        WHEN price < lag_price AND price < lead_price THEN '波谷'
        ELSE '无'
    END AS price_type
FROM (
    SELECT 
        id,
        ds,
        price,
        LAG(price, 1) OVER (PARTITION BY id ORDER BY ds) AS lag_price,
        LEAD(price, 1) OVER (PARTITION BY id ORDER BY ds) AS lead_price
    FROM 
        test
) AS subquery;

结果（最终结果）：

id	ds	price	price_type
1	2023-10-01 08:00:00	100	无
1	2023-10-01 09:00:00	110	波峰
1	2023-10-02 10:00:00	105	波谷
1	2023-10-03 11:00:00	115	无
2	2023-10-01 10:00:00	200	无
2	2023-10-02 12:00:00	190	波谷
2	2023-10-03 14:00:00	195	无
2	2023-10-04 15:00:00	210	无
3	2023-10-01 16:00:00	300	无

完整步骤解析

1. 计算前后价格：

   • 使用 LAG 和 LEAD 函数，基于每个 id 的时间顺序提取前后价格。

2. 识别波峰与波谷：

   • 使用 CASE 语句判断当前价格的波动状态，标记为“波峰”、“波谷”或“无”。