sql - POSTGRESQL：将值分组为 ARRAY 的 ARRAY

Question

下面是我的输入数据表

输入 ：

System NameYear Quarter Value 1 Value 2                     
1   2019    Q1  AB  CD                      
1   2019    Q2  EF  GH                      
1   2019    Q3  IJ  KL                      
1   2019    Q4  MN  OP                      
1   2020    Q1  XX  YY                      
1   2020    Q2  ZZ  MM                      
1   2020    Q3  NN  KK                      
1   2020    Q4  TT  QQ                      

我需要将值排序为每个系统名称的 Quarters [Q1,Q2,Q3,Q4] 的 ARRAY 值，并且在每个 Quarter 下它应该具有 Years [Y1,Y2]
Value 1 的数组值：{{" AB","XX"},{"EF","ZZ"},{"IJ","NN"},{"MN","TT"}}
值 2：{{"CD","YY" },{"GH","MM"},{"KL","KK"},{"OP","QQ"}}

预期的输出是这样的：

输出 ：

System Name Value 1                 Value 2             
1   {{"AB","XX"},{"EF","ZZ"},{"IJ","NN"},{"MN","TT"}}               {{"CD","YY"},{"GH","MM"},{"KL","KK"},{"OP","QQ"}}

我试过按季度使用 ARRAY_AGG 函数顺序。

标签： sqlpostgresql

Answer 1

使用ARRAY_AGG, 并执行两级聚合。首先，按系统和季度聚合以生成包含每个季度数组的一系列记录。然后，单独按系统再次聚合以生成单个数组数组。

WITH cte AS (
    SELECT
        System,
        ARRAY_AGG(Value1 ORDER BY NameYear) AS Val1,
        ARRAY_AGG(Value2 ORDER BY NameYear) AS Val2
    FROM yourTable
    GROUP BY System, Quarter
)

SELECT
    System,
    ARRAY_AGG(Val1) AS Val1,
    ARRAY_AGG(Val2) AS Val2
FROM cte
GROUP BY
    System;

演示