Выберите объединенные столбцы на основе списка критериев в другой таблице

у меня есть таблица1

линия а б с г е ф г час 1 18 2 2 22 0 2 1 2 2 20 2 2 2 0 0 0 2 3 10 2 2 222 0 2 1 2 4 12 2 2 3 0 0 0 0 5 15 2 2 3 0 0 0 0

И стол2

линия критерии 1 а, б 2 б, в, е, з 3 а, б, д, г, з 4 в, е

Я использую этот код для просмотра/выбора уникальных результатов объединенных/объединенных столбцов, таких как concat(c,',',d), concat(b,',',d,',',g) и т. д. из table1, и он отлично работает:

SELECT DISTINCT(CONCAT(c,',',d)) 
FROM table1

Но вместо того, чтобы писать вручную, как concat(c,',',d), я хочу обратиться к table2.criteria, чтобы получить ссылки на столбцы, которые будут объединены/объединены из table1, чтобы я мог видеть все уникальные результаты по каждому объединенному критерию.

Пробовал это, но получил ошибку:

SELECT DISTINCT(SELECT criteria FROM table2) 
FROM table1

ОШИБКА: более одной строки возвращено подзапросом, используемым в качестве выражения
Состояние SQL: 21000

Ожидаемый уникальный результат примерно такой;

| criteria     | result     |
| ------------ | ---------- |
| a,b          | 15,2       |
| a,b          | 10,2       |
| a,b          | 20,2       |
| a,b          | 12,2       |
| a,b          | 18,2       |
| b,c,f,h      | 2,2,2,2    |
| b,c,f,h      | 2,2,0,2    |
| b,c,f,h      | 2,2,0,0    |
| a,b,e,g,h    | 20,2,0,0,2 |
| a,b,e,g,h    | 12,2,0,0,0 |
| a,b,e,g,h    | 15,2,0,0,0 |
| a,b,e,g,h    | 10,2,0,1,2 |
| a,b,e,g,h    | 18,2,0,1,2 |
| c,e          | 2,0        |
ReactJs | Supabase | Добавление данных в базу данных
ReactJs | Supabase | Добавление данных в базу данных
Это и есть ваш редактор таблиц в supabase.👇
Понимание Python и переход к SQL
Понимание Python и переход к SQL
Перед нами лабораторная работа по BloodOath:
0
0
70
1
Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

Ответ принят как подходящий

SQL не позволяет параметризовать идентификаторы. Существуют различные способы обойти это ограничение.

Из вопроса неясно, но, согласно комментариям, вы хотите объединить данный шаблон для каждой строки в table1.

1. Динамический SQL

Создайте вспомогательную функцию (один раз!), которая динамически объединяет и выполняет операторы. Основы:

CREATE OR REPLACE FUNCTION f_concat_cols(_cols text)
  RETURNS TABLE (result text)
  LANGUAGE plpgsql AS
$func$
BEGIN
   RETURN QUERY EXECUTE format(
     $q$SELECT concat_ws(',', %s) FROM table1 ORDER BY line$q$, _cols);
END
$func$;

Это функция, возвращающая множество (также известная как «табличная функция»), которая возвращает одну строку результата для каждой строки в table1 для каждого заданного шаблона.

Предупреждение. Преобразование пользовательского ввода в такой код — отличная возможность для SQL-инъекций. Вы должны убедиться, что table1.criteria может содержать только допустимые строки!

Чтобы получить полную матрицу результатов (с различными результатами для каждой строки в таблице 2), запрос теперь прост:

SELECT DISTINCT line AS t2_line, criteria, t1.*
FROM   table2, f_concat_cols(criteria) t1
ORDER  BY t2_line;

2. Обходной путь с преобразованием в JSON

SELECT DISTINCT t2.line AS t2_line, t2.criteria, c.*
FROM   table2 t2
CROSS  JOIN (SELECT line, to_json(t) AS js FROM table1 t) t1
CROSS  JOIN LATERAL (
   SELECT string_agg(t1.js->>sub, ',') AS result
   FROM   unnest(string_to_array(t2.criteria, ',')) sub
   ) c
ORDER  BY t2_line;

После преобразования строк из t1 в запись JSON мы можем напрямую обращаться к ключам (преобразованным из имен столбцов). Я раскрываю шаблон, получаю доступ к каждому отдельному ключу и агрегирую результат в подзапросе LATERAL. Видеть:

Вы можете инкапсулировать логику в функцию, как в 1., но в данном случае это необязательно.

3. Обходной путь с преобразованием в массивы Postgres

SELECT DISTINCT t2.line AS t2_line, t2.criteria, c.*
FROM   table2 t2
CROSS  JOIN (SELECT line, ARRAY [a,b,c,d,e,f,g,h] AS arr FROM table1 t) t1
CROSS  JOIN LATERAL (
   SELECT string_agg(t1.arr[idx]::text, ',') AS result
   FROM   unnest(string_to_array(translate(t2.criteria, 'abcdefgh', '12345678'), ',')::int[]) idx
   ) c
ORDER  BY t2_line;

Подобно «трюку» с JSON, мы можем избежать динамического SQL, преобразовав столбцы в простой массив Postgres. Затем проецируйте имена столбцов на индексы целочисленного массива. Я использую translate() для простого случая, но это работает только для отдельных букв! Используйте replace() или regexp_replace() или какой-либо другой метод для более длинных имен.
Остальное как выше.

рабочий пример - показываю все.

Не могли бы вы проверить следующую ссылку [dbfiddle.uk/huOOMD8G], вывод основан только на 1-й строке, но мне нужна сводка всей таблицы. Спасибо

Gulya 15.01.2023 20:02

@Gulya: я тоже учел номер строки.

Erwin Brandstetter 15.01.2023 20:21

@ Erwin Brandstetter по-прежнему общий вывод составляет всего 4 строки, результат самого «a, b» должен быть 5 строк, например («18,2», «20,2», «10,2», «12,2» , и "15,2")

Gulya 15.01.2023 20:55

@Gulya Можете ли вы включить это требование в вопрос? И объясните, почему вы добавили DISTINCT. Я добавил еще несколько решений выше.

Erwin Brandstetter 16.01.2023 09:10

@ Эрвин, я изменил вопрос, пожалуйста, проверьте его. Спасибо

Gulya 16.01.2023 12:48

Интегрированный РАЗЛИЧНЫЙ.

Erwin Brandstetter 16.01.2023 17:50

Другие вопросы по теме