今天給大家準(zhǔn)備的面試題解答的題目如下：
我們同樣先根據(jù)需求構(gòu)造表
create table sale_order(
id int primary key auto_increment comment '訂單編號(hào)',
sale_dt date comment '訂單日期',
user_id int comment '用戶id',
sku_id int comment '商品id',
sale_count int comment '銷售數(shù)量',
price int comment '商品單價(jià)',
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');
按照需求完成代碼書(shū)寫(xiě)
/*
需求1：求每個(gè)月份的銷量 銷售額
月份 銷量 銷售額
2019-01 3 300
2019-02 4 300
2019-03 6 550
*/
-- 根據(jù)月份分組 對(duì)銷量 銷售額求和 這里需要時(shí)間函數(shù)對(duì)時(shí)間格式化
select date_format(sale_dt, '%Y-%m') `月份`,
sum(sale_count) `銷量`,
sum(amount) `銷售額`
from sale_order group by date_format(sale_dt, '%Y-%m');
/*
需求2： 每個(gè)用戶各個(gè)月份的銷售額
user_id 2019-01 2019-02 2019-03
1 200 0 0
2 100 200 0
3 0 100 550
*/
-- 先判斷月份對(duì)應(yīng)的數(shù)據(jù) 是否有銷售額 有就設(shè)置為銷售額 沒(méi)有就設(shè)置為0 最后根據(jù)用戶歸類 銷售額求和
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: 月份之間的累計(jì)銷量 與 累計(jì)銷售額
月份 累計(jì)銷量 累計(jì)銷售額
2019-01 3 300
2019-02 7 600
2019-03 13 1150
*/
-- 關(guān)于累計(jì)的問(wèn)題 我們需要知道sum的應(yīng)用
/*
1. 不在分組中使用 獲取指定字段所有行的求和
2. 在分組中 是按照分類字段的值 將統(tǒng)計(jì)到的行中對(duì)應(yīng)的字段的值求和 【分組會(huì)折疊 把折疊的行中指定字段的數(shù)據(jù) 求和】
3. sum還可以在窗口函數(shù)來(lái)用
sum(字段名) over(partition by 歸類字段)
--- 如果是這樣使用的話 在查詢中增加一列 根據(jù)歸類字段把數(shù)據(jù)整合并行排放的多行中
顯示的是根據(jù)歸類字段對(duì)字段的求和
不會(huì)折疊數(shù)據(jù) 這個(gè)因?yàn)闆](méi)有排序 只有歸類 求的就是歸類完成之后 該字段的對(duì)應(yīng)的所有行求和的結(jié)果
4. sum還可以在窗口函數(shù)來(lái)用
sum(字段名) over(partition by 歸類字段 order by 排序字段)
--- 如果是這樣使用的話 在查詢中增加一列 根據(jù)歸類字段把數(shù)據(jù)整合在并行排放的多行中
并按照排序字段把數(shù)據(jù)排序好， 完成的是該歸類字段下 當(dāng)前行以上數(shù)據(jù)的累加和
*/
-- 在需求1的基礎(chǔ)上 按照年歸類 按照月份升序排序 使用sum窗口函數(shù)進(jìn)行累計(jì)
select `月份`,
sum(`銷量`) over(partition by substr(`月份`, 1, 4) order by substr(`月份`, 6)) as 累計(jì)銷量,
sum(`銷售額`) over(partition by substr(`月份`, 1, 4) order by substr(`月份`, 6)) as 累計(jì)銷售額
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: 每個(gè)產(chǎn)品的銷量與銷售額 并給出排名
*/
-- 連接兩表 按照商品分組 求和
select products.*, sum(sale_count) 銷量, sum(amount) 銷售額
from sale_order join products
on sale_order.sku_id = products.sku_id
group by sku_id;
更多關(guān)于python培訓(xùn)的問(wèn)題，歡迎咨詢千鋒教育在線名師。千鋒教育擁有多年IT培訓(xùn)服務(wù)經(jīng)驗(yàn)，采用全程面授高品質(zhì)、高體驗(yàn)培養(yǎng)模式，擁有國(guó)內(nèi)一體化教學(xué)管理及學(xué)員服務(wù)，助力更多學(xué)員實(shí)現(xiàn)高薪夢(mèng)想。
京公網(wǎng)安備 11010802030320號(hào)