松果出行-数据仓库SQL面试题_松果出行面试问题-CSDN博客

本文链接：https://blog.csdn.net/sinat_30371347/article/details/122145280

本文详细介绍了如何使用Hive SQL设计表结构，模拟数据，以及针对特定业务场景统计连续用车交易总额、天数、开始和结束时间以及间隔天数。涉及表结构设计、数据模拟和复杂SQL查询逻辑，适合理解和实践数据库操作。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一、SQL题目

松果出行解决了千千万万用户短途出行的需求，现PM想统计最近3个月松果用户连续用车交易的总额,连续用车天数,连续用车开始时间和连续用车结束时间，用车间隔天数等数据指标。已知表字段和类型有:用户id (userjd)为整型，用车开始时间(start_time)为date类型,用车结束时间(end.time)为date类型;交易金额(amout)为浮点型。请按以下回答以下问题：
(1)请根据已知条件来设计一张Hive表来存储数据,要求给出完整表结构.
(2)请根据设计出的表结构，自行模拟数据，写到数据表中，要求给出模拟数据的SQL.
(3)请根据表和模拟的数据，统计出连续用车交易的总额，连续用车天数，连续用车开始时间和连续用车结束时间，用车间隔天数等数据指标，要求给出完整的SQL .

二、题目解答

1、数据表设计

现有原表用户出行记录事实表tbs_dw_sg_traffic_fct，设定最近3个月为近90天以start_time过滤，且每个用户用车每个开始日期记录唯一。
表名字段名字段类型字段注释
tbs_dw_sg_traffic_fct user_id int 用户id
start_time date 用车开始日期
end_time date 用车结束日期
amount float 金额
建表语句：

CREATE TABLE `tbs_dw_sg_traffic_fct`(
  `user_id` int, 
  `start_time` date, 
  `end_time` date, 
  `amount` float)；

目标表设计连续出行用户统计表tbs_dm_sg_user_statistics
表名字段名字段类型字段注释
tbs_dm_sg_user_statistics user_id int 用户id
first_time date 连续开始日期
last_time date 连续结束日期
continuous_days int 连续用车天数
total_amount float 连续用车交易的总额
interval_days int 用车间隔天数

2、业务场景分析

连续用车有两种场景：
场景1：用户连续每天用车并在当天结束用车
样例数据如下：
user_id start_time end_time amount
1001 2021-12-20 2021-12-20 1.5
1001 2021-12-21 2021-12-21 2
1001 2021-12-22 2021-12-22 3
1001 2021-12-24 2021-12-24 2
1001 2021-12-25 2021-12-25 2
插入语句：

insert into tbs_dw_sg_traffic_fct values(1001,'2021-12-20','2021-12-20',1.5),(1001,'2021-12-21','2021-12-21',2),(1001,'2021-12-22','2021-12-22',3),(1001,'2021-12-24','2021-12-24',2) ,(1001,'2021-12-25','2021-12-25',2);

场景2：用户用车当天未结束
样例数据如下：
user_id start_time end_time amount
1002 2021-12-20 2021-12-25 20
插入语句：

insert into tbs_dw_sg_traffic_fct values(1002,'2021-12-20','2021-12-25',20);

3、处理逻辑分析

场景1:
通过按照user_id分组start_time排序获取每个用户按照start_time排序后的row_number序号，再通过start_time和row_number获取差值后和user_id分组的数据量大于1的记录获取连续用户，具体代码如下：
–对场景1数据进行处理

select 
	D.user_id,
	D.first_time,
	D.last_time,
	D.continuous_days,
	D.tot