股票连续上涨天数
读阮一峰的博客，里面有这样一个问题，使用SQL找出某一只股票连续上涨的最大天数。股票价格自是有涨有跌，我们创建一个股票收盘价格表，记录多日的股价，要求就是算出这个股票连续上涨天数的最大值。我们先创建测试表和部分数据（下面代码在MySQL 8上测试通过）。
DROP TABLE IF EXISTS dec_stocks;
CREATE TABLE dec_stocks (
stock_id VARCHAR(16),
deal_date Date,
price decimal(10,2)
);
INSERT INTO dec_stocks VALUES ('stock001', '2020-01-01', 10.0);
INSERT INTO dec_stocks VALUES ('stock001', '2020-01-02', 10.1);
INSERT INTO dec_stocks VALUES ('stock001', '2020-01-03', 10.0);
INSERT INTO dec_stocks VALUES ('stock001', '2020-01-04', 11.0);
INSERT INTO dec_stocks VALUES ('stock001', '2020-01-05', 12.0);
INSERT INTO dec_stocks VALUES ('stock001', '2020-01-06', 12.0);
INSERT INTO dec_stocks VALUES ('stock001', '2020-01-07', 11.5);
这个一个典型的需要使用Window函数的例子，怎么讲呢，就是说如果用命令式的编程方式来解决，那么非常简单，一个遍历，找到关键的后一条小于等于前一条的那个点，那么连续的两个点之间的最大距离就是所求的连续上涨的最多天数。但通常SQL不擅长这种处理，为了使SQL能够处理这样的需求，才加上的Window函数。Window函数使SQL有了“逐条遍历”的功能。
明白了这一层，我们需要使用Window函数首先找到这些关键的点，比如例子中的Jan 3, Jan 6, Jan 7这3个日期。
第一步，我们先给每一行打这么一个标签，把这些价格下降的关键点用1标记。这里我们先使用Window的LAG函数，LAG函数像是在说，你现在处理第n行数据，等着我取第n-1行给你看。
select stock_id, deal_date, price,
case when price > lag(price) over (
partition by stock_id order by deal_date
) then 0 else 1 end as rise_mark
from dec_stocks
还有一种方式使用ROWS BETWEEN，也是可以取当前行的一个范围，1 preceding and 1 preceding就是严格的当前行的前一行，1 preceding and 1 following则表示取当前行前一行到后一行共3行的范围(window)，对这个小范围可以使用min,max,avg,sum等。
select stock_id, deal_date, price,
case when last_price is null then 1
when price - last_price <=0 then 1
else 0 end as rise_mark
from (
select stock_id, deal_date, price, max(price) over (
partition by stock_id order by deal_date
rows between 1 preceding and 1 preceding
) as last_price
from dec_stocks
) tmp_rise_flag
| stock_id | deal_date | price | rise_mark |
|---|---|---|---|
| stock001 | 2020-01-01 | 10.00 | 1 |
| stock001 | 2020-01-02 | 10.01 | 0 |
| stock001 | 2020-01-03 | 10.00 | 1 |
| stock001 | 2020-01-04 | 11.00 | 0 |
| stock001 | 2020-01-05 | 12.00 | 0 |
| stock001 | 2020-01-06 | 12.00 | 1 |
| stock001 | 2020-01-07 | 11.50 | 1 |
我们对上面结果的观察可以看出，连续的0的个数就是连续上涨的天数（当然可以加上当天，那就是连续0的个数再加1），但怎么求呢？SQL又不能定义变量，靠if判断去累加。但SQL可以用Window从上到下逐渐累加，这里用到了一个小技巧，从上到下累加，因为遇到0，累加值不变，所以我们可以用这个累加值做分组。
所以，第二步，用刚才打的标签给数据分组：
select stock_id, deal_date, rise_mark, sum(rise_mark)
over (partition by stock_id order by deal_date) as rise_group_id from (
select stock_id, deal_date, case when price > lag(price)
over (partition by stock_id order by deal_date)
then 0 else 1 end as rise_mark
from dec_stocks
) tmp_rise_group
| stock_id | deal_date | rise_mark | rise_group_id |
|---|---|---|---|
| stock001 | 2020-01-01 | 1 | 1 |
| stock001 | 2020-01-02 | 0 | 1 |
| stock001 | 2020-01-03 | 1 | 2 |
| stock001 | 2020-01-04 | 0 | 2 |
| stock001 | 2020-01-05 | 0 | 2 |
| stock001 | 2020-01-06 | 1 | 3 |
| stock001 | 2020-01-07 | 1 | 4 |
至此，答案就呼之欲出了，我们终于可以使用普通的SQL去求分组后元素个数的最大值了。
第三步，先用group by求count数，再选择最大count:
select max(consecutive_rising_days) from (
select rise_group_id, count(*) as consecutive_rising_days from(
select stock_id, deal_date, sum(rise_mark) over (
partition by stock_id order by deal_date
) as rise_group_id from (
select stock_id, deal_date, case when price > lag(price)
over (partition by stock_id order by deal_date)
then 0 else 1 end as rise_mark
from dec_stocks
) tmp_rise_group
) tmp_rise_group_ele_cound
group by rise_group_id
) tmp_consecutive_rising_days
这个SQL果然麻烦，核心其实就是上面高亮的那个小技巧，但也需要对SQL的窗口函数熟练使用。SQL的运算基础是关系代数，交、并、差、笛卡尔积等，窗口函数让SQL拥有逐行处理的能力。