英文:
Postgres latest column value from each row
问题
| id | time | firstname | lastname | salary | location | country |
|---|---|---|---|---|---|---|
| 1 | 2023-03-08 07:55:58 | John | Lenny | 5000 | Phoenix | USA |
英文:
I have a sql table which is being updated hourly through API. Sample is:
| id | time | firstname | lastname | salary | location | country |
|---|---|---|---|---|---|---|
| 1 | 2023-03-08 07:47:58 | John | 10000 | |||
| 1 | 2023-03-08 07:50:58 | Lenny | Phoenix | USA | ||
| 1 | 2023-03-08 07:55:58 | 5000 |
What i am looking in target is only 1 row that should contain all latest updated value, so the sample will look somewhat like this:
| id | time | firstname | lastname | salary | location | country |
|---|---|---|---|---|---|---|
| 1 | 2023-03-08 07:55:58 | John | Lenny | 5000 | Phoenix | USA |
Is there any way this can be achieved through Postgres query?
Please suggest.
Thanks in advance
答案1
得分: 0
使用array_agg就能搞定：
使用array_agg结合order和filter将数据分组到一个数组中（只有非空数据）。
在filter中，如果有null值，那么使用where column is not null代替。
使用to_json将数组转换为json，然后使用->>-1获取最新的元素
select id, max(time),
to_json(array_agg(firstname order by time) filter (where firstname <> '' ))->>-1 AS firstname,
to_json(array_agg(lastname order by time) filter (where lastname <> '' ))->>-1 AS firstname,
to_json(array_agg(salary order by time) filter (where salary <> '' ))->>-1 AS salary,
to_json(array_agg(location order by time) filter (where location <> '' ))->>-1 AS location,
to_json(array_agg(country order by time) filter (where country <> '' ))->>-1 AS country
from mytable
group by id
英文:
Using array_agg can do the trick :
array_agg with order and filter to group data into an array (only not empty data).
In where filter, if you have null values then use where column is not null instead.
to_json to transform the array into json then using ->-1 we get the latest element
select id, max(time),
to_json(array_agg(firstname order by time) filter (where firstname <> '' ))->-1 AS firstname,
to_json(array_agg(lastname order by time) filter (where lastname <> '' ))->-1 AS firstname,
to_json(array_agg(salary order by time) filter (where salary <> '' ))->-1 AS salary,
to_json(array_agg(location order by time) filter (where location <> '' ))->-1 AS location,
to_json(array_agg(country order by time) filter (where country <> '' ))->-1 AS country
from mytable
group by id
答案2
得分: 0
小例子使用array_agg，有一个按顺序排列的和一个过滤器：
SELECT id
, MAX(time) as time
, (array_agg(firstname ORDER BY time DESC) FILTER(WHERE firstname IS NOT NULL))[1] firstname
, (array_agg(salary ORDER BY time DESC) FILTER(WHERE salary IS NOT NULL))[1] salary
FROM t1
GROUP BY id;
英文:
Small example using array_agg, an order by and a filter:
SELECT id
, MAX(time) as time
, (array_agg(firstname ORDER BY time DESC) FILTER(WHERE firstname IS NOT NULL))[1] firstname
, (array_agg(salary ORDER BY time DESC) FILTER(WHERE salary IS NOT NULL))[1] salary
FROM t1
GROUP BY id;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论