【Hive SQL 逐日一题】统计迩来1天/7天/30天商品的销量
测试数据create table if not exists sales(
id int,
product_id int,
quantity int,
sale_date string);
INSERT INTO sales (id, product_id, quantity, sale_date) VALUES
(1, 101, 2, '2024-05-16'),
(2, 102, 1, '2024-05-15'),
(3, 101, 3, '2024-05-15'),
(4, 103, 4, '2024-05-14'),
(5, 102, 2, '2024-05-14'),
(6, 101, 1, '2024-05-13'),
(7, 103, 3, '2024-05-13'),
(8, 104, 5, '2024-05-12'),
(9, 102, 4, '2024-05-11'),
(10, 105, 2, '2024-05-11'),
(11, 104, 2, '2024-05-11'),
(12, 106, 2, '2024-05-10'),
(13, 102, 2, '2024-05-10'),
(14, 101, 2, '2024-05-08'),
(15, 101, 2, '2024-05-08'),
(16, 105, 2, '2024-05-05'),
(17, 104, 2, '2024-05-01'),
(18, 106, 2, '2024-04-29'),
(19, 102, 2, '2024-04-20'),
(20, 101, 2, '2024-04-15');
需求分析
统计迩来 1 天/ 7 天/ 30 天各个商品的销量（假设本日为 2024-05-17）。
效果示例：
product_idrecent_daystotal_quantitytotal_sales10113310176410130106…………效果按 recent_days 升序、total_quantity 降序分列。
此中：
[*]product_id 体现商品 ID；
[*]recent_days 体现迩来 n 天；
[*]total_quantity 体现该商品的贩卖数量；
[*]total_sales 体现该商品的贩卖次数（用户一次性购买多件该商品，只纪录一次贩卖）。
需求实现
-- 最近1天
select
product_id,
1 recent_days,
sum(quantity) total_quantity,
count(product_id) total_sales
from
sales
where
sale_date = "2024-05-16"
group by
product_id
union all
-- 最近7天
select
product_id,
7 recent_days,
sum(quantity) total_quantity,
count(product_id) total_sales
from
sales
where
sale_date >= date_sub("2024-05-16",6) and sale_date <= "2024-05-16"
group by
product_id
union all
-- 最近30天
select
product_id,
30 recent_days,
sum(quantity) total_quantity,
count(product_id) total_sales
from
sales
where
sale_date >= date_sub("2024-05-16",29) and sale_date <= "2024-05-16"
group by
product_id
order by
recent_days,total_quantity desc;
输出效果如下：
https://dis.qidao123.com/imgproxy/aHR0cHM6Ly9pLWJsb2cuY3NkbmltZy5jbi9ibG9nX21pZ3JhdGUvZGU4MzRkOTdmZDEwZDAwNzNiM2JiMGI3MmM1YmM5ZmMucG5n
固然这种方法可以算出效果，但是服从很低，我们必要算三次然后再举行归并，数据量一大的时间那就太慢了，那么有没有更好的方法呢？固然有！
起首来看优化完成后的 SQL 代码：
select
product_id,
rds recent_days,
sum(quantity) total_quantity,
count(product_id) total_sales
from
sales lateral view explode(array(1,7,30)) tmp as rds
where
sale_date >= date_sub("2024-05-16",rds - 1) and sale_date <= "2024-05-16"
group by
rds,product_id
order by
recent_days,total_quantity desc;
这里接纳炸裂的方式，将一行数据变为了三行数据，（场景假设）如下所示：
炸裂前
idproduct_idquantitysale_date110122024-05-16210212024-05-15炸裂后
idproduct_idquantitysale_daterds110122024-05-161110122024-05-167110122024-05-1630210212024-05-151210212024-05-157210212024-05-1530炸裂后，会新增一列 rds，也就是用来体现迩来 n 天的标记。此中每行数据都会变成 3 行数据，纵然数据量变多了也没有关系，由于我们设置了 where 条件举行过滤，它只会生存符合要求的数据，同样也不会对我们的效果造成影响。
这里不明白的话，大概是不相识 lateral view explode 方法的利用规则，可以百度相识一下。
假设本日为：2024-05-17
比方：
[*] 商品 101 在 2024-05-16 有效户举行了购买，以是该数据会生存在迩来 1 天/ 7 天/ 30 天商品的销量效果中。
[*] 商品 102 在 2024-05-15 有效户举行了购买，以是该数据会生存在迩来 7 天/ 30 天商品的销量效果中。
[*] …
通过这种方法，我们不再必要写三个子查询然后再举行归并，一个查询即可搞定，进步了团体的运行速率。
https://dis.qidao123.com/imgproxy/aHR0cHM6Ly9pLWJsb2cuY3NkbmltZy5jbi9ibG9nX21pZ3JhdGUvYjQ3YWNhOWZkOTc4YjRmMmM4OWU1MmVhMzc2ZmQzMGYucG5n
在这么小数据量的场景下都节省了 1 秒左右，可见一斑。
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！qidao123.com:ToB企服之家，中国第一个企服评测及软件市场,开放入驻,技术点评得现金
页:
[1]