时间戳

首页 > 解决方案 > 制作每组值 1-4 的列

postgresql - 制作每组值 1-4 的列

问题描述

我有一个 postgres 9.6 数据库，其中包含一个包含人员和国籍的表，如下所示：

  person_id   nationality  
 ----------- ------------- 
          1   American     
          2   British      
          3   Canadian     
          3   Dutch        
          3   Ethiopian    
          3   French       
          3   German

我正在制作一张用于分析目的的表格，其中每人包含一行。我想为每人的前四个国籍添加四列。这是我的预期结果：

  person_id    nat_a     nat_b     nat_c     nat_d   
 ----------- ---------- ------- ----------- -------- 
          1   American                               
          2   British                                
          3   Canadian   Dutch   Ethiopian   French

人 3 的第五个国籍（德国）由于是第五个，所以不可见。人 1 和 2 的国籍 B 到 D 是NULLs。

我目前正在通过以下方式创建此表：

SELECT DISTINCT
    person_id,
    nth_value(nationality, 1) OVER w AS nat_a,
    nth_value(nationality, 2) OVER w AS nat_b,
    nth_value(nationality, 3) OVER w AS nat_c,
    nth_value(nationality, 4) OVER w AS nat_d
FROM nationalities
WINDOW w AS (PARTITION BY person_id ORDER BY nationality ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

此查询按预期交付结果。但是，我对它的方法不太满意。因为nth_value是窗口函数，所以需要指定一个窗口，然后再应用一个DISTINCT操作。我更喜欢使用 aGROUP BY或类似的东西。

有没有更有效的方法来解决这个问题？

标签： postgresqlwindow-functionspostgresql-9.6

解决方案

如果不想使用 WINDOW 函数，可以使用 Postgres 的LATERAL 子查询：

SELECT DISTINCT person_id、a.nat_a、b.nat_b、c.nat_c、d.nat_d
从国籍
    -------------------------------------------------- --------
    -  一个
    内连接横向
    (
        SELECT person_id, MIN(国籍)
        从国籍
        GROUP BY person_id
    ) AS a(person, nat_a) ON a.person = nationalities.person_id
    -------------------------------------------------- --------
    -- 乙
    左连接横向
    (
        SELECT person_id, MIN(国籍)
        从国籍
        WHERE 国籍 > a.nat_a
        GROUP BY person_id
    ) AS b(person, nat_b) ON b.person = nationalities.person_id
    -------------------------------------------------- --------
    -  C
    左连接横向
    (
        SELECT person_id, MIN(国籍)
        从国籍
        WHERE 国籍 > b.nat_b
        GROUP BY person_id
    ) AS c(person, nat_c) ON b.person = nationalities.person_id
    -------------------------------------------------- --------
    -- D
    左连接横向
    (
        SELECT person_id, MIN(国籍)
        从国籍
        WHERE 国籍 > c.nat_c
        GROUP BY person_id
    ) AS d(person, nat_d) ON d.person = nationalities.person_id

由于您按字母顺序排序，nat_a因此将始终是MIN(nationality). 连续的横向连接（使用LEFT JOIN, 对于只有 1 个国籍的人）可以查看“下一个 MIN”国籍。

推荐阅读