Hive left join on and where 对结果集的影响 + group by 和 row_number 的执行顺序

Hive 的 sql 过程

select * from table 历经下面几个过程：

• 解析：将SQL字符串解析成语法树AST，判断表是否存在、字段是否正确
• 编译：将AST编译成逻辑执行计划
• 优化：对执行计划进行优化
• 执行：逻辑执行计划转为物理执行计划，MR/Spark
  另外：

Hive 仅仅支持等值连接，on a.id >= b.id 不允许，on a.id = b.id 是可以的。

1，深入理解 left join on and where 筛选条件对数据的影响

下面将从实际的小例子中去一起发现 在left join 语法条件下，on and where 三个关键字对结果集产生的影响

1.1 数据准备：

order：订单表， 其中order_no有两条订单，是由于未知的业务原因造成的，属于脏数据，这里认为order_no = 2 的为脏数据。

order_goods : 订单商品表，一个订单对应多个订单商品。order_no=2 的记录的下单日期是 2021-05-23，这里由于某种原因，order_goods表中的日期错了一条。

1.2 on 后添加and 作为关联条件

1.3 on 后 添加 and（从表） 作为关联过滤条件

1.4 on 后面 添加 where 作为过滤条件

2.1 子查询

2.2 on 后面添加 and （针对主表）

2.x总结：

1.不考虑where条件下,left join 会把左表所有数据查询出来，on及其后面的条件仅仅会影响右表的数据(符合就显示,不符合全部为null)，如果on 后面的and 条件针对主表，满足and条件的主表记录参与和从表的匹配，不满足的直接关联的时候 填写 null值；如果on 后面and 条件针对 从表，将会对从表进行剪裁，然后再去和主表关联

2.在匹配阶段，where子句的条件都不会被使用，仅在匹配阶段完成以后，where子句条件才会被使用，它将从匹配阶段产生的数据中检索过滤，所以左连接关注的是左边的主表数据，不应该把on后面的从表中的条件加到where后，这样会影响原有主表中的数据

3.对于条件在on加个and还是用子查询，查询结果是一模一样的，至于如何使用这个需要分情况，用子查询的话会多一个maptask，但是如果利用这个子查询能过滤到很多数据的话，用子查询还是比较建议的，因为不会加载太多的数据到内存中，如果过滤数据不多的情况下，建议用on后面加and条件。

3，group by 和 row_number 的执行顺序

对于order_goods 表而言

SQL 1：

SQL 2

sql3

可以发现SQL1 等效于SQL2 ，从这里可以得出结论:
group by A,B,C 之后，A,B,C 的记录数都恒定为1，row_number等窗口函数仅仅针对group by之后的数据集，并且，where优先于 窗口函数的执行顺序

对比SQL2 和 SQL3，可以得出结论：
group by 之后进行 开窗，是对group by之后的结果进行开窗，相对于不group by (也即直接开窗)结果集更少

sql 4

sql 5

而下面的语法无法通过：

select order_no
     , order_date
     , row_number() over (partition by order_no order by sku_num desc ) rk
from order_goods
group by order_no, order_date

full join 的作用

full join 在数仓中通常用来历史数据量和增量数据的合并，从下面的例子中注意where 条件的前后顺序对 full join 之后的结果集的影响：

-- full join 主表t1 会在full join 之后 where 是不一样的,这一点和left join 并不相同
select coalesce(t1.field, t2.field) as field
from `table_A` t1
full join
(
    select *
    from `table_A`
    where date_id = '${D-T_1}'
) t2
on t1.id = t2.id
where t1.date_id = '${D-T}'
;

select coalesce(t1.field, t2.field) as field
from (
    select *
    from `table_A`
    where date_id = '${D-T}'
) t1
full join
(
    select *
    from `table_A`
    where date_id = '${D-T_1}'
) t2
on t1.id = t2.id
;

left semi join （左半连接） 的作用

从下面的case中取理解 left semi join 的作用：

select t1.id, t1.fieldA
from `table_A` t1
where t1.id in (
    select id
    from `tmp_B`
)
;
-- 改写为：
select t1.* -- 不允许出现t2 的字段
from `table_A` t1
left semi join  `table_B` t2
on (t1.id = t2.id)
;