postgresql - 有条件的 ROW NUMBER 或 COMBINE GROUP BY
问题描述
我需要 GROUP BY 两个不同的列并组合。此外,如果具有不同group_id的大小与另一个size匹配,那么我想包含该group_id的所有这些行。
例如,由于55在 group_id 10和 group_id 20中,我想将所有结果包含在 group_id 20 (IE 55,88,55,88) 中。
p_id | group_id | size | the_date
------+----------+-----------+--------
1 | 10 | 55 | 2012-10-24
1 | 10 | 54 | 2014-08-09
1 | 20 | 55 | 2013-05-20
1 | 20 | 88 | 2014-05-20
1 | 20 | 55 | 2015-05-20
1 | 30 | 33 | 2014-05-20
1 | 30 | 55 | 2015-05-20
1 | 30 | 33 | 2015-05-20
1 | 40 | 99 | 2015-05-20
我查看了堆栈上的其他一些问题并尝试使用row_number
,first_value
但没有任何效果。下面是我想要的结果。
p_id | group_id | size | group_id_arr | size_arr | date_arr
------+----------+------+----------------------------+-----------------------------+-----------
1 | 10 | 55 | {10,20,30,20,10,30,30,20} | {55,55,33,88,55,55,33,55} | {2012-10-24,2013-05-20,2014-05-20,2014-05-20,2014-08-09,2015-05-20,2015-05-20,2015-05-20}
1 | 40 | 99 | {40} | {99} | {2015-05-20}
这是我的DBFiddle,我正在尝试实现以下目标,以便我可以聚合。
rn | p_id | group_id | size | the_date
------+----------+----------+------+-----------
1 | 1 | 10 | 55 | 2012-10-24
2 | 1 | 20 | 55 | 2013-05-20
3 | 1 | 30 | 33 | 2014-05-20
4 | 1 | 20 | 88 | 2014-05-20
5 | 1 | 10 | 54 | 2014-08-09
6 | 1 | 30 | 55 | 2015-05-20
7 | 1 | 30 | 33 | 2015-05-20
8 | 1 | 20 | 55 | 2015-05-20
1 | 1 | 40 | 99 | 2015-05-20
请让我知道,如果你有任何问题。我对所有方法都持开放态度,谢谢。
解决方案
我不确定最终数组中的顺序,但这应该是您可以修复的细节:
WITH sizes as (
SELECT group_id, the_date, array_agg(size) sizes
FROM base_table
GROUP BY group_id, the_date
)
SELECT
a_group_id,
array_agg(b_group_id order by the_date),
array_agg(size order by the_date) as sizes,
array_agg(the_date order by the_date)
FROM (
SELECT
a.group_id as a_group_id,
b.group_id as b_group_id,
unnest(b.sizes) size, b.the_date
FROM sizes a
INNER JOIN sizes b
ON a.sizes && b.sizes
) s
GROUP BY a_group_id
- 聚合每个大小
group_id
- 将聚合与自身交叉连接,其中一个大小的数组至少有一个成员。这为您提供了您期望和描述的表格。
- 按 分组
group_id
。