连续问题SQL-券商场景-合并用户连续交易日期
连续问题SQL-券商场景-合并用户连续交易日期
数据仓库晨曦
发布于 2024-10-09 21:22:24
发布于 2024-10-09 21:22:24
代码可运行
运行总次数：0
代码可运行
一、题目
本题是券商实际工作中的场景，依旧是连续问题，解决方法在一文搞懂连续问题中都有提到，但是该题目相对较难，是几个问题的混合。
已知有交易记录表，已经经过处理，如果用户当天有交易则有一条记录。 t21_user_trade
+------------+-----------+
| cust_name | trd_date |
+------------+-----------+
| AAA | 20240913 |
| AAA | 20240918 |
| AAA | 20240919 |
| AAA | 20240923 |
| AAA | 20240924 |
| BBB | 20240920 |
| BBB | 20240923 |
| BBB | 20240924 |
| BBB | 20240925 |
+------------+-----------+
A股交易日历表，记录A股开放日期 t21_dim_trade_date
+-----------+
| trd_date |
+-----------+
| 20240912 |
| 20240913 |
| 20240918 |
| 20240919 |
| 20240920 |
| 20240923 |
| 20240924 |
| 20240925 |
+-----------+
要求： 分用户合并连续交易日日期结果，输出连续交易的开始日期和结束日期，以及连续交易的天数
+------------+-------------+-------------+---------------+
| cust_name | s_trd_date | e_trd_date | trd_date_cnt |
+------------+-------------+-------------+---------------+
| AAA | 20240913 | 20240919 | 3 |
| AAA | 20240923 | 20240924 | 2 |
| BBB | 20240920 | 20240925 | 4 |
+------------+-------------+-------------+---------------+
二、分析
题目属于连续问题，但是属于混合问题，A股交易本身不连续，且未必有规则，这增加连连续判断条件的难度。并且题目中要求统计连续日期的起始和截止日期、连续天数都是判断完连续后的附加问题。
维度 | 评分 |
|---|---|
题目难度 | ⭐️⭐️⭐️⭐️⭐️ |
题目清晰度 | ⭐️⭐️⭐️⭐️⭐️ |
业务常见度 | ⭐️⭐️⭐️⭐️⭐️ |
三、SQL
1.对交易日历维表进行排序，得到连续日期序列
使用row_number函数开窗，对交易日历进行排序，得到一个连续交易日期的连续序列，以便进行判断是否连续。
执行SQL
select trd_date,
row_number() over (order by trd_date asc) as rn
from t21_dim_trade_date；
SQL结果
+-----------+-----+
| trd_date | rn |
+-----------+-----+
| 20240912 | 1 |
| 20240913 | 2 |
| 20240918 | 3 |
| 20240919 | 4 |
| 20240920 | 5 |
| 20240923 | 6 |
| 20240924 | 7 |
| 20240925 | 8 |
+-----------+-----+
2.用户交易数据与交易数据关联得到用户交易序列
使用用户交易记录表与1中的计算结果进行关联，得到一个带有交易序列的交易日期数据。为了方便查看加了order by.
执行SQL
select t1.cust_name, t1.trd_date, t2.rn
from t21_user_trade t1
join(select trd_date,
row_number() over (order by trd_date asc) as rn
from t21_dim_trade_date) t2
on t1.trd_date = t2.trd_date
order by 1,3
SQL结果
+------------+-----------+-----+
| cust_name | trd_date | rn |
+------------+-----------+-----+
| AAA | 20240913 | 2 |
| AAA | 20240918 | 3 |
| AAA | 20240919 | 4 |
| AAA | 20240923 | 6 |
| AAA | 20240924 | 7 |
| BBB | 20240920 | 5 |
| BBB | 20240923 | 6 |
| BBB | 20240924 | 7 |
| BBB | 20240925 | 8 |
+------------+-----------+-----+
3.判断是否连续。
我们使用lag取上一行的rn与当前行rn进行差值计算，确认是否连续，连续的记为0不连续的记为1。
执行SQL
select t1.cust_name,
t1.trd_date,
t2.rn,
if(t2.rn - 1 = lag(t2.rn) over (partition by t1.cust_name order by t2.rn), 0, 1) as is_conn
from t21_user_trade t1
join(select trd_date,
row_number() over (order by trd_date asc) as rn
from t21_dim_trade_date) t2
on t1.trd_date = t2.trd_date
order by 1, 3
SQL结果
+------------+-----------+-----+----------+
| cust_name | trd_date | rn | is_conn |
+------------+-----------+-----+----------+
| AAA | 20240913 | 2 | 1 |
| AAA | 20240918 | 3 | 0 |
| AAA | 20240919 | 4 | 0 |
| AAA | 20240923 | 6 | 1 |
| AAA | 20240924 | 7 | 0 |
| BBB | 20240920 | 5 | 1 |
| BBB | 20240923 | 6 | 0 |
| BBB | 20240924 | 7 | 0 |
| BBB | 20240925 | 8 | 0 |
+------------+-----------+-----+----------+
4.计算分组ID
使用sum()over()对is_conn累积求和，得出连续分组ID 执行SQL
select cust_name,
trd_date,
rn,
is_conn,
sum(is_conn) over (partition by cust_name order by rn) as group_id
from (select t1.cust_name,
t1.trd_date,
t2.rn,
if(t2.rn - 1 = lag(t2.rn) over (partition by t1.cust_name order by t2.rn), 0, 1) as is_conn
from t21_user_trade t1
join(select trd_date,
row_number() over (order by trd_date asc) as rn
from t21_dim_trade_date) t2
on t1.trd_date = t2.trd_date) t
SQL结果
+------------+-----------+-----+----------+-----------+
| cust_name | trd_date | rn | is_conn | group_id |
+------------+-----------+-----+----------+-----------+
| AAA | 20240913 | 2 | 1 | 1 |
| AAA | 20240918 | 3 | 0 | 1 |
| AAA | 20240919 | 4 | 0 | 1 |
| AAA | 20240923 | 6 | 1 | 2 |
| AAA | 20240924 | 7 | 0 | 2 |
| BBB | 20240920 | 5 | 1 | 1 |
| BBB | 20240923 | 6 | 0 | 1 |
| BBB | 20240924 | 7 | 0 | 1 |
| BBB | 20240925 | 8 | 0 | 1 |
+------------+-----------+-----+----------+-----------+
5.连续判断后计算，计算出开始日期，结束日期，连续天数
根据用户和分组ID进行分组，得到每个连续组，然后计算出题目要求内容 执行SQL
select cust_name,
min(trd_date) as s_trd_date,
max(trd_date) as e_trd_date,
count(1) as trd_date_cnt
from (select cust_name,
trd_date,
rn,
is_conn,
sum(is_conn) over (partition by cust_name order by rn) as group_id
from (select t1.cust_name,
t1.trd_date,
t2.rn,
if(t2.rn - 1 = lag(t2.rn) over (partition by t1.cust_name order by t2.rn), 0, 1) as is_conn
from t21_user_trade t1
join(select trd_date,
row_number() over (order by trd_date asc) as rn
from t21_dim_trade_date) t2
on t1.trd_date = t2.trd_date) t) tt
group by cust_name, group_id
SQL结果
+------------+-------------+-------------+---------------+
| cust_name | s_trd_date | e_trd_date | trd_date_cnt |
+------------+-------------+-------------+---------------+
| AAA | 20240913 | 20240919 | 3 |
| AAA | 20240923 | 20240924 | 2 |
| BBB | 20240920 | 20240925 | 4 |
+------------+-------------+-------------+---------------+
四、建表语句和数据插入
--建表语句
--交易记录表
CREATE TABLE IF NOT EXISTS t21_user_trade
(
cust_name string comment '客户姓名',
trd_date string comment '交易日期'
)
COMMENT '交易记录表';
--交易日历
CREATE TABLE IF NOT EXISTS t21_dim_trade_date
(
trd_date string comment '交易日期'
)
COMMENT '交易日历';
--插入数据
insert into t21_user_trade(cust_name,trd_date)
values
('AAA','20240913'),
('AAA','20240918'),
('AAA','20240919'),
('AAA','20240923'),
('AAA','20240924'),
('BBB','20240920'),
('BBB','20240923'),
('BBB','20240924'),
('BBB','20240925');
insert into t21_dim_trade_date(trd_date)
values
('20240912'),
('20240913'),
('20240918'),
('20240919'),
('20240920'),
('20240923'),
('20240924'),
('20240925');本文参与 腾讯云自媒体同步曝光计划，分享自微信公众号。
原始发表：2024-10-07，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
目录
腾讯云开发者
