目录
遇到需要进行行列转换的数据处理需求,以 oracle 自带的表作为例子复习一下:
一、lead、lag 函数
需要行列转换的表:
select deptno,count(empno) emp_num from emp group by deptno
先将转化结果贴出来易于理解
select emp_num dept1,
lead(emp_num,1) over(order by e.deptno) dept2, # 根据 emp_num 得到后1行数据
lead(emp_num,2) over(order by e.deptno) dept3 # 根据 emp_num 得到后2行数据
from (select deptno,count(empno) emp_num
from emp group by deptno) e
得到:
可以看出使用位移函数后,在第二列得到的是第二行往后的数据,第三列得到的是第三行开开始的数据,以此类推,第N列第一行就是第一列的第N行了,顺序也一样,此时只需要得到第一行,就是原来的第一列数据了
select dept1 "部门10",dept2 "部门20",dept3 "部门30"
from (select emp_num dept1,
lead(emp_num,1) over(order by e.deptno) dept2,
lead(emp_num,2) over(order by e.deptno) dept3
from (select deptno,count(empno) emp_num
from emp group by deptno) e)
where rownum=1;
rownum是oracle的伪列,会在原表中新增一个新的列,来记录每一行的数据(用来表示序号),只能使用 < 或 <=(除=1以外)
实际中可能可以使用for循环处理较大量的数据
二、wm_concat 函数
该函数可以将目标字段的值用逗号连成一行,一般结合 group by 一起使用,查看每个类别的明细,仍使用如下表:
select deptno,count(*) 人数,wm_concat(ename) 姓名
from emp
Group by deptno;
根据部门分组统计人数,列出每组的 ename (姓名)
三、pivot 函数
格式:select * from 表 pivot(聚合函数 for 分组的列 in (显示分解后的列名1,列名2,…));
select deptno,empno from emp; --员工编号
select *
from (select deptno,empno from emp) --由上表选择
pivot(count(empno) for deptno in --部门分组重复计数
('10' as 部门10,'20' as 部门20,'30' as 部门30))
这里的 '10' 为值,后接 as+列名,即所有值为'10'的统计完记为"部门10 "
四、判断函数
select deptno,empno from emp; --员工编号
(一)判断,得到要统计的员工号,不满足的为空
select case when deptno=10 then empno end as "10",
case when deptno=20 then empno end as "20",
case when deptno=30 then empno end as "30"
from emp;
(二)统计,count+列名会统计过滤空值,统计非空值
select count(case when deptno=10 then empno end )as "10",
count(case when deptno=20 then empno end )as "20",
count(case when deptno=30 then empno end )as "30"
from emp;