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

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

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;

更多關于python培訓的問題，歡迎咨詢千鋒教育在線名師。千鋒教育擁有多年IT培訓服務經驗，采用全程面授高品質、高體驗培養模式，擁有國內一體化教學管理及學員服務，助力更多學員實現高薪夢想。