6.SQL案例分析：数据透视表
1.问题描述
销售数据表包含了2019年1月1日到2019年6月30日每天的销售情况。
如何通过sql查询语句实现Excel数据透视表功能。
2.初始化脚本
-- 创建销售数据表sales_data
-- saledate表示销售日期，product表示产品名称，channel表示销售渠道，amount表示销售金额
CREATE TABLE sales_data(saledate DATE, product VARCHAR(20), channel VARCHAR(20), amount NUMERIC(10, 2));
-- 插入测试数据
-- 只有Oracle数据库需要执行以下alter语句
-- alter session set nls_date_format = 'YYYY-MM-DD';
INSERT INTO sales_data VALUES ('2019-01-01','桔子','淘宝',1864.00);
INSERT INTO sales_data VALUES ('2019-01-01','桔子','京东',1329.00);
....
有兴趣自行补全
3.思路分析
查询不同产品不同渠道在各个月份销量情况还有合计,使用with rollup 进行每月合计汇总
select
coalesce (sd.product,'总计')as "产品",
coalesce (sd.channel,'--' )as "渠道",
sum(case extract(month from saledate) when 1 then amount end ) as "1月",
sum(case extract(month from saledate) when 2 then amount end ) as "2月",
sum(case extract(month from saledate) when 3 then amount end ) as "3月",
sum(case extract(month from saledate) when 4 then amount end ) as "4月",
sum(case extract(month from saledate) when 5 then amount end ) as "5月",
sum(case extract(month from saledate) when 6 then amount end ) as "6月",
sum(amount) as "合计"
from sales_data as sd
group by product ,channel with rollup
得到如下结果
4.with rollup介绍
1.基本介绍
MySQL 中的 ROLLUP 是一个修饰符，用于生成汇总输出，包括表示超聚合（更高级别）汇总操作的额外行。它使我们能够使用单个查询总结多个分析级别的输出。它主要用于为OLAP（在线分析处理）操作提供支持。
ROLLUP 修饰符只能MySQL中的 GROUP BY 查询一起使用。
2.句法：
以下是使用 ROLLUP 修饰符的语法：
SELECT
column1, column2, column3, ...
FROM
table_name
GROUP BY
column1, column2,... WITH ROLLUP;
3.具体内容
ROLLUP 修饰符是使用 GROUP BY 查询的一个选项，该查询包括用于表示小计的额外字段。这些额外的行称为超聚合行，它是总计行的组合。因此，ROLLUP 修饰符允许我们根据MySQL 中 GROUP BY 子句中指定的列在单个查询中创建多个集合行分组。
如果 GROUP BY 子句有多个列，则 ROLLUP 修饰符具有更复杂的效果。在这种情况下，ROLLUP 修饰符假定在 GROUP BY 子句中指定的列之间存在层次结构。每次列值发生变化时，查询都会在结果末尾生成一个额外的超聚合汇总行。
例如，假设我们在 GROUP BY 子句中指定了三列，如下所示：
GROUP BY c1, c2, c3 with rollup
ROLLUP 修饰符假定层次结构如下：
c1 > c2 > c3
并生成以下分组集：
1. (c1, c2, c3)
2. (c1, c2)
3. (c1)
4. ()
5.Oracle 写法
-- 在oracle的sql语法中
select
coalesce (sd.product,'总计')as "产品",
coalesce (sd.channel,'--' )as "渠道",
sum(case extract(month from saledate) when 1 then amount end ) as "1月",
sum(case extract(month from saledate) when 2 then amount end ) as "2月",
sum(case extract(month from saledate) when 3 then amount end ) as "3月",
sum(case extract(month from saledate) when 4 then amount end ) as "4月",
sum(case extract(month from saledate) when 5 then amount end ) as "5月",
sum(case extract(month from saledate) when 6 then amount end ) as "6月",
sum(amount) as "合计"
from sales_data as sd
group by rollup(product,channel)
