为什么这些连接根据大小不同？

debugcn 发表于 Dev

马丁

在Postgresql中，如果取消嵌套两个相同大小的数组，它们会将一个数组中的每个值与另一个数组中的一个对齐，但是如果两个数组的大小不同，则它将一个中的每个值与另一个中的每个值连接在一起。

select unnest(ARRAY[1, 2, 3, 4, 5]::bigint[]) as id,
unnest(ARRAY['a', 'b', 'c', 'd', 'e']) as value

将返回

1 | "a"
2 | "b"
3 | "c"
4 | "d"
5 | "e"

但

select unnest(ARRAY[1, 2, 3, 4, 5]::bigint[]) as id, -- 5 elements
unnest(ARRAY['a', 'b', 'c', 'd']) as value -- 4 elements
order by id

将返回

1 | "a"
1 | "b"
1 | "c"
1 | "d"
2 | "b"
2 | "a"
2 | "c"
2 | "d"
3 | "b"
3 | "d"
3 | "a"
3 | "c"
4 | "d"
4 | "a"
4 | "c"
4 | "b"
5 | "d"
5 | "c"
5 | "b"
5 | "a"

为什么是这样？我假设正在使用某种隐式规则，并且我想知道是否可以显式地执行它（例如，当我具有匹配的数组大小时是否想要第二种样式，或者是否希望一个数组中的值丢失）视为NULL）。

克雷格·林格（Craig Ringer）

SELECTPostgreSQL扩展是对set-returning函数的支持，而IMO非常奇怪。人们普遍认为它已过时，最好在可能的情况下避免使用。

避免`SELECT`在可能的地方使用SRF-in-

现在LATERAL在9.3中受支持，两个主要用途之一已消失。SELECT如果您想将一个SRF的输出用作另一个SRF的输入，则必须使用set-returning函数。不再需要LATERAL。

添加后，其他用法将在9.4中替换WITH ORDINALITY，从而使您可以保留set-returning函数的输出顺序。当前，这是主要的剩余用途：执行诸如将两个SRF的输出压缩到匹配值对的行集中的操作。WITH ORDINALITY最预期用于unnest，但可与任何其他SRF一起使用。

为什么输出奇怪？

PostgreSQL在这里使用的逻辑（由于IMO在古代历史中最初引入的IMO疯狂原因）是：每当任何一个函数产生输出时，就发出一行。如果只有一个函数产生了输出，请再次扫描另一个函数的输出以获取所需的行。如果两者均未产生输出，请停止发射行。

使用比较容易看到generate_series。

regress=> SELECT generate_series(1,2), generate_series(1,2);
 generate_series | generate_series 
-----------------+-----------------
               1 |               1
               2 |               2
(2 rows)

regress=> SELECT generate_series(1,2), generate_series(1,3);
 generate_series | generate_series 
-----------------+-----------------
               1 |               1
               2 |               2
               1 |               3
               2 |               1
               1 |               2
               2 |               3
(6 rows)

regress=> SELECT generate_series(1,2), generate_series(1,4);
 generate_series | generate_series 
-----------------+-----------------
               1 |               1
               2 |               2
               1 |               3
               2 |               4
(4 rows)

在大多数情况下，您真正想要的是两者之间的简单交叉联接，这非常明智。

regress=> SELECT a, b FROM generate_series(1,2) a, generate_series(1,2) b;
 a | b 
---+---
 1 | 1
 1 | 2
 2 | 1
 2 | 2
(4 rows)

regress=> SELECT a, b FROM generate_series(1,2) a, generate_series(1,3) b;
 a | b 
---+---
 1 | 1
 1 | 2
 1 | 3
 2 | 1
 2 | 2
 2 | 3
(6 rows)

regress=> SELECT a, b FROM generate_series(1,2) a, generate_series(1,4) b;
 a | b 
---+---
 1 | 1
 1 | 2
 1 | 3
 1 | 4
 2 | 1
 2 | 2
 2 | 3
 2 | 4
(8 rows)

当前主要的例外是当您希望以成对的锁步方式（例如a zip）运行多个函数时，当前无法使用联接执行此操作。

`WITH ORDINALITY`

它将在9.4中使用WITH ORDINALITY，ad进行改进，尽管它的效率要比SELECT中的多次SRF扫描要低一些（除非添加了优化程序改进），但它会更精明。

假设您要配对，1..3并10..40为多余的元素添加空值。使用的with ordinality将是（仅限PostgreSQL 9.4）：

regress=# SELECT aval, bval 
           FROM generate_series(1,3) WITH ORDINALITY a(aval,apos) 
           RIGHT OUTER JOIN generate_series(1,4) WITH ORDINALITY b(bval, bpos) 
           ON (apos=bpos);

 aval | bval 
------+------
    1 |    1
    2 |    2
    3 |    3
      |    4
(4 rows)

srf-in-from将返回：

regress=# SELECT generate_series(1,3) aval, generate_series(1,4) bval;
 aval | bval 
------+------
    1 |    1
    2 |    2
    3 |    3
    1 |    4
    2 |    1
    3 |    2
    1 |    3
    2 |    4
    3 |    1
    1 |    2
    2 |    3
    3 |    4
(12 rows)

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。

编辑于2021-06-7

我来说两句

0条评论

登录后参与评论

来自分类Dev

Related 相关文章

文章