В стиле 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]

О, помидорчики!
1. Ты, видимо, предлагаешь char(1)? Странный это тип, не уверен, что имеет смысл связываться с ним ради экономии пары байтиков.
2. Полностью согласен, но, поскольку пример из жизни, то и таблица® взята такая, какая в жизни.
3. Тут не понял. Мы не посчитаем ранг без people, он ведь там используется. Вот если бы не использовался, то да.
4. См. п. 2, но пожалуй ты прав, это стоило бы сделать.

[hardsign.livejournal.com]

1. char - прекрасный тип, если использовать его для строк действительно постоянной длины
2. а проектирование структуры™ - отдельная интересная тема
3. ты прав, да, но см. п. 2. По зрелом размышлении я бы завёл отдельные классы для отношения "мать сына", "отец дочери", "муж" и т. д., чтобы можно было однозначно инвертировать значение.

[egorius.livejournal.com]

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