用戶累計銷量面試題

來源：千鋒教育

發布人：wjy

時間： 2022-06-07 13:35:00 1654580100

今天給大家準備的面試題解答的題目如下：

我們同樣先根據需求構造表

create table sale_order(
id int primary key auto_increment comment '訂單編號',
sale_dt date comment '訂單日期',
user_id int comment '用戶id',
sku_id int comment '商品id',
sale_count int comment '銷售數量',
price int comment '商品單價',
amount int comment '銷售金額'
);

insert into sale_order values
(1, '2019-01-01', 1, 1001, 2, 100, 200),
(2, '2019-01-02', 2, 1001, 1, 100, 100),
(3, '2019-02-10', 3, 1002, 2, 50, 100),
(4, '2019-02-11', 2, 1001, 2, 100, 200),
(5, '2019-03-01', 3, 1001, 1, 100, 100),
(6, '2019-03-01', 3, 1002, 1, 50, 50),
(7, '2019-03-02', 3, 1003, 4, 100, 400);
select * from sale_order;

create table products(
sku_id int primary key comment '商品id',
sku_name varchar(100) comment '商品名稱'
);

insert into products values(1001, '商品1'), (1002, '商品2'),(1003, '商品3');

按照需求完成代碼書寫

/*
需求1：求每個月份的銷量銷售額
  月份銷量銷售額
2019-01    3        300
2019-02    4        300
2019-03    6        550
*/
-- 根據月份分組對銷量銷售額求和這里需要時間函數對時間格式化
select date_format(sale_dt, '%Y-%m') `月份`,
sum(sale_count) `銷量`,
sum(amount) `銷售額`
from sale_order group by date_format(sale_dt, '%Y-%m');

/*
需求2：每個用戶各個月份的銷售額
user_id 2019-01      2019-02    2019-03
  1           200           0          0
  2           100           200        0
  3           0             100        550
*/
-- 先判斷月份對應的數據是否有銷售額有就設置為銷售額沒有就設置為0 最后根據用戶歸類銷售額求和
select user_id,
sum(if(date_format(sale_dt, '%Y-%m') = '2019-01', amount, 0)) `2019-01`,
sum(if(date_format(sale_dt, '%Y-%m') = '2019-02', amount, 0)) `2019-02`,
sum(if(date_format(sale_dt, '%Y-%m') = '2019-03', amount, 0)) `2019-03`
from sale_order
group by user_id;

/*
需求3: 月份之間的累計銷量與累計銷售額
  月份累計銷量累計銷售額
2019-01    3        300
2019-02    7        600
2019-03    13       1150
*/
-- 關于累計的問題我們需要知道sum的應用
/*
1. 不在分組中使用獲取指定字段所有行的求和
2. 在分組中是按照分類字段的值將統計到的行中對應的字段的值求和【分組會折疊把折疊的行中指定字段的數據求和】
3. sum還可以在窗口函數來用
sum(字段名) over(partition by 歸類字段)
--- 如果是這樣使用的話在查詢中增加一列根據歸類字段把數據整合并行排放的多行中
顯示的是根據歸類字段對字段的求和
不會折疊數據這個因為沒有排序只有歸類求的就是歸類完成之后該字段的對應的所有行求和的結果
4. sum還可以在窗口函數來用
sum(字段名) over(partition by 歸類字段 order by 排序字段)
--- 如果是這樣使用的話在查詢中增加一列根據歸類字段把數據整合在并行排放的多行中
并按照排序字段把數據排序好，完成的是該歸類字段下當前行以上數據的累加和
*/
-- 在需求1的基礎上按照年歸類按照月份升序排序使用sum窗口函數進行累計
select `月份`,
sum(`銷量`) over(partition by substr(`月份`, 1, 4) order by substr(`月份`, 6)) as 累計銷量,
sum(`銷售額`) over(partition by substr(`月份`, 1, 4) order by substr(`月份`, 6)) as 累計銷售額
from (select date_format(sale_dt, '%Y-%m') `月份`,
sum(sale_count) `銷量`,
sum(amount) `銷售額`
from sale_order group by date_format(sale_dt, '%Y-%m')) as t;

/*
需求4: 每個產品的銷量與銷售額并給出排名
*/
-- 連接兩表按照商品分組求和
select products.*, sum(sale_count) 銷量, sum(amount) 銷售額
from sale_order join products
on sale_order.sku_id = products.sku_id
group by sku_id;