Miravild

Имеется база данных по клиентам, имеющая структуру:

Клиенты
ID	number
NAME	Varchar2	Наименование

Контакты
ID	number
CLIENT_ID	number	FK CLIENT
C_TYPE	number	Тип контакта 1-телефон 2-email
C_INFO	varchar2	Контакт – телефон либо адрес email
CREATED	date	Дата внесения в базу
ACTIVE	Char(1)	Y/N активный или архив

Адреса
ID	number
CLIENT_ID	number	FK CLIENT
A_TYPE	number	Тип адреса 1-домашний 2-регистрации 3- фактический
CITY	varchar2	Город
STREET	varchar2	Улица
HOUSE	varchar2	Дом
FLAT	varchar2	Квартира
CREATED	date	Дата внесения в базу
ACTIVE	Char(1)	Y/N активный или архив

Отделу маркетинга требуется сводная выгрузка по клиентам, с гранулярностью до клиента, при этом для каждого клиента в выборке должны быть «лучшие»  адрес, телефон и адрес электронной почты. То есть, в результирующей выборке по каждому клиенту есть только одна строка. При этом:

1. Лучший адрес отбирается по приоритету фактический> регистрации> домашний, при наличии нескольких адресов одного приоритета выбирается наиболее полный (заполнено больше из перечня атрибутов city-street-house-flat, при равенстве по заполненности выбирается последний по дате внесения в базу.
2. Лучший телефон это последний по дате внесения в базу
3. Лучший email это первый по дате внесения в базу
4. Данные по контактам и адресам – не архивные

Вариант ответа:

with a as
 (select *
    from (select a.client_id,
                 a.city,
                 a.street,
                 a.house,
                 a.flat,
                 rank() over(partition by a.client_id order by a.a_type asc,case
                   when a.city is not null then
                    1
                   else
                    null
                 end asc nulls last,case
                   when a.street is not null then
                    1
                   else
                    null
                 end asc nulls last,case
                   when a.house is not null then
                    1
                   else
                    null
                 end asc nulls last,case
                   when a.flat is not null then
                    1
                   else
                    null
                 end asc nulls last, a.created desc) as rn,
                 rowid
            from address a
           where a.active = 'Y') a
   where a.rn = 1),
p as
 (select *
    from (select p.client_id,
                 p.c_info,
                 rank() over(partition by p.client_id ORDER BY p.created DESC) as rn
            from contact p
           where p.active = 'Y'
             and p.c_type = 1) p
   where p.rn = 1),
e as
 (select *
    from (select e.client_id,
                 e.c_info,
                 rank() over(partition by e.client_id ORDER BY e.created ASC) as rn
            from contact e
           where e.active = 'Y'
             and e.c_type = 2) e
   where e.rn = 1)
select c.name, a.city, a.street, a.house, a.flat, p.c_info, e.c_info
  from client c
  left join a
    on a.client_id = c.id
  left join p
    on p.client_id = c.id
  left join e
    on e.client_id = c.id;

Tags: Oracle, SQL