【SQL开发实战技巧】系列（三十四）：数仓报表场景☞如何对数据分级并行转为列
系列文章目录
【SQL开发实战技巧】系列（一）:关于SQL不得不说的那些事
【SQL开发实战技巧】系列（二）：简单单表查询
【SQL开发实战技巧】系列（三）：SQL排序的那些事
【SQL开发实战技巧】系列（四）：从执行计划讨论UNION ALL与空字符串&UNION与OR的使用注意事项
【SQL开发实战技巧】系列（五）：从执行计划看IN、EXISTS 和 INNER JOIN效率，我们要分场景不要死记网上结论
【SQL开发实战技巧】系列（六）：从执行计划看NOT IN、NOT EXISTS 和 LEFT JOIN效率，记住内外关联条件不要乱放
【SQL开发实战技巧】系列（七）：从有重复数据前提下如何比较出两个表中的差异数据及对应条数聊起
【SQL开发实战技巧】系列（八）：聊聊如何插入数据时比约束更灵活的限制数据插入以及怎么一个insert语句同时插入多张表
【SQL开发实战技巧】系列（九）：一个update误把其他列数据更新成空了？Merge改写update！给你五种删除重复数据的写法！
【SQL开发实战技巧】系列（十）：从拆分字符串、替换字符串以及统计字符串出现次数说起
【SQL开发实战技巧】系列（十一）：拿几个案例讲讲translate|regexp_replace|listagg|wmsys.wm_concat|substr|regexp_substr常用函数
【SQL开发实战技巧】系列（十二）：三问(如何对字符串字母去重后按字母顺序排列字符串？如何识别哪些字符串中包含数字？如何将分隔数据转换为多值IN列表?)
【SQL开发实战技巧】系列（十三）：讨论一下常用聚集函数&通过执行计划看sum()over()对员工工资进行累加
【SQL开发实战技巧】系列（十四）：计算消费后的余额&计算银行流水累计和&计算各部门工资排名前三位的员工
【SQL开发实战技巧】系列（十五）：查找最值所在行数据信息及快速计算总和百之max/min() keep() over()、fisrt_value、last_value、ratio_to_report
【SQL开发实战技巧】系列（十六）：数据仓库中时间类型操作（初级）日、月、年、时、分、秒之差及时间间隔计算
【SQL开发实战技巧】系列（十七）：数据仓库中时间类型操作（初级）确定两个日期之间的工作天数、计算—年中周内各日期出现次数、确定当前记录和下一条记录之间相差的天数
【SQL开发实战技巧】系列（十八）：数据仓库中时间类型操作（进阶）INTERVAL、EXTRACT以及如何确定一年是否为闰年及周的计算
【SQL开发实战技巧】系列（十九）：数据仓库中时间类型操作（进阶）如何一个SQL打印当月或一年的日历？如何确定某月内第一个和最后—个周内某天的日期？
【SQL开发实战技巧】系列（二十）：数据仓库中时间类型操作（进阶）获取季度开始结束时间以及如何统计非连续性时间的数据
【SQL开发实战技巧】系列（二十一）：数据仓库中时间类型操作（进阶）识别重叠的日期范围，按指定10分钟时间间隔汇总数据
【SQL开发实战技巧】系列（二十二）：数仓报表场景☞ 从分析函数效率一定快吗聊一聊结果集分页和隔行抽样实现方式
【SQL开发实战技巧】系列（二十三）：数仓报表场景☞ 如何对数据排列组合去重以及通过如何找到包含最大值和最小值的记录这个问题再次用执行计划给你证明分析函数性能不一定高
【SQL开发实战技巧】系列（二十四）：数仓报表场景☞通过案例执行计划详解”行转列”,”列转行”是如何实现的
【SQL开发实战技巧】系列（二十五）：数仓报表场景☞结果集中的重复数据只显示一次以及计算部门薪资差异高效的写法以及如何对数据进行快速分组
【SQL开发实战技巧】系列（二十六）：数仓报表场景☞聊聊ROLLUP、UNION ALL是如何分别做分组合计的以及如何识别哪些行是做汇总的结果行
【SQL开发实战技巧】系列（二十七）：数仓报表场景☞通过对移动范围进行聚集来详解分析函数开窗原理以及如何一个SQL打印九九乘法表
【SQL开发实战技巧】系列（二十八）：数仓报表场景☞人员分布问题以及不同组(分区)同时聚集如何实现
【SQL开发实战技巧】系列（二十九）：数仓报表场景☞简单的树形(分层)查询以及如何确定根节点、分支节点和叶子节点
【SQL开发实战技巧】系列（三十）：数仓报表场景☞树形(分层)查询如何排序？以及如何在树形查询中正确的使用where条件
【SQL开发实战技巧】系列（三十一）：数仓报表场景☞分层查询如何只查询树形结构某一个分支？如何剪掉一个分支？
【SQL开发实战技巧】系列（三十二）：数仓报表场景☞对表中某个字段内的值去重
【SQL开发实战技巧】系列（三十三）：数仓报表场景☞从不固定位置提取字符串的元素以及搜索满足字母在前数字在后等条件的数据
【SQL开发实战技巧】系列（三十四）：数仓报表场景☞如何对数据分级并行转为列
文章目录
- 系列文章目录
- 前言
- 一、把结果分级并转为列
- 1、生成序号
- 2、按照一定要求(我这里随意)
- 3、要对三列数据重新生成序号
- 总结
前言
本篇文章讲解的主要内容是：有个需求：把emp中的结果按工资分级,其中最高的三档作为一列、次高的三档作为一列、其余的作为一列。本篇文章我们来讨论下这个需求怎么实现。给出case when 和pivot两种行转列的方法。通过此案例理解隐藏列信息的重要性。
【SQL开发实战技巧】这一系列博主当作复习旧知识来进行写作，毕竟SQL开发在数据分析场景非常重要且基础，面试也会经常问SQL开发和调优经验，相信当我写完这一系列文章，也能再有所收获，未来面对SQL面试也能游刃有余~。
一、把结果分级并转为列
现在有个需求：把emp中的结果按工资分级,其中最高的三档作为一列、次高的三档作为一列、其余的作为一列。
本篇文章我们来讨论下这个需求怎么实现。该问题解决思路如下：
1、生成序号
这里让数据相同的(3000)排序相同,且不占排序位置,所以需要用dense_rank
来生成序号：
with t as (
select ename,sal,dense_rank()over(order by sal desc) as rn
from emp
)
select * from t;
ENAME SAL RN
---------- --------- ----------
KING 5000.00 1
FORD 3000.00 2
SCOTT 3000.00 2
JONES 2975.00 3
BLAKE 2850.00 4
CLARK 2450.00 5
ALLEN 1600.00 6
TURNER 1500.00 7
MILLER 1300.00 8
WARD 1250.00 9
MARTIN 1250.00 9
ADAMS 1100.00 10
JAMES 950.00 11
SMITH 800.00 12
14 rows selected
从上面查询结果可看到(FORD,SCOTT)sal都为3000，排序序号rn都为2。
2、按照一定要求(我这里随意)
将上面数据划分为三档,这可通过CASE WHEN完成：
with t as
(select ename, sal, dense_rank() over(order by sal desc) as rn from emp)
select t.*,
case
when rn <= 3 then
1
when rn <= 6 then
2
else
3
end as new_rn
from t;
ENAME SAL RN NEW_RN
---------- --------- ---------- ----------
KING 5000.00 1 1
FORD 3000.00 2 1
SCOTT 3000.00 2 1
JONES 2975.00 3 1
BLAKE 2850.00 4 2
CLARK 2450.00 5 2
ALLEN 1600.00 6 2
TURNER 1500.00 7 3
MILLER 1300.00 8 3
WARD 1250.00 9 3
MARTIN 1250.00 9 3
ADAMS 1100.00 10 3
JAMES 950.00 11 3
SMITH 800.00 12 3
14 rows selected
3、要对三列数据重新生成序号
这样行转列时才能把序号相同的归为一行：
with t as
(select ename, sal, dense_rank() over(order by sal desc) as rn from emp),
t1 as
(select t.*,
case
when rn <= 3 then
1
when rn <= 6 then
2
else
3
end as new_rn
from t)
select t1.*, row_number() over(partition by new_rn order by sal) as flag
from t1
ENAME SAL RN NEW_RN FLAG
---------- --------- ---------- ---------- ----------
JONES 2975.00 3 1 1
FORD 3000.00 2 1 2
SCOTT 3000.00 2 1 3
KING 5000.00 1 1 4
ALLEN 1600.00 6 2 1
CLARK 2450.00 5 2 2
BLAKE 2850.00 4 2 3
SMITH 800.00 12 3 1
JAMES 950.00 11 3 2
ADAMS 1100.00 10 3 3
MARTIN 1250.00 9 3 4
WARD 1250.00 9 3 5
MILLER 1300.00 8 3 6
TURNER 1500.00 7 3 7
14 rows selected
4、根据最后生成的“分组”列进行“行转列”即可
with t as --l.对数据分档
(select ename, sal, dense_rank() over(order by sal desc) as rn from emp),
t1 as --2.根据档次把数据分为三类
(select t.*,
case
when rn <= 3 then
1
when rn <= 6 then
2
else
3
end as new_rn
from t),
t2 as --3.分别对三列的数据重新取序号,这样相同序号的可以汇总后放在同一行
(select t1.*, row_number() over(partition by new_rn order by sal) as flag
from t1)
--4.行转列
select max(case new_rn
when 1 then
ename || '(' || sal || ')'
end) as 第一档,
max(case new_rn
when 2 then
ename || '(' || sal || ')'
end) as 第二档,
max(case new_rn
when 3 then
ename || '(' || sal || ')'
end) as 第三档
from t2
group by flag
order by flag;
第一档 第二档 第三档
-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
JONES(2975) ALLEN(1600) SMITH(800)
FORD(3000) CLARK(2450) JAMES(950)
SCOTT(3000) BLAKE(2850) ADAMS(1100)
KING(5000) MARTIN(1250)
WARD(1250)
MILLER(1300)
TURNER(1500)
7 rows selected
上面写法使用的是case when的方式写的，下面再给一个用pivot的写法：
with t as --l.对数据分档
(select ename, sal, dense_rank() over(order by sal desc) as rn from emp),
t1 as --2.根据档次把数据分为三类
(select t.*,
case
when rn <= 3 then
1
when rn <= 6 then
2
else
3
end as new_rn
from t),
t2 as --3.分别对三列的数据重新取序号,这样相同序号的可以汇总后放在同一行
(select t1.*, row_number() over(partition by new_rn order by sal) as flag
from t1)
--4.行转列
select max(第一档), max(第二档), max(第三档)
from (select ename || '(' || sal || ')' as enames, new_rn, flag from t2)
pivot (max(enames) for new_rn in(1 as 第一档,
2 as 第二档,
3 as 第三档
))
group by flag;
MAX(第一档) MAX(第二档) MAX(第三档)
-------------------------------------------------------------------------------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
JONES(2975) ALLEN(1600) SMITH(800)
FORD(3000) CLARK(2450) JAMES(950)
SCOTT(3000) BLAKE(2850) ADAMS(1100)
KING(5000) MARTIN(1250)
WARD(1250)
MILLER(1300)
TURNER(1500)
7 rows selected
排序后生成的行号属于隐含信息,而这种隐含信息常用在各种复杂的查询中。对于这种查询,当你知道需要哪种隐含信息时,你就成功了一半！！！
总结
本篇文章讲解的主要内容是：有个需求：把emp中的结果按工资分级,其中最高的三档作为一列、次高的三档作为一列、其余的作为一列。本篇文章我们来讨论下这个需求怎么实现。给出case when 和pivot两种行转列的方法。通过此案例理解隐藏列信息的重要性。