SQL面试题练习 —— 满足连续5天以上每天上涨超过5%的股票

题目

现有一张股票价格表stock_data有3个字段分别是股票代码(stock_code),日期(trade_date)，收盘价格(closing_price) ，请找出满足连续5天以上（含）每天上涨超过5%的股票,并给出连续满足天数及开始和结束日期。

备注：不考虑停牌或其他情况，仅仅关注每天连续5天上涨超过5%的股票。

样例数据

+-------------+-------------+----------------+
| stock_code  | trade_date  | closing_price  |
+-------------+-------------+----------------+
| AAPL        | 2023-02-26  | 100.00         |
| AAPL        | 2023-02-27  | 105.00         |
| AAPL        | 2023-02-28  | 110.25         |
| AAPL        | 2023-03-01  | 115.78         |
| AAPL        | 2023-03-02  | 121.59         |
| AAPL        | 2023-03-03  | 128.73         |
| AAPL        | 2023-03-04  | 137.00         |
| AAPL        | 2023-03-05  | 144.67         |
| AAPL        | 2023-03-06  | 147.64         |
| GOOG        | 2023-02-26  | 2000.00        |
| GOOG        | 2023-02-27  | 2100.00        |
| GOOG        | 2023-02-28  | 2205.00        |
| GOOG        | 2023-03-01  | 2313.25        |
| GOOG        | 2023-03-02  | 2431.01        |
| GOOG        | 2023-03-03  | 2547.56        |
| GOOG        | 2023-03-04  | 2680.19        |
| GOOG        | 2023-03-05  | 2814.20        |
| GOOG        | 2023-03-06  | 2955.91        |
+-------------+-------------+----------------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

建表语句

CREATE TABLE stock_data (
  stock_code VARCHAR(255),      -- 股票代码
  trade_date DATE,              -- 交易日期
  closing_price DECIMAL(10,2)   -- 收盘价
);


INSERT INTO stock_data
VALUES 
('AAPL', '2023-02-26', 100.00),
('AAPL', '2023-02-27', 105.00),
('AAPL', '2023-02-28', 110.25),
('AAPL', '2023-03-01', 115.78),
('AAPL', '2023-03-02', 121.59),
('AAPL', '2023-03-03', 128.73),
('AAPL', '2023-03-04', 137.00),
('AAPL', '2023-03-05', 144.67),
('AAPL', '2023-03-06', 147.64),
('GOOG', '2023-02-26', 2000.00),
('GOOG', '2023-02-27', 2100.00),
('GOOG', '2023-02-28', 2205.00),
('GOOG', '2023-03-01', 2313.25),
('GOOG', '2023-03-02', 2431.01),
('GOOG', '2023-03-03', 2547.56),
('GOOG', '2023-03-04', 2680.19),
('GOOG', '2023-03-05', 2814.20),
('GOOG', '2023-03-06', 2955.91);
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

题解

select
stock_code,min(trade_date ),max(trade_date ),count(*)
from 
(select
*,
row_number() over(partition by stock_code order by trade_date asc) -row_number() over(partition by stock_code,flag order by trade_date) diff_rn
from
(select
    stock_code,
    trade_date,
		-- 计算每天相对昨天的涨幅,使用lag函数
    if(closing_price / LAG(closing_price) OVER (partition by stock_code order by trade_date) - 1 >=0.05,1,0) flag
from stock_data)t)tt
where flag=1
group by stock_code,diff_rn
having count(*)>=5
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16