我在postgresql中有一个表,它的开头是这样的:
car_id part_ids total_inventory
------ -------- ----------
10134 101,506,589 50
12236 201,506,101 20
78865 201,399,304 10
我正在尝试编写一个查询和/或视图来分隔逗号上的每个part_ids,计算每个part_id的total_inventory总和,然后将所有part_ids包含在一个列中,如下所示:
part_ids total_inventory
-------- ----------
101 70
506 70
589 50
201 30
399 10
304 10
我尝试过在part_ids列上使用unnest(string_to_array)来获得最终结果--但是没有太多的运气。
有谁有什么想法吗?感谢您的帮助!
附言:这是我的第一个问题--任何建议/编辑请让我知道
发布于 2018-06-08 05:17:06
像这样的东西应该是可行的
select p.part_id,
sum(t.total_inventory) as total_inventory
from parts t
cross join lateral unnest(string_to_array(part_ids, ',')::int[]) as p(part_id)
group by p.part_id
发布于 2018-06-08 05:26:53
试试这个:
WITH X AS
(
SELECT car_id, UNNEST(REGEXP_SPLIT_TO_ARRAY(part_ids, ',')) AS part_ids, total_inventory FROM parts
)
SELECT part_ids, SUM(total_inventory) AS total_inventory FROM X GROUP BY part_ids
ORDER BY total_inventory DESC;
在第一步中,我创建了一个临时表来保存未嵌套的值。
然后,我对part_ids进行分组,并对它们相关的total_inventory求和。
https://stackoverflow.com/questions/50750086
复制相似问题