В стиле SQL - 5 (сортировка)

[egorius]

Нередки случаи, когда сама постановка задачи подталкивает разработчика к процедурному решению. Вот пример из практики. В кадровый отчет по сотруднику требуется выводить тип родства, ФИО и дату рождения ближайшего родственника (например: «Отец — Эпиктетов Полуэкт Полуэктович, 01.04.1900»).

Модель данных упрощенно выглядит следующим образом. Есть таблица people с информацией о людях (как сотрудниках, так и их родственниках):

create table people(
  person_id         number,
  employee_flag     varchar2(1) check(   employee_flag = 'Y' -- yes
                                      or employee_flag = 'N' -- no
                                     ),
  full_name         varchar2(240),
  marital_status    varchar2(1) check(   marital_status = 'M' -- married (or null)
                                     ),
  sex               varchar2(1) check(   sex = 'M' -- male
                                      or sex = 'F' -- female
                                     ),
  date_of_birth     date
);

И есть таблица, связывающая людей с родственниками (связь односторонняя):

create table contact_relationships(
  person_id         number,
  contact_person_id number,
  contact_type      varchar2(1) check(   contact_type = 'C' -- child
                                      or contact_type = 'P' -- parent
                                      or contact_type = 'S' -- spouse
                                     ) 
);

Поскольку в системе может быть зарегистрировано много родственников, нужно определить, кто из них считается ближайшим. Вот что говорит на этот счет тех. задание:

Если в поле «Статус» = Состоит в зарегистрированном браке и Отношение = Супруг(а), то выводить данные супруга\супруги.
Если в поле «Статус» значение отличное от значения «Состоит в зарегистрированном браке», то выводим в отчет родственника в зависимости от приоритета.
Сначала проверяется наличие детей (Тип отношения = ребенок), причем если детей несколько, то выводить самого старшего.
Если детей нет, то выводить мать (тип отношения = родитель, пол = жен.)
Если матери нет, то выводить отца (тип отношения = родитель, пол = муж.).

Алгоритм определения ближайшего родственника сформулирован в терминах «если-то» и напрашивается процедурное решение. Напишем функцию, получающую person_id сотрудника и возвращающую... что? Логично было бы вернуть запись из трех полей (тип родства, ФИО и дата рождения), но что с ней потом делать в запросе? Можно написать три отдельных функции, а чтобы не ухудшать в три раза производительность, приделать кэширование. Но не будем усложнять: пусть функция будет одна и пусть она возвращает конкатенацию полей. Она могла бы выглядеть следующим образом:

create or replace package contacts as
  function conc(
    p_contact_type  varchar2,
    p_sex           varchar2,
    p_full_name     varchar2,
    p_date_of_birth date
  )
  return varchar2;
  
  function get_contact_info(
    p_person_id number
  )
  return varchar2;
end;
/
create or replace package body contacts as
  function conc(
    p_contact_type  varchar2,
    p_sex           varchar2,
    p_full_name     varchar2,
    p_date_of_birth date
  )
  return varchar2
  is
  begin
    return case p_contact_type || p_sex
             when 'CM' then 'Сын'
             when 'CF' then 'Дочь'
             when 'PM' then 'Отец'
             when 'PF' then 'Мать'
             when 'SM' then 'Муж'
             when 'SF' then 'Жена'
           end
        || ' - '
        || p_full_name
        || ', '
        || to_char(p_date_of_birth, 'DD.MM.YYYY');
  end;
  
  function get_contact_info(
    p_person_id number
  )
  return varchar2
  is
    cursor c_spouse(p_person_id number) is
        select   conc(cr.contact_type, c.sex, c.full_name, c.date_of_birth)
        from     contact_relationships cr,
                 people c
        where    cr.person_id = p_person_id
        and      cr.contact_type = 'S'
        and      c.person_id = cr.contact_person_id;
    cursor c_child(p_person_id number) is
        select   conc(cr.contact_type, c.sex, c.full_name, c.date_of_birth)
        from     contact_relationships cr,
                 people c
        where    cr.person_id = p_person_id
        and      cr.contact_type = 'C'
        and      c.person_id = cr.contact_person_id
        order by c.date_of_birth;
    cursor c_mother(p_person_id number) is
        select   conc(cr.contact_type, c.sex, c.full_name, c.date_of_birth)
        from     contact_relationships cr,
                 people c
        where    cr.person_id = p_person_id
        and      cr.contact_type = 'P'
        and      c.person_id = cr.contact_person_id
        and      c.sex = 'F';
    cursor c_father(p_person_id number) is
        select   conc(cr.contact_type, c.sex, c.full_name, c.date_of_birth)
        from     contact_relationships cr,
                 people c
        where    cr.person_id = p_person_id
        and      cr.contact_type = 'P'
        and      c.person_id = cr.contact_person_id
        and      c.sex = 'M';
    res varchar2(1000);
    m_status varchar2(1);
  begin
    select   marital_status
    into     m_status
    from     people
    where    person_id = p_person_id;

    if m_status = 'M' then
      open c_spouse(p_person_id);
      fetch c_spouse into res;
      close c_spouse;
      return res;
    else
      open c_child(p_person_id);
      fetch c_child into res;
      close c_child;
      if res is not null then
        return res;
      end if;
      
      open c_mother(p_person_id);
      fetch c_mother into res;
      close c_mother;
      if res is not null then
        return res;
      end if;
      
      open c_father(p_person_id);
      fetch c_father into res;
      close c_father;
      return res;
    end if;
  end;
end;
/

Накидаем тестовых данных:

insert into people values(1,'Y','Муми-папа',  'M', 'M',to_date('01.01.1901','DD.MM.YYYY'));
insert into people values(2,'Y','Муми-мама',  'M', 'F',to_date('02.02.1902','DD.MM.YYYY'));
insert into people values(3,'Y','Муми-тролль',null,'M',to_date('03.03.1933','DD.MM.YYYY'));
insert into contact_relationships values (1,2,'S');
insert into contact_relationships values (1,3,'C');
insert into contact_relationships values (2,3,'C');
insert into contact_relationships values (3,1,'P');

insert into people values(4,'Y','Мюмла-мама', null, 'F',to_date('04.04.1904','DD.MM.YYYY'));
insert into people values(5,'N','Снусмумрик', null, 'M',to_date('05.05.1935','DD.MM.YYYY'));
insert into people values(6,'Y','Мюмла',      null, 'F',to_date('06.06.1936','DD.MM.YYYY'));
insert into contact_relationships values (4,5,'C');
insert into contact_relationships values (4,6,'C');

И вот как можно воспользоваться функцией:

select   full_name,
         contacts.get_contact_info(person_id)
from     people
where    employee_flag = 'Y';

FULL_NAME   CONTACT_INFO                 
----------- ---------------------------- 
Муми-папа   Жена - Муми-мама, 02.02.1902 
Муми-мама                                
Муми-тролль Отец - Муми-папа, 01.01.1901 
Мюмла-мама  Сын - Снусмумрик, 05.05.1935 
Мюмла                                    

Так себе решение. Помимо неадекватно раздутого кода такой подход чреват проблемами с производительностью на больших объемах данных. Запросы внутри функции, которая сама используется в запросе — это, фактически, nested loops, навязанный оптимизатору. Об этом я писал когда-то.

Как подойти к задаче в стиле SQL? Можно заметить, что курсоры c_mother и c_father ничем не отличаются друг от друга, кроме одного условия. Их можно свести в один запрос, записи отсортировать в нужном порядке и выбрать из них первую.

Дальше можно заметить, что и остальные курсоры вполне укладываются в эту схему, если несколько усложнить условие сортировки. И тогда вместо процедурного кода вырисовывается запрос:

with ranked_contacts as (
  select   p.person_id,
           p.full_name,
           case cr.contact_type || c.sex
             when 'CM' then 'Сын'
             when 'CF' then 'Дочь'
             when 'PM' then 'Отец'
             when 'PF' then 'Мать'
             when 'SM' then 'Муж'
             when 'SF' then 'Жена'
           end contact_type,
           c.full_name contact_name,
           c.date_of_birth contact_birth,
           case -- ранг родственника: чем меньше, тем приоритетнее
             when p.marital_status = 'M' then
               case
                 when cr.contact_type = 'S' then 0 -- супруг(а)
                 else null
               end
             else
               case
                 when cr.contact_type = 'C' then 0 -- ребенок
                 when cr.contact_type || c.sex = 'PF' then 1 -- мать
                 when cr.contact_type || c.sex = 'PM' then 2 -- отец
               end
           end rnk
  from     people p,
           contact_relationships cr,
           people c
  where    p.employee_flag = 'Y'
  and      cr.person_id(+) = p.person_id
  and      c.person_id(+) = cr.contact_person_id
), 
ranked_contacts2 as (
  select   t.person_id,
           t.full_name,
           case when t.rnk is null then null else t.contact_type end contact_type,
           case when t.rnk is null then null else t.contact_name end contact_name,
           case when t.rnk is null then null else t.contact_birth end contact_birth,
           t.rnk
  from     ranked_contacts t
)
select   t.full_name,
         min(t.contact_type)  keep (dense_rank first order by t.rnk nulls last, t.contact_birth) contact_type,
         min(t.contact_name)  keep (dense_rank first order by t.rnk nulls last, t.contact_birth) contact_name,
         min(t.contact_birth) keep (dense_rank first order by t.rnk nulls last, t.contact_birth) contact_birth
from     ranked_contacts2 t
group by t.person_id,
         t.full_name;

Тут нелишними будут некоторые пояснения. Во-первых, в подзапросе ranked_contacts используется левое соединение, чтобы не потерять сотрудников, у которых в системе нет родственников.

Во-вторых, каждому родственнику присваивается ранг в порядке «близости». Причем если ранг пустой, то такого родственника выводить не надо, даже если нет никого больше (а самого сотрудника, конечно, надо). Именно для этого нужен подзапрос ranked_contacts2 и еще фрагменты, выделенные курсивом. Формально последние не нужны, но делают этот тонкий момент чуть более явным.

Чтобы тонкость была понятнее, можно посмотреть на результат подзапроса ranked_contacts:

PERSON_ID FULL_NAME   CONTACT_TYPE CONTACT_NAME CONTACT_BIRTH RNK 
--------- ----------- ------------ ------------ ------------- --- 
        1 Муми-папа   Жена         Муми-мама         02.02.02   0
        1 Муми-папа   Сын          Муми-тролль       03.03.33     -- не выводить
        2 Муми-мама   Сын          Муми-тролль       03.03.33     -- не выводить
        3 Муми-тролль Отец         Муми-папа         01.01.01   2 
        4 Мюмла-мама  Сын          Снусмумрик        05.05.35   0 
        4 Мюмла-мама  Дочь         Мюмла             06.06.36   0 
        6 Мюмла                                                   -- никого нет

И сравнить с финальным результатом:

FULL_NAME   CONTACT_TYPE CONTACT_NAME CONTACT_BIRTH 
----------- ------------ ------------ ------------- 
Муми-папа   Жена         Муми-мама         02.02.02 
Муми-мама                                           
Муми-тролль Отец         Муми-папа         01.01.01 
Мюмла-мама  Сын          Снусмумрик        05.05.35 
Мюмла                                               

Какое решение лучше?

Процедурный код неказист, имеет большой размер и проигрывает по производительности. Зато он был написан быстро и заработал с первого раза.

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

Так какое же лучше? Не знаю. Но лично я, конечно, за SQL.

Comments

[egorius.livejournal.com]

По п. 2 у меня опыт невелик... Тем не менее он подсказывает мне, что не стоит сильно затачивать структуру под отдельно взятую и, скорее всего, второстепенную задачу (:
Но тема для размышлений интересная.