[笔记] SQL join/on/ROW_NUMBER/LAG

目录

• inner join
• left/right outer join
• full outer join
• cross join (或不指定 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 也是不同的