集合运算

除了我们平时常用的crud查询，sql中的还有集合运算。比如，最近因为，要确定一个id，在另外一个表中是否存在，即用到了该运算。而自己用到的是联表left join运算。貌似直接用expect更方便。

本文部分内容来自转载。

参考资源

https://www.cnblogs.com/kissdodog/p/3152743.html

三种集合运算

三种运算和、差、交。

union 形成并集

Union可以对两个或多个结果集进行连接，形成“并集”。子结果集所有的记录组合在一起形成新的结果集。

1、限定条件

要是用Union来连接结果集，有4个限定条件。

(1)、子结果集要具有相同的结构。

(2)、字结果集的列数必须相同。

(3)、子结果集对应的数据类型必须可以兼容。

(4)、每个子结果集不能包含order by和compute子句。

2、语法形式

select_statement union [all] select_statement

all代表最终的结果集中将包含所有的行，而不能删除重复行。

示例：

SELECT Name FROM Person_1
UNION
SELECT Name FROM Person_2

UNION ALL 与 UNION的不同之处：结果是否出重。

Except形成差集

Except可以对两个或多个结果集进行连接，形成“差集”。返回左边结果集合中已经有的记录，而右边结果集中没有的记录。

1、限定条件：

1、子结果集要具有相同的结构。

2、子结果集的列数必须相同。

3、子结果集对应的数据类型必须可以兼容。

4、每个子结果集不能包含order by 和 compute子句。

2、语法形式：

select_statement except select_statement　

自动删除重复行。

示例：

SELECT Name FROM Person_1
EXCEPT
SELECT Name FROM Person_2

InterSect形成交集

InterSect可以对两个或多个结果集进行连接，形成“交集”。返回左边结果集和右边结果集中都有的记录。

1、限定条件　　

要是用Except来连接结果集，有4个限定条件。

(1)、子结果集要具有相同的结构。

(2)、子结果集的列数必须相同。

(3)、子结果集对应的数据类型必须可以兼容。

(4)、每个子结果集不能包含order by或compute子句。

2、语法形式

select_statement intersect select_statement　　　　　　　　

示例：

SELECT Name FROM Person_1
INTERSECT
SELECT Name FROM Person_2

结果集排序

SELECT Name FROM Person_1
INTERSECT
SELECT Name FROM Person_2
ORDER BY Name DESC　　　　--此处的字段名相同了，如果不同，请切记排序列名，只能够是第一个表的列名

这里只有两点要注意

1.ORDER BY是对整个运算后的结果排序，并不是对单个数据集。

2.ORDER BY后面排序的字段名称是第一个数据集的字段名或者别名。

实例

测试expcept/except all

CREATE TABLE demo1 (
    id int,
    title text
);
CREATE TABLE demo2 (
    id int,
    title text
);

insert into demo1 values
        (1,'测试1'),
		(2,'测试2'),
		(3,'测试3');

insert into demo2 values
        (1,'测试1'),
		(2,'测试2'),
		(3,'测试3');
-- 1、2内容一样，以下结果  无。
SELECT id,title from demo1 EXCEPT All  SELECT id,title from demo2;

insert into demo1 values
    (4,'测试4');
-- 1，新增记录4，故多1条
SELECT id,title from demo1 EXCEPT All  SELECT id,title from demo2;
-- 2更改了内容，
UPDATE demo2 set title = '测试22' where id = 2;
-- 故，又多出1条。
SELECT id,title from demo1 EXCEPT All  SELECT id,title from demo2;

-- 1插入了重复的数据  All 二者有差别了
insert into demo1 values (1,'测试1');
-- 输出3条，有重复的那条
SELECT id,title from demo1 EXCEPT All  SELECT id,title from demo2;
-- 无重复的那条。
SELECT id,title from demo1 EXCEPT SELECT id,title from demo2;

对比计算结果

有这么一种场景，一个复杂的sql输出的结果，重复计算两次，如果比较两次的结果是否完全一致。

比如，修改了某个字段的表达式的值，想跟踪该值的新、旧两次变化情况。这都可以用except表达试来处理。联表，是作为对比，可能联表方式需要写各个字段更麻烦一些，但是对比更清楚。

-- except
select ship_id,exist_zhipao_code from test_data_uniq_20210902_02 Except ALL  select ship_id,exist_zhipao_code from test_data_uniq_20210902

-- except all
select ship_id,exist_zhipao_code from test_data_uniq_20210902_02 Except select ship_id,exist_zhipao_code from test_data_uniq_20210902

-- left join and  compare
select t2.ship_id,t1.exist_zhipao_code  as code1 ,t2.exist_zhipao_code as code2 from test_data_uniq_20210902_02 as t2 left join test_data_uniq_20210902 as t1 on t1.ship_id = t2.ship_id
select count(*) from test_duibi2 where code1 != code2 ;


-- 统计
select count(1) as cnt1,count(ship_id) as cnt2,count(distinct ship_id) as cnt3 from test_duibi;
select count(1) as cnt1,count(ship_id) as cnt2,count(distinct ship_id) as cnt3 from test_duibi1;
select count(1) as cnt1,count(ship_id) as cnt2,count(distinct ship_id) as cnt3 from test_duibi2;

经对比发现，Except All的结果会比Except多。

其他

求差集实践

DROP TABLE IF EXISTS tmp_check_mailno;
CREATE TABLE tmp_check_mailno WITH (
    APPENDONLY = TRUE,
    COMPRESSLEVEL = 5,
    ORIENTATION = COLUMN,
    COMPRESSTYPE = ZLIB
)  AS 



-- 求差集合
select mailno  from bm_order_data_02 where insert_time  >= '2021-8-15' and  insert_time < '2021-8-16' 
except 
select doc_sn as mailno from tu_doc_info where dtime >= '2021-8-15' and dtime < '2021-8-16'


DISTRIBUTED BY (mailno) 
;