Skip to content
TheChatArchiveThe Chat Archive
Perplexitysonar-pro-high· PerplexityHugging Face LMArena Search Arena 24K

Есть запись, которая дублируется, нужно изменить логику SQL запроса чтобы убрать…

From a Chatbot Arena battleThe same person sent this prompt to two anonymous models at once. Compare it with gpt-4o-search-preview-high-loc’s answer.
  1. User

    Есть запись, которая дублируется, нужно изменить логику SQL запроса чтобы убрать дублирование: Кишаева Нино Шотовна 10.06.1984 [PHONE_NUMBER] 90 Не указан Не указан Нет детей до 18 лет Нет детей старше 18 лет Не указано Кишаева Нино Шотовна 10.06.1984 [PHONE_NUMBER] 90 Не указан Не указан "2. Кишаев Адам Русланович 06.09.2012 3. Кишаев Сабир Русланович 18.07.2016

    1. Кишаева Самия Руслановна 01.05.2010" Нет детей старше 18 лет 104000002084985 Напиши измененную аннотацию Query
            @Query(value = "SELECT " +
            // Формируем ФИО заявителя (основного родителя)
                            "(p.surname || ' ' || p.name || ' ' || p.patronymic) AS applicantFullName, " +
                            "p.birthdate AS applicantBirthDate, " + // Дата рождения заявителя
                            "p.snils AS applicantSnils, " + // СНИЛС заявителя
                            // Определяем второго родителя, если он есть
                            "COALESCE(sp.second_parent_fullname, 'Не указан') AS secondParentFullName, " + // ФИО второго
                                                                                                           // родителя
                            "COALESCE(sp.second_parent_snils, 'Не указан') AS secondParentSnils," + // СНИЛС второго
                                                                                                    // родителя
                            // Определяем список детей младше 18 лет
                            "COALESCE(cl_under_18.children_list, 'Нет детей до 18 лет') AS childrenList, " +
                            // Определяем список детей от 18 до 23 лет
                            "COALESCE(cl_over_18.adult_children_list, 'Нет детей старше 18 лет') AS adultChildrenList, " +
                            "COALESCE(d.attachment, 'Не указано') AS attachment " +
                            "FROM family f " +
                            // Присоединяем таблицу решений (decision), связывая ее по decision_id
                            "INNER JOIN decision d ON d.id = f.decision_id " +
                            // Присоединяем таблицу person, определяя заявителя
                            "INNER JOIN person p ON p.id = f.person_id " +
                            // Определяем второго родителя (если есть)
                            "LEFT JOIN LATERAL ( " +
                            "    SELECT " +
                            "        s.id, " +
                            "        s.surname || ' ' || s.name || ' ' || s.patronymic AS second_parent_fullname, " +
                            "        s.snils AS second_parent_snils " +
                            "    FROM person s " +
                            "    INNER JOIN family f2 ON f2.person_id = s.id " + // Проверяем, что человек тоже связан с
                                                                                 // этим решением
                            "    WHERE f2.decision_id = d.id " +
                            "      AND s.id <> p.id " + // Исключаем основного родителя
                            "      AND s.birthdate <= CURRENT_DATE - INTERVAL '23 years' " + // Условие 1: второй родитель
                                                                                             // старше 23 лет
                            "      AND ABS(EXTRACT(YEAR FROM AGE(p.birthdate, s.birthdate))) < 16 " + // Условие 2: разница
                                                                                                      // возраста < 16 лет
                                                                                                      // по модулю
                            // Исключаем записи с отчествами, характерными для детей
                            "  AND s.patronymic NOT IN ('', 'NULL') " +
                            "  AND (" +
                            // Проверка специфических форматов отчеств
                            "    s.patronymic ~ '(ович|евич|ич|оглы|кызы|улы|уулу)$' OR " +
                            // Стандартные проверки для определения пола
                            "    (" +
                            // Проверка для женщин (заявитель)
                            "    ((p.patronymic ~ '(овна|евна|ична|инична|кызы)$' OR " +
                            "      p.surname ~ '(ова|ева|ина|ская|цкая|ая)$' OR " +
                            "      p.name ~ '(а|я)$') AND " +
                            "     (s.patronymic ~ '(ович|евич|ич|оглы|улы|уулу)$' OR " +
                            "      s.surname !~ '(ова|ева|ина|ская|цкая|ая)$')) " +
                            "    OR " +
                            // Проверка для мужчин (заявитель)
                            "    ((p.patronymic ~ '(ович|евич|ич|оглы|улы|уулу)$' OR " +
                            "      p.surname !~ '(ова|ева|ина|ская|цкая|ая)$') AND " +
                            "     (s.patronymic ~ '(овна|евна|ична|инична|кызы)$' OR " +
                            "      s.surname ~ '(ова|ева|ина|ская|цкая|ая)$')) " +
                            "    )" +
                            "  ) " +
                            // Исключаем записи, где отчество содержит имя родителя
                            "  AND NOT EXISTS (" +
                            "    SELECT 1 FROM family f3 " +
                            "    INNER JOIN person p3 ON p3.id = f3.person_id " +
                            "    WHERE f3.decision_id = d.id " +
                            "      AND p3.name = SUBSTRING(s.patronymic FROM 1 FOR LENGTH(s.patronymic) - 4) " +
                            "  ) " +
                            "  ORDER BY s.birthdate DESC " +
                            "  LIMIT 1 " +
                            ") sp ON TRUE " +
                            // Определяем список детей до 18 лет
                            "LEFT JOIN LATERAL ( " +
                            "    SELECT STRING_AGG( " +
                            "        CONCAT(num || '. ', child_data), E'\\n' " + // Объединяем список детей в строку с
                                                                                 // номерами
                            "    ) AS children_list " +
                            "    FROM ( " +
                            "        SELECT " +
                            "            ROW_NUMBER() OVER (ORDER BY p_child.birthdate) AS num, " + // Пронумеровываем детей
                            "            p_child.surname || ' ' || p_child.name || ' ' || p_child.patronymic || ' ' || " +
                            "            TO_CHAR(p_child.birthdate, 'DD.MM.YYYY') AS child_data " + // Формируем строку с
                                                                                                    // данными
                                                                                                    // ребенка
                            "        FROM family f_child " +
                            "        INNER JOIN person p_child ON p_child.id = f_child.person_id " +
                            "        WHERE f_child.decision_id = d.id " +
                            "          AND p_child.birthdate > CURRENT_DATE - INTERVAL '18 years' " + // Только дети младше
                                                                                                      // 18 лет
                            "    ) n " +
                            ") cl_under_18 ON TRUE " +
                            // Определяем список детей от 18 лет
                            "LEFT JOIN LATERAL ( " +
                            "    SELECT STRING_AGG( " +
                            "        CONCAT(num || '. ', adult_child_data), E'\\n' " +
                            "    ) AS adult_children_list " +
                            "    FROM ( " +
                            "        SELECT " +
                            "            ROW_NUMBER() OVER (ORDER BY p_child.birthdate) AS num, " +
                            "            p_child.surname || ' ' || p_child.name || ' ' || p_child.patronymic || ' ' || " +
                            "            TO_CHAR(p_child.birthdate, 'DD.MM.YYYY') AS adult_child_data " + // Данные ребенка
                            "        FROM family f_child " +
                            "        INNER JOIN person p_child ON p_child.id = f_child.person_id " +
                            "        WHERE f_child.decision_id = d.id " +
                            "          AND p_child.birthdate < CURRENT_DATE - INTERVAL '18 years' AND p_child.id <> p.id AND (sp.id IS NULL OR p_child.id <> sp.id) "
                            + // Дети от 18 лет
                            "    ) n " +
                            ") cl_over_18 ON TRUE " +
                            "WHERE " +
                            "    p.birthdate <= CURRENT_DATE - INTERVAL '18 years' " + // Заявитель должен быть старше 18
                                                                                       // лет
                            // " AND (p.patronymic ~ '(на|ва)$' OR p.surname ~ '(ва)$') " + // Проверяем,
                            // что заявитель — женщина
                            "    AND d.emp_org_inn LIKE :empOrgInn " + // Фильтрация по организации
                            "    AND p.is_major_beneficiary IS TRUE " + // Проверяем, что заявитель является основным
                                                                        // получателем
                            "    AND d.number IS NOT NULL AND d.number <> '' " + // Проверяем, что у решения есть номер
                            "    AND d.date_decision BETWEEN :startDate AND :endDate " + // Фильтрация по дате решения
                            "    AND (:familyChildrenNumber = 'any' OR (SELECT COUNT(*) FROM family f_child " + // Фильтрация
                                                                                                                // по общему
                                                                                                                // количеству
                                                                                                                // детей в
                                                                                                                // семье
                            "                                           INNER JOIN person p_child ON p_child.id = f_child.person_id "
                            +
                            "                                           WHERE f_child.decision_id = d.id AND p_child.id <> p.id AND (sp.id IS NULL OR p_child.id <> sp.id)) = CAST(:familyChildrenNumber AS INTEGER)) "
                            +
                            "ORDER BY " +
                            "    d.number, " + // Сортировка по номеру решения
                            "    CASE " +
                            "        WHEN (p.patronymic ~ '(на|ва|де)$') THEN 100 " + // Приоритет по отчеству
                            "        WHEN (p.surname ~ '(ва)$') THEN 10 " + // Приоритет по фамилии
                            "        WHEN (p.name ~ '(а)$') THEN 1 " + // Приоритет по имени
                            "        ELSE 0 " +
                            "    END DESC, " +
                            "    EXTRACT(YEAR FROM AGE(p.birthdate)) DESC", // Сортировка по возрасту заявителя (от старших
                                                                            // к младшим)
                            nativeQuery = true)
            List<FamilyReportProjection> findFamilyReport(@Param("empOrgInn") String empOrgInn,
                            @Param("startDate") LocalDate startDate, @Param("endDate") LocalDate endDate,
                            @Param("familyChildrenNumber") String familyChildrenNumber);
    
  2. Perplexity Assistant

    Ваша проблема с дублированием записей возникает из-за того, что для одной и той же семьи (decision) формируется несколько строк ― обычно это связано с тем, что в подзапросах (особенно для детей) возвращается несколько вариантов, либо из-за особенностей соединений.

    Рекомендация:
    Чтобы убрать дублирование, нужно сделать так, чтобы на каждый decision + основной родитель (p.id) возвращалась только одна строка. Обычно это достигается с помощью DISTINCT ON, либо группировкой (GROUP BY), либо корректным использованием оконных функций и агрегатных подзапросов.

    Как исправить ваш запрос

    1. Используйте DISTINCT ON

    В PostgreSQL можно использовать DISTINCT ON для выбора только одной строки на уникальную комбинацию полей (например, по заявителю и решению):

    SELECT DISTINCT ON (d.id, p.id)
      -- ... ваш SELECT ...
    FROM ...
    -- остальной запрос
    ORDER BY d.id, p.id, d.number, ... -- порядок важен!
    

    2. Убедитесь, что подзапросы для детей возвращают агрегированные значения

    Ваши подзапросы для детей уже используют STRING_AGG, что правильно. Но если где-то в JOIN или WHERE остаются условия, которые могут размножить строки (например, неочевидные связи через family), это может привести к дублированию.

    3. Проверьте, что нет лишних JOIN-ов

    Особенно обратите внимание на LEFT JOIN LATERAL ― если в них что-то возвращает несколько строк, это размножит результат.


    Исправленный фрагмент аннотации запроса

    Вариант с DISTINCT ON (для PostgreSQL):

    @Query(value = "SELECT DISTINCT ON (d.id, p.id) " +
        "(p.surname || ' ' || p.name || ' ' || p.patronymic) AS applicantFullName, " +
        "p.birthdate AS applicantBirthDate, " +
        "p.snils AS applicantSnils, " +
        "COALESCE(sp.second_parent_fullname, 'Не указан') AS secondParentFullName, " +
        "COALESCE(sp.second_parent_snils, 'Не указан') AS secondParentSnils, " +
        "COALESCE(cl_under_18.children_list, 'Нет детей до 18 лет') AS childrenList, " +
        "COALESCE(cl_over_18.adult_children_list, 'Нет детей старше 18 лет') AS adultChildrenList, " +
        "COALESCE(d.attachment, 'Не указано') AS attachment " +
        "FROM family f " +
        "INNER JOIN decision d ON d.id = f.decision_id " +
        "INNER JOIN person p ON p.id = f.person_id " +
        // ... остальной ваш запрос ...
        "ORDER BY d.id, p.id, d.number, " +
        "CASE WHEN (p.patronymic ~ '(на|ва|де)$') THEN 100 " +
        "     WHEN (p.surname ~ '(ва)$') THEN 10 " +
        "     WHEN (p.name ~ '(а)$') THEN 1 " +
        "     ELSE 0 END DESC, " +
        "EXTRACT(YEAR FROM AGE(p.birthdate)) DESC",
        nativeQuery = true)
    List<familyreportprojection> findFamilyReport(
        @Param("empOrgInn") String empOrgInn,
        @Param("startDate") LocalDate startDate,
        @Param("endDate") LocalDate endDate,
        @Param("familyChildrenNumber") String familyChildrenNumber);
    

    Кратко: что изменилось

    • Добавлен DISTINCT ON (d.id, p.id) в начало SELECT.
    • В ORDER BY первым идет d.id, p.id (это важно для работы DISTINCT ON).
    • Остальная логика запроса не меняется.

    Почему это работает?

    DISTINCT ON (d.id, p.id) гарантирует, что для каждой уникальной пары решение+заявитель будет только одна строка, даже если подзапросы или JOIN-ы по каким-то причинам возвращают больше.


    Если используете не PostgreSQL

    Если у вас не PostgreSQL, используйте оконную функцию ROW_NUMBER():

    SELECT *
    FROM (
      -- ваш SELECT
      ROW_NUMBER() OVER (PARTITION BY d.id, p.id ORDER BY d.number, ...) as rn
      -- ...
    ) t
    WHERE t.rn = 1
    

    Итог

    Добавьте DISTINCT ON (d.id, p.id) в SELECT и соответствующий ORDER BY ― это уберет дублирование по заявителю и решению.

    Если потребуется, помогу адаптировать под другой диалект SQL или под ваши дополнительные условия!

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.