sql中开窗函数，列转行，avg(DISTINCT|ALL)，空值转换函数以及merge into语法的使用

开窗函数over()

over()：指定窗口的范围。

> select *, count(st_id) over() as count from student;

over() 默认的窗口大小是从第一行到最后一行的所有数据， count 统计的数据就是 over 窗口中的数据。 count(st_id) over() as count 产生了新的一列，并给这一列起了个名字： count。

我们还可以指定窗口的大小为一个分组：

> select *, count(st_id) over(partition by st_dept) count from student;

over(partition by st_dept) 中 partition by 的意思是按照 st_dept 进行分组，这样设定之后，over 的窗口大小就是一个分组。 count(st_id) 就是统计每个分组内有多少行。

我们还可以在 over() 中指定分组内的数据进行 正序或倒序 排序：

> select *, count(st_id) over(partition by st_dept order by st_id) count from student;

通过 sort by 进行排序，over 的窗口大小也发生了变化，当指定排序后，窗口的大小为：从分组内第一行 到 当前行。

sum()和avg()用法类似count()，当只指定分组时， sum、 avg 统计的数据就是分组内的数据，当既指定分组又指定排序时， sum、 avg 统计的数据就是：分组内 从第一行到当前行的数据。

row_number() 的功能和 count() 一致，只不过 row_number 括号中不需要指定列，更加常用：

> select *, row_number() over(partition by st_dept order by st_id) from student;

数据：

10001,郑楠,男,20,ES

10002,李娜,女,19,IS

10003,高小海,男,20,ES

10004,张巍,女,19,IS

10005,孙鹏,男,20,ES

10006,周宏伟,女,19,IS

10007,张海龙,男,20,ES

10008,刘勇,女,19,IS

create table student(st_id int,st_name string,st_sex string,st_age int,st_dept string)

row format delimited fields terminated by ',';

load data local inpath '/usr/local/data/student.txt' into table student;

over：指定窗口的范围。比较抽象，我们先看一个例子：

student表中的数据：

student表的结构：

> select *, count(st_id) over() as count from student;

over() 默认的窗口大小是从第一行到最后一行的所有数据，count统计的数据就是over窗口中的数据。count(st_id) over() as count 产生了新的一列，并给这一列起了个名字：count。我们看到，每一行的数据都是22，表示这个表有22行。

我们还可以指定窗口的大小为一个分组：

  > select *, count(st_id) over(partition by st_dept) count from student;

我们看到，这次 count这个列的数据，不同学院的值不一样，学院内是一样，也就是说：

over(partition by st_dept) 中 partition by 的意思是按照st_dept进行分组，这样设定之后，over的窗口大小就是一个分组。count(st_id) 就是统计每个分组内有多少行。

我们还可以在 over() 中指定分组内的数据进行 正序或倒序 排序：

> select *, count(st_id) over(partition by st_dept order by st_id) count from student;

我们看到，这次count这个列的数据发生变化了，因为通过 sort by进行排序，则over的窗口大小也发生了变化，当指定排序后，窗口的大小为：从分组内第一行 到 当前行。

第一行的count值为1，因为窗口的大小就只包含这一行，所以count(st_id) 为1

第二行的count值为2，因为窗口的大小包含两行，所以count(st_id) 为2。

以此类推。

我们再来看一下 sum和avg函数：

> select *, sum(st_age) over(partition by st_dept) sum1,

> sum(st_age) over(partition by st_dept order by st_id) sum2,

> avg(st_age) over(partition by st_dept) avg1,

> avg(st_age) over(partition by st_dept order by st_id) avg2

> from student;

当只指定分组时，sum、avg统计的数据就是分组内的数据，

当既指定分组又指定排序时，sum、avg统计的数据就是：分组内 从第一行到当前行的数据。

row_number() 的功能和count() 一致，只不过 row_number 括号中不需要指定列，更加常用：

> select *, row_number() over(partition by st_dept order by st_id) from student;

再提一点：

over(partition by… order by …) 和 over(distribute by … sort by …)

功能是一样的。

Oracle列转行函数LISTAGG() WITHIN GROUP ()

查询部门为20的员工列表: 
SELECT t.DEPTNO,t.ENAME FROM SCOTT.EMP t where t.DEPTNO = '20';

使用 listagg() WITHIN GROUP () 将多行合并成一行：

SELECT
T .DEPTNO,
LISTAGG (T .ENAME, ',') WITHIN GROUP (ORDER BY T .ENAME) NAMES
FROM SCOTT.EMP T
WHERE
T .DEPTNO = '20' GROUP BY T .DEPTNO

2.高级用法：over(partition by XXX)。在不使用Group by语句时候，也可以使用LISTAGG函数：

WITH TEMP AS(  
SELECT 500 POPULATION, 'CHINA' NATION ,'GUANGZHOU' CITY FROM DUAL UNION ALL  
SELECT 1500 POPULATION, 'CHINA' NATION ,'SHANGHAI' CITY FROM DUAL UNION ALL  
SELECT 500 POPULATION, 'CHINA' NATION ,'BEIJING' CITY FROM DUAL UNION ALL  
SELECT 1000 POPULATION, 'USA' NATION ,'NEW YORK' CITY FROM DUAL UNION ALL  
SELECT 500 POPULATION, 'USA' NATION ,'BOSTOM' CITY FROM DUAL UNION ALL  
SELECT 500 POPULATION, 'JAPAN' NATION ,'TOKYO' CITY FROM DUAL   
)  
SELECT POPULATION,  
NATION,  
CITY,  
LISTAGG(CITY,',') WITHIN GROUP (ORDER BY CITY) OVER (PARTITION BY NATION) RANK  
FROM TEMP

运行结果：

AVG(DISTINCT|ALL)

all表示对所有的值求平均值,distinct只对不同的值求平均值

SQL> create table table(xm varchar(8),sal number(7,2));
SQL> insert into table values(’gao’,1111.11);
SQL> insert into table values(’gao’,1111.11);
SQL> insert into table values(’zhu’,5555.55);
SQL> commit;

SQL> select avg(distinct sal) from gao.table;

3333.33

SQL> select avg(all sal) from gao.table;

2592.59

空值转换

1 Oracle数据库的空值转换有2个函数，分别是NVL、NVL2。
1）NVL(s1,s2)：若s1不为null，则返回s1，否则返回s2。

SELECT NVL('S', 'R') FROM DUAL;   -- S
SELECT NVL(NULL, 'R') FROM DUAL;  -- R

2）NVL2(s1,s2,s3)：若s1不为null，则返回s2，否则返回s3。

SELECT NVL2('S', 'R1', 'R2') FROM DUAL;   -- R1
SELECT NVL2(NULL, 'R1', 'R2') FROM DUAL;  -- R2

 3）coalesce(s1,s2,…,R)：此函数可有多个参数，前面多个参数为判断条件，若非最后一位的参数都为null，则返回最后一位参数R，否则返回第一个非null的参数。当只有两个参数时可替代Oracle的NVL函数。

SELECT coalesce(NULL, 'R');              -- R
SELECT coalesce('S1', 'S2', 'S3', 'R');  -- S1
SELECT coalesce(NULL, 'S2', 'S3', 'R');  -- S2
SELECT coalesce(NULL, NULL, 'S3', 'R');  -- S3
SELECT coalesce('S1', NULL, 'S3', 'R');  -- S1
SELECT coalesce(NULL, NULL, NULL, 'R');  -- R

2 MySQL数据库的空值转换也有1个函数，分别是ifnull。
1）ifnull(s1,s2)：和Oracle的NVL(s1,s2)一样，若s1不为null，则返回s1，否则返回s2。所以可以用ifnull函数替代Oracle的NVL函数。

select ifnull('S', 'R');   -- S
select ifnull(null, 'R');  -- R

3 MySQL替换Oracle
1）Oracle的NVL()函数可以用 ifnull(s1,s2) 这两个函数替代。

2）Oracle的NVL2()函数MySQL无现成函数可替代，只能用case when 来替代。

select case when S1 is not null then R1 else R2 end;

select case when 'S' is not null then 'R1' else 'R2' end;   -- R1
select case when null is not null then 'R1' else 'R2' end;  -- R2

Oracle中merge Into的用法

使用场景
在操作数据库时，数据存在的情况下，进行update操作；不存在的情况下，进行insert操作；在Oracle数据库中，能够使用merge into来实现。

基本语法

merge into table_name  alias1                  -- 目标表 可以用别名表示
using (table|view|sub_query) alias2            -- 数据源表 可以是表、视图、子查询等
on (join condition)                            -- 关联条件 
when matched then                              -- 当关联条件成立时 更新，删除，插入的where部分为可选 
  update table_name set col1 = colvalue  where ……        -- 更新操作
  delete from table_name  where  col2=colvalue  where……  -- 删除操作 
                                                         -- 可以只更新不删除 也可以只删除不更新
                                                         -- 如果更新和删除同时存在，删除的条件一定要在更新的条件内，否则数据不能删除
when not matched then                                    -- 当关联条件不成立时   
insert (col3) values (col3values)  where……               -- 关联条件进行插入操作

merge into HYSHF_ZT.DT_CORE_HOU T1 
using (
select * from HYSHF_ZT.TEST_CORE_HOU
where HOU_ID in (
select HOU_ID from HYSHF_ZT.TEST_CORE_HOU
group by HOU_ID
having count(*)=1
) 
) T2 
on (T1.HOU_ID=T2.HOU_ID)
when matched then 
  update set T1.UNIT_ID=T2.UNIT_ID,
  T1.HOU_NO=T2.HOU_NO
  ,T1.HOU_AREA=T2.HOU_AREA
  ,T1.UNIT_NO=T2.UNIT_NO
 -- ,T1.HOU_STATUS=T2.HOU_CURR_STAT
  ,T1.UPDATE_DATE=T2.UPDATE_DATE
;
COMMIT;

演示示例
为了演示，下面提供了两张测试表以及数据：

-- 测试表(1) tmp 
create table tmp
(
  id               VARCHAR2(20) not null,
  tmp_name        VARCHAR2(120),
  tmp_date  VARCHAR2(8),
  is_delete        VARCHAR2(1),
  creator          VARCHAR2(24),
  created_at       NUMBER(20),
  updater          VARCHAR2(24),
  updated_at       NUMBER(20)
)
-- 测试表(2) temp
create table temp
(
  id               VARCHAR2(20) not null,
  tmp_id        VARCHAR2(20),
  temp_name  VARCHAR2(120),
  temp_date  VARCHAR2(8),
  is_delete        VARCHAR2(1),
  creator          VARCHAR2(24),
  created_at       NUMBER(20),
  updater          VARCHAR2(24),
  updated_at       NUMBER(20)
)

tmp表数据

temp表数据

两表的关联关系

select t.*,tt.* from tmp t,temp tt where t.id = tt.tmp_id;

merge into示例：

merge into temp  t   
using  tmp tt on (t.tmp_id = tt.id)
when matched then           
  update set t.temp_name ='xxkfz' where t.tmp_id = '00001'
  delete where (t.tmp_id  =  '00002')
when not matched then    
 insert (t.id, t.tmp_id,t.temp_name,t.temp_date,t.is_delete) values (sys_guid(),tt.id,tt.tmp_name,tt.tmp_date,tt.is_delete);
 commit;

几点说明：

被更新的表写在merge into之后
更新来源数据表写在using之后，并将相关字段查询出来，为查询结果定义别名
on 之后表示更新满足的条件
when matched then：表示当满足条件时要执行的操作
update set： 被更新表.被更新字段 = 更新表.更新字段—此更新语句不同于常规更新语句
when not matched then：表示当不满足条件时要执行的操作。
insert (被更新表.被更新字段,…) values (更新表.更新字段,…)
commit：表示提交事务
执行完成以上语句后，结果如下：

发现：temp表中tmp_id为00002的数据没有被删除。

因为：如果更新和删除同时存在，删除的条件一定要在更新的条件内，否则数据不能删除！！！

为了更好的测试该场景，修改如下：

merge into temp  t    
using  tmp tt on (t.tmp_id = tt.id)
when matched then              
  update set t.temp_name ='xxkfz' where t.tmp_id  in ('00001','00002')
  delete where (t.tmp_id  =  '00002')
when not matched then    
 insert (t.id, t.tmp_id,t.temp_name,t.temp_date,t.is_delete) values (sys_guid(),tt.id,tt.tmp_name,tt.tmp_date,tt.is_delete);
 commit;

再次重置两表测试数据，执行以上语句，结果如下：

发现tmp_id为00001的数据temp_name字段成功更新为xxkfz；tmp_id为00002的数据成功删除。

对于没有匹配的数据：tmp表id为00003的数据也成功插入带temp表中。

发现tmp_id为00001的数据temp_name字段成功更新为xxkfz；tmp_id为00002的数据成功删除