postgresql - 根据默认顺序加入 2 组

Question

如何仅根据默认顺序加入 2 组记录？

所以如果我有一个表 x(col(1,2,3,4,5,6,7)) 和另一个表 z(col(a,b,c,d,e,f,g))

它会回来

c1 c2
-- --    
1   a
2   b
3   c
4   d
5   e
6   f
7   g

实际上，我想从参数中加入一对一维数组，并将它们视为表中的列。

示例代码:

CREATE OR REPLACE FUNCTION "Test"(timestamp without time zone[],
                                  timestamp without time zone[])
  RETURNS refcursor AS
$BODY$
DECLARE
curr refcursor;
BEGIN
    OPEN curr FOR 
        SELECT DISTINCT "Start" AS x, "End" AS y, COUNT("A"."id") 
        FROM UNNEST($1) "Start" 
        INNER JOIN 
        (
            SELECT "End", ROW_NUMBER() OVER(ORDER BY ("End")) rn
                FROM UNNEST($2) "End" ORDER BY ("End") 
        ) "End" ON ROW_NUMBER() OVER(ORDER BY ("Start")) = "End".rn 
        LEFT JOIN "A" ON ("A"."date" BETWEEN x AND y) 
        GROUP BY 1,2 
        ORDER BY "Start";
    return curr;
END

$BODY$

Answer 1

现在，回答评论中显示的真实问题，它看起来是这样的:

Given two arrays 'a' and 'b', how do I pair up their elements so I can get the element pairs as column aliases in a query?

有几种方法可以解决这个问题:

• 当且仅当数组长度相等时，在 SELECT 子句中使用多个 unnest 函数(一种不推荐使用的方法，仅应用于向后兼容性)；

• 使用generate_subscripts 遍历数组；

• 如果您需要支持，对 array_lower 和 array_upper 的子查询使用 generate_series 来模拟 generate_subscripts版本太旧，没有 generate_subscripts;

• 根据 unnest 返回元组的顺序并希望 - 就像在我的其他答案中一样，如下所示。它会起作用，但不能保证在未来的版本中起作用。

• 使用 the WITH ORDINALITY functionality added in PostgreSQL 9.4 (另请参阅 its first posting)在 9.4 发布时获取 unnest 的行号。

• 使用多数组 UNNEST，这是 SQL 标准但 which PostgreSQL doesn't support yet .

所以，假设我们有函数 arraypair 和数组参数 a 和 b:

CREATE OR REPLACE FUNCTION arraypair (a integer[], b text[]) 
RETURNS TABLE (col_a integer, col_b text) AS $$
  -- blah code here blah
$$ LANGUAGE whatever IMMUTABLE;

它被调用为:

SELECT * FROM arraypair( ARRAY[1,2,3,4,5,6,7], ARRAY['a','b','c','d','e','f','g'] );

可能的函数定义是:

SRF-in-SELECT(已弃用)

CREATE OR REPLACE FUNCTION arraypair (a integer[], b text[])
RETURNS TABLE (col_a integer, col_b text) AS $$
    SELECT unnest(a), unnest(b);
$$ LANGUAGE sql IMMUTABLE;

如果数组的长度不相等，将会产生奇怪的和意想不到的结果；请参阅有关集合返回函数及其在 SELECT 列表中的非标准用法的文档，以了解原因以及究竟发生了什么。

generate_subscripts

这可能是最安全的选择:

CREATE OR REPLACE FUNCTION arraypair (a integer[], b text[])
RETURNS TABLE (col_a integer, col_b text) AS $$
    SELECT
       a[i], b[i]
    FROM generate_subscripts(CASE WHEN array_length(a,1) >= array_length(b,1) THEN a::text[] ELSE b::text[] END, 1) i;
$$ LANGUAGE sql IMMUTABLE;

如果数组的长度不等，如所写，它将为较短的返回 null 元素，因此它的工作方式类似于完整的外部连接。颠倒案例的意义以获得类似内部连接的效果。该函数假定数组是一维的，并且它们从索引 1 开始。如果整个数组参数为 NULL，则该函数返回 NULL。

一个更通用的版本将用 PL/PgSQL 编写并检查 array_ndims(a) = 1，检查 array_lower(a, 1) = 1，测试空数组等。我会把它留给你。

希望成对返回:

这不能保证有效，但对 PostgreSQL 的当前查询执行器有效:

CREATE OR REPLACE FUNCTION arraypair (a integer[], b text[])
RETURNS TABLE (col_a integer, col_b text) AS $$
 WITH
    rn_c1(rn, col) AS (
      SELECT row_number() OVER (), c1.col
      FROM unnest(a) c1(col) 
    ),
    rn_c2(rn, col) AS (
      SELECT row_number() OVER (), c2.col
      FROM unnest(b) c2(col)
    )
    SELECT
      rn_c1.col AS c1, 
      rn_c2.col AS c2
    FROM rn_c1 
    INNER JOIN rn_c2 ON (rn_c1.rn = rn_c2.rn);
$$ LANGUAGE sql IMMUTABLE;

我会考虑使用 generate_subscripts 更安全。

多参数unnest:

这应该工作，但不是因为 PostgreSQL 的 unnest 不接受多个输入数组(目前):

SELECT * FROM unnest(a,b);