postgresql array_agg group by和null

a14dhokn 于 2023-06-29 发布在 PostgreSQL

关注(0)|答案(2)|浏览(162)

给定此表：

SELECT * FROM CommodityPricing order by dateField

"SILVER";60.45;"2002-01-01"
"GOLD";130.45;"2002-01-01"
"COPPER";96.45;"2002-01-01"
"SILVER";70.45;"2003-01-01"
"GOLD";140.45;"2003-01-01"
"COPPER";99.45;"2003-01-01"
"GOLD";150.45;"2004-01-01"
"MERCURY";60;"2004-01-01"
"SILVER";80.45;"2004-01-01"

2004年，铜被淘汰，汞被引入。
如何将(array_agg(value order by date desc) ) [1]的值作为NULL的COPPER？

select commodity,(array_agg(value order by date desc) ) --[1]
from CommodityPricing
group by commodity

"COPPER";"{99.45,96.45}"
"GOLD";"{150.45,140.45,130.45}"
"MERCURY";"{60}"
"SILVER";"{80.45,70.45,60.45}"

postgresql

来源：https://stackoverflow.com/questions/22443882/array-agg-group-by-and-null

2条答案

按热度按时间

xienkqul1#

SQL Fiddle

select
    commodity,
    array_agg(
        case when commodity = 'COPPER' then null else price end
        order by date desc
    )
from CommodityPricing
group by commodity
;

赞(0）回复(0）举报 2023-06-29

wpx232ag2#

要在结果数组中用空值“填充”缺失的行，请在完整的行网格和**LEFT JOIN**上构建查询，以将实际值添加到网格中。
给定此表定义：

CREATE TABLE price (
  commodity text
, value     numeric
, ts        timestamp  -- using ts instead of the inappropriate name date 
);

使用generate_series()获取表示年份的时间戳列表，使用CROSS JOIN获取所有商品的唯一列表（SELECT DISTINCT ...）。

SELECT commodity, array_agg(value ORDER BY ts DESC) AS years
FROM   generate_series (timestamp '2002-01-01'
                      , timestamp '2004-01-01'
                      , '1 year') t(ts)
CROSS  JOIN (SELECT DISTINCT commodity FROM price) c(commodity)
LEFT   JOIN price p USING (ts, commodity)
GROUP  BY commodity;

结果：
| 年| years |
| - -----| ------------ |
| {NULL，99.45，96.45}| {NULL,99.45,96.45} |
| 【2019 - 04 - 25】| {150.45,140.45,130.45} |
| {60，NULL，NULL}| {60,NULL,NULL} |
| [2019 - 04 - 25]| {80.45,70.45,60.45} |
fiddle
老麻雀
相关内容：

在PostgreSQL中生成两个日期之间的时间序列

赞(0）回复(0）举报 2023-06-29

我来回答

postgresql array_agg group by和null

2条答案

相关问题

热门标签

最新问答