文章目录
使用 CASE 表达式和分组聚合
使用 PIVOT 运算符
创建动态透视表
总结
数据透视表(Pivot Table)是 Excel 中一个非常实用的分析功能,可以用于实现复杂的数据分类汇总和对比分析,是数据分析师和运营人员必备技能之一。前文已经介绍了 MySQL/MariaDB 和 Oracle 中的数据透视表实现,今天我们来谈谈如何在 Microsoft SQL Server 中实现相同的功能。
📝本文使用的示例数据可以点此下载。
使用 CASE 表达式和分组聚合
实现数据行转列的一个通用方法就是利用 CASE 条件表达式和分组聚合操作。首先使用以下 GROUP BY 子句对销售数据进行分类汇总:
select coalesce(product, '【全部产品】') "产品",
coalesce(channel, '【所有渠道】') "渠道",
coalesce(format(saledate, 'yyyyMM'), '【所有月份】') "月份",
sum(amount) "销量"
from sales_data
group by rollup (product,channel,format(saledate, 'yyyyMM'));
以上语句按照产品、渠道以及月份进行汇总;rollup 选项用于生成不同层次的小计、合计以及总计;coalesce 函数用于将汇总行中的 NULL 值显示为相应的信息。该查询返回的结果如下:
产品 |渠道 |月份 |销量 |
---------|---------|-----------|-------|
桔子 |店面 |201901 | 41306|
桔子 |店面 |201902 | 37906|
桔子 |店面 |201903 | 48866|
桔子 |店面 |201904 | 48673|
桔子 |店面 |201905 | 58998|
桔子 |店面 |201906 | 58931|
桔子 |店面 |【所有月份】| 294680|
桔子 |京东 |201901 | 41289|
桔子 |京东 |201902 | 43913|
桔子 |京东 |201903 | 49803|
桔子 |京东 |201904 | 49256|
桔子 |京东 |201905 | 64889|
桔子 |京东 |201906 | 62649|
桔子 |京东 |【所有月份】| 311799|
桔子 |淘宝 |201901 | 43488|
桔子 |淘宝 |201902 | 37598|
桔子 |淘宝 |201903 | 48621|
桔子 |淘宝 |201904 | 49919|
桔子 |淘宝 |201905 | 58530|
桔子 |淘宝 |201906 | 64626|
桔子 |淘宝 |【所有月份】| 302782|
桔子 |【所有渠道】|【所有月份】| 909261|
...
香蕉 |【所有渠道】|【所有月份】| 925369|
【全部产品】|【所有渠道】|【所有月份】|2771682|
接下来我们将数据按照不同月份显示为不同的列,也就是将行转换为列,这个功能可以使用 CASE 表达式实现:
select coalesce(product, '【全部产品】') "产品", coalesce(channel, '【所有渠道】') "渠道",
sum(case format(saledate, 'yyyyMM') when '201901' then amount else 0 end) "一月",
sum(case format(saledate, 'yyyyMM') when '201902' then amount else 0 end) "二月",
sum(case format(saledate, 'yyyyMM') when '201903' then amount else 0 end) "三月",
sum(case format(saledate, 'yyyyMM') when '201904' then amount else 0 end) "四月",
sum(case format(saledate, 'yyyyMM') when '201905' then amount else 0 end) "五月",
sum(case format(saledate, 'yyyyMM') when '201906' then amount else 0 end) "六月",
sum(amount) "总计"
from sales_data
group by rollup (product, channel);
产品 |渠道 |一月 |二月 |三月 |四月 |五月 |六月 |总计 |
----------|----------|------|------|------|------|------|------|-------|
桔子 |店面 | 41306| 37906| 48866| 48673| 58998| 58931| 294680|
桔子 |京东 | 41289| 43913| 49803| 49256| 64889| 62649| 311799|
桔子 |淘宝 | 43488| 37598| 48621| 49919| 58530| 64626| 302782|
桔子 |【所有渠道】|126083|119417|147290|147848|182417|186206| 909261|
苹果 |店面 | 43845| 40539| 44909| 55646| 56771| 64933| 306643|
苹果 |京东 | 38269| 40593| 56552| 56662| 64493| 62045| 318614|
苹果 |淘宝 | 42969| 43289| 48769| 58052| 58872| 59844| 311795|
苹果 |【所有渠道】|125083|124421|150230|170360|180136|186822| 937052|
香蕉 |店面 | 41210| 39420| 50884| 52085| 60249| 67597| 311445|
香蕉 |京东 | 36879| 36981| 51748| 54801| 64936| 60688| 306033|
香蕉 |淘宝 | 42468| 41955| 52780| 54971| 56504| 59213| 307891|
香蕉 |【所有渠道】|120557|118356|155412|161857|181689|187498| 925369|
【全部产品】|【所有渠道】|371723|362194|452932|480065|544242|560526|2771682|
第一个 SUM 函数中的 CASE 表达式只汇总 201901 月份的销量,其他月份销量设置为 0;后面的 SUM 函数依次类推,得到了每个月的销量汇总和所有月份的总计。
📝使用 CASE 条件表达式和分组聚合的方法也适用于其他数据库。
使用 PIVOT 运算符
Microsoft SQL Server 为数据的行列转换提供了专用的 PIVOT 和 UNPIVOT 运算符。其中,PIVOT 用于将行转化为列。例如:
with d(saledate, product, channel, amount) as (
select format(saledate, 'yyyyMM'),
product,
channel,
amount
from sales_data
)
select *
from d
pivot (
sum(amount)
for saledate
in ([201901], [201902], [201903], [201904], [201905], [201906])
) as pt
order by product;
首先通过 with 子句对数据进行初步处理;然后利用 pivot 进行行转列,包括 3 个部分;sum(amount) 是需要汇总的数据,for saledate 指定了需要转换成多个列的字段,in 列出了转换为列的数据值和转换之后的字段名。
PRODUCT |CHANNEL |'201901'|'201902'|'201903'|'201904'|'201905'|'201906'|
---------|--------|--------|--------|--------|--------|--------|--------|
桔子 |店面 | 41306| 37906| 48866| 48673| 58998| 58931|
桔子 |京东 | 41289| 43913| 49803| 49256| 64889| 62649|
桔子 |淘宝 | 43488| 37598| 48621| 49919| 58530| 64626|
苹果 |店面 | 43845| 40539| 44909| 55646| 56771| 64933|
苹果 |京东 | 38269| 40593| 56552| 56662| 64493| 62045|
苹果 |淘宝 | 42969| 43289| 48769| 58052| 58872| 59844|
香蕉 |店面 | 41210| 39420| 50884| 52085| 60249| 67597|
香蕉 |京东 | 36879| 36981| 51748| 54801| 64936| 60688|
香蕉 |淘宝 | 42468| 41955| 52780| 54971| 56504| 59213|
接下来我们还需要增加一个总计行和总计列,为此可以先将 sales_data 数据进行分组统计然后再使用 PIVOT 进行转换: