[笔记] SQL join/on/ROW_NUMBER/LAG
inner join
inner join 可以简写为 join
产生两张表满足 on 条件的记录的交集
select
*
from
table_a
join
table_b
on
table_a.id = table_b.id
on 条件也可以是不等式
select
*
from
table_a
join
table_b
on
table_a.id != table_b.id
不等于这种方式比较少用
left/right outer join
left/right outer join 可以简写为 left/right join
以 left join 为例子，左表必然出现在结果中，
对于右表存在满足 on 条件的记录的，取交集，和 inner join 一样，
对于右表不存在满足 on 条件的记录的，取左表部分 + 补全 null
select
*
from
table_a
left join
table_b
on
table_a.id = table_b.id
right join 则相反
full outer join
full outer join 可以简写为 full join，
对于满足 on 条件的记录的，取交集，和 inner join 一样，
对于右表不存在满足 on 条件的记录的，取左表部分 + 补全 null，和 left join 一样，
对于左表不存在满足 on 条件的记录的，取右表部分 + 补全 null，和 right join 一样
select
*
from
table_a
full join
table_b
on
table_a.id = table_b.id
相当于 left join 和 right join 的结合
cross join (或不指定 join 关键字)
把 table_a 和 table_b 的数据进行一个 N*M 的组合
select
*
from
table_a
cross join
table_b
或者
select
*
from
table_a, table_b
不需要加 on 条件，加上会报错
on 条件用于过滤
on 条件除了做链接用，也可以做筛选用，
对于 inner join 过滤条件放在 on 还是 where 效果一样，而 outer join 效果不一样，
比如 left join 的左表有记录 a 而右表有记录 b 并且能满足 on 条件，如果是在 where 过滤比如
where table_right.name != 'xxxx'
那么 a 记录和 b 记录都不会出现在结果中，因为是先 join 再过滤，
而如果是在 on 过滤比如
on table_right.name != 'xxxx'
那么 a 记录依然出现在结果中，只是右表部分用 null 补全，因为先过滤掉 b 记录再 left join
ROW_NUMBER
ROW_NUMBER() OVER(
[PARTITION BY column_1, column_2,…]
[ORDER BY column_3,column_4,…]
)
按字段分组，然后组内排序，并按组内顺序添加组内行号列
比如下面的 SQL
SELECT
group_id,
created_time,
Row_Number() OVER (partition by group_id ORDER BY created_time desc) row_id
FROM
table_a
数据按 group_id 分组，组内按 created_time 排序，组内排序后的行号作为新字段 row_id 添加到记录中
group_id | created_time | row_id
----------+---------------------+--------
group_1 | 2021-08-06 11:13:32 | 1
group_1 | 2021-08-06 11:12:32 | 2
group_1 | 2021-08-06 11:11:32 | 3
group_2 | 2021-08-06 12:13:32 | 1
group_2 | 2021-08-06 12:12:32 | 2
group_3 | 2021-08-06 13:13:32 | 1
可以看到 row_id 就是分组内部的序号
LAG
获取前面的行的数据，格式如下
LAG(expression [, offset [, default_value]])
OVER (
[PARTITION BY partition_expression, ... ]
ORDER BY sort_expression [ASC | DESC], ...
)
expression 要获取的值，可以是列名、子查询等
offset 往前多少行，默认是 1
default_value 如果超出了范围，比如第一行的前面已经没有行了，就返回这个值，默认是 null
PARTITION BY ... ORDER BY ... 如何分组排序
比如现在有一张表是这样
group_id | mode | created_time | value
----------+------+---------------------+-------
group_1 | | 2021-08-06 11:11:32 | 100
group_1 | 2 | 2021-08-06 11:12:32 | 110
group_1 | 1 | 2021-08-06 11:13:32 | 130
group_1 | 1 | 2021-08-06 11:14:32 | 160
group_1 | 2 | 2021-08-06 11:15:32 | 200
group_1 | 1 | 2021-08-06 11:16:32 | 250
group_2 | 1 | 2021-08-06 12:12:32 | 210
group_2 | | 2021-08-06 12:13:32 | 250
group_2 | 1 | 2021-08-06 12:14:32 | 350
group_3 | 2 | 2021-08-06 13:13:32 | 320
group_3 | 2 | 2021-08-06 13:14:32 | 380
如果执行下面语句
SELECT
*,
LAG(created_time) OVER (
partition by group_id, coalesce(mode, '1')
ORDER BY created_time asc
) as pre_created_time,
LAG(value) OVER (
partition by group_id, coalesce(mode, '1')
ORDER BY created_time asc
) as pre_value,
Row_Number() OVER (
partition by group_id, coalesce(mode, '1')
ORDER BY created_time asc
) as row_id,
RANK() OVER (
partition by group_id
ORDER BY coalesce(mode, '1') asc
) as rank_id
FROM
table_c
结果返回
group_id | mode | created_time | value | pre_created_time | pre_value | row_id | rank_id
----------+------+---------------------+-------+---------------------+-----------+--------+---------
group_1 | | 2021-08-06 11:11:32 | 100 | | | 1 | 1
group_1 | 1 | 2021-08-06 11:13:32 | 130 | 2021-08-06 11:11:32 | 100 | 2 | 1
group_1 | 1 | 2021-08-06 11:14:32 | 160 | 2021-08-06 11:13:32 | 130 | 3 | 1
group_1 | 1 | 2021-08-06 11:16:32 | 250 | 2021-08-06 11:14:32 | 160 | 4 | 1
group_1 | 2 | 2021-08-06 11:12:32 | 110 | | | 1 | 5
group_1 | 2 | 2021-08-06 11:15:32 | 200 | 2021-08-06 11:12:32 | 110 | 2 | 5
group_2 | 1 | 2021-08-06 12:12:32 | 210 | | | 1 | 1
group_2 | | 2021-08-06 12:13:32 | 250 | 2021-08-06 12:12:32 | 210 | 2 | 1
group_2 | 1 | 2021-08-06 12:14:32 | 350 | 2021-08-06 12:13:32 | 250 | 3 | 1
group_3 | 2 | 2021-08-06 13:13:32 | 320 | | | 1 | 1
group_3 | 2 | 2021-08-06 13:14:32 | 380 | 2021-08-06 13:13:32 | 320 | 2 | 1
可以看到，就是按照 group_id, coalesce(mode, '1') 分组，然后按 created_time 排序，最后组内的每一条记录都会把前面一条记录的 created_time 和 value 拿过来，作为新字段 pre_created_time 和 pre_value，这样就可以计算两行数据间的差、和之类的操作
同时用 Row_Number() 给分组排序后的数据打上行号，作为新字段 row_id
而 RANK() 则是按值排大小，这里按 group_id 分组，按 coalesce(mode, '1') 排序，如果值 coalesce(mode, '1') 相同，那么 rank_id 就是一样的，并且下一个 rank_id 不是简单加 1，而是按实际排位来的，如果是 Row_Number() 的话就算值相同 id 也是不同的
