Skip to content
TheChatArchiveThe Chat Archive
gpt-4o-mini-2024-07-18· OpenAIAi2 WildChat-4.8M (AI2)

select vas.ordernumber as "Номер поручения", vas.orderdate as "Дата поручения",…

  1. 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; есть вот такой код, мне теперь нужно сделать так, чтобы выводилась самая встречающаяся причина и выводить ее

  2. 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;
    

    Объяснение изменений:

    1. CTE reasons_count: Извлекает все причины переносов с указанием, какая причина соответствует каждому поручению.
    2. CTE counted_reasons: Подсчитывает количество вхождений каждой причины для каждого поручения.
    3. CTE most_common_reason: Использует ROW_NUMBER() для присвоения номера каждой причине в зависимости от частоты ее встречаемости.
    4. Основной запрос: Получает все уникальные поручения и их детали, добавляя новую колонку "Самая встречающаяся причина", извлекая наибольшую по количеству причину из most_common_reason.

    Этот окончательный запрос приведет к тому, что будет отображаться наиболее частая причина для каждого поручения.

  3. User

    давай другое решение

  4. 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;
    

    Объяснение изменений:

    1. Подзапрос: В основном запросе мы добавляем подзапрос, который начинает с выборки всех причин для данного поручения (vas.order_number, vas.order_date, vas.short_description) и группирует их, посчитывая количество.
    2. Поиск самой частой причины: Мы упорядочиваем результаты подзапроса по убыванию количества и берём только первую запись (LIMIT 1), что и будет самой встречающейся причиной.
    3. Основной запрос: Оставляет структуру вашего изначального запроса почти такой же, но теперь включает новую колонку для самой частой причины.

    Этот подход также даёт возможность получить желаемый результат, однако реализация остаётся более компактной и понятной с использованием подзапроса.