Блог

Я пытаюсь сделать так, чтобы приведенные ниже фрагменты запроса содержали меньше повторяющихся данных и выглядели более элегантно. Не могли бы вы посоветовать?

 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, возвращающий [‘gender’, ‘age’, ‘ethnicity’, ‘continent’, ‘country’, ‘city’] и имитировать некоторый цикл, чтобы избежать повторения вышеуказанных 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
 

И здесь я пытаюсь подсчитать количество элементов массива в 2D-массиве — та же история. Похоже, что слишком много избыточных повторяющихся данных.
Пожалуйста, посоветуйте любые способы оптимизации этих запросов.

Я не прошу точных решений, просто идеи будут оценены.

Для вашего первого запроса, если ваши столбцы audience и params имеют тип jsonb , вы можете напрямую использовать функции json для извлечения некоторых данных. В частности, вы можете внимательно прочитать главы jsonpath в руководстве 8.14.7. Тип jsonpath и 9.16.2. Язык SQL / JSON Path.

Это эквивалентная версия вашего запроса в формате 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
 

и которые следует упростить следующим образом, поскольку результирующие данные будут автоматически установлены NULL , когда их соответствующее json значение находится null в столбцах json 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