如何从PostgreSQL查询中删除重复冗余数据

Question

我试图使下面的查询片段包含较少的重复数据，并使之看起来更优雅。你能告诉我吗？

SELECT 
 (select gender from jsonb_to_recordset(audience) as x(gender text[]) where x.gender is not null) as gender,
 (select age from jsonb_to_recordset(audience) as x(age text[]) where x.age is not null) as age,
 (select ethnicity from jsonb_to_recordset(audience) as x(ethnicity text[]) where x.ethnicity is not null) as ethnicity,
 (select continent from jsonb_to_recordset(params) as x(continent text[]) where x.continent is not null) as continent,
 (select country from jsonb_to_recordset(params) as x(country text[]) where x.country is not null) as country,
 (select city from jsonb_to_recordset(params) as x(city text[]) where x.city is not null) as city
FROM user_data

是否可以使用数组或CTE返回“性别”、“年龄”、“种族”、“大陆”、“国家”、“城市”，并模拟一些循环以避免重复上述6种选择？

SELECT jsonb_build_array(
    jsonb_build_array(ARRAY(SELECT UNNEST(dpu1.gender) INTERSECT SELECT UNNEST(dpu2.gender)), COALESCE(dpu1.gender, ARRAY[]::text[]), COALESCE(dpu2.gender, ARRAY[]::text[])),
    jsonb_build_array(ARRAY(SELECT UNNEST(dpu1.age) INTERSECT SELECT UNNEST(dpu2.age)), COALESCE(dpu1.age, ARRAY[]::text[]), COALESCE(dpu2.age, ARRAY[]::text[])),
    jsonb_build_array(ARRAY(SELECT UNNEST(dpu1.ethnicity) INTERSECT SELECT UNNEST(dpu2.ethnicity)), COALESCE(dpu1.ethnicity, ARRAY[]::text[]), COALESCE(dpu2.ethnicity, ARRAY[]::text[])),
    jsonb_build_array(ARRAY(SELECT UNNEST(dpu1.continent) INTERSECT SELECT UNNEST(dpu2.continent)), COALESCE(dpu1.continent, ARRAY[]::text[]), COALESCE(dpu2.continent, ARRAY[]::text[])),
    jsonb_build_array(ARRAY(SELECT UNNEST(dpu1.country) INTERSECT SELECT UNNEST(dpu2.country)), COALESCE(dpu1.country, ARRAY[]::text[]), COALESCE(dpu2.country, ARRAY[]::text[])),
    jsonb_build_array(ARRAY(SELECT UNNEST(dpu1.city) INTERSECT SELECT UNNEST(dpu2.city)), COALESCE(dpu1.city, ARRAY[]::text[]), COALESCE(dpu2.city, ARRAY[]::text[]))
) as values
FROM data_per_user dpu1 CROSS JOIN data_per_user dpu2
WHERE dpu1.user_uuid <> dpu2.user_uuid

下面是同样的故事-我发现交叉数组元素，并必须重复相同的转换6次。有没有更优雅的方法？

SELECT
jsonb_build_array(
    ARRAY[jsonb_array_length(values->0->0), jsonb_array_length(values->0->1), jsonb_array_length(values->0->2)],
    ARRAY[jsonb_array_length(values->1->0), jsonb_array_length(values->1->1), jsonb_array_length(values->1->2)],
    ARRAY[jsonb_array_length(values->2->0), jsonb_array_length(values->2->1), jsonb_array_length(values->2->2)],
    ARRAY[jsonb_array_length(values->3->0), jsonb_array_length(values->3->1), jsonb_array_length(values->3->2)],
    ARRAY[jsonb_array_length(values->4->0), jsonb_array_length(values->4->1), jsonb_array_length(values->4->2)],
    ARRAY[jsonb_array_length(values->5->0), jsonb_array_length(values->5->1), jsonb_array_length(values->5->2)]
) as scores
FROM matched_users

在这里，我试着计算二维数组中数组元素的数量--同样的故事。看起来冗余的重复数据太多了。请建议优化这些查询的任何方法。

我不是想要确切的解决办法，我只是想知道你的想法。

Answer 1

对于第一个查询，如果您的列audience和params类型为jsonb，则可以直接使用json函数提取某些数据。特别是，您可以仔细阅读手册8.14.7。jsonpath型和9.16.2SQL/JSON路径语言中的jsonpath章节。

这是您的查询的等效json版本：

SELECT 
  jsonb_path_query(audience, '$[*] ? (@.gender <> null)')->>'gender' :: text[] AS gender
, jsonb_path_query(audience, '$[*] ? (@.age <> null)')->>'age' :: text[] AS age
, jsonb_path_query(audience, '$[*] ? (@.ethnicity <> null)')->>'ethnicity' :: text[] AS ethnicity
, jsonb_path_query(params, '$[*] ? (@.continent <> null)')->>'continent' :: text[] AS continent
, jsonb_path_query(params, '$[*] ? (@.country <> null)')->>'country' :: text[] AS country
, jsonb_path_query(params, '$[*] ? (@.city <> null)')->>'city' :: text[] AS city
FROM user_data

哪些数据应该简化如下，因为当相应的json值是json列中的null时，生成的数据将自动设置为audience或params：

SELECT 
  aud->>'gender' :: text[] AS gender
, aud->>'age' :: text[] AS age
, aud->>'ethnicity' :: text[] AS ethnicity
, par->>'continent' :: text[] AS continent
, par->>'country' :: text[] AS country
, par->>'city' :: text[] AS city
FROM user_data
CROSS JOIN LATERAL jsonb_path_query(audience, '$[*]) AS aud
CROSS JOIN LATERAL jsonb_path_query(params, '$[*]) AS par

您的第三个查询

SELECT
jsonb_build_array(
    ARRAY[jsonb_array_length(values->0->0), jsonb_array_length(values->0->1), jsonb_array_length(values->0->2)],
    ARRAY[jsonb_array_length(values->1->0), jsonb_array_length(values->1->1), jsonb_array_length(values->1->2)],
    ARRAY[jsonb_array_length(values->2->0), jsonb_array_length(values->2->1), jsonb_array_length(values->2->2)],
    ARRAY[jsonb_array_length(values->3->0), jsonb_array_length(values->3->1), jsonb_array_length(values->3->2)],
    ARRAY[jsonb_array_length(values->4->0), jsonb_array_length(values->4->1), jsonb_array_length(values->4->2)],
    ARRAY[jsonb_array_length(values->5->0), jsonb_array_length(values->5->1), jsonb_array_length(values->5->2)]
) as scores
FROM matched_users

可以被

SELECT jsonb_array_agg(c.arr ORDER BY c.a) AS scores
FROM 
   (SELECT a, jsonb_array_agg(jsonb_array_length(values#>array[a :: text, b :: text]) AS arr
      FROM matched_users
     CROSS JOIN generate_series(0,5) AS a
     CROSS JOIN generate_series(0,2) AS b
     GROUP BY a
     ORDER BY b
   ) AS c