腾讯大数据面试SQL-连续5天涨幅超过5%的股票

数据仓库晨曦

发布于 2024-06-27 20:33:26

1310

发布于 2024-06-27 20:33:26

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

一、题目

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

二、分析

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

维度	评分
题目难度	⭐️⭐️⭐️
题目清晰度	⭐️⭐️⭐️⭐️⭐️
业务常见度	⭐️⭐️⭐️⭐️⭐️

三、SQL

1.使用lag函数计算每天的涨幅。

执行SQL

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;

执行结果

+-------------+-------------+------------------+
| 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

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
;

执行结果

+-------------+-------------+-------+
| 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

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

查询结果

+-------------+-------------+-------------+------+
| stock_code  |     _c1     |     _c2     | _c3  |
+-------------+-------------+-------------+------+
| AAPL        | 2023-02-27  | 2023-03-05  | 7    |
+-------------+-------------+-------------+------+

四、建表语句和数据插入

--建表语句
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);

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

原始发表：2024-06-18，如有侵权请联系 cloudcommunity@tencent.com 删除

sql