Loading [MathJax]/jax/output/CommonHTML/config.js
前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
圈层
工具
发布
首页
学习
活动
专区
圈层
工具
MCP广场
社区首页 >专栏 >常见大数据面试SQL-物流线路分析SQL

常见大数据面试SQL-物流线路分析SQL

作者头像
数据仓库晨曦
发布于 2024-09-10 13:05:11
发布于 2024-09-10 13:05:11
18400
代码可运行
举报
文章被收录于专栏:数据仓库技术数据仓库技术
运行总次数:0
代码可运行

一、题目

有t20_logistics_route(物流线路表)

  • route_id(线路ID): 线路的唯一标识符,BIGINT
  • route name(线路名称): 物流线路的名称,STRING

t20_package_info(包裹信息)表:

  • package id (包裹 ID);包裹的唯一标识符,BIGINT
  • route id(线路ID):关联物流线路表的线路 ID,BIGINT
  • package_type (包裹类型):包裹的类型,STRING(大型和小型)
  • start_time(包裹配送时间):包裹开始配送的时间,DATETIMME
  • delivery_time(送达时间): 包裹送达的时间,DATETIMME

t20_lost_package(丢失包裹)表:

  • lost_id(丢失 ID);丢失的唯一标识符,BIGINT
  • package_id(包裹ID):关联包裹信息表的包裹 ID,BIGINT 【要求】: 查询出每条物流线路在每个季节的平均送达时间(单位小时,分钟部分以小数展示),以及每条物流线路全年包裹丢失率(丢失包裹数量/总包裹数量),每条物流线路运输包裹类型占比,结果按照线路ID升序
    • route_id:线路ID
    • route_name:线路名称
    • spring_average_delivery_time:春季该线路平均送达时间:delivery_time-start_time。春季3月、4月、5月(round保留2位小数)
    • summer_average_delivery_time:夏季该线路平均送达时间:delivery_time-start_time。夏季6月、7月、8月(round保留2位小数)
    • autumn_average_delivery_time:秋季该线路平均送达时间:delivery_time-start_time。秋季9月、10月、11月(round保留2位小数)
    • winter_average_delivery_time:冬季该线路平均送达时间:delivery_time-start_time。夏季12月、1月、2月(round保留2位小数)
    • lost_count:该线路全年包裹丢失总量
    • lost_rate:该线路全年包裹丢失率(round保留2位小数)
    • small_packages:该线路运输“小型”包裹类型占比 “小型”包裹数量/总包裹数量(round保留2位小数)
    • big_packages:该线路运输“大型”包裹类型占比 “大型”包裹数量/总包裹数量(round保留2位小数)

样例数据

t20_logistics_route(物流线路)表

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
+-----------+-------------+
| route_id  | route_name  |
+-----------+-------------+
| 1         | 线路 A        |
| 2         | 线路 B        |
| 3         | 线路 C        |
| 4         | 线路 D        |
+-----------+-------------+

t20_lost_package(包裹信息)表

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
+-------------+-----------+---------------+----------------------+----------------------+
| package_id  | route_id  | package_type  |      start_time      |    delivery_time     |
+-------------+-----------+---------------+----------------------+----------------------+
| 1           | 1         | 小型            | 2024-01-04 09:10:00  | 2024-01-05 10:00:00  |
| 2           | 2         | 大型            | 2024-01-09 11:05:00  | 2024-01-10 12:00:00  |
| 3           | 3         | 小型            | 2024-02-09 11:30:00  | 2024-02-10 12:00:00  |
| 4           | 4         | 大型            | 2024-03-09 11:15:00  | 2024-03-10 12:00:00  |
| 5           | 1         | 小型            | 2024-04-09 10:50:00  | 2024-04-10 12:00:00  |
| 6           | 2         | 小型            | 2024-05-09 11:20:00  | 2024-05-10 12:00:00  |
| 7           | 3         | 大型            | 2024-06-09 14:30:00  | 2024-06-10 15:00:00  |
| 8           | 4         | 小型            | 2024-07-09 11:06:00  | 2024-07-10 12:00:00  |
+-------------+-----------+---------------+----------------------+----------------------+

t20_logistics_route(丢失包裹)表

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
+----------+-------------+
| lost_id  | package_id  |
+----------+-------------+
| 1        | 1           |
| 2        | 6           |
+----------+-------------+

** 期望结果**

二、分析

该题目难度不高,但是计算起来比较麻烦,但是实际业务中这样的加工相对常见。面试遇到这样的题目,说明团队相对比较务实,但是日常工作可能也是类似繁琐内容较多。在面试过程中,从内容理解和解题上,都属于内容量比较多的。

维度

评分

题目难度

⭐️⭐️⭐️

题目清晰度

⭐️⭐️⭐️⭐️

业务常见度

⭐️⭐️⭐️⭐️

三、SQL

1.明细数据关联

我们根据粒度发现,最细粒度的数据是包裹数据,所以将包裹表作为主表,连接物流线路表(这里join和left join都可以,因为有包裹一定有线路),左连接丢失包裹表数据。

执行SQL

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
select *
from t20_package_info t1
         join t20_logistics_route t2
              on t1.route_id = t2.route_id
         left join t20_lost_package t3
                   on t1.package_id = t3.package_id;

SQL结果

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
+-------------+-----------+---------------+----------------------+----------------------+-----------+-------------+----------+-------------+
| package_id  | route_id  | package_type  |      start_time      |    delivery_time     | route_id  | route_name  | lost_id  | package_id  |
+-------------+-----------+---------------+----------------------+----------------------+-----------+-------------+----------+-------------+
| 1           | 1         | 小型            | 2024-01-04 09:10:00  | 2024-01-05 10:00:00  | 1         | 线路 A        | 1        | 1           |
| 2           | 2         | 大型            | 2024-01-09 11:05:00  | 2024-01-10 12:00:00  | 2         | 线路 B        | NULL     | NULL        |
| 3           | 3         | 小型            | 2024-02-09 11:30:00  | 2024-02-10 12:00:00  | 3         | 线路 C        | NULL     | NULL        |
| 4           | 4         | 大型            | 2024-03-09 11:15:00  | 2024-03-10 12:00:00  | 4         | 线路 D        | NULL     | NULL        |
| 5           | 1         | 小型            | 2024-04-09 10:50:00  | 2024-04-10 12:00:00  | 1         | 线路 A        | NULL     | NULL        |
| 6           | 2         | 小型            | 2024-05-09 11:20:00  | 2024-05-10 12:00:00  | 2         | 线路 B        | 2        | 6           |
| 7           | 3         | 大型            | 2024-06-09 14:30:00  | 2024-06-10 15:00:00  | 3         | 线路 C        | NULL     | NULL        |
| 8           | 4         | 小型            | 2024-07-09 11:06:00  | 2024-07-10 12:00:00  | 4         | 线路 D        | NULL     | NULL        |
+-------------+-----------+---------------+----------------------+----------------------+-----------+-------------+----------+-------------+

2.计算分季节的送到时间

由于题目中没有明确出季节划分是按照包裹开始配送时间还是送达时间,这里我们就直接使用start_time来计算。 题目是计算每条线路数据,所以粒度是线路。

执行SQL

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
with t as (select t1.package_id,
                  t1.package_type,
                  t1.start_time,
                  t1.delivery_time,
                  t2.route_id,
                  t2.route_name,
                  t3.lost_id
           from t20_package_info t1
                    join t20_logistics_route t2
                         on t1.route_id = t2.route_id
                    left join t20_lost_package t3
                              on t1.package_id = t3.package_id)
select route_id,
       route_name,
       round(avg(case
                     when month(to_date(start_time)) in (3, 4, 5)
                         then (unix_timestamp(delivery_time) - unix_timestamp(start_time)) / 3600 end),
             2)                                                                                            as spring_avg_time,
       round(avg(case
                     when month(to_date(start_time)) in (6, 7, 8)
                         then (unix_timestamp(delivery_time) - unix_timestamp(start_time)) / 3600 end),
             2)                                                                                            as summer_avg_time,
       round(avg(case
                     when month(to_date(start_time)) in (9, 10, 11)
                         then (unix_timestamp(delivery_time) - unix_timestamp(start_time)) / 3600 end),
             2)                                                                                            as autumn_avg_time,
       round(avg(case
                     when month(to_date(start_time)) in (12, 1, 2)
                         then (unix_timestamp(delivery_time) - unix_timestamp(start_time)) / 3600 end),
             2)                                                                                            as winter_avg_time
from t
group by route_id,
         route_name,
         order by route_id asc

SQL结果

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
+-----------+-------------+------------------+------------------+------------------+------------------+
| route_id  | route_name  | spring_avg_time  | summer_avg_time  | autumn_avg_time  | winter_avg_time  |
+-----------+-------------+------------------+------------------+------------------+------------------+
| 1         | 线路 A        | 25.17            | NULL             | NULL             | 24.83            |
| 2         | 线路 B        | 24.67            | NULL             | NULL             | 24.92            |
| 3         | 线路 C        | NULL             | 24.5             | NULL             | 24.5             |
| 4         | 线路 D        | 24.75            | 24.9             | NULL             | NULL             |
+-----------+-------------+------------------+------------------+------------------+------------------+

3.最终结果

添加其他特征,得到最终结果

执行SQL

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
with t as (select t1.package_id,
                  t1.package_type,
                  t1.start_time,
                  t1.delivery_time,
                  t2.route_id,
                  t2.route_name,
                  t3.lost_id
           from t20_package_info t1
                    join t20_logistics_route t2
                         on t1.route_id = t2.route_id
                    left join t20_lost_package t3
                              on t1.package_id = t3.package_id)
select route_id,
       route_name,
       round(avg(case
                     when month(to_date(start_time)) in (3, 4, 5)
                         then (unix_timestamp(delivery_time) - unix_timestamp(start_time)) / 3600 end),
             2)                                                                        as spring_avg_time,
       round(avg(case
                     when month(to_date(start_time)) in (6, 7, 8)
                         then (unix_timestamp(delivery_time) - unix_timestamp(start_time)) / 3600 end),
             2)                                                                        as summer_avg_time,
       round(avg(case
                     when month(to_date(start_time)) in (9, 10, 11)
                         then (unix_timestamp(delivery_time) - unix_timestamp(start_time)) / 3600 end),
             2)                                                                        as autumn_avg_time,
       round(avg(case
                     when month(to_date(start_time)) in (12, 1, 2)
                         then (unix_timestamp(delivery_time) - unix_timestamp(start_time)) / 3600 end),
             2)                                                                        as winter_avg_time,
       count(lost_id)                                                                  as lost_count,    --丢失包裹数量
       round(count(lost_id) / count(1), 2)                                             as lost_rate,     --包裹丢失率
       round(count(case when package_type = '小型' then package_id end) / count(1), 2) as small_packages,--小型包裹率
       round(count(case when package_type = '大型' then package_id end) / count(1), 2) as big_packages--大型包裹率
from t
group by route_id,
         route_name,
         order by route_id asc

SQL结果

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
+-----------+-------------+------------------+------------------+------------------+------------------+-------------+------------+-----------------+---------------+
| route_id  | route_name  | spring_avg_time  | summer_avg_time  | autumn_avg_time  | winter_avg_time  | lost_count  | lost_rate  | small_packages  | big_packages  |
+-----------+-------------+------------------+------------------+------------------+------------------+-------------+------------+-----------------+---------------+
| 1         | 线路 A        | 25.17            | NULL             | NULL             | 24.83            | 1           | 0.5        | 1.0             | 0.0           |
| 2         | 线路 B        | 24.67            | NULL             | NULL             | 24.92            | 1           | 0.5        | 0.5             | 0.5           |
| 3         | 线路 C        | NULL             | 24.5             | NULL             | 24.5             | 0           | 0.0        | 0.5             | 0.5           |
| 4         | 线路 D        | 24.75            | 24.9             | NULL             | NULL             | 0           | 0.0        | 0.5             | 0.5           |
+-----------+-------------+------------------+------------------+------------------+------------------+-------------+------------+-----------------+---------------+
4 rows selected (0.588 seconds)

四、建表语句和数据插入

代码语言:javascript
代码运行次数:0
运行
AI代码解释
复制
--建表语句
--物流线路表
CREATE TABLE IF NOT EXISTS t20_logistics_route
(
    route_id    bigint comment '线路id',
    route_name  string comment '线路名称'
)
    COMMENT '物流线路表';
--包裹信息表
CREATE TABLE IF NOT EXISTS t20_package_info
(
    package_id      bigint comment  '包裹id',
    route_id        bigint comment  '线路id',
    package_type    string comment  '包裹类型',
    start_time      string comment  '开始配送时间',
    delivery_time   string comment  '包裹送达时间'
)
    COMMENT '包裹信息表';
--丢失包裹表
 CREATE TABLE IF NOT EXISTS t20_lost_package
(
    lost_id      bigint comment  '丢失id',
    package_id        bigint comment  '包裹id'
)
    COMMENT '丢失包裹表';

--插入数据
--物流线路表数据
INSERT INTO t20_logistics_route (route_id, route_name)
VALUES 
(1,'线路 A'),
(2,'线路 B'),
(3,'线路 C'),
(4,'线路 D');

--包裹信息表数据
INSERT INTO t20_package_info (package_id,route_id,package_type,start_time,delivery_time)
VALUES
(1,1,'小型','2024-01-04 09:10:00','2024-01-05 10:00:00'),
(2,2,'大型','2024-01-09 11:05:00','2024-01-10 12:00:00'),
(3,3,'小型','2024-02-09 11:30:00','2024-02-10 12:00:00'),
(4,4,'大型','2024-03-09 11:15:00','2024-03-10 12:00:00'),
(5,1,'小型','2024-04-09 10:50:00','2024-04-10 12:00:00'),
(6,2,'小型','2024-05-09 11:20:00','2024-05-10 12:00:00'),
(7,3,'大型','2024-06-09 14:30:00','2024-06-10 15:00:00'),
(8,4,'小型','2024-07-09 11:06:00','2024-07-10 12:00:00');

--丢失包裹表数据
INSERT INTO t20_lost_package (lost_id,package_id)
VALUES
(1,1),
(2,6);
本文参与 腾讯云自媒体同步曝光计划,分享自微信公众号。
原始发表:2024-09-08,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 数据仓库技术 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
暂无评论
推荐阅读
编辑精选文章
换一批
最强最全面的大数据SQL面试题和答案(由31位大佬共同协作完成)
本套SQL题的答案是由许多大佬共同贡献,1+1的力量是远远大于2的,有不少题目都采用了非常巧妙的解法,也有不少题目有多种解法。本套大数据SQL题不仅题目丰富多样,答案更是精彩绝伦!
五分钟学大数据
2021/12/27
5.2K0
大数据面试SQL048-泳池问题(下)
我们接着上两题继续讨论泳池问题,还是相同的数据。现有一份数据记录了用户进入和离开游泳池的时间,请计算出泳池内的平均人数
数据仓库晨曦
2024/04/11
1300
大数据面试SQL048-泳池问题(下)
客快物流大数据项目(六十五):仓库主题
从2005年开始,网购快递每年以倍增的速度增长。重大节日前是快递爆仓发生的时段。如五一节前夕、国庆节前夕、圣诞节前夕、元旦前夕、春节前夕。新兴的光棍节,网购日,2010年“光棍节、圣诞节、元旦”和春节前夕,淘宝网、京东商城等网商集中促销造成部分民营快递企业多次发生爆仓现象。如2011年11月11日世纪光棍节,淘宝网当天交易额33亿,包裹堆积成山,快递公司原有的交通工具和人员,远远无法满足运送这么多包裹的要求,因此造成包裹被堆积在仓库长达十几天。广州市甚至出现同城快件10天不到的情况。
Lansonli
2022/05/12
7860
客快物流大数据项目(六十五):仓库主题
大数据面试SQL047-泳池问题(中)
我们接着上一题大数据面试SQL046-泳池问题(上)继续讨论泳池问题。现有一份数据记录了用户进入和离开游泳池的时间,请找出一天中泳池最多人数持续时长,如有出现多次最高人数,对时间求和
数据仓库晨曦
2024/04/11
1480
大数据面试SQL047-泳池问题(中)
客快物流大数据项目(六十四):运单主题
“运单是运输合同的证明,是承运人已经接收货物的收据。一份运单,填写一个托运人、收货人、起运港、到达港。如同一托运人的货物分别属到达港的两个或两个以上收货人,则应分别填制运单。”
Lansonli
2022/03/13
8830
客快物流大数据项目(六十四):运单主题
常见大数据面试SQL-各用户最长的连续登录天数-可间断
现有各用户的登录记录表t_login_events如下,表中每行数据表达的信息是一个用户何时登录了平台。现要求统计各用户最长的连续登录天数,间断一天也算作连续,例如:一个用户在1,3,5,6登录,则视为连续6天登录。
数据仓库晨曦
2024/07/12
5590
常见大数据面试SQL-各用户最长的连续登录天数-可间断
美团字节大数据面试SQL-每分钟最大直播人数
有如下数据记录直播平台主播上播及下播时间,根据该数据计算出平台每分钟最大直播人数。
数据仓库晨曦
2024/05/18
3910
美团字节大数据面试SQL-每分钟最大直播人数
客快物流大数据项目(二十五):初始化业务数据
chown -R oracle:oinstall /u01/app/oracle/oggdata/orcl
Lansonli
2022/02/08
5630
客快物流大数据项目(二十五):初始化业务数据
快手大数据面试SQL-用户中两人一定认识的组合数
有某城市网吧上网记录表,包含字段:网吧id,访客id(身份证号),上线时间,下线时间。
数据仓库晨曦
2024/06/17
1230
快手大数据面试SQL-用户中两人一定认识的组合数
基于Hadoop生态圈的数据仓库实践 —— 进阶技术(九)
九、退化维度 本节讨论一种称为退化维度的技术。该技术减少维度的数量,简化维度数据仓库模式。简单的模式比复杂的更容易理解,也有更好的查询性能。当一个维度没有数据仓库需要的任何数据时就可以退化此维度,此时需要把退化维度的相关数据迁移到事实表中,然后删除退化的维度。 1. 退化订单维度 本小节说明如何退化订单维度,包括对数据仓库模式和定期装载脚本的修改。使用维度退化技术时你首先要识别数据,分析从来不用的数据列。例如,订单维度的order_number列就可能是这样的一列。但如果用户想看事务的细节,还需要订单号。因此,在退化订单维度前,要把订单号迁移到sales_order_fact表。下图显示了迁移后的模式。
用户1148526
2019/05/25
3840
腾讯大数据面试SQL-连续登陆超过N天的用户
现有用户登录日志表 t_login_log,包含用户ID(user_id),登录日期(login_date)。数据已经按照用户日期去重,请查出连续登录超过4天的用户ID
数据仓库晨曦
2024/06/27
1640
腾讯大数据面试SQL-连续登陆超过N天的用户
客快物流大数据项目(五十一):数据库表分析
​​​​​​​7、客户寄件信息表(tbl_consumer_sender_info)
Lansonli
2022/02/28
1.2K0
客快物流大数据项目(五十一):数据库表分析
牛客-SQL练习
题目地址:查找学校是北大的学生信息_牛客题霸_牛客网 (nowcoder.com)
小简
2023/01/04
2.4K0
字节快手大数据面试SQL-最高峰同时直播人数
有如下数据记录直播平台主播上播及下播时间,根据该数据计算出平台最高峰同时直播人数。
数据仓库晨曦
2024/04/30
2280
字节快手大数据面试SQL-最高峰同时直播人数
Oracle DBA的SQL编写技能提升宝典(含SQL资源)
背景:要迁移数据库,需要创建与源库相同的表空间,大小与源库相同。由于个别表空间较大,手工添加可能需要写很多的脚本,于是同事通过PL/SQL解决了问题。
数据和云
2021/10/13
1.2K0
Oracle DBA的SQL编写技能提升宝典(含SQL资源)
HAWQ取代传统数仓实践(十四)——事实表技术之累积快照
本文总结了使用ETL处理大数据技术进行数据仓库建设的过程,包括数据提取、转换和加载(ETL)过程的构建和部署。主要介绍了ETL处理大数据的几种方法和技术,重点讲解了Apache NiFi和Talend这两个流行的开源ETL工具在大数据环境中的使用。
用户1148526
2018/01/03
2.1K0
HAWQ取代传统数仓实践(十四)——事实表技术之累积快照
美团大数据面试SQL-计算用户首单是即时单的比例
在外卖订单中,有时用户会指定订单的配送时间。现定义:如果用户下单日期与期望配送日期相同则认为是即时单,如果用户下单日期与期望配送时间不同则是预约单。每个用户下单时间最早的一单为用户首单,请计算用户首单中即时单的占比。
数据仓库晨曦
2024/07/25
1750
美团大数据面试SQL-计算用户首单是即时单的比例
SQL 进阶挑战(26 - 30)
现有用户信息表user_info(uid用户ID,nick_name昵称, achievement成就值, level等级, job职业方向, register_time注册时间):
村雨遥
2022/06/27
4660
常见大数据面试SQL-查询每个产品每年总销售额
已知有表如下,记录了每个产品id、产品名称、产品销售开始日期、产品销售结束日期以及产品日均销售金额,请计算出每个产品每年的销售金额
数据仓库晨曦
2024/06/28
2060
常见大数据面试SQL-查询每个产品每年总销售额
腾讯大数据面试SQL-合并连续支付订单
现有一张用户支付表:t_user_pay包含字段订单ID,用户ID,商户ID,支付时间,支付金额。如果同一用户在同一商户存在多笔订单,且中间该用户没有其他商户的支付记录,则认为是连续订单,请把连续订单进行合并,时间取最早支付时间,金额求和。
数据仓库晨曦
2024/06/17
1522
腾讯大数据面试SQL-合并连续支付订单
相关推荐
最强最全面的大数据SQL面试题和答案(由31位大佬共同协作完成)
更多 >
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档
本文部分代码块支持一键运行,欢迎体验
本文部分代码块支持一键运行,欢迎体验