代码之家 › 专栏 › 技术社区 › Daryn

Postgresql从表创建数组

postgresql sql

Daryn · 技术社区 · 5 年前

我有一张这样的桌子:

  serial    | channel | sum   | date 
 210062912      1     0.827    2019-01-01 
 210062912      2    20.864    2019-01-01 
 210062912      3     0.000    2019-01-01 
 210062912      1     0.650    2019-01-02 
 210062912      2    29.666    2019-01-02

有没有可能用postgres返回以下内容?

serial_channel |  array
210062912-1     [[2019-01-01,0.827],[2019-01-02,0.650]]
210062912-2     [[2019-01-01,20.864],[2019-01-02,29.666]]
210062912-3     [[2019-01-01,0.000]]

0 回复 | 直到 5 年前

S-Man 5 年前

demo:db<>fiddle

问题是你内心的“阵列”。数组只能组合来自同一类型的值。但你想把 numeric 类型和a date .

SELECT
    serial || '-' || channel as serialchannel,
    array_agg(row(date, sum))
FROM
    mytable
GROUP BY 1

结果:

serial_channel |  array_agg
:--------------| :----------------------------------------
210062912-1    | [(2019-01-01,0.827),(2019-01-02,0.650)]
210062912-2    | [(2019-01-01,20.864),(2019-01-02,29.666)]
210062912-3    | [(2019-01-01,0.000)]

或者,您可以将这两个值强制转换为类型 text

SELECT
    serial || '-' || channel as serialchannel,
    array_agg(ARRAY[date::text, sum::text])
FROM
    mytable
GROUP BY 1

结果:

serial_channel |  array_agg
:--------------| :----------------------------------------
210062912-1    | [["2019-01-01","0.827"],["2019-01-02","0.650"]]
210062912-2    | [["2019-01-01","20.864"],["2019-01-02","29.666"]]
210062912-3    | [["2019-01-01","0.000"]]

第三种方法是使用JSON:

SELECT
    serial || '-' || channel as serialchannel,
    json_agg(json_build_object('date', mydate, 'sum', mysum))
FROM
    mytable
GROUP BY 1

serialchannel | json_agg                                                                          
:------------ | :---------------------------------------------------------------------------------
210062912-3   | [{"sum" : 0.000, "date" : "2019-01-01"}]                                          
210062912-2   | [{"sum" : 20.864, "date" : "2019-01-01"}, {"sum" : 29.666, "date" : "2019-01-02"}]
210062912-1   | [{"sum" : 0.827, "date" : "2019-01-01"}, {"sum" : 0.650, "date" : "2019-01-02"}]

a_horse_with_no_name 5 年前

您可以返回一个JSONB数组,其中键是日期:

select concat_ws('-', "serial", channel) as serial_channel, 
       jsonb_agg(jsonb_build_object("date", sum))
from the_table
group by serial_channel;

将返回:

serial_channel | jsonb_agg                                       
---------------+-------------------------------------------------
210062912-3    | [{"2019-01-01": 0.000}]                         
210062912-2    | [{"2019-01-01": 20.864}, {"2019-01-02": 29.666}]
210062912-1    | [{"2019-01-01": 0.827}, {"2019-01-02": 0.650}]

在线示例: https://rextester.com/FZS1666