一、题目
现有一张股票价格表stock_data有3个字段分别是股票代码(stock_code),日期(trade_date)，收盘价格(closing_price) ，请找出满足连续5天以上（含）每天上涨超过5%的股票,并给出连续满足天数及开始和结束日期。备注：不考虑停牌或其他情况，仅仅关注每天连续5天上涨超过5%的股票。
样例数据
代码语言：javascript复制 ------------- ------------- ----------------
| 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 |
------------- ------------- ----------------
二、分析
首先应该计算每天相对昨天的涨幅，这个使用lag函数；然后判断是否符合涨幅超过5%；然后处理连续问题，使用row_number()函数；最后计算天数、开始日期、结束日期，返回结果；
维度 | 评分 |
|---|---|
题目难度 | ⭐️⭐️⭐️ |
题目清晰度 | ⭐️⭐️⭐️⭐️⭐️ |
业务常见度 | ⭐️⭐️⭐️⭐️⭐️ |
三、SQL
1.使用lag函数计算每天的涨幅。
执行SQL
代码语言：javascript复制SELECT stock_code,
trade_date,
closing_price / LAG(closing_price) OVER (PARTITION BY stock_code ORDER BY trade_date ASC) - 1 AS daily_return
FROM stock_data;
执行结果
代码语言：javascript复制 ------------- ------------- ------------------
| stock_code | trade_date | daily_return |
------------- ------------- ------------------
| AAPL | 2023-02-26 | NULL |
| AAPL | 2023-02-27 | 0.0500000000000 |
| AAPL | 2023-02-28 | 0.0500000000000 |
| AAPL | 2023-03-01 | 0.0501587301587 |
| AAPL | 2023-03-02 | 0.0501813784764 |
| AAPL | 2023-03-03 | 0.0587219343696 |
| AAPL | 2023-03-04 | 0.0642429892022 |
| AAPL | 2023-03-05 | 0.0559854014599 |
| AAPL | 2023-03-06 | 0.0205294808875 |
| GOOG | 2023-02-26 | NULL |
| GOOG | 2023-02-27 | 0.0500000000000 |
| GOOG | 2023-02-28 | 0.0500000000000 |
| GOOG | 2023-03-01 | 0.0490929705215 |
| GOOG | 2023-03-02 | 0.0509067329515 |
| GOOG | 2023-03-03 | 0.0479430360221 |
| GOOG | 2023-03-04 | 0.0520615804927 |
| GOOG | 2023-03-05 | 0.0500001865539 |
| GOOG | 2023-03-06 | 0.0503553407718 |
------------- ------------- ------------------
2.将涨幅换算为是否符合涨幅>=5%
执行SQL
代码语言：javascript复制SELECT stock_code,
trade_date,
if(closing_price / LAG(closing_price) OVER (PARTITION BY stock_code ORDER BY trade_date ASC) - 1 >= 0.05, 1,
0) AS flag
FROM stock_data
;
执行结果
代码语言：javascript复制 ------------- ------------- -------
| stock_code | trade_date | flag |
------------- ------------- -------
| AAPL | 2023-02-26 | 0 |
| AAPL | 2023-02-27 | 1 |
| AAPL | 2023-02-28 | 1 |
| AAPL | 2023-03-01 | 1 |
| AAPL | 2023-03-02 | 1 |
| AAPL | 2023-03-03 | 1 |
| AAPL | 2023-03-04 | 1 |
| AAPL | 2023-03-05 | 1 |
| AAPL | 2023-03-06 | 0 |
| GOOG | 2023-02-26 | 0 |
| GOOG | 2023-02-27 | 1 |
| GOOG | 2023-02-28 | 1 |
| GOOG | 2023-03-01 | 0 |
| GOOG | 2023-03-02 | 1 |
| GOOG | 2023-03-03 | 0 |
| GOOG | 2023-03-04 | 1 |
| GOOG | 2023-03-05 | 1 |
| GOOG | 2023-03-06 | 1 |
------------- ------------- -------
3.查询结果
执行SQL
代码语言：javascript复制with tmp as
(SELECT stock_code,
trade_date,
if(closing_price / LAG(closing_price) OVER (PARTITION BY stock_code ORDER BY trade_date ASC) - 1 >=
0.05, 1, 0) AS flag
FROM stock_data),
tmp2 as
(select stock_code,
trade_date,
flag,
row_number() over (partition by stock_code order by trade_date asc) -
row_number() over (partition by stock_code,flag order by trade_date asc) as diff_rn
from tmp
order by stock_code, trade_date)
select stock_code,
min(trade_date),
max(trade_date),
count(1)
From tmp2
Where flag = 1
Group by stock_code, diff_rn
Having count(1) >= 5
查询结果
代码语言：javascript复制 ------------- ------------- ------------- ------
| stock_code | _c1 | _c2 | _c3 |
------------- ------------- ------------- ------
| AAPL | 2023-02-27 | 2023-03-05 | 7 |
------------- ------------- ------------- ------
四、建表语句和数据插入
代码语言：javascript复制--建表语句
CREATE TABLE stock_data (
stock_code STRING,
trade_date DATE,
closing_price DECIMAL(10,2)
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE;
--样例数据插入
INSERT INTO TABLE 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);
