SQL总结大厂真题-满足连续5天以上每天上涨超过5%的股票

数据仓库晨曦

发布于 2024-01-08 15:39:13

3080

发布于 2024-01-08 15:39:13

文章被收录于专栏：数据仓库技术

1.题目

现有一张股票价格表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        |
+-------------+-------------+----------------+

2.问题分析

首先应该计算每天相对昨天的涨幅，这个使用lag函数；
然后判断是否符合涨幅超过5%；
然后处理连续问题，使用row_number()函数；
最后计算天数、开始日期、结束日期，返回结果；

3.SQL

step1:使用lag函数计算每天的涨幅。

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
;

执行结果

step2:将涨幅换算为是否符合涨幅>=5%

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
;

执行结果

step3:至此，我们又回到了熟悉的老问题，连续问题

，不熟悉的朋友欢迎阅读底部的参考文章。搞起~

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

查询结果

数据准备语句

1.建表语句

CREATE TABLE stock_data (
  stock_code STRING,
  trade_date DATE,
  closing_price DECIMAL(10,2)
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE;

2.样例数据插入

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);

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2023-03-13，如有侵权请联系 cloudcommunity@tencent.com 删除

sql