开发者社区> 云化数据仓库> 正文

ADB PG 6.0 新特性支持 - LATERAL语法

简介: 随着ADB PG 6.0对PG内核的升级,ADB PG 6.0中支持了LATERAL语法。LATERAL语法在Select语句中,LATERAL关键词可以放置在sub-select的from对象之前,这样在sub-select中,在LATERAL后的对象可以引用其之前对象的列(如果不使用LATERAL,sub-select的各个对象会被单独处理,所以就无法引用from列表中的其它对象)。

随着ADB PG 6.0对PG内核的升级,ADB PG 6.0中支持了LATERAL语法。

LATERAL语法

在Select语句中,LATERAL关键词可以放置在sub-select的from对象之前,这样在sub-select中,在LATERAL后的对象可以引用其之前对象的列(如果不使用LATERAL,sub-select的各个对象会被单独处理,所以就无法引用from列表中的其它对象)。

LATERAL也可以放置在from列表中函数对象之前,这样在函数中就可以引用在其之前任何from列表中的其它对象。另外,LATERAL对象也可以出现在最外层的from列表中。

具体语法使用如下:

[ WITH [ RECURSIVE ] with_query [, ...] ]
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
    [ * | expression [ [ AS ] output_name ] [, ...] ]
    [ FROM from_item [, ...] ]
    [ WHERE condition ]
    [ GROUP BY expression [, ...] ]
    [ HAVING condition [, ...] ]
    [ WINDOW window_name AS ( window_definition ) [, ...] ]
    [ { UNION | INTERSECT | EXCEPT } [ ALL | DISTINCT ] select ]
    [ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
    [ LIMIT { count | ALL } ]
    [ OFFSET start [ ROW | ROWS ] ]
    [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]
    [ FOR { UPDATE | NO KEY UPDATE | SHARE | KEY SHARE } [ OF table_name [, ...] ] [ NOWAIT ] [...] ]

where from_item can be one of:

    [ ONLY ] table_name [ * ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
    [ LATERAL ]( select ) [ AS ] alias [ ( column_alias [, ...] ) ]
    with_query_name [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
    [ LATERAL ] function_name ( [ argument [, ...] ] )
                [ WITH ORDINALITY ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
    [ LATERAL ] function_name ( [ argument [, ...] ] ) [ AS ] alias ( column_definition [, ...] )
    [ LATERAL ] function_name ( [ argument [, ...] ] ) AS ( column_definition [, ...] )
    [ LATERAL ] ROWS FROM( function_name ( [ argument [, ...] ] ) [ AS ( column_definition [, ...] ) ] [, ...] )
                [ WITH ORDINALITY ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
    from_item [ NATURAL ] join_type from_item [ ON join_condition | USING ( join_column [, ...] ) ]

and with_query is:

    with_query_name [ ( column_name [, ...] ) ] AS ( select | values | insert | update | delete )

TABLE [ ONLY ] table_name [ * ]

当from列表中某个LATERAL对象有对其它对象的引用时,查询处理流程如下:对于被引用对象的每一行,LATERAL对象会使用改行做查询处理,处理完得到的结果会与源对象的行做join。因此,源对象中的列必须是和LATERAL对象做INNER或者LEFT join,因为尽管X RIGHT JOIN LATERAL Y在语法层面是有效的,但是实际上Y来引用X是不行的(因为需要从源表展开)。

示例:

SELECT * FROM foo, LATERAL (SELECT * FROM bar WHERE bar.id = foo.bar_id) ss;

create table test (c1 int, c2 int, c3 int);
insert into test values (1,1,1),(1,2,3),(1,3,3),(2,2,2),(2,3,2),(2,4,2),(3,4,1),(3,4,2);

create table teest1 (c1 int, c2 int);
insert into test1 values (1,2),(2,3),(3,4);

select test1.c1 from test1 left join LATERAL (select * from test where test1.c1 = test.c1) tt on true;

with t1 as (select c1, avg(c2) as avg_c2 from test group by c1)
select tbl.c1, tbl.c2, tbl.c3, t1.avg_c2 from test tbl, t1 where tbl.c1 = t1.c1;

上面的with语句可以等价写成下面LATERAL语句

select test.*, t1.avg_c2 from test, lateral (select c1, avg(c2) as avg_c2 from test group by c1) t1 where test.c1 = t1.c1;

参考资料

https://www.postgresql.org/docs/9.4/sql-select.html

版权声明:本文中所有内容均属于阿里云开发者社区所有,任何媒体、网站或个人未经阿里云开发者社区协议授权不得转载、链接、转贴或以其他方式复制发布/发表。申请授权请邮件developerteam@list.alibaba-inc.com,已获得阿里云开发者社区协议授权的媒体、网站,在转载使用时必须注明"稿件来源:阿里云开发者社区,原文作者姓名",违者本社区将依法追究责任。 如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件至:developer2020@service.aliyun.com 进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
上一篇:ADB PG 6.0 新特性支持 - Range数据类型
官网链接