在线午夜精品自拍小视频_无码av无码专区线_亚洲无码精品人妻_人人澡欧美一区

某電商銷售數(shù)據(jù)分析 SQL 面試題解析

作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學(xué)，十多年數(shù)據(jù)庫(kù)管理與開(kāi)發(fā)經(jīng)驗(yàn)，目前在一家全球性的金融公司從事數(shù)據(jù)庫(kù)架構(gòu)設(shè)計(jì)。CSDN學(xué)院簽約講師以及GitChat專欄作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net

文章目錄

        題目描述
        問(wèn)題 1
            解析
        問(wèn)題 2
            解析
        問(wèn)題 3
            解析
        問(wèn)題 4
            解析
        總結(jié)

本文基于微信群里的一個(gè)問(wèn)題，感謝這位朋友提供的分享。

題目描述

假設(shè)某電商銷售數(shù)據(jù)有如下幾張表：

其中，

month：date 類型，存儲(chǔ)的是每個(gè)月的第一天；
bid：對(duì)應(yīng) Brand.bid；
cid：對(duì)應(yīng) Category.cid；
paltform：對(duì)應(yīng)不同的電商平臺(tái)，有 2 種：1，2；
sales：銷售額；
主鍵為 (month, paltform, bid, cid)，一行數(shù)據(jù)對(duì)應(yīng)一個(gè)月一個(gè)平臺(tái)一個(gè) bid 的一個(gè) cid 的銷售額。

基于上面的描述，我們可以創(chuàng)建以下示例表和模擬數(shù)據(jù)（使用 MySQL 數(shù)據(jù)庫(kù)）：

create table brand(bid int auto_increment primary key, name varchar(50));
insert into brand(name) values (‘品牌1’);
insert into brand(name) values (‘品牌2’);
insert into brand(name) values (‘品牌3’);
insert into brand(name) values (‘品牌4’);
insert into brand(name) values (‘品牌5’);

create table category(cid int auto_increment primary key, name varchar(10));
insert into category(name) values (‘食品’);
insert into category(name) values (‘手機(jī)’);
insert into category(name) values (‘服飾’);
insert into category(name) values (‘圖書’);
insert into category(name) values (‘電腦’);

create table monthlysales(month date, bid int, cid int, platform int,sales int);
alter table monthlysales add constraint pk_monthlysales primary key(month, platform, bid, cid);

insert into monthlysales
with recursive dt as (
select date ‘2019-01-01’ as v
union all
select v + interval ‘1’ month from dt where v < date ‘2019-12-01’
),
platform(pid) as (
values row(1), row(2)
)
select v, bid, cid, pid, 1000 + round((rand(1) * 500))
from dt
cross join brand
cross join category
cross join platform;

為了生成示例數(shù)據(jù)，我們使用了遞歸形式的通用表表達(dá)式（WITH子句），相關(guān)語(yǔ)法可以參考這篇文章。另外，rand(1) 函數(shù)確保了結(jié)果的可復(fù)現(xiàn)。
問(wèn)題 1

對(duì)于指定的 cid 范圍（cid 列表：1，3，5），查詢 2019 年每個(gè)平臺(tái)上每個(gè) bid 對(duì)應(yīng)每個(gè) cid 的累計(jì)銷售額，輸出格式如下：

請(qǐng)寫出 SQL。
解析

這個(gè)題目比較簡(jiǎn)單，就是按照品牌、品類以及平臺(tái)分組再加上 sum 函數(shù)統(tǒng)計(jì)銷售額；同時(shí)關(guān)聯(lián)其他表獲取品牌和品類名稱。

select b.bid, b.name brand_name, c.cid, c.name category_name, s.platform, sum(s.sales) total_sales
from monthlysales s
join brand b on (b.bid = s.bid)
join category c on (c.cid = s.cid)
where b.bid in (1, 3, 5)
and s.month between date ‘2019-01-01’ and date ‘2019-12-01’
group by b.bid, b.name, c.cid, c.name, s.platform;

…

對(duì)于 2019 年的判斷，最好不要使用 year(month) 函數(shù)，因?yàn)檫@樣會(huì)導(dǎo)致索引失效。另外，關(guān)于各種數(shù)據(jù)庫(kù)中的分組匯總和聚合函數(shù)可以參考這篇文章。
問(wèn)題 2

查詢 2019 年有 5 個(gè)以上（包含 5 個(gè)）不同 cid 的單月單平臺(tái)銷售額大于等于 1480 的品牌列表，以及對(duì)應(yīng)的不同 cid 數(shù)量，輸出格式如下：

解析

這個(gè)問(wèn)題和上面的問(wèn)題差不多，主要是按照品牌分組統(tǒng)計(jì)；但是在分組統(tǒng)計(jì)之后，還需要過(guò)濾一下統(tǒng)計(jì)的數(shù)量。

select b.bid, b.name brand_name, count(distinct s.cid) cid_count
from monthlysales s
join brand b on (b.bid = s.bid)
where s.month between date ‘2019-01-01’ and date ‘2019-12-01’
and s.sales >= 1480
group by b.bid, b.name
having cid_count >= 5;

其中，count() 函數(shù)用于統(tǒng)計(jì)各種品牌單月在單平臺(tái)上不同 cid 的數(shù)量；having 最終返回?cái)?shù)量大于等于 5 的品牌。
問(wèn)題 3

查詢 2019 年只在平臺(tái) 1 上有銷售額的品牌中（排除平臺(tái)為 2 時(shí)銷售累計(jì)額大于 0 的品牌），平臺(tái) 1 累計(jì)銷售額最大的 Top 3 品牌以及對(duì)應(yīng)的銷售額，輸出格式如下：

解析

同樣是先進(jìn)行分組統(tǒng)計(jì)，獲取每個(gè)品牌在平臺(tái) 1 上的累計(jì)銷售額；然后加上排序和數(shù)量限定操作返回 Top 3 結(jié)果。

select b.bid, b.name brand_name, sum(s.sales) total_sales_p1
from monthlysales s
join brand b on (b.bid = s.bid)
where s.month between date ‘2019-01-01’ and date ‘2019-12-01’
and s.platform = 1
group by b.bid, b.name
having total_sales_p1 > 0
order by total_sales_p1 desc
limit 3;

其中，ORDER BY用于按照累計(jì)銷售額從高到低進(jìn)行排序；LIMIT用于返回前 3 條記錄。
問(wèn)題 4

查詢 2019 年在兩個(gè)平臺(tái)中分別同時(shí)都能進(jìn)入銷售額 Top 3 的品牌以及對(duì)應(yīng)的全平臺(tái)累計(jì)銷售額，輸出格式如下：

解析

這個(gè)問(wèn)題至少有兩種解決辦法：基于問(wèn)題 3 中的解決方法分別獲取平臺(tái) 1 和平臺(tái) 2 上的 Top 3 品牌，然后使用一個(gè)連接查詢；或者使用 MySQL 8.0 中的窗口函數(shù)。

第一種方法的實(shí)現(xiàn)如下：

select top3_p1.bid, top3_p1.brand_name, top3_p1.total_sales_p1 + top3_p2.total_sales_p2 as total_sales_all
from (
select b.bid, b.name brand_name, sum(s.sales) total_sales_p1
from monthlysales s
join brand b on (b.bid = s.bid)
where s.month between date ‘2019-01-01’ and date ‘2019-12-01’
and s.platform = 1
group by b.bid, b.name
having total_sales_p1 > 0
order by total_sales_p1 desc
limit 3) top3_p1
join (
select b.bid, b.name brand_name, sum(s.sales) total_sales_p2
from monthlysales s
join brand b on (b.bid = s.bid)
where s.month between date ‘2019-01-01’ and date ‘2019-12-01’
and s.platform = 2
group by b.bid, b.name
having total_sales_p2 > 0
order by total_sales_p2 desc
limit 3) top3_p2
on (top3_p1.bid = top3_p2.bid)
order by total_sales_all desc;

其中，top3_p1 代表了平臺(tái) 1 上的 Top 3 品牌和累計(jì)銷售額；op3_p2 代表了平臺(tái) 2 上的 Top 3 品牌和累計(jì)銷售額。

如果使用的 MySQL 8.0 版本，我們可以利用窗口函數(shù)計(jì)算排名：

with sales2019 as (
select bid, platform, sum(sales) sales
from monthlysales
where month between date ‘2019-01-01’ and date ‘2019-12-01’
group by bid, platform
),
top3 as (
select * from (
select bid, platform, sum(sales) over (partition by bid) total_sales_all,
rank() over (partition by platform order by sales desc) as rk
from sales2019
) t where rk <= 3
)
select p1.bid, b.name brand_name, p1.total_sales_all
from top3 p1
join top3 p2 on (p1.bid = p2.bid)
join brand b on (b.bid = p1.bid)
where p1.platform = 1
and p2.platform = 2;

其中，sales2019 代表了 2019 年各種品牌在不同平臺(tái)上的銷售額；top3 基于 sales2019 計(jì)算不同平臺(tái)上的 Top 3 品牌和累計(jì)銷售額；這里使用了兩個(gè)窗口函數(shù)（OVER子句是窗口函數(shù)的標(biāo)識(shí)），sum() 用于獲取品牌的累計(jì)銷售額，rank() 用于計(jì)算不同平臺(tái)上的銷售額排名。

除此之外，WITH語(yǔ)句比第一種方法語(yǔ)義上更加清晰；而且從性能角度來(lái)說(shuō)也會(huì)更好，因?yàn)樗恍枰L問(wèn) monthlysales 表一次。
總結(jié)

總的來(lái)說(shuō)，這幾道題目都是高頻的 SQL 數(shù)據(jù)分析問(wèn)題。依次考察了GROUP BY分組加聚合函數(shù)、HAVING過(guò)濾和DISTINCT去重、ORDER BY排序加上LIMIT子句實(shí)現(xiàn) Top-N 排行榜，以及窗口函數(shù)的使用。