从前慢现在也慢

这个屌丝很懒，什么也没留下！

热门标签

介绍 KingbaseES 的 Lateral 连接

作者：从前慢现在也慢 | 2024-06-27 08:07:23

踩

left join lateral 是什么意思

什么是 Lateral 连接
Lateral 的用途
Lateral 的限制

什么是 Lateral 连接

根据文档，它的作用是：

LATERAL 关键字可以位于子 SELECT FROM 项之前。这允许子 SELECT 引用 FROM 列表中出现在它之前的 FROM 项的列。(没有 LATERAL，每个子 SELECT 都是独立评估的，因此不能交叉引用任何其他 FROM 项。)
FROM中出现的表函数，前面也可以加上关键字Lateral，但对于函数来说，Lateral是可选的；FROM在任何情况下，函数的参数都可以引用对前“表”的列。

基本上，它的作用是对于主“表”中的每一行，它使用主选择行作为参数来计算子查询。与for循环遍历查询返回的行，非常相似。

Lateral 的用途

语法糖

语法糖(Syntactic sugar)，指计算机语言中添加的某种语法，这种语法对语言的功能并没有影响，但是更方便程序员使用。通常来说使用语法糖能够增加程序的可读性，从而减少程序代码出错的机会。

横向连接允许您重用计算列，使您的查询整洁易读。让我们通过一起重写一个糟糕的查询来了解横向连接。


select
    (pledged / fx_rate) as pledged_usd,
    (pledged / fx_rate) / backers_count as avg_pledge_usd,
    (goal / fx_rate) - (pledged / fx_rate) as amt_from_goal,
    (deadline - launched_at) / 86400.00 as duration,
    ((goal / fx_rate) - (pledged / fx_rate)) / ((deadline - launched_at) / 86400.00) as usd_needed_daily
from kickstarter_data;

使用横向连接，可以只定义一次计算列，然后可以在查询的其他部分引用这些列值。


select
    pledged_usd,
    avg_pledge_usd,
    amt_from_goal,
    duration,
    (usd_from_goal / duration) as usd_needed_daily
from kickstarter_data,
    lateral (select pledged / fx_rate as pledged_usd) pu,
    lateral (select pledged_usd / backers_count as avg_pledge_usd) apu,
    lateral (select goal / fx_rate as goal_usd) gu,
    lateral (select goal_usd - pledged_usd as usd_from_goal) ufg,
    lateral (select (deadline - launched_at)/86400.00 as duration) dr;

子查询增强模式

LATERAL连接更像是相关子查询，而不是普通子查询，因为LATERAL连接右侧的表达式对其左侧的每一行进行比较 - 就像相关子查询一样 - 而普通子查询只根据关联条件比较一次。(查询计划器有方法，可以优化两者的性能。)
相关答案与代码示例并排解决相同的问题：

优化 GROUP BY 查询以检索每个用户的最新行
对于返回多个 column，LATERAL连接通常更简单、更干净和更快。
另外，请记住，相关子查询的等价物是LEFT JOIN LATERAL ... ON true：

LATERAL 和交叉应用是一回事。

参考以下查询。


Select A.*
, (Select min(B.val) Column1 from B where B.Fk1 = A.PK  )
, (Select max(B.val) Column2 from B where B.Fk1 = A.PK  )
FROM A ;

在这种情况下，可以使用 LATERAL 。


Select A.*
     , x.Column1
     , x.Column2
FROM A
         LEFT JOIN LATERAL (
    Select  min(B.val) Column1, max(B.val) Column2, B.Fk1 from B where B.Fk1 = A.PK 
    ) x ON true;

在此查询中，由于条件子句，不能使用普通连接，可以使用 LATERAL 或交叉应用。

LATERAL 或交叉应用有更多用法，但这是最常见的一种。

避免重复执行子查询或函数

用户行为A的每行记录的optdate值，与用户行为B表的最大 optdate值并列表格。

数据准备

用户字典表，用户行为A表，用户行为B表


create table users (user_id int PRIMARY KEY, username text);
 
create table optA (opta_id int PRIMARY KEY, user_id int, optdate timestamp , note text) ;
 
create index optA_i1 on optA(user_id,optdate desc );
 
create table optB (optb_id int PRIMARY KEY, user_id int, optdate timestamp , note text) ;
 
create index optB_i1 on optB(user_id,optdate desc );

普通语句

每行都执行用户行为B表的查询语句，消耗很多CPU时长


select optA.*,(select max(optdate) from optB where optB.user_id=optA.user_id) 
from optA
where opta.user_id=88;

基本优化

使用子查询，实现避免重复执行用户行为B表的查询语句


select optA.*, optB.*
from optA
         join (select user_id, max(optdate) from optB group by user_id) optB on optB.user_id = optA.user_id
where opta.user_id = 88;

LATERAL 连接

依然有逐行执行子查询的现象


select optA.*, optB.*
from optA
         cross join lateral (select max(optdate)
                             from optB
                             where optB.user_id = optA.user_id) optB
where opta.user_id = 88;

使用字典表和 LATERAL 连接

在两个事实表之间，使用字典表作为过度，可以避免重复执行子查询


select optA.*, optB.*
from optA
    join users on users.user_id=optA.user_id
         cross join lateral (select max(optdate)
                             from optB
                             where optB.user_id = users.user_id) optB
where opta.user_id = 88;

分组查询，获得每个用户的最新时间，或者最新行

数据准备

用户日志表，包含user_id 和 log_date


create table log
(
    log_date timestamp,
    user_id  int,
    note     text
);
 
insert into log
select now() - ((100000 * random())::numeric(20, 3)::text)::interval log_date,
       (random() * 1000000)::int % 100                               id,
       md5(id)
from generate_series(1, 100000) id
order by random();
 
-- 索引列与排序列的次序和模式，保持一致
create index log_i1 on log (user_id, log_date DESC NULLS LAST);

普通语句

顺序扫描log表或复合条件的所有记录，通过聚合函数max和窗口函数row_number


explain analyse
select user_id, max(log_date)
from log
group by user_id;
 
explain analyse
select *
from (select *, row_number() over (partition by user_id order by log_date desc ) sn from log) l
where sn = 1;

递归 CTE 语句

方便检索单列或整行，使用表格的整行类型。仅读取每个用户的最新记录，使用的总数据块数和执行时长，远少于普通语句。


WITH RECURSIVE cte AS (
    ( -- 需要括号
        SELECT l AS my_row -- 整行记录
        FROM log l
        ORDER BY user_id, log_date DESC NULLS LAST
        LIMIT 1
    )
    UNION ALL
    SELECT (SELECT l -- 整行记录
            FROM log l
            WHERE l.user_id > (c.my_row).user_id
            ORDER BY l.user_id, l.log_date DESC NULLS LAST
            LIMIT 1)
    FROM cte c
    WHERE (c.my_row).user_id IS NOT NULL
)
SELECT (my_row).* -- 分解行
FROM cte
WHERE (my_row).user_id IS NOT NULL
ORDER BY (my_row).user_id;

使用 LATERAL 连接的递归 CTE 语句

递归 CTE 语句，逻辑复杂不易理解，而且每行记录的列，有聚合分解计算。
使用LATERAL 连接，不仅语句易读，而且可以节省10%的CPU计算时长。


WITH RECURSIVE cte AS (
    ( -- 需要括号
        SELECT * 
        FROM log
        WHERE 1 = 1
        ORDER BY user_id, log_date DESC NULLS LAST
        LIMIT 1
    )
    UNION ALL
    SELECT l.*
    FROM cte c
             CROSS JOIN LATERAL (
        SELECT l.*
        FROM log l
        WHERE l.user_id > c.user_id -- lateral 参照条件
        ORDER BY l.user_id, l.log_date DESC NULLS LAST
        LIMIT 1
        ) l
)
    TABLE cte
        ORDER BY user_id;

users字典表和 LATERAL 连接

只要user_id保证每个相关项恰好有一行，表格布局就几乎无关紧要，理想情况下，表格的物理排序与log表格同步。
查询语句，包含字典表和 LATERAL 连接。由于使用更简洁的查询树，执行时长较递归CTE节省10%。


CREATE TABLE users (
   user_id  INT PRIMARY KEY
 , username text NOT NULL
);
 
insert into users select generate_series(1,100) id, md5(id) ;
 
SELECT u.user_id, l.*
FROM users u
         cross join  LATERAL (
    SELECT l.*
    FROM log l
    WHERE l.user_id = u.user_id -- lateral参照
    ORDER BY l.log_date DESC NULLS LAST
    LIMIT 1
    ) l ;

不使用 LATERAL 连接的 select 子查询

拥有users字典表时，也可以不依靠 LATERAL 连接，达到不读取多余记录的查询语句。
由于记录行，分解成若干列，需要CPU计算用时，比 LATERAL 连接多用时长10%，且与列的数量正相关。


SELECT  (combo1).* 
FROM (
   SELECT u.user_id
        , (SELECT (l.*)::log
           FROM   log l
           WHERE  l.user_id = u.user_id
           ORDER  BY l.log_date DESC NULLS LAST
           LIMIT  1) AS combo1
   FROM   users u
   ) sub;

Lateral 的限制

数据类型转换，cast vs :: 。

这两种语法格式，都是“显式类型转换”，完全相同。在SQL代码中的某些特殊位置的表达式，只允许使用函数式表示法。


-- 合法一
SELECT elem[1], elem[2]
FROM   ( VALUES ('1,2'::TEXT) ) AS q(arr),
       LATERAL CAST(String_To_Array(q.arr, ',') AS INT[]) AS elem
;
 
-- 合法二
SELECT elem[1], elem[2]
FROM   ( VALUES ('1,2'::TEXT) ) AS q(arr),
       LATERAL  (select String_To_Array(q.arr, ',')::INT[] AS elem) as t
;
 
-- 非法
SELECT elem[1], elem[2]
FROM   ( VALUES ('1,2'::TEXT) ) AS q(arr),
       LATERAL String_To_Array(q.arr, ',')::int[] AS elem ；
错误:  语法错误 在 "::" 或附近的
第3行       LATERAL String_To_Array(q.arr, ',')::int[] AS elem;

另一种SQL语句，CREATE INDEX 语句，也会触发相同的错误信息。如果使用cast函数和数据类型名函数，则是合法的 create index 语句。
create index t02_i1 on t02 (id::int);
错误:  语法错误 在 "::" 或附近的
第1行create index t02_i2 on t02 (id::int);
 
-- 合法改写 CREATE INDEX 语句
create index t02_i1 on t02 ((id::int));

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/从前慢现在也慢/article/detail/761808