如何访问匿名记录字段？拆分SQL聚合元组为独立列

问题背景 #

现有PostgreSQL查询如下：

WITH t as (
    SELECT name, array_agg(DISTINCT("age", "gender")) as "ages_and_genders"
    FROM (
        SELECT * FROM (VALUES ('bob', 33, 'm'), ('bob', 33, 'f'), ('alice', 30, 'f')) AS t ("name","age", "gender")
    ) as t
    GROUP BY name
)

SELECT name, "ages_and_genders"[1]
FROM t
WHERE array_length("ages_and_genders", 1) = 1

当前查询返回的ages_and_genders[1]是匿名元组，需将其拆分为age和gender独立列，得到预期结果：

name    | age | gender
-------------------
"alice" | 30  | 'f'

解决方案 #

方法1：直接拆分现有查询的匿名元组 #

PostgreSQL中匿名元组的字段可通过f1、f2（对应元组第1、第2个元素）直接引用，修改查询的SELECT部分即可：

WITH t as (
    SELECT name, array_agg(DISTINCT("age", "gender")) as "ages_and_genders"
    FROM (
        SELECT * FROM (VALUES ('bob', 33, 'm'), ('bob', 33, 'f'), ('alice', 30, 'f')) AS t ("name","age", "gender")
    ) as t
    GROUP BY name
)

SELECT 
    name,
    "ages_and_genders"[1].f1 AS age,
    "ages_and_genders"[1].f2 AS gender
FROM t
WHERE array_length("ages_and_genders", 1) = 1

如果数组长度固定为1，也可通过unnest展开数组后提取字段：

WITH t as (
    SELECT name, array_agg(DISTINCT("age", "gender")) as "ages_and_genders"
    FROM (
        SELECT * FROM (VALUES ('bob', 33, 'm'), ('bob', 33, 'f'), ('alice', 30, 'f')) AS t ("name","age", "gender")
    ) as t
    GROUP BY name
)

SELECT 
    name,
    (unnest("ages_and_genders")).f1 AS age,
    (unnest("ages_and_genders")).f2 AS gender
FROM t
WHERE array_length("ages_and_genders", 1) = 1

方法2：优化原查询（更高效简洁） #

原查询通过array_agg筛选唯一组合数为1的记录，可直接用GROUP BY+HAVING或窗口函数实现，避免数组操作：

方式A：GROUP BY + HAVING

SELECT 
    name,
    age,
    gender
FROM (
    SELECT * FROM (VALUES ('bob', 33, 'm'), ('bob', 33, 'f'), ('alice', 30, 'f')) AS t ("name","age", "gender")
) as t
GROUP BY name, age, gender
HAVING COUNT(*) = (SELECT COUNT(*) FROM t t2 WHERE t2.name = t.name)

方式B：窗口函数

WITH t as (
    SELECT 
        name, age, gender,
        COUNT(DISTINCT (age, gender)) OVER (PARTITION BY name) AS distinct_count
    FROM (VALUES ('bob', 33, 'm'), ('bob', 33, 'f'), ('alice', 30, 'f')) AS t ("name","age", "gender")
)
SELECT DISTINCT name, age, gender
FROM t
WHERE distinct_count = 1

内容的提问来源于stack exchange，提问作者aren55555