string_agg (case when r."1" = greatest (r."1", r."2", r."3", r."4", r."5", r."6", r."7",…
- User
string_agg (case when r."1" = greatest (r."1", r."2", r."3", r."4", r."5", r."6", r."7", r."8") then 'Не завершена проработка вопроса' when r."2" = greatest (r."1", r."2", r."3", r."4", r."5", r."6", r."7", r."8") then 'Не завершены процедуры' when r."3" = greatest (r."1", r."2", r."3", r."4", r."5", r."6", r."7", r."8") then 'Не завершены строительство, ввод в эксплуатацию, поставка оборудования' when r."4" = greatest (r."1", r."2", r."3", r."4", r."5", r."6", r."7", r."8") then 'Не обеспечено финансирование' when r."5" = greatest (r."1", r."2", r."3", r."4", r."5", r."6", r."7", r."8") then 'Не завершено согласование' when r."6" = greatest (r."1", r."2", r."3", r."4", r."5", r."6", r."7", r."8") then 'Не завершена подготовка проекта документа' when r."7" = greatest (r."1", r."2", r."3", r."4", r."5", r."6", r."7", r."8") then 'Не исполнены контрактные обязательства подрядчиками' when r."8" = greatest (r."1", r."2", r."3", r."4", r."5", r."6", r."7", r."8") then 'Иное (ручной ввод)' end, ', ') as "Причины переносов", можно ли как-то сделать так, что если r.1 равно максимуму, то не произойдет выход из case, а продолжиться сравнение по другим when?
- Assistant
Да, вы можете изменить структуру вашего выражения
CASEтаким образом, чтобы в случае выполнения одного из условий его выполнение не прекращалось, а продолжалось до конца. Для этого вместо использования конструкцииCASEс единственным значением на выходе, вы можете вызывать функцию агрегацииSTRING_AGGвнутри вложенногоCASE.Это позволит вам собрать все причины в одну строку, в зависимости от того, какое условие выполнено. Вот как это может выглядеть:
string_agg( case when r."1" = greatest (r."1", r."2", r."3", r."4", r."5", r."6", r."7", r."8") then 'Не завершена проработка вопроса' else null end, ', ') || string_agg( case when r."2" = greatest (r."1", r."2", r."3", r."4", r."5", r."6", r."7", r."8") then 'Не завершены процедуры' else null end, ', ') || string_agg( case when r."3" = greatest (r."1", r."2", r."3", r."4", r."5", r."6", r."7", r."8") then 'Не завершены строительство, ввод в эксплуатацию, поставка оборудования' else null end, ', ') || string_agg( case when r."4" = greatest (r."1", r."2", r."3", r."4", r."5", r."6", r."7", r."8") then 'Не обеспечено финансирование' else null end, ', ') || string_agg( case when r."5" = greatest (r."1", r."2", r."3", r."4", r."5", r."6", r."7", r."8") then 'Не завершено согласование' else null end, ', ') || string_agg( case when r."6" = greatest (r."1", r."2", r."3", r."4", r."5", r."6", r."7", r."8") then 'Не завершена подготовка проекта документа' else null end, ', ') || string_agg( case when r."7" = greatest (r."1", r."2", r."3", r."4", r."5", r."6", r."7", r."8") then 'Не исполнены контрактные обязательства подрядчиками' else null end, ', ') || string_agg( case when r."8" = greatest (r."1", r."2", r."3", r."4", r."5", r."6", r."7", r."8") then 'Иное (ручной ввод)' else null end, ', ')Обратите внимание, что использованы отдельные
STRING_AGGдля каждого условия. В случае, если условие истинно, будет возвращена соответствующая строка, иначе —NULL. После этого строки будут объединены с помощью||(конкатенации строк).Этот подход позволяет вам вернуть все причины, соответствующие максимуму значений.
- User
а есть другие решения?
- Assistant
Да, есть несколько других подходов, которые можно использовать для достижения желаемого результата. Вот несколько альтернативных решений:
1. Использование агрегатной функции с условием
Вы можете использовать одну агрегатную функцию
STRING_AGGи обрабатывать каждое условие внутри самого вызова функции. Например, так:SELECT STRING_AGG(reason, ', ') AS "Причины переносов" FROM ( SELECT CASE WHEN r."1" = greatest(r."1", r."2", r."3", r."4", r."5", r."6", r."7", r."8") THEN 'Не завершена проработка вопроса' WHEN r."2" = greatest(r."1", r."2", r."3", r."4", r."5", r."6", r."7", r."8") THEN 'Не завершены процедуры' WHEN r."3" = greatest(r."1", r."2", r."3", r."4", r."5", r."6", r."7", r."8") THEN 'Не завершены строительство, ввод в эксплуатацию, поставка оборудования' WHEN r."4" = greatest(r."1", r."2", r."3", r."4", r."5", r."6", r."7", r."8") THEN 'Не обеспечено финансирование' WHEN r."5" = greatest(r."1", r."2", r."3", r."4", r."5", r."6", r."7", r."8") THEN 'Не завершено согласование' WHEN r."6" = greatest(r."1", r."2", r."3", r."4", r."5", r."6", r."7", r."8") THEN 'Не завершена подготовка проекта документа' WHEN r."7" = greatest(r."1", r."2", r."3", r."4", r."5", r."6", r."7", r."8") THEN 'Не исполнены контрактные обязательства подрядчиками' WHEN r."8" = greatest(r."1", r."2", r."3", r."4", r."5", r."6", r."7", r."8") THEN 'Иное (ручной ввод)' ELSE NULL END AS reason FROM your_table r ) AS reasons WHERE reason IS NOT NULL;В этом случае мы создаем подзапрос, который генерирует список причин, а затем используем
STRING_AGGв основном запросе для объединения ненулевых значений.2. Использование ARRAY_AGG и STRING_AGG
Если ваша СУБД поддерживает массивы и вы хотите избежать дублирования кода, вы можете сначала собрать причины в массив, а затем использовать
STRING_AGG:SELECT STRING_AGG(reason, ', ') AS "Причины переносов" FROM ( SELECT ARRAY_AGG( CASE WHEN r."1" = greatest(r."1", r."2", r."3", r."4", r."5", r."6", r."7", r."8") THEN 'Не завершена проработка вопроса' WHEN r."2" = greatest(r."1", r."2", r."3", r."4", r."5", r."6", r."7", r."8") THEN 'Не завершены процедуры' WHEN r."3" = greatest(r."1", r."2", r."3", r."4", r."5", r."6", r."7", r."8") THEN 'Не завершены строительство, ввод в эксплуатацию, поставка оборудования' WHEN r."4" = greatest(r."1", r."2", r."3", r."4", r."5", r."6", r."7", r."8") THEN 'Не обеспечено финансирование' WHEN r."5" = greatest(r."1", r."2", r."3", r."4", r."5", r."6", r."7", r."8") THEN 'Не завершено согласование' WHEN r."6" = greatest(r."1", r."2", r."3", r."4", r."5", r."6", r."7", r."8") THEN 'Не завершена подготовка проекта документа' WHEN r."7" = greatest(r."1", r."2", r."3", r."4", r."5", r."6", r."7", r."8") THEN 'Не исполнены контрактные обязательства подрядчиками' WHEN r."8" = greatest(r."1", r."2", r."3", r."4", r."5", r."6", r."7", r."8") THEN 'Иное (ручной ввод)' END ) AS reasons FROM your_table r GROUP BY r.some_grouping_column -- укажите, если необходимо группировать по столбцу ) AS subquery WHERE reason IS NOT NULL;3. Использование внешних библиотек или функций
Если ваша база данных поддерживает расширения или пользовательские функции, вы можете создать кастомную функцию, которая будет возвращать список причин по входным данным. Это позволит более удобно управлять логикой и повторно использовать код.
Выбор конкретного подхода зависит от ваших предпочтений, архитектуры проекта и требований к производительности. Каждое из предложенных решений имеет свои плюсы и минусы.
- User
давай еще что-нибудь
- Assistant
Конечно! Вот еще несколько подходов, которые могут помочь вам добиться аналогичного результата, при этом открывая новые возможности для обработки данных.
4. Использование временной таблицы или CTE
Вы можете использовать временную таблицу или общее табличное выражение (CTE), чтобы сначала получить все совпадения, а затем собрать их в одну строку на основе условия. Это делает код более компактным и понятным.
Пример с CTE:
WITH Reasons AS ( SELECT r.*, CASE WHEN r."1" = greatest(r."1", r."2", r."3", r."4", r."5", r."6", r."7", r."8") THEN 'Не завершена проработка вопроса' WHEN r."2" = greatest(r."1", r."2", r."3", r."4", r."5", r."6", r."7", r."8") THEN 'Не завершены процедуры' WHEN r."3" = greatest(r."1", r."2", r."3", r."4", r."5", r."6", r."7", r."8") THEN 'Не завершены строительство, вввод в эксплуатацию, поставка оборудования' WHEN r."4" = greatest(r."1", r."2", r."3", r."4", r."5", r."6", r."7", r."8") THEN 'Не обеспечено финансирование' WHEN r."5" = greatest(r."1", r."2", r."3", r."4", r."5", r."6", r."7", r."8") THEN 'Не завершено согласование' WHEN r."6" = greatest(r."1", r."2", r."3", r."4", r."5", r."6", r."7", r."8") THEN 'Не завершена подготовка проекта документа' WHEN r."7" = greatest(r."1", r."2", r."3", r."4", r."5", r."6", r."7", r."8") THEN 'Не исполнены контрактные обязательства подрядчиками' WHEN r."8" = greatest(r."1", r."2", r."3", r."4", r."5", r."6", r."7", r."8") THEN 'Иное (ручной ввод)' ELSE null END AS reason FROM your_table r ) SELECT STRING_AGG(reason, ', ') AS "Причины переносов" FROM Reasons WHERE reason IS NOT NULL;5. Использование JSON или XML для агрегации
Если ваша база данных поддерживает JSON или XML, вы можете агрегировать данные в формате JSON или XML, чтобы затем извлекать нужные значения. Например, в PostgreSQL можно создавать массивы или JSON-объекты:
SELECT json_agg(reason) AS "Причины переносов" FROM ( SELECT CASE WHEN r."1" = greatest(r."1", r."2", r."3", r."4", r."5", r."6", r."7", r."8") THEN 'Не завершена проработка вопроса' WHEN r."2" = greatest(r."1", r."2", r."3", r."4", r."5", r."6", r."7", r."8") THEN 'Не завершены процедуры' WHEN r."3" = greatest(r."1", r."2", r."3", r."4", r."5", r."6", r."7", r."8") THEN 'Не завершены строительство, ввод в эксплуатацию, поставка оборудования' WHEN r."4" = greatest(r."1", r."2", r."3", r."4", r."5", r."6", r."7", r."8") THEN 'Не обеспечено финансирование' WHEN r."5" = greatest(r."1", r."2", r."3", r."4", r."5", r."6", r."7", r."8") THEN 'Не завершено согласование' WHEN r."6" = greatest(r."1", r."2", r."3", r."4", r."5", r."6", r."7", r."8") THEN 'Не завершена подготовка проекта документа' WHEN r."7" = greatest(r."1", r."2", r."3", r."4", r."5", r."6", r."7", r."8") THEN 'Не исполнены контрактные обязательства подрядчиками' WHEN r."8" = greatest(r."1", r."2", r."3", r."4", r."5", r."6", r."7", r."8") THEN 'Иное (ручной ввод)' ELSE NULL END AS reason FROM your_table r ) AS reasons WHERE reason IS NOT NULL;6. Использование временных переменных или курсоров
Если вы работаете с PL/pgSQL (или аналогичными языками), вы можете использовать временные переменные и курсоры для обработки логики в более детализированном способе.
DECLARE reason_list TEXT := ''; BEGIN FOR r IN SELECT * FROM your_table LOOP IF r."1" = greatest(r."1", r."2", r."3", r."4", r."5", r."6", r."7", r."8") THEN reason_list := reason_list || 'Не завершена проработка вопроса, '; END IF; IF r."2" = greatest(r."1", r."2", r."3", r."4", r."5", r."6", r."7", r."8") THEN reason_list := reason_list || 'Не завершены процедуры, '; END IF; -- Добавьте остальные условия аналогично END LOOP; -- Удаляем последнюю запятую и пробел reason_list := rtrim(reason_list, ', '); RAISE NOTICE 'Причины переносов: %', reason_list; END;Этот метод позволяет вам использовать более сложные логические конструкции и выводить результат по мере выполнения.
Каждый из этих подходов имеет свои преимущества и недостатки в зависимости от конкретных требований вашего проекта, используемой базы данных и объема данных. Выбор в пользу одного из них должен основываться на тех аспектах, которые вам важны, таких как простота кода, производительность или гибкость обработки данных.
- User
а я могу filter использовать как-то?
- Assistant
Да, вы можете использовать оператор
FILTERв PostgreSQL для того, чтобы более эффективно применять условия к агрегатным функциям. Это позволяет избежать написания несколькихCASEи упрощает код, делая его более читабельным. Например, вы можете использоватьSTRING_AGGв сочетании сFILTER, чтобы агрегировать только те значения, которые соответствуют определенным условиям.Вот пример использования
FILTERдля вашей задачи:SELECT STRING_AGG(reason, ', ') AS "Причины переносов" FROM ( SELECT -- Используем STRING_AGG с FILTER для каждого условия STRING_AGG(CASE WHEN r."1" = greatest(r."1", r."2", r."3", r."4", r."5", r."6", r."7", r."8") THEN 'Не завершена проработка вопроса' END) FILTER (WHERE r."1" = greatest(r."1", r."2", r."3", r."4", r."5", r."6", r."7", r."8") ) AS reason, STRING_AGG(CASE WHEN r."2" = greatest(r."1", r."2", r."3", r."4", r."5", r."6", r."7", r."8") THEN 'Не завершены процедуры' END) FILTER (WHERE r."2" = greatest(r."1", r."2", r."3", r."4", r."5", r."6", r."7", r."8") ) AS reason, STRING_AGG(CASE WHEN r."3" = greatest(r."1", r."2", r."3", r."4", r."5", r."6", r."7", r."8") THEN 'Не завершены строительство, ввод в эксплуатацию, поставка оборудования' END) FILTER (WHERE r."3" = greatest(r."1", r."2", r."3", r."4", r."5", r."6", r."7", r."8") ) AS reason, STRING_AGG(CASE WHEN r."4" = greatest(r."1", r."2", r."3", r."4", r."5", r."6", r."7", r."8") THEN 'Не обеспечено финансирование' END) FILTER (WHERE r."4" = greatest(r."1", r."2", r."3", r."4", r."5", r."6", r."7", r."8") ) AS reason, STRING_AGG(CASE WHEN r."5" = greatest(r."1", r."2", r."3", r."4", r."5", r."6", r."7", r."8") THEN 'Не завершено согласование' END) FILTER (WHERE r."5" = greatest(r."1", r."2", r."3", r."4", r."5", r."6", r."7", r."8") ) AS reason, STRING_AGG(CASE WHEN r."6" = greatest(r."1", r."2", r."3", r."4", r."5", r."6", r."7", r."8") THEN 'Не завершена подготовка проекта документа' END) FILTER (WHERE r."6" = greatest(r."1", r."2", r."3", r."4", r."5", r."6", r."7", r."8") ) AS reason, STRING_AGG(CASE WHEN r."7" = greatest(r."1", r."2", r."3", r."4", r."5", r."6", r."7", r."8") THEN 'Не исполнены контрактные обязательства подрядчиками' END) FILTER (WHERE r."7" = greatest(r."1", r."2", r."3", r."4", r."5", r."6", r."7", r."8") ) AS reason, STRING_AGG(CASE WHEN r."8" = greatest(r."1", r."2", r."3", r."4", r."5", r."6", r."7", r."8") THEN 'Иное (ручной ввод)' END) FILTER (WHERE r."8" = greatest(r."1", r."2", r."3", r."4", r."5", r."6", r."7", r."8") ) AS reason FROM your_table r ) AS reasons WHERE reason IS NOT NULL;Краткий обзор подхода с FILTER:
- Структура: Вместо повторного использования
CASEдля каждой причины, вы используетеSTRING_AGGс условиемFILTER. - Условия: Условия
FILTERпроверяют, является ли каждый из элементов текущим максимумом. - Агрегация: Вы можете собрать и отфильтровать только те строки, которые удовлетворяют условиям.
Этот код более лаконичный и легко читаемый, так как все агрегации выполняются в одном запросе с использованием
FILTER. Вы можете добавлять или изменять условия, не меняя при этом структуру запроса.Таким образом, использование
FILTERв этом контексте удобно и эффективно. - Структура: Вместо повторного использования
- User
а я могу сделать что-то типо такого: String_agg (мои причины filter..)
- Assistant
Да, вы можете использовать
STRING_AGGвместе сFILTER, чтобы агрегировать причины переносов в одном вызове функции. В данном случае вы просто проверяете условия, и если они истинны, соответствующая причина будет включена в результирующий набор.Попробуем использовать
STRING_AGGсFILTERдля каждой причины и затем объединить все в одну строку. Здесь приведен пример, как это можно сделать:SELECT STRING_AGG(reason, ', ') AS "Причины переносов" FROM ( SELECT CASE WHEN r."1" = greatest(r."1", r."2", r."3", r."4", r."5", r."6", r."7", r."8") THEN 'Не завершена проработка вопроса' WHEN r."2" = greatest(r."1", r."2", r."3", r."4", r."5", r."6", r."7", r."8") THEN 'Не завершены процедуры' WHEN r."3" = greatest(r."1", r."2", r."3", r."4", r."5", r."6", r."7", r."8") THEN 'Не завершены строительство, ввод в эксплуатацию, поставка оборудования' WHEN r."4" = greatest(r."1", r."2", r."3", r."4", r."5", r."6", r."7", r."8") THEN 'Не обеспечено финансирование' WHEN r."5" = greatest(r."1", r."2", r."3", r."4", r."5", r."6", r."7", r."8") THEN 'Не завершено согласование' WHEN r."6" = greatest(r."1", r."2", r."3", r."4", r."5", r."6", r."7", r."8") THEN 'Не завершена подготовка проекта документа' WHEN r."7" = greatest(r."1", r."2", r."3", r."4", r."5", r."6", r."7", r."8") THEN 'Не исполнены контрактные обязательства подрядчиками' WHEN r."8" = greatest(r."1", r."2", r."3", r."4", r."5", r."6", r."7", r."8") THEN 'Иное (ручной ввод)' ELSE NULL END AS reason FROM your_table r ) AS reasons WHERE reason IS NOT NULL GROUP BY r.some_grouping_column; -- если необходимо группировать по столбцуИспользование
STRING_AGGсFILTERТеперь если вы хотите упростить этот процесс и использовать
FILTER, чтобы избежать создания подзапроса, вот как это можно сделать:SELECT STRING_AGG(reason, ', ') AS "Причины переносов" FROM ( SELECT STRING_AGG('Не завершена проработка вопроса') FILTER (WHERE r."1" = greatest(r."1", r."2", r."3", r."4", r."5", r."6", r."7", r."8")) AS reason, STRING_AGG('Не завершены процедуры') FILTER (WHERE r."2" = greatest(r."1", r."2", r."3", r."4", r."5", r."6", r."7", r."8")) AS reason, STRING_AGG('Не завершены строительство, ввод в эксплуатацию, поставка оборудования') FILTER (WHERE r."3" = greatest(r."1", r."2", r."3", r."4", r."5", r."6", r."7", r."8")) AS reason, STRING_AGG('Не обеспечено финансирование') FILTER (WHERE r."4" = greatest(r."1", r."2", r."3", r."4", r."5", r."6", r."7", r."8")) AS reason, STRING_AGG('Не завершено согласование') FILTER (WHERE r."5" = greatest(r."1", r."2", r."3", r."4", r."5", r."6", r."7", r."8")) AS reason, STRING_AGG('Не завершена подготовка проекта документа') FILTER (WHERE r."6" = greatest(r."1", r."2", r."3", r."4", r."5", r."6", r."7", r."8")) AS reason, STRING_AGG('Не исполнены контрактные обязательства подрядчиками') FILTER (WHERE r."7" = greatest(r."1", r."2", r."3", r."4", r."5", r."6", r."7", r."8")) AS reason, STRING_AGG('Иное (ручной ввод)') FILTER (WHERE r."8" = greatest(r."1", r."2", r."3", r."4", r."5", r."6", r."7", r."8")) AS reason FROM your_table r ) AS filtered_reasons WHERE reason IS NOT NULL;Обратите внимание на следующие моменты:
- Упрощение: Использование
FILTERпозволяет вам избежать дублирования кода, так как вы используете одну функциюSTRING_AGGдля каждой причины, и при этом проверяется, будет ли этот конкретный элемент включен в результат. - Конкатенация: Если просто вызывать
STRING_AGGсFILTER, то результат будет отдельными строками для каждой причины. Чтобы объединить их в одну строку, вы можете использовать внешнийSTRING_AGG(как показано в предыдущих примерах). - Группировка: Обязательно указать группировку, если вам это необходимо, чтобы избежать дублирования результатов.
Таким образом, использование
FILTERделает ваш запрос более элегантным и удобным для чтения, а также позволяет избежать множества условийCASE. - Упрощение: Использование