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

實(shí)戰(zhàn) SQL：銷售數(shù)據(jù)的小計/合計/總計以及數(shù)據(jù)透視表

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

文章目錄

        小計、合計與總計
        多維度交叉統(tǒng)計
        自定義統(tǒng)計維度
        數(shù)據(jù)透視表
        總結(jié)
1
2
3
4
5

學(xué)習(xí)過 SQL 的人都知道，使用聚合函數(shù)（AVG、SUM、COUNT、MIN/MAX 等）和分組操作（GROUP BY）可以對數(shù)據(jù)進(jìn)行基本的統(tǒng)計分析，例如統(tǒng)計公司員工的人數(shù)、每個部門的平均月薪等。如果想要回顧這些基礎(chǔ)概念，可以參考這篇文章。

不過 SQL 不僅僅能夠進(jìn)行這些基本的分組匯總，它還提供了許多高級的統(tǒng)計分析功能。本文就來介紹一下如何使用 SQL 實(shí)現(xiàn)銷售數(shù)據(jù)的小計、合計、總計以及多維度交叉統(tǒng)計和數(shù)據(jù)透視表。我們首先列出這些功能在主流數(shù)據(jù)庫中的支持情況：

• 參考下文中的具體討論。本文使用的示例數(shù)據(jù)可以點(diǎn)此下載，如果沒有特殊說明，以下示例適用于上面的 5 種數(shù)據(jù)庫。
  小計、合計與總計

我們先查看一下示例表中的數(shù)據(jù)：

select * from sales_data;

這是一個模擬的銷售數(shù)據(jù)，記錄了不同日期（2019-01-01 到 2019-06-30）、三種不同產(chǎn)品、三種不同渠道的銷量情況。

以下語句使用GROUP BY統(tǒng)計了三種不同產(chǎn)品各自的總銷量：

select product, sum(amount)
from sales_data
group by product;

顯然，還可以編寫 SQL 語句統(tǒng)計三種不同產(chǎn)品在不同渠道各自的銷量合計、所以產(chǎn)品的銷量總計等。但是如何一次獲取這些按照不同維度進(jìn)行統(tǒng)計的結(jié)果呢？我們可以使用GROUP BY的第一個擴(kuò)展選項：ROLLUP。例如：

– Oracle、SQL Server、PostgreSQL
select product “產(chǎn)品”, channel “渠道”, sum(amount) “銷量”
from sales_data
group by rollup (product, channel);

其中，ROLLUP表示先按照 (product, channel) 的組合計算不同產(chǎn)品、不同渠道的銷量小計，然后按照計算不同產(chǎn)品（product）、所有渠道的銷量和計（結(jié)果中的 channel 字段顯示為 NULL），最后計算所有產(chǎn)品、所有渠道的銷量總計（結(jié)果中的 product 和 channel 字段都為 NULL）。

如果使用 MySQL 數(shù)據(jù)庫，ROLLUP的使用略有不同：

– MySQL
select product, channel, sum(amount)
from sales_data
group by product, channel with rollup;

MySQL 在分組字段之后使用with rollup選項，查詢的結(jié)果與其他數(shù)據(jù)庫相同。

ROLLUP選項可以使用UNION合并多個查詢結(jié)果進(jìn)行模擬：

with d as (
select product, channel , sum(amount) amount
from sales_data
group by product, channel
)
select product “產(chǎn)品”, channel “渠道”, amount “銷量” from d
union all
select product, NULL, sum(amount) from d group by product
union all
select NULL, NULL, sum(amount) from d;

其中，WITH表示定義通用表表達(dá)式，類似于臨時表；關(guān)于通用表表達(dá)式的概念可以參考這篇文章。以上語句正好解釋了ROLLUP選項的作用。

??GROUP BY子句的ROLLUP選項是一種按照層次從下往上依次匯總的過程，需要匯總 N + 1 個維度，N 是分組字段的個數(shù)。
1

多維度交叉統(tǒng)計

如果我們的銷量報表需要統(tǒng)計以下信息：

不同產(chǎn)品、不同渠道的銷量小計；
不同產(chǎn)品、所有渠道的銷量合計；
所有產(chǎn)品、不同渠道的銷量合計；
所有產(chǎn)品、所有渠道的銷量總計。
1
2
3
4

由于ROLLUP選項是按照分組字段的順序依次往上匯聚，(product, channel) 無法獲取所有產(chǎn)品、不同渠道的銷量合計，(channel, product) 又無法獲取不同產(chǎn)品、所有渠道的銷量合計。雖然可以查詢兩次然后去除重復(fù)結(jié)果，但是不方便；況且當(dāng)我們的分組字段增加到 3 個或者 4 個時，組合情況更多。

為此，我們可以使用GROUP BY的第二個擴(kuò)展選項：CUBE。以下語句可以實(shí)現(xiàn)上面的統(tǒng)計需求：

– Oracle、SQL Server、PostgreSQL
select coalesce(product, ‘【全部產(chǎn)品】’) “產(chǎn)品”, coalesce(channel, ‘【所有渠道】’) “渠道”, sum(amount) “銷量”
from sales_data
group by cube (product,channel)
order by product, channel;

為了更好地查看結(jié)果，我們使用 coalesce 函數(shù)對 NULL 進(jìn)行了轉(zhuǎn)換顯示；CUBE基于分組字段的任意組合進(jìn)行匯總，比ROLLUP獲得的更多維度的統(tǒng)計信息。

MySQL 目前沒有提供CUBE選項。以上示例中的分組字段只有 2 個，可以使用UNION合并多個查詢結(jié)果進(jìn)行模擬：

with d as (
select product, channel , sum(amount) amount
from sales_data
group by product, channel
)
select product “產(chǎn)品”, channel “渠道”, amount “銷量” from d
union all
select product, NULL, sum(amount) from d group by product
union all
select NULL, channel, sum(amount) from d group by channel
union all
select NULL, NULL, sum(amount) from d;

這種方法正好解釋了CUBE選項的作用，也適用于其他數(shù)據(jù)庫；但是如果分組字段達(dá)到 3 個以上就比較麻煩。

??GROUP BY子句的CUBE選項是一種按照各種層次組合匯總的過程，需要匯總 2 的 N 次方個維度，N 是分組字段的個數(shù)。
1

自定義統(tǒng)計維度

ROLLUP和CUBE都是按照預(yù)定義好的維度進(jìn)行數(shù)據(jù)統(tǒng)計，SQL 還提供了第三GROUP BY個選項：GROUPING SETS。它允許我們指定自定義的分組集，例如：

GROUP BY ROLLUP (product, channel)

實(shí)際上等價于：

GROUP BY GROUPING SETS ((product, channel), (product), ())

其中，每一個維度都位于一對括號之內(nèi)。(product, channel) 表示按照產(chǎn)品和渠道的組合進(jìn)行統(tǒng)計、(product) 表示按照產(chǎn)品進(jìn)行統(tǒng)計、() 表示所有數(shù)據(jù)進(jìn)行統(tǒng)計。

同樣，對于CUBE選項：

GROUP BY CUBE (product, channel)

等價于：

GROUP BY GROUPING SETS ((product, channel), (product), (channel), ())

而沒有任何選項的GROUP BY：

GROUP BY product, channel

等價于：

GROUP BY GROUPING SETS ((product, channel))

注意，(product, channel) 需要使用括號包含；GROUP BY GROUPING SETS (product, channel)統(tǒng)計的是產(chǎn)品維度的數(shù)據(jù)和渠道維度的數(shù)據(jù)。

以下示例用于計算按照季度、產(chǎn)品、渠道以及不同季度不同渠道的銷量統(tǒng)計：

– Oracle、PostgreSQL
select coalesce(to_char(saledate,‘Q’),’【半年】’) “季度”, coalesce(product, ‘【全部產(chǎn)品】’) “產(chǎn)品”,
coalesce(channel, ‘【所有渠道】’) “渠道”, sum(amount) “銷量”
from sales_data
group by grouping sets ((to_char(saledate,‘Q’)), (product), (channel), (to_char(saledate,‘Q’), channel));

我們使用了 to_char(saledate,‘Q’) 函數(shù)獲取銷售日期對應(yīng)的季度；如果使用 SQL Server 數(shù)據(jù)庫，可以換成 datename(q, saledate) 函數(shù)；MySQL 目前沒有提供CUBE選項。

從上面的示例可以看出，GROUPING SETS可以實(shí)現(xiàn)任意的維度統(tǒng)計；ROLLUP和CUBE都屬于預(yù)定義的特定統(tǒng)計維度。
數(shù)據(jù)透視表

在 Excel 中有一個分析功能叫做數(shù)據(jù)透視表（Pivot Table），如下圖所示：

其中，產(chǎn)品和渠道出現(xiàn)在行中，可以進(jìn)行展開和折疊；日期出現(xiàn)在列中，最后一列是所有月份的匯總；透視表可以通過將行移動到列或?qū)⒘幸苿拥叫校瑥亩榭床煌墑e的數(shù)據(jù)匯總。

使用 SQL 同樣可以實(shí)現(xiàn)上面的數(shù)據(jù)透視表：

– Oracle、PostgreSQL
with d(saledate, product, channel, amount) as (
select extract(month from saledate), product, channel, sum(amount)
from sales_data
group by extract(month from saledate), product, channel
)
select coalesce(product, ‘【全部產(chǎn)品】’) “產(chǎn)品”, coalesce(channel, ‘【所有渠道】’) “渠道”,
sum(case saledate when 1 then amount else 0 end) “1月”,
sum(case saledate when 2 then amount else 0 end) “2月”,
sum(case saledate when 3 then amount else 0 end) “3月”,
sum(case saledate when 4 then amount else 0 end) “4月”,
sum(case saledate when 5 then amount else 0 end) “5月”,
sum(case saledate when 6 then amount else 0 end) “6月”,
sum(amount) “合計”
from d
group by rollup (product, channel)
order by product, channel desc;

首先，使用WITH獲得了按照月份、產(chǎn)品、渠道統(tǒng)計的銷量；extract(month from saledate) 函數(shù)用于提取銷售日期中的月份；然后使用 SUM 函數(shù)和CASE表達(dá)式將月份從行轉(zhuǎn)換為列，最終得到了一個數(shù)據(jù)透視表。

MySQL 中需要使用with rollup；SQL Server 中需要使用 datepart(month, saledate) 函數(shù)。
總結(jié)

Excel 中的數(shù)據(jù)透視表通過展開、折疊、篩選、行列轉(zhuǎn)換等操作得到不同層次和視角的數(shù)據(jù)小計/合計/總計。這些功能在數(shù)據(jù)庫的 OLAP（在線分析處理系統(tǒng)）中被稱為下鉆（Drill down）、上鉆(Roll up)、切塊（Dicing）、切片（Slicing）、旋轉(zhuǎn)（Pivot）等，利用 SQL 中的聚合函數(shù)、GROUP BY子句的ROLLUP、CUBE和GROUPING SETS選項以及CASE表達(dá)式實(shí)現(xiàn)，而且更加靈活。