SQL面试题练习 —— 根据4节气推算季节

题目来源：美团。

1 题目

有一张用户骑车日期节气表dwd_ride_date_term_dd, 包含user_id（用户id）、ride_dt（骑行日期）以及dt_term（日期对应节气）

注意：

• 如果用户当天多次骑行，该表只会记录一次
• 如果骑行日不是立春、立夏、立秋、立冬这四个日期，那么该字段为NULL

问题：请求出所有用户骑行日对应的季节（春、夏、秋、冬）

样例数据

-- 输入
user_id   ride_dt    dt_term 
001       20240101    NULL
001       20240203    立春
001       20240311    NULL
001       20240401    NULL
001       20240505    立夏
001       20240522    NULL
001       20240807    立秋 
001       20241028    NULL
001       20241107    立冬
001       20241217    NULL
-- 输出
user_id   ride_dt    season 
001       20240101     冬
001       20240203     春
001       20240311     春
001       20240401     春
001       20240505     夏
001       20240522     夏
001       20240807     秋 
001       20241028     秋
001       20241107     冬
001       20241217     冬

2 建表语句

create table dwd_ride_date_term_dd (
user_id varchar(20),
ride_dt varchar(20),
dt_term varchar(20)
);
INSERT INTO dwd_ride_date_term_dd VALUES 
('001','20240101',NULL),
('001','20240203','立春'),
('001','20240311',NULL),
('001','20240401',NULL),
('001','20240505','立夏'),
('001','20240522',NULL),
('001','20240807','立秋'),
('001','20241028',NULL),
('001','20241107','立冬'),
('001','20241217',NULL)
;

3 题解

1. 获取下一个季度的开始时间

select 
    user_id,
    ride_dt as start_dt,
    dt_term,
    lead(ride_dt, 1, '99991231') over(partition by user_id order by ride_dt) as end_dt
from dwd_ride_date_term_dd
where dt_term in ('立春','立夏','立秋','立冬')

2. join 两表

with t2 as (
    select 
        user_id,
        ride_dt as start_dt,
        dt_term,
        lead(ride_dt, 1, '99991231') over(partition by user_id order by ride_dt) as end_dt
    from dwd_ride_date_term_dd
    where dt_term in ('立春','立夏','立秋','立冬')
)
select 
*
from dwd_ride_date_term_dd t1
left join t2
on t1.user_id = t2.user_id
and t1.ride_dt >= t2.start_dt 
and t1.ride_dt < t2.end_dt
;

执行结果

3. 得出结果

with t2 as (
    select 
        user_id,
        ride_dt as start_dt,
        dt_term,
        lead(ride_dt, 1, '99991231') over(partition by user_id order by ride_dt) as end_dt
    from dwd_ride_date_term_dd
    where dt_term in ('立春','立夏','立秋','立冬')
)
select 
    t1.user_id,
    t1.ride_dt,
    case  
        when t2.dt_term = '立春' then '春'
        when t2.dt_term = '立夏' then '夏'
        when t2.dt_term = '立秋' then '秋'
        when t2.dt_term = '立冬' then '冬'
    else '冬' end as season
from dwd_ride_date_term_dd t1
left join t2
on t1.user_id = t2.user_id
and t1.ride_dt >= t2.start_dt 
and t1.ride_dt < t2.end_dt
;

执行结果