分析函数带有一个开窗函数over(),包含三个分析子句:分组(partition by), 排序(order by), 窗口(rows) ,他们的使用形式如下:over(partition by xxx order by yyy rows between zzz)。
业务背景:三张数据表 A B C
其中 A表是最大的表,包含 B和C中的数据(部分或全部);同时,B和C之间也可能存在部分重复的数据。
实现:需要写一个SQL,查询A B C三张表中的数据,要求是:
1、如果A B C三张表中都有的数据,那么只显示A表中的那条
2、如果是B C中有的数据 A中没有的 那么就显示C表中的数据
3、如果是A B C表中各自独有的数据,那么直接展示
方法:
利用over()分析函数的形式,可以很好的做排序,然后筛选,首先我们知道 A B C的筛选次————A>C>B,那么我们可以select中通过自定义字段来区分这个次序,具体SQL如下:
select
EBCU.CODE,
EBCU.TABLE_SOURCE ,
row_number() over(PARTITION BY EBCU.PM_CODE ORDER BY EBCU.TABLE_SOURCE ASC) as group_idx
FROM
(select
CODE,
'1' TABLE_SOURCE
from
A
union
select
CODE,
'3' TABLE_SOURCE
from
B
union
select
CODE,
'2' TABLE_SOURCE
from
C
) EBCU
以上查询结果存在如下情况:
之后我们需要对以上重复的数据进行筛选,进一步的SQL如下:
SELECT * FROM (
select EBCU.CODE,
EBCU.TABLE_SOURCE ,row_number() over(PARTITION BY EBCU.PM_CODE ORDER BY EBCU.TABLE_SOURCE ASC) as group_idx
FROM
(select
CODE,
'1' TABLE_SOURCE
from
A
union
select
CODE,
'3' TABLE_SOURCE
from
B
union
select
CODE,
'2' TABLE_SOURCE
from
C
) EBCU ) AAA_
where AAA_.group_idx = 1;
以上处理结果就是满足了我们要求的数据。