我有一个带有三个维度数组的表(还有一个主键和其他几列,但它们并不重要)。所有这些数组都可以是NULL
。在每列中,数组经常重叠:许多值都在几个数组中。我现在想要一个查询,该查询返回一行,三个数组是整个数组列的不同值。
创建测试表像So
DROP TABLE IF EXISTS my_array_test;
CREATE TABLE IF NOT EXISTS my_array_test(id integer, my_txt text[], my_int1 integer[], my_int2 integer[]);
INSERT INTO my_array_test(id, my_txt, my_int1, my_int2) VALUES (1,'{text1,text2}','{1,2}','{21,22}');
INSERT INTO my_array_test(id, my_txt, my_int1, my_int2) VALUES (2,null,'{7,8}','{21,22}');
INSERT INTO my_array_test(id, my_txt, my_int1, my_int2) VALUES (3,'{text2,text4}',null,null);
INSERT INTO my_array_test(id, my_txt, my_int1, my_int2) VALUES (3,null,null,'{17,18}');
INSERT INTO my_array_test(id, my_txt, my_int1, my_int2) VALUES (4,'{text1,text2}','{1,2,3}','{21,22}');
INSERT INTO my_array_test(id, my_txt, my_int1, my_int2) VALUES (5,'{text1,text5}','{1,5}','{21,25}');
INSERT INTO my_array_test(id, my_txt, my_int1, my_int2) VALUES (6,null,null,null);
结果是
select * from my_array_test ;
id | my_txt | my_int1 | my_int2
----+---------------+---------+---------
1 | {text1,text2} | {1,2} | {21,22}
2 | | {7,8} | {21,22}
3 | {text2,text4} | |
3 | | | {17,18}
4 | {text1,text2} | {1,2,3} | {21,22}
5 | {text1,text5} | {1,5} | {21,25}
6 | | |
(7 rows)
预期的结果是{text1,text2,text4,text5},{1,2,7,8,2,5},{21,22,17,18,25}
(数组中的顺序不重要。)
我尝试的是多个横向查询,例如:
SELECT
array_agg(DISTINCT t) AS text_array_result,
array_agg(DISTINCT i1) AS integer_array1_result,
array_agg(DISTINCT i2) AS integer_array2_result
FROM
my_array_test,
unnest(my_txt) AS t,
unnest(my_int1) AS i1,
unnest(my_int2) AS i2
但是,这杀死了仅在它们中有NULL
数组的行中的所有值。
我也尝试了unnest(COALESCE(my_txt,'{}')) AS t,
等,但无济于事。
您可以使用本文中描述的我的自定义汇总。
select
array_merge_agg(my_txt) AS text_array_result,
array_merge_agg(my_int1) AS integer_array1_result,
array_merge_agg(my_int2) AS integer_array2_result
from
my_array_test;
text_array_result | integer_array1_result | integer_array2_result
---------------------------+-----------------------+-----------------------
{text1,text2,text4,text5} | {1,2,3,5,7,8} | {17,18,21,22,25}
(1 row)
如果我理解您正确,您想要所有不同的值,但是null?然后,您只需删除NULL
s即可。绝对看起来不整洁,而是:
t=# with u as (select unnest(my_txt) a,unnest(my_int1) b,unnest(my_int2) v from my_array_test)
select array_remove(array_agg(distinct a),NULL),array_remove(array_agg(distinct b),NULL),array_remove(array_agg(distinct v),NULL) from u;
array_remove | array_remove | array_remove
---------------------------+---------------+------------------
{text1,text2,text4,text5} | {1,2,3,5,7,8} | {17,18,21,22,25}
(1 row)
for Pre10版本:
t=# SELECT
array_remove(array_agg(DISTINCT t),NULL) AS text_array_result,
array_remove(array_agg(DISTINCT i1),NULL) AS integer_array1_result,
array_remove(array_agg(DISTINCT i2),NULL) AS integer_array2_result
FROM
my_array_test
left outer join unnest(my_txt) AS t on true
left outer join unnest(my_int1) AS i1 on true
left outer join unnest(my_int2) AS i2 on true
;
text_array_result | integer_array1_result | integer_array2_result
---------------------------+-----------------------+-----------------------
{text1,text2,text4,text5} | {1,2,3,5,7,8} | {17,18,21,22,25}
(1 row)