SQL 分析大盘走势
下表（stock）记录了某指数过去一段时间的收盘价，我们要从这张表中找出收盘价持续上涨的日期。
deal_date price
---------- --------
2020-11-20 3377
2020-11-23 3414
2020-11-24 3402
2020-11-25 3362
2020-11-26 3369
2020-11-27 3408
2020-11-30 3391
2020-12-01 3451
2020-12-02 3449
期望得到的结果 >>>
deal_date min_price max_price
----------------------- --------- -----------
2020-11-20 ~ 2020-11-23 3377 3414
2020-11-25 ~ 2020-11-27 3362 3408
2020-11-30 ~ 2020-12-01 3391 3451
一种常规的解决思路：
将当前行的值和上一行的值作比较，我们就能知道当前行的值是增加了还是减少了，或者保持不变。 那怎么把单调递增的序列归到同一组呢？给序列中的第一个值分配唯一的序号，序列中的其它值的序号保持和第一个值的序号一致。
在 SQL 中，使用窗口函数 lag() 可以在当前行获取到上一行某个字段的值。窗口函数 sum() 可以实现累加的操作，要给满足某种规则下的多行数据分配同一个序号，只需要给这些数据行增加一个新字段，对于新字段，除了第一行的值是目标序号，其它行的值都为 0 ，然后对新字段做累加操作就能做到同一个序列的序号一致。
完整的 SQL ：
--获取前一行的 price
with x0 AS
(SELECT
*,
lag (price) over (
ORDER BY deal_date) AS last_price
FROM
stock),
-- 如果是递增，则将标识字段设置为 0，其它的设置为 1
x1 AS
(SELECT
*,
IF(price - last_price > 0, 0, 1) AS inc
FROM
x0),
-- 累加标识字段，将递增序列的行归为一组
x2 AS
(SELECT
*,
SUM(inc) over (
ORDER BY deal_date) AS g
FROM
x1),
-- 过滤掉非单调递增的组（只有一行数据）
x3 AS
(SELECT
g,
CONCAT_WS(' ~ ', MIN(deal_date), MAX(deal_date)) AS deal_date,
MIN(price) AS min_price,
MAX(price) AS max_price
FROM
x2
GROUP BY g
HAVING COUNT(*) >= 2)
SELECT
deal_date,
min_price,
max_price
FROM
x3
另一种非常规的思路：
找到所有日期的组合数据，筛选较后的交易日的价格大于较前的交易日的价格的组合。如果两个交易日之间的所有交易日期的价格都是单调递增的，则这是一个单调递增组合。最后，剔除那些交易日期被包含的组合。
获取之后的交易日的价格比之前的交易日的价格高的所有组合：
SELECT
a.deal_date AS start_date,
b.deal_date AS end_date,
a.price AS start_price,
b.price AS end_price
FROM
stock a
INNER JOIN stock b
ON b.deal_date > a.deal_date
AND a.price < b.price
ORDER BY a.deal_date,
b.deal_date;
-- 数据太多，只放了部分结果
start_date end_date start_price end_price
---------- ---------- ----------- -----------
2020-11-20 2020-11-23 3377 3414
2020-11-20 2020-11-24 3377 3402
2020-11-20 2020-11-27 3377 3408
2020-11-20 2020-11-30 3377 3391
2020-11-20 2020-12-01 3377 3451
2020-11-20 2020-12-02 3377 3449
2020-11-23 2020-12-01 3414 3451
2020-11-23 2020-12-02 3414 3449
...
2020-11-30 2020-12-01 3391 3451
2020-11-30 2020-12-02 3391 3449
对于"2020-11-20 ~ 2020-12-02" 这个组合，怎么知道这个组合之间的所有交易日期的价格是否都是单调递增的呢？
任意取出在这个组合内的两个交易日的价格，如果这两个交易日期的价格不在这个组合的价格之内，或者交易日期在前的价格大于等于交易日期靠后的价格，则这个组合不是单调递增的。
2020-12-01 的价格是 3451，大于 2020-12-02 的价格，因此"2020-11-20 ~ 2020-12-02" 不是价格单调递增的组合。
完整的 SQL ：
x0 AS
(SELECT
a.deal_date AS start_date,
b.deal_date AS end_date,
a.price AS start_price,
b.price AS end_price
FROM
stock a
INNER JOIN stock b
ON b.deal_date > a.deal_date
AND a.price < b.price
AND NOT EXISTS
(SELECT
NULL
FROM
stock c,
stock d
-- c 和 d 的交易日在 a 和 b 的交易日期的范围内
WHERE c.deal_date > a.deal_date
AND c.deal_date < b.deal_date
AND d.deal_date > a.deal_date
AND d.deal_date < b.deal_date
-- 非单调递增的判断规则
AND (
(
d.deal_date > c.deal_date
AND d.price <= c.price
)
OR (
c.price <= a.price
OR c.price >= b.price
)
OR (
d.price <= a.price
OR d.price >= b.price
)
)))
SELECT
MIN(start_date) AS start_date,
end_date,
MIN(start_price) AS start_price,
MAX(end_price) AS end_price
FROM
(SELECT
start_date,
MAX(end_date) AS end_date,
MIN(start_price) AS start_price,
MAX(end_price) AS end_price
FROM
x0
GROUP BY start_date) x1
GROUP BY end_date
本文参与 腾讯云自媒体分享计划，分享自微信公众号。
原始发表：2020-12-12，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读