一、 on 和 where 条件执行顺序

inner join：

二者同时发挥作用。

若有「分区」条件，则优先执行分区条件。

会对on中关联键进行 is not NULL 过滤操作

left join/right join:

非主表时：先执行on ，再执行left join，再执行where

主表：先 where on ,再执行left join

Tip: a left join b. a:主表 b:非主表

Eg:

   select t1.a,t2.b 
   from t1 
   left join t2 
   on t1.c=t2.c 
   where t1.d>1 and t2.e>1;

先执行 t1.d>1. 再执行 t1.c=t2.c, 最后结果集执行 t2.e>1

建议

inner join 都一样，先过滤再join

left/right join 对主表过滤条件写到where条件中，对非主表过滤条件写到 on中，可以保证先过滤再join速度快。

参考：https://www.cnblogs.com/zsql/p/14183904.html#_label1

二、单行多行转换

字符串拼接：concat & concat_ws

concat(str1, str2, str3) -> str1str2str3
​
Concat_ws(sep, str1, str2) -> str1sepstr2

多行变一列

concat_ws(sep, collect_set(col))  -- collect_set 可以变成collect_list

一行变多行

lateral view explode(ori_col_name(数组列表)) table_tmp as new_col_name;
-- ori_col_name为数组形式, explode 炸裂这个列表

eg: 数据

col1   col2     col3
a       b       1,2,3
c       d       4,5,6
​
select col1, col2, col4
from test
lateral view explode(split(col3, ',')) table_tmp as col4;
​
col1   col2     col4
a       b       1
a       b       2
a       b       3
c       d       4
c       d       5
c       d       6

三 开窗函数 over()

聚合函数(col_1) over(聚合函数执行条件) 
​
示例：
sum(amt) over (partition by province) as province_amt 统计每个省的花费
​
sum(amt) over(order by amt rows between unbounded preceding and current row) as 累计_amt: amt 升序排列并计算累计和
​
row_number() over(partition by sex order by age desc) as rank
​

参考：https://www.dazhuanlan.com/2020/02/27/5e57376ead208/

四 排序函数: row_number & rank & dense_rank

row_number() ：值相同也按照 1234

rank() ：值相同排名相同会跳跃 11335

dense_rank: 值相同排名相同不会跳跃 11223