HiveSQL-面试题033 存在销售额连续3天增长的商户

数据仓库晨曦

发布于 2024-01-08 15:47:37

5980

一、题目

有一张订单记录表 t_order_033 包含订单ID(order_id),商户ID(shop_id),订单时间(order_time)和订单金额(order_amt),请查询出过去至少存在3天销售额连续增长的商户

+-----------+----------+----------------------+------------+
| order_id  | shop_id  |      order_time      | order_amt  |
+-----------+----------+----------------------+------------+
| 1         | 1001     | 2023-08-21 09:01:00  | 9.99       |
| 2         | 1001     | 2023-08-22 10:00:00  | 19.99      |
| 3         | 1001     | 2023-08-22 13:00:00  | 8.88       |
| 4         | 1001     | 2023-08-23 08:00:00  | 29.99      |
| 5         | 1001     | 2023-08-23 09:00:00  | 19.99      |
| 6         | 1001     | 2023-08-24 11:00:00  | 99.99      |
| 7         | 1001     | 2023-08-25 15:00:00  | 1.99       |
| 8         | 1001     | 2023-08-26 16:00:00  | 2.99       |
| 9         | 1001     | 2023-08-26 17:00:00  | 95.99      |
| 10        | 1002     | 2023-08-21 09:00:00  | 9.99       |
| 11        | 1002     | 2023-08-22 11:00:00  | 1.99       |
| 12        | 1002     | 2023-08-22 11:01:00  | 19.99      |
| 13        | 1002     | 2023-08-22 12:05:00  | 14.99      |
| 14        | 1002     | 2023-08-22 13:00:00  | 6.99       |
| 15        | 1002     | 2023-08-23 14:00:00  | 99.99      |
| 16        | 1002     | 2023-08-24 13:00:00  | 19.99      |
| 17        | 1002     | 2023-08-25 09:00:00  | 19.99      |
| 18        | 1002     | 2023-08-25 11:00:00  | 5.99       |
| 19        | 1002     | 2023-08-25 13:00:00  | 6.99       |
| 20        | 1002     | 2023-08-25 13:07:00  | 7.0        |
| 21        | 1002     | 2023-08-25 15:00:00  | 10.0       |
| 22        | 1002     | 2023-08-26 07:00:00  | 9.99       |
| 23        | 1003     | 2023-08-21 13:07:00  | 7.0        |
| 24        | 1003     | 2023-08-22 15:00:00  | 8.0        |
| 25        | 1003     | 2023-08-23 07:00:00  | 9.99       |
| 26        | 1003     | 2023-08-25 13:07:00  | 10.0       |
| 27        | 1003     | 2023-08-26 15:00:00  | 11.0       |
+-----------+----------+----------------------+------------+

二、分析

1.计算出每天商户的总销售额；

2.由于要连续3日增长，对每个商户计算是否比上一条记录增长，剔除掉不增长的记录；

3.连续问题，使用双排序法计算出每次连续增长天数；

4.查询最后结果

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

三、SQL

1）计算出每天商户的总销售额；

select
shop_id,
to_date(order_time) as order_date,
sum(order_amt) as order_amt
from t_order_033
group by shop_id,to_date(order_time)

查询结果

2)查询增长额，如果差额是null 代表本商户的第一条记录，如果是>0 则代表增长，<=0 不增长

--计算每个商户每天销售金额
with tmp as
(
    select
        shop_id,
        to_date(order_time) as order_date,
        sum(order_amt) as order_amt
from t_order_033
group by shop_id,to_date(order_time)
),
tmp2 as
(
--计算每个商户是否比上一个有销售额日期增加
select
shop_id,
order_date,
order_amt,
order_amt -lag(order_amt,1)over(partition by  shop_id order by order_date) as order_amt_diff
from tmp
)
select * from tmp2;

查询结果

3.剔除不增长的记录，计算每次连续次数。（连续问题，这里使用双排序法，就不赘叙了）

--计算每个商户每天销售金额
with tmp as
(
    select
        shop_id,
        to_date(order_time) as order_date,
        sum(order_amt) as order_amt
from t_order_033
group by shop_id,to_date(order_time)
),
t2 as
(
--计算每个商户是否比上一个有销售额日期增加
select
shop_id,
order_date,
order_amt,
order_amt -lag(order_amt,1)over(partition by  shop_id order by order_date) as order_amt_diff
from tmp
)
select
shop_id,date_diff,count(1) aa
from
(
--按照商户排序，计算是否连续
select
*,
datediff(order_date,'2023-08-01')  as shop_date_diff1,
row_number()over(partition by shop_id order by order_date) as shop_date_diff2,
datediff(order_date,'2023-08-01')  - row_number()over(partition by shop_id order by order_date) as date_diff
from t2
where order_amt_diff >0
) t 
group by shop_id,date_diff

4.计算最终结果

--计算每个商户每天销售金额
with tmp as
(
    select
        shop_id,
        to_date(order_time) as order_date,
        sum(order_amt) as order_amt
from t_order_033
group by shop_id,to_date(order_time)
),
t2 as
(
--计算每个商户是否比上一个有销售额日期增加
select
shop_id,
order_date,
order_amt,
order_amt -lag(order_amt,1)over(partition by  shop_id order by order_date) as order_amt_diff
from tmp
)
select
    shop_id
from
(
select
shop_id,date_diff,count(1) aa
from
(
--按照商户排序，计算是否连续
select
*,
datediff(order_date,'2023-08-01')  as shop_date_diff1,
row_number()over(partition by shop_id order by order_date) as shop_date_diff2,
datediff(order_date,'2023-08-01')  - row_number()over(partition by shop_id order by order_date) as date_diff
from t2
where order_amt_diff >0
) t 
group by shop_id,date_diff
)tt 
where aa >=3;

查询结果

四、建表语句和数据插入

--建表语句
CREATE TABLE t_order_033 (
order_id bigint COMMENT '订单ID',
shop_id bigint COMMENT '商户ID',
order_time string COMMENT '订单时间',
order_amt double COMMENT '订单金额'
) COMMENT '订单记录表'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' 
;
-- 插入数据
insert into t_order_033(order_id,shop_id,order_time,order_amt)
values
(1,1001,'2023-08-21 09:01:00',9.99),
(2,1001,'2023-08-22 10:00:00',19.99),
(3,1001,'2023-08-22 13:00:00',8.88),
(4,1001,'2023-08-23 08:00:00',29.99),
(5,1001,'2023-08-23 09:00:00',19.99),
(6,1001,'2023-08-24 11:00:00',99.99),
(7,1001,'2023-08-25 15:00:00',1.99),
(8,1001,'2023-08-26 16:00:00',2.99),
(9,1001,'2023-08-26 17:00:00',95.99),
(10,1002,'2023-08-21 09:00:00',9.99),
(11,1002,'2023-08-22 11:00:00',1.99),
(12,1002,'2023-08-22 11:01:00',19.99),
(13,1002,'2023-08-22 12:05:00',14.99),
(14,1002,'2023-08-22 13:00:00',6.99),
(15,1002,'2023-08-23 14:00:00',99.99),
(16,1002,'2023-08-24 13:00:00',19.99),
(17,1002,'2023-08-25 09:00:00',19.99),
(18,1002,'2023-08-25 11:00:00',5.99),
(19,1002,'2023-08-25 13:00:00',6.99),
(20,1002,'2023-08-25 13:07:00',7.00),
(21,1002,'2023-08-25 15:00:00',10.00),
(22,1002,'2023-08-26 07:00:00',9.99),
(23,1003,'2023-08-21 13:07:00',7.00),
(24,1003,'2023-08-22 15:00:00',8.00),
(25,1003,'2023-08-23 07:00:00',9.99),
(26,1003,'2023-08-25 13:07:00',10.00),
(27,1003,'2023-08-26 15:00:00',11.00);

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

原始发表：2023-10-16，如有侵权请联系 cloudcommunity@tencent.com 删除

date