销售额连续3天增长的商户

魔法少女小严

已于 2024-10-08 15:02:21 修改

阅读量358

点赞数 3

文章标签：前端 linux javascript

于 2024-10-08 14:37:11 首次发布

本文链接：https://blog.csdn.net/2301_76352996/article/details/142757168

版权

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

CREATE TABLE t5_order (
order_id bigint COMMENT '订单ID',
shop_id bigint COMMENT '商户ID',
order_time date COMMENT '订单时间',
order_amt double COMMENT '订单金额'
) COMMENT '订单记录表';

insert into t5_order(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);

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

select shop_id,order_time,sum(order_amt) order_amt
from t5_order
group by shop_id,order_time

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

LAG 是一个窗口函数，它在 SQL 中用于访问当前行的前一行或多行之前的值。

LAG(expression [, offset [, default_value]]) OVER (
    [PARTITION BY column] 
    [ORDER BY column] 
    [ROWS | RANGE frame_extent]
)

expression：必需的参数，指定你想从当前行之前的行中检索的表达式。
offset（可选）：指定从当前行开始回溯的行数。默认值为 1，表示前一行。
default_value（可选）：如果指定了 offset 并且结果集中当前行为第一行，则返回该默认值。如果未指定，默认返回 NULL。
PARTITION BY（可选）：子句将结果集划分为多个分区，每个分区内部独立应用窗口函数。
ORDER BY（可选）：子句定义了窗口内的行排序方式。
frame_extent（可选）：定义了窗口的开始和结束位置。


select shop_id,order_time,
order_amt-lag(order_amt) over(partition by shop_id order by order_time) order_amt_change
from(
select shop_id,order_time,sum(order_amt) order_amt
from t5_order
group by shop_id,order_time) temp

3.剔除不增长的记录，计算每次连续次数

DATEDIFF 是一个 SQL 函数，用于计算两个日期之间的差异。它返回两个日期参数之间的天数差异。在 MySQL 中，DATEDIFF() 函数通常用于比较日期和时间。


select shop_id,order_time,  datediff(order_time, '2023-08-01')  ,row_number()over(partition by shop_id order by order_time)
from(
select shop_id,order_time,
order_amt-lag(order_amt) over(partition by shop_id order by order_time) order_amt_change
from(
select shop_id,order_time,sum(order_amt) order_amt
from t5_order
group by shop_id,order_time) temp)temp1
where order_amt_change>0

设a=datediff-row_number

按照(shop_id,aa)分组,计算每组的个数

select shop_id,aa,count(1)
from(
select shop_id, datediff(order_time, '2023-08-01')  -row_number()over(partition by shop_id order by order_time) aa
from(
select shop_id,order_time,
order_amt-lag(order_amt) over(partition by shop_id order by order_time) order_amt_change
from(
select shop_id,order_time,sum(order_amt) order_amt
from t5_order
group by shop_id,order_time) temp)temp1
where order_amt_change>0) temp2
group by shop_id,aa

4.计算最终结果

select distinct shop_id from(
select shop_id,aa,count(1) times
from(
select shop_id, datediff(order_time, '2023-08-01')  -row_number()over(partition by shop_id order by order_time) aa
from(
select shop_id,order_time,
order_amt-lag(order_amt) over(partition by shop_id order by order_time) order_amt_change
from(
select shop_id,order_time,sum(order_amt) order_amt
from t5_order
group by shop_id,order_time) temp)temp1
where order_amt_change>0) temp2
group by shop_id,aa
)temp3
where times>=3