SQL 报表制作和整形( 三 ) _生活百科

文章插图

文章插图
图一图二
解决方案：通过 SUM 聚合函数和 Case 表达式，先将各部门薪水总额转置成一行，然后作为子结果集进行运算。
select DeptNo_10-DeptNo_20 as diff_20_10,DeptNo_10-DeptNo_30 as diff_30_10from (selectsum(case DeptNo when 10 then SAL end) as DeptNo_10, sum(case DeptNo when 20 then SAL end) as DeptNo_20,sum(case DeptNo when 30 then SAL end) as DeptNo_30 from test.emps ) a 7.创建尺寸固定的数据桶
情景：基于员工表中的员工进行分，每组包含5位员工。最终结果集如下图：

文章插图
解决方案：主要要解决的问题是将数据分组，所以要给数据编号，然后划分组。
使用排名函数 row_number 进行排名，然后执行除法运算并将商向上取整，最后的值既是组号。
SELECT row_number() over() 排名,row_number() over() / 5.0 商, ceil(row_number() over() / 5.0) 组号,EName FROM test.emps;

文章插图
8.创建预定数量的桶数
将数据划分到数量固定的几个桶中。这是一种组织分类数据的常见方式，因为在很多分析中，将一个集合分成多个规模相同的集合是第一步。
情景：将员工表中的数据划分到3个组内。如下：

文章插图
解决方案：
1.使用窗函数 ntile，ntile 会将一个集合划分到指定数量的桶中。如果无法均分，就将多出来的元素放到前面的捅中。
SELECT EName,ntile(3) over() 组号 FROM test.emps;2.另一种方法是，对数据进行分组。按顺序将数据放到三个桶中，先将数据编号，然后取余数，余数即组号。最后按照组号排序。
SELECT EName,((row_number() over()) % 3 )+ 1 组号,row_number() over()编号,(row_number() over()) % 3 余数 FROM test.emps order by 组号注意：根据上一个情景和本次情景找到规律。将一个集合划分到固定尺寸的组中时使用求商数，将集合划分到固定组数时使用求余数。
9.创建水平直方图
情景：创建沿水平方向延伸的直方图。以水平直方图的方式显示每个角色的员工数量，在直方图中每个星号表示一个员工。

文章插图
解决方案：方案的关键是，将统计后的数字用 * 字符的形式展示。可以使用字符串函数 lpad 填充生成对应数量的字符串。
SELECT Role,lpad('*',count(*),'*') 数量 FROM test.emps group by Role; 10.创建垂直直方图
情景：以垂直直方图的方式显示每个部门的员工数量，如下：

文章插图
解决方案：从最终结果集看出，首先需要行转列，然后替换字符串。最关键的是需要是按照部门编号分区分组编号，再根据这个编号分组去除空值。
select rn,max(Dept10) Dept10Count,max(Dept20) Dept20Count,max(Dept30)Dept30Countfrom (SELECT row_number() over(partition by DeptNo) rn,case DeptNo when 10 then '*' else ''end as Dept10,case DeptNo when 20 then '*' else ''end as Dept20,case DeptNo when 30 then '*' else ''end as Dept30FROM test.emps order by DeptNo ) a group by rn order by rn desc分拆：
（1）行转列，且替换字符串：
SELECTcase DeptNo when 10 then '*' else ''end as Dept10,case DeptNo when 20 then '*' else ''end as Dept20,case DeptNo when 30 then '*' else ''end as Dept30FROM test.emps order by DeptNo