Есть запись, которая дублируется, нужно изменить логику SQL запроса чтобы убрать…
- User
Есть запись, которая дублируется, нужно изменить логику SQL запроса чтобы убрать дублирование: Кишаева Нино Шотовна 10.06.1984 [PHONE_NUMBER] 90 Не указан Не указан Нет детей до 18 лет Нет детей старше 18 лет Не указано Кишаева Нино Шотовна 10.06.1984 [PHONE_NUMBER] 90 Не указан Не указан "2. Кишаев Адам Русланович 06.09.2012 3. Кишаев Сабир Русланович 18.07.2016
- Кишаева Самия Руслановна 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); 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 или под ваши дополнительные условия!
- Добавлен
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.