学习大数据DAY04 SQL基础语法4

目录

Case when

注意：

decode 等值翻译

decode 与 case when 的比较：

聚合 case 实现行转列

行转列 列转行 函数

作业 1

开窗函数

窗口函数

RANK()

用聚合函数来开窗

wm_concat 函数

listagg 函数

偏移分析函数

作业 2

---

SQL 基础语法（四）

Case when

Case 具有两种格式。简单 Case 函数和 Case 搜索函数。

简单 Case 函数

select deptno,case deptno

when 10 then '第一部门'

when 20 then '第二部门'

when 30 then '第三部门'

else '第四部门'

end

from emp;

Case 搜索函数

select deptno,

case when deptno=10 then '第一部门'

when deptno=20 then '第二部门'

when deptno=30 then '第三部门'

else '第四部门'

end

from emp;

这两种方式，可以实现相同的功能。简单 Case 函数的写法相对比较简洁，但是和

Case 搜

索函数相比，功能方面会有些限制，比如写判断式。

还有一个需要注意的问题，Case 函数只返回第一个符合条件的值，剩下的 Case 部

分将会

被自动忽略。

注意：

Case when 生成的列也可以丢到 group by 后面去分组的,where,having 都可以

使用 CASE 表达式需注意的点

各个分支<表达式>返回的数据类型要统一；

CASE 写完后不能丢了 END

ELSE 可省略但不建议省，没有值时可写 ELSE NULL。

decode 等值翻译

--把 10 号部门翻译成“十号部门” ，20 号部门翻译成“二十号部门” ，30 号部

门翻译成“三十号部门”，其他显示其他部门，翻译时可以对列进行处理

select ename,deptno,

decode(deptno,10,'十号部门',20,'二十号部门',30,'三十号部门','其他部门')

from emp

decode 与 case when 的比较：

1、decode 只有 Oracle、informix 才有，其它数据库不支持；

2、CASE WHEN 的用法， Oracle、SQL Server、 MySQL、 informix、都支持；

3、decode 只能用做相等判断，但是可以配合 sign 函数进行大于，小于，等于的判

断，

CASE when 可用于=,>=,<,<=,<>,is null,is not null 等的判断；

4、DECODE 使用其来比较简洁，CASE 虽然复杂但更为灵活；

5、另外，在 decode 中，null 和 null 是相等的，但在 case when 中，只能用 is null

来判断。

--简单 Case 函数

select deptno,case deptno

when 10 then '十号部门'

when 20 then '二十号部门'

when 30 then '三十号部门'

else '其他部门' end

from emp;

--搜索式(表达式)case 函数

select deptno,case

when deptno=10 then '十号部门'

when deptno=20 then '二十号部门'

when deptno=30 then '三十号部门'

else '其他部门' end

from emp;

--分组位置

select case

when deptno=10 then '十号部门'

when deptno=20 then '二十号部门'

when deptno=30 then '三十号部门'

else '其他部门' end,max(sal)

from emp group by

case

when deptno=10 then '十号部门' when deptno=20 then '二十号部门'

when deptno=30 then '三十号部门'

else '其他部门' end;

聚合 case 实现行转列

--行转列方法一

select deptno,

max ( case when job = 'SALESMAN' then sal end ) salesman,

max ( case when job = 'MANAGER' then sal end ) manager,

max ( case when job = 'CLERK' then sal end ) clerk

from emp

group by deptno

行转列 列转行 函数

pivot 格式

SELECT * FROM （数据查询集）

PIVOT

(

SUM(Score/ 行转列后 列的值 /) FOR

coursename/ 需要行转列的列 / IN (转换后列的值)

)

--行转列方法二

select * from

( select deptno,job,sal from emp)

pivot

(

max (sal) for job in ( 'SALESMAN' , 'MANAGER' , 'CLERK' )

) --行转列 列转行

select * from

( select deptno,job,sal from emp)

pivot

(

max (sal) for job in ( 'SALESMAN' salesman, 'MANAGER' manager, 'CLERK' clerk)

)

unpivot

(

newsal for job in (salesman,manager,clerk)

)

作业 1

--1.把 10 号部门翻译成“十号部门”，20 号部门翻译成“二十号部门”，30 号部门翻
译成“三十号部门”
select deptno,dname,
case deptno
when 10 then '十号部门'
when 20 then '二十号部门'
when 30 then '三十号部门'
else '其它部门'
end from dept;
--2.给所有的10号部门员工加薪10% 20号部门的员工加薪20% 30号员工加薪30% ,
其他部门加薪 5%
select deptno,ename,
case when deptno=10 then sal*1.1
when deptno=20 then sal*1.2
when deptno=30 then sal*1.3
else sal*1.05
end as AddSal from emp;
--3.统计工资级别相应的数量（1600 以下 C 级,1600-3000 B 级,3000 以上 A 级）
select ename,sal,
case when sal<1600 then 'C'
when sal between 1600 and 3000 then 'B'
when sal>3000 then 'A'
end as SalLevel from emp;
--4.实现两种方法的行转列
select deptno,
case when job ='SALEMAN' then sal end SALEMAN,
case when job='MANAGER' then sal end MANAGER,
case when job='CLERK' then sal end CLERK
from emp;
select * from
(select deptno,job,sal from emp)
pivot
(
max(sal) for job in ('SALEMAN','MANAGER', 'CLERK')
)

开窗函数

窗口函数

（分析函数）（主要做排序）

开窗函数格式： 函数名(列) OVER(选项)

over(partition by 分组列 order by 排序列)

注：order by 后面可以是处理后的列

over 关键字表示把函数当成开窗函数而不是聚合函数。

S

QL 标准允许将所有聚合函

数用

做开窗函数，使用 over 关键字来区分这两种用法。

ROW_NUMBER()

ROW_NUMBER()函数作用就是将 select 查询到的数据进行排序，每一条数据加一个序

号。排序的序号和 rownum 伪列相同，连续序号，不考虑值相等的情况（值相同序号

不相

同）

RANK()

RANK()函数，顾名思义排名函数，可以对某一个字段进行排名，可以用来做排序，

它序

号不连续，考虑重复数据，如果值相等序号相同

DENSE_RANK()

DENSE_RANK()密集的排名，紧密排序，可以用来做排序，它序号连续，考虑重复数

据，

如果值相等序号就相同

他和 RANK()区别在于，排名的连续性，DENSE_RANK()排名是连续的，RANK()是跳跃

的

排名 例子：

select sal,row_number()over(order by sal desc) from emp;

select sal,dense_rank()over(order by sal desc) from emp;

select sal,rank()over(order by sal desc) from emp

用聚合函数来开窗

avg() max() count() sum() min()

可以不加任何条件，如果加 order by 则是按照顺序依次进行计算，一般不加 order

by

--用开窗函数按照部门降序排列

select sal,ename,row_number()over(order by sal desc) from emp;

select sal,ename,dense_rank()over(order by sal desc) from emp;

select sal,ename,rank()over(order by sal desc) from emp

--partition 分区/分组 按照部门对工资进行降序排序

select emp.* ,row_number()over(partition by deptno order by sal desc) from

emp;

select deptno,sal,dense_rank()over(partition by deptno order by sal desc)

from emp;

select deptno,sal,rank()over(partition by deptno order by sal desc) from

emp;

--聚合函数

select distinct deptno,

avg(sal) over(),

max(sal) over()

from emp;

--显示部门平均工资,以及部门内最高工资

select distinct deptno,

avg(sal) over(partition by deptno),

max(sal) over(partition by deptno)

from emp;

--累计聚合

select sal,ename,sum(sal)over(partition by deptno order by sal) from emp

--指定范围内聚合

select sal,ename,avg(sal)over(partition by deptno

order by sal rows between 1 preceding and 1 following) from emp

--开窗条件不能作为判断条件，只能用作结果，需要封装一层子查询

select ename, deptno, t.r

from (select deptno,ename,

row_number() over(partition by deptno order by sal desc) r from emp) t

where t.r = 2

wm_concat 函数

wm_concat(列名),该函数可以把列值以","号分隔起来,并显示成一行

select wm_concat(ename) from emp;

listagg 函数

LISTAGG 将多行合并成一行

--作为普通函数，对工资进行排序，用逗号进行拼接。

select listagg(ename,',')within group(order by sal)name from emp;

select * from emp order by sal

--作为分组函数：

select deptno,listagg(ename,',')within group(order by sal)name from emp

group by

deptno;

--作为分析函数：

select deptno,ename,sal,listagg(ename,',')within group(order by

sal)over(partition by

deptno)name from emp;

--listagg 将多行合并成一行

--作为普通函数，对工资进行排序，用逗号进行拼接。

select listagg(ename,',')within group(order by sal)name from emp;

select * from emp order by sal

--作为分组函数：

select deptno,listagg(ename,',')within group(order by sal)name

from emp group by deptno;

--作为分析函数：

select deptno,ename,sal,

listagg(ename,',')within group(order by sal)over(partition by deptno)name

from emp;

偏移分析函数

lead(params,m,n) 以 params 为目标向下 m 位取数，当取不到时默认为 n,

lag(params,m,n) 以 params 为目标向上 m 位取数，当取不到时默认为 n

例如：lead(field, num, defaultvalue) field 需要查找的字段，num 往后查找的

num 行的数

据，defaultvalue 没有符合条件的默认值。

--偏移函数

select ename,job,sal,lag(sal,1,0)over(order by sal) lastsal from emp; select ename,job,sal,lead(sal,1,0)over(order by sal) lastsal from emp;

--环比 （sal-lastsal）/lastsal*100%

select ename,job,sal,lag(sal,1,0)over(order by sal) lastsal,

concat(

to_char(round((sal-lag(sal,1,0)over(order by sal))/lag(sal,1,0)over(order

by sal)*100,2),'990.99')

,'%')

from emp;

同比是本年度和去年的相比，可以使用 lag(sal,12,0)来实现

如果不是连续月份可以使用序号开窗配合偏移开窗相减判断

作业 2

--1.按照部门编号升序查找所有部门名称，用、隔开
select listagg(dname,'、')within group(order by deptno asc) from dept;
--2.按照工资降序查找每个部门的员工姓名，用、隔开
select deptno,listagg(ename,'、')within group(order by sal desc)
from emp group by deptno;
--3.使用工资偏移计算环比 （sal-lastsal）/lastsal*100%
select ename,job,sal,lag(sal,1,0)over(order by sal) lastsal,
concat(
to_char(round((sal-lag(sal,1,0)over(order by sal))/lag(sal,1,1)over(order by
sal)*100,2),'9990.99')
,'%')
from emp;
--4.查询员工表中工资最高的前三名
select * from
(select ename,sal,row_number()over(order by sal desc) as 排名 from emp)
where 排名<=3 ;
--5.查询员工表中每个部门的工资第 2~3 名的员工信息
select * from
(select emp.*,row_number()over(partition by deptno order by sal desc) as 排名 from
emp)
where 排名 between 2 and 3;
--6.查询员工姓名、部门及部门平均工资,以及部门内最高工资
select ename,deptno,avg(sal)over(partition by deptno),max(sal)over(partition by deptno)
from emp;
--7.每种工作累计求工资和
select job,sum(sal)over(partition by job order by sal) from emp;