select vas.ordernumber as "Номер поручения", vas.orderdate as "Дата поручения",…
- User
select vas.order_number as "Номер поручения", vas.order_date as "Дата поручения", vas.short_description as "Наименование поручения", coalesce (vas.avg_deadline_postponement, 0) as "Количество переносов", string_agg(case when postpone_reason_1 >= 1 then 'Не завершена проработка вопроса' when postpone_reason_2 >= 1 then 'Не завершены процедуры' when postpone_reason_3 >= 1 then 'Не завершены строительство, ввод в эксплуатацию, поставка оборудования' when postpone_reason_4 >=1 then 'Не обеспечено финансирование' when postpone_reason_5 >=1 then 'Не завершено согласование' when postpone_reason_6 >=1 then 'Не завершена подготовка проекта документа' when postpone_reason_7 >=1 then 'Не исполнены контрактные обязательства подрядчиками' when postpone_reason_8 >=1 then 'Иное (ручной ввод)' end, ', ') as "Причины переносов" from v_additional_summary vas group by vas.order_number, vas.order_date, vas.short_description, vas.avg_deadline_postponement; есть вот такой код, мне теперь нужно сделать так, чтобы выводилась самая встречающаяся причина и выводить ее
- Assistant
Для того чтобы получить самую встречающуюся причину переносов в вашем SQL-запросе, вы можете использовать подзапрос или CTE (Common Table Expression), чтобы подсчитать количество вхождений каждой причины, а затем выбрать из них максимальное значение.
Вам нужно будет изменить ваш запрос следующим образом:
WITH reasons_count AS ( SELECT vas.order_number, vas.order_date, vas.short_description, coalesce(vas.avg_deadline_postponement, 0) AS avg_deadline_postponement, CASE WHEN postpone_reason_1 >= 1 THEN 'Не завершена проработка вопроса' WHEN postpone_reason_2 >= 1 THEN 'Не завершены процедуры' WHEN postpone_reason_3 >= 1 THEN 'Не завершены строительство, ввод в эксплуатацию, поставка оборудования' WHEN postpone_reason_4 >= 1 THEN 'Не обеспечено финансирование' WHEN postpone_reason_5 >= 1 THEN 'Не завершено согласование' WHEN postpone_reason_6 >= 1 THEN 'Не завершена подготовка проекта документа' WHEN postpone_reason_7 >= 1 THEN 'Не исполнены контрактные обязательства подрядчиками' WHEN postpone_reason_8 >= 1 THEN 'Иное (ручной ввод)' END AS reason FROM v_additional_summary vas WHERE postpone_reason_1 >= 1 OR postpone_reason_2 >= 1 OR postpone_reason_3 >= 1 OR postpone_reason_4 >= 1 OR postpone_reason_5 >= 1 OR postpone_reason_6 >= 1 OR postpone_reason_7 >= 1 OR postpone_reason_8 >= 1 ), counted_reasons AS ( SELECT order_number, order_date, short_description, avg_deadline_postponement, reason, COUNT(*) AS reason_count FROM reasons_count GROUP BY order_number, order_date, short_description, avg_deadline_postponement, reason ), most_common_reason AS ( SELECT order_number, order_date, short_description, avg_deadline_postponement, reason, ROW_NUMBER() OVER (PARTITION BY order_number, order_date, short_description, avg_deadline_postponement ORDER BY reason_count DESC) AS rn FROM counted_reasons ) SELECT order_number AS "Номер поручения", order_date AS "Дата поручения", short_description AS "Наименование поручения", avg_deadline_postponement AS "Количество переносов", (SELECT reason FROM most_common_reason mcr WHERE mcr.order_number = oc.order_number AND mcr.order_date = oc.order_date AND mcr.short_description = oc.short_description AND mcr.rn = 1) AS "Самая встречающаяся причина" FROM (SELECT DISTINCT order_number, order_date, short_description, avg_deadline_postponement FROM reasons_count) oc;Объяснение изменений:
- CTE
reasons_count: Извлекает все причины переносов с указанием, какая причина соответствует каждому поручению. - CTE
counted_reasons: Подсчитывает количество вхождений каждой причины для каждого поручения. - CTE
most_common_reason: ИспользуетROW_NUMBER()для присвоения номера каждой причине в зависимости от частоты ее встречаемости. - Основной запрос: Получает все уникальные поручения и их детали, добавляя новую колонку "Самая встречающаяся причина", извлекая наибольшую по количеству причину из
most_common_reason.
Этот окончательный запрос приведет к тому, что будет отображаться наиболее частая причина для каждого поручения.
- CTE
- User
давай другое решение
- Assistant
Конечно! Мы можем сделать решение более прямолинейным, избегая использование CTE. Основная идея заключается в том, чтобы агрегировать причины переносов и затем выбрать наиболее часто встречающуюся. Вот как это можно сделать с использованием подзапроса:
SELECT vas.order_number AS "Номер поручения", vas.order_date AS "Дата поручения", vas.short_description AS "Наименование поручения", COALESCE(vas.avg_deadline_postponement, 0) AS "Количество переносов", (SELECT reason FROM ( SELECT CASE WHEN postpone_reason_1 >= 1 THEN 'Не завершена проработка вопроса' WHEN postpone_reason_2 >= 1 THEN 'Не завершены процедуры' WHEN postpone_reason_3 >= 1 THEN 'Не завершены строительство, ввод в эксплуатацию, поставка оборудования' WHEN postpone_reason_4 >= 1 THEN 'Не обеспечено финансирование' WHEN postpone_reason_5 >= 1 THEN 'Не завершено согласование' WHEN postpone_reason_6 >= 1 THEN 'Не завершена подготовка проекта документа' WHEN postpone_reason_7 >= 1 THEN 'Не исполнены контрактные обязательства подрядчиками' WHEN postpone_reason_8 >= 1 THEN 'Иное (ручной ввод)' END AS reason, COUNT(*) AS reason_count FROM v_additional_summary WHERE order_number = vas.order_number AND order_date = vas.order_date AND short_description = vas.short_description AND (postpone_reason_1 >= 1 OR postpone_reason_2 >= 1 OR postpone_reason_3 >= 1 OR postpone_reason_4 >= 1 OR postpone_reason_5 >= 1 OR postpone_reason_6 >= 1 OR postpone_reason_7 >= 1 OR postpone_reason_8 >= 1) GROUP BY reason ORDER BY reason_count DESC LIMIT 1 ) AS subquery ) AS "Самая встречающаяся причина" FROM v_additional_summary vas GROUP BY vas.order_number, vas.order_date, vas.short_description, vas.avg_deadline_postponement;Объяснение изменений:
- Подзапрос: В основном запросе мы добавляем подзапрос, который начинает с выборки всех причин для данного поручения (
vas.order_number,vas.order_date,vas.short_description) и группирует их, посчитывая количество. - Поиск самой частой причины: Мы упорядочиваем результаты подзапроса по убыванию количества и берём только первую запись (
LIMIT 1), что и будет самой встречающейся причиной. - Основной запрос: Оставляет структуру вашего изначального запроса почти такой же, но теперь включает новую колонку для самой частой причины.
Этот подход также даёт возможность получить желаемый результат, однако реализация остаётся более компактной и понятной с использованием подзапроса.
- Подзапрос: В основном запросе мы добавляем подзапрос, который начинает с выборки всех причин для данного поручения (