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

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

作者: 不剪發(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

文章目錄

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

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

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

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

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

select * from sales_data;

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

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

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

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

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

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

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

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

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

ROLLUP選項(xiàng)可以使用UNION合并多個(gè)查詢結(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á)式，類似于臨時(shí)表；關(guān)于通用表表達(dá)式的概念可以參考這篇文章。以上語(yǔ)句正好解釋了ROLLUP選項(xiàng)的作用。

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

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

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

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

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

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

– 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ù)對(duì) NULL 進(jìn)行了轉(zhuǎn)換顯示；CUBE基于分組字段的任意組合進(jìn)行匯總，比ROLLUP獲得的更多維度的統(tǒng)計(jì)信息。

MySQL 目前沒(méi)有提供CUBE選項(xiàng)。以上示例中的分組字段只有 2 個(gè)，可以使用UNION合并多個(gè)查詢結(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選項(xiàng)的作用，也適用于其他數(shù)據(jù)庫(kù)；但是如果分組字段達(dá)到 3 個(gè)以上就比較麻煩。

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

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

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

GROUP BY ROLLUP (product, channel)

實(shí)際上等價(jià)于：

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

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

同樣，對(duì)于CUBE選項(xiàng)：

GROUP BY CUBE (product, channel)

等價(jià)于：

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

而沒(méi)有任何選項(xiàng)的GROUP BY：

GROUP BY product, channel

等價(jià)于：

GROUP BY GROUPING SETS ((product, channel))

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

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

– 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ù)獲取銷售日期對(duì)應(yīng)的季度；如果使用 SQL Server 數(shù)據(jù)庫(kù)，可以換成 datename(q, saledate) 函數(shù)；MySQL 目前沒(méi)有提供CUBE選項(xiàng)。

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

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

其中，產(chǎn)品和渠道出現(xiàn)在行中，可以進(jìn)行展開(kāi)和折疊；日期出現(xiàn)在列中，最后一列是所有月份的匯總；透視表可以通過(guò)將行移動(dòng)到列或?qū)⒘幸苿?dòng)到行，從而查看不同級(jí)別的數(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) “合計(jì)”
from d
group by rollup (product, channel)
order by product, channel desc;

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

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

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