VPF::Умный distinct - Форум программистов

MadCoder

Дата 29.4.2009, 18:17 (ссылка)

(нет голосов)

Загрузка ...

Опытный

Профиль
Группа: Участник
Сообщений: 478
Регистрация: 24.8.2007
Где: Москва

Репутация: нет
Всего: 2

Доброго времени суток!

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

Итак, у меня есть таблица:

-------------------------------------------------
id | блок | дата входа | дата выхода |
-------------------------------------------------
01| дом  | 12.01.2009  | 13.01.2009   |
02| дом  | 13.01.2009  | 15.02.2009   |
03|дача | 15.02.2009  | 15.03.2009   |
04|дом   | 15.03.2009  | 16.03.2009   |
05|дом2 | 16.03.2009  | 17.04.2009   |
06|дача | 17.04.2009  | 18.04.2009   |
07|дом   | 18.04.2009  | 29.04.2009   |
-------------------------------------------------

на выходе хочу получить следующее:
-------------------------------------------------
блок       | Дата входа | Дата выхода
-------------------------------------------------
дом        | 12.01.2009  | 15.02.2009    |
дача      | 15.02.2009  | 15.03.2009    |
дом        | 15.03.2009  | 16.03.2009    |
дом2      | 16.03.2009  | 17.04.2009    |
дача      | 17.04.2009  | 18.04.2009    |
дом        | 18.04.2009  | 29.04.2009    |
-------------------------------------------------

т.е. если идет несколько раз подряд одинаковый блок, нужно выгрузить его одной строкой с минимальной датой входа и максимальной датой выхода для этих строк подряд.
Если делать distinct, то выгружается 1 раз для всех строк, group by тоже самое, подскажите, как можно это сделать.

--------------------

Домашняя страничка | Как скопировать настройки | Умный оптимизатор

Gluttton

Дата 29.4.2009, 21:59 (ссылка)

(нет голосов)

Загрузка ...

Начинающий

Профиль
Группа: Завсегдатай
Сообщений: 1170
Регистрация: 28.8.2008
Где: Феодосия

Репутация: 24
Всего: 54

Для таблицы:

user posted image

запрос:

Код


select distinct
    s.block,
    s.date_in,
    s.date_out
from (
    select
        T.id, 
        T."BLOCK",
        case
            when exists (
                select *
                from T as TA
                    where TA.id=T.id-1
                    and TA.block=T.block
            ) or exists
            (
                select *
                from T as TA
                    where TA.id=T.id+1
                    and TA.block=T.block
            )
            then (
                select min(sa.date_in) as date_in
                from (
                    select TB.date_in
                    from T AS TB
                        where not exists (
                            select *
                            from T as TC
                                where TC.block<>TB.block
                                and (
                                    (TC.id>=T.id and TC.id<=TB.id)
                                    or
                                    (TC.id<=T.id and TC.id>=TB.id)
                                )
                        )
                )   as sa
            )
            else T.date_in
        end as date_in,
        case
            when exists (
                select *
                from T as TA
                    where TA.id=T.id-1
                    and TA.block=T.block
            ) or exists
            (
                select *
                from T as TA
                    where TA.id=T.id+1
                    and TA.block=T.block
            )
            then (
                select max(sa.date_out) as date_out
                from (
                    select TB.date_out
                    from T AS TB
                        where not exists (
                            select *
                            from T as TC
                                where TC.block<>TB.block
                                and (
                                    (TC.id>=T.id and TC.id<=TB.id)
                                    or
                                    (TC.id<=T.id and TC.id>=TB.id)
                                )
                        )
                )   as sa
            )
            else T.date_out
        end as date_out
    from T
)   as S
    order by S.date_out

Вернет данные:

user posted image

--------------------

Слава Україні!

Zloxa

Дата 29.4.2009, 23:25 (ссылка)

(нет голосов)

Загрузка ...

Чо?

Профиль
Группа: Завсегдатай
Сообщений: 3473
Регистрация: 12.9.2008

Репутация: 53
Всего: 161

MadCoder, на сколько я помню, у Вас MS SQL, если не ошибаюсь то 2005, а значит аналитические функции хоть както реализованы.
1) Если в исходных данных не магия данных а действительно предыдущая "Дата выхода" равна следующей "дате входа"

Код


with t as (
  select 01 id, 'дом' block, to_date('12.01.2009') date_in, to_date('13.01.2009') date_out from dual
  union all select 02,'дом',to_date('13.01.2009'),to_date('15.02.2009') from dual
  union all select 03,'дача',to_date('15.02.2009'),to_date('15.03.2009') from dual
  union all select 04,'дом',to_date('15.03.2009'),to_date('16.03.2009') from dual
  union all select 05,'дом2',to_date('16.03.2009'),to_date('17.04.2009') from dual
  union all select 06,'дача',to_date('17.04.2009'),to_date('18.04.2009') from dual
  union all select 07,'дом',to_date('18.04.2009'),to_date('29.04.2009') from dual
)
select 
  max(block) block
  ,min(date_in) date_in
  ,max(date_out) date_out
from (
  select t1.*
         ,sum(case when t1.block = t2.block then 0 else 1 end) over (order by t1.date_in) grp_id
  from t t1       
  left join t t2 on t1.date_in=t2.date_out
)
group by grp_id
order by 2

BLOCK DATE_IN                   DATE_OUT                  
----- ------------------------- ------------------------- 
дом   12.01.09                  15.02.09                  
дача  15.02.09                  15.03.09                  
дом   15.03.09                  16.03.09                  
дом2  16.03.09                  17.04.09                  
дача  17.04.09                  18.04.09                  
дом   18.04.09                  29.04.09                  

6 rows selected

2) Если предположение 1 не верно, остается только посчитать ROW_NUMBER в нужном нам порядке и выполнять соединение с предыдущей строкой по нему.

У оракла есть более развитая аналитическая функция, которая нам позволит избежать self join'a, аналога которой в ms sql я не нашел

Код


select 
  max(block) block
  ,min(date_in) date_in
  ,max(date_out) date_out
from (
  select t1.*
         ,sum(start_of_group) over (order by t1.date_in) grp_id
  from (
 
  select t.*
         ,case when block = lag(block) over (order by date_in) then 0 else 1 end start_of_group
        from t
       ) t1       
)
group by grp_id
order by 2

BLOCK DATE_IN                   DATE_OUT                  
----- ------------------------- ------------------------- 
дом   12.01.09                  15.02.09                  
дача  15.02.09                  15.03.09                  
дом   15.03.09                  16.03.09                  
дом2  16.03.09                  17.04.09                  
дача  17.04.09                  18.04.09                  
дом   18.04.09                  29.04.09                  

6 rows selected

Gluttton, Как всегда зачет. ;) Отвык я от синтаксиса без аналитики, с ходу не смог бы бацнуть решения. Завтречка помедитирую над твоим примером. ;)

--------------------

Достоверно известно, что 89% людей доверяют статистике взятой с потолка smile

Gluttton

Дата 30.4.2009, 00:17 (ссылка)

(нет голосов)

Загрузка ...

Начинающий

Профиль
Группа: Завсегдатай
Сообщений: 1170
Регистрация: 28.8.2008
Где: Феодосия

Репутация: 24
Всего: 54

Цитата

Gluttton, Как всегда зачет. ;)

Цитата

с ходу не смог бы бацнуть решения

Да я вот тоже далеко не "с ходу"... smile

Цитата

Завтречка помедитирую над твоим примером. ;)

Ну а, что бы процесс медитации был приятнее я прокоментирую:

Внешний запрос отбрасываем, что бы не мешал.

Код


select distinct
    s.block,
    s.date_in,
    s.date_out
from (
...
...
)   as S
    order by S.date_out

Основная логика зарыта во внутреннем запросе. Суть в следующем: если строка имеет значение поля "блок" отличное от предыдущей и последующей записи, (иными словами отдельно идущая запись), то оставляем её без изменений, а если мы "наткнулись" на группу, то в поле начальной даты мы для всей текущей группы подставляем наименьшее значение этой группы, а в поле даты окончание – максимальное. А внешний запрос, просто это всё дело ужимает.

Несколько искусственно смотриться order by S.date_out в конце внешнего запроса (т.к. построен на предположении "нарастании" дат), его можно заменить например на сортировку по id, при этом distinct заменить на какой-нибудь "агрегат", например min().

Код


    select
        T.id, 
        T."BLOCK",
        case
            when exists (                         -- Если перед
                select *
                from T as TA
                    where TA.id=T.id-1
                    and TA.block=T.block
            ) or exists                           -- или после
            (
                select *
                from T as TA
                    where TA.id=T.id+1            -- есть запись с таким же 
                    and TA.block=T.block          -- полем "блок"
            )
            then (                                    -- то 
                select min(sa.date_in) as date_in     -- вставляем наименьшую дату
                from (
                    select TB.date_in                 -- из тех записей
                    from T AS TB                                                 
                        where not exists (            -- которые не допускают 
                            select *                  -- между собой и текущей записью
                            from T as TC              -- наличие поля "блок" с другим значением.
                                where TC.block<>TB.block            -- Если по русски, то среди группы ограниченной отличными от текущего полями 
                                and (                               -- "блок" выбираем минимальное значение
                                    (TC.id>=T.id and TC.id<=TB.id)  -- А вот тут у меня танци с бубном
                                    or                              -- в оригинале было TC.id between T.id and TB.id
                                    (TC.id<=T.id and TC.id>=TB.id)  -- но работало не корректно, почему, сам не разобрался.
                                )
                        )
                )   as sa
            )
            else T.date_in                                          
        end as date_in,
        case                                                        -- Для даты окончания - аналогично, только ищем не минимум, а максимум.
            when exists (
 ................
                select max(sa.date_out) as date_out
.................
    from T

--------------------

Слава Україні!

Zloxa

Дата 30.4.2009, 09:11 (ссылка)

(нет голосов)

Загрузка ...

Чо?

Профиль
Группа: Завсегдатай
Сообщений: 3473
Регистрация: 12.9.2008

Репутация: 53
Всего: 161

Gluttton, У меня не завелся твой пример. У оракли есть ограничение на глубину вложенности коррелированного подзапроса. Но принцип, кажется, я уловил. Спасибо за пояснения.
Заодно, мне кажется, заложенный тобой принцип я слегка упростил.
Заодно, отвязал его от привязки к отношению TA.id=T.id-1, которое в принципе не может быть жизненным
Однако работать должно только для случая, когда предыдущий date_out = текущий date_in

Код


SQL> with t as (
  2    select 01 id, 'дом' block, to_date('12.01.2009') date_in, to_date('13.01.2009') date_out from dual
  3    union all select 02,'дом',to_date('13.01.2009'),to_date('15.02.2009') from dual
  4    union all select 03,'дача',to_date('15.02.2009'),to_date('15.03.2009') from dual
  5    union all select 04,'дача',to_date('15.03.2009'),to_date('16.03.2009') from dual
  6    union all select 05,'дача',to_date('16.03.2009'),to_date('17.04.2009') from dual
  7    union all select 06,'дача',to_date('17.04.2009'),to_date('18.04.2009') from dual
  8    union all select 07,'дом',to_date('18.04.2009'),to_date('29.04.2009') from dual
  9    union all select 08,'дача',to_date('29.04.2009'),to_date('30.04.2009') from dual
 10    union all select 09,'дача',to_date('30.04.2009'),to_date('01.05.2009') from dual
 11    union all select 10,'дом',to_date('01.05.2009'),to_date('02.05.2009') from dual
 12    union all select 11,'дом',to_date('02.05.2009'),to_date('03.05.2009') from dual
 13  )
 14  select
 15    block
 16    ,nvl(new_date_in,min(date_in)) date_in
 17    ,nvl(new_date_out,max(date_out)) date_out
 18  from
 19    (select
 20     t.*
 21     ,(select  max(date_out) from t t1 where t1.date_in < t.date_out and t1.block != t.block) new_date_in
 22     ,(select  min(date_in) from t t1 where t1.date_in >= t.date_out and t1.block != t.block) new_date_out
 23    from t
 24    order by date_in
 25   )
 26  group by block,new_date_in,new_date_out
 27  order by 2
 28  ;
 
BLOCK DATE_IN     DATE_OUT
----- ----------- -----------
дом   12.01.2009  15.02.2009
дача  15.02.2009  18.04.2009
дом   18.04.2009  29.04.2009
дача  29.04.2009  01.05.2009
дом   01.05.2009  03.05.2009
 
SQL>

Буду рад, если ктонить найдет брешь в предложенном решении.

Это сообщение отредактировал(а) Zloxa - 30.4.2009, 09:13

--------------------

Достоверно известно, что 89% людей доверяют статистике взятой с потолка smile

DimW

Дата 30.4.2009, 09:35 (ссылка)

(нет голосов)

Загрузка ...

Эксперт

Профиль
Группа: Завсегдатай
Сообщений: 1330
Регистрация: 24.2.2005
Где: Орёл

Репутация: 4
Всего: 44

Цитата(Zloxa @ 30.4.2009, 09:11

)

Буду рад, если ктонить найдет брешь в предложенном решении.

Код


with t as (
     select 01 id, 'дом' block, to_date('12.01.2009', 'dd.mm.yyyy') date_in, to_date('13.01.2009', 'dd.mm.yyyy') date_out from dual
     union all select 02,'дом',to_date('13.01.2009', 'dd.mm.yyyy'),to_date('15.02.2009', 'dd.mm.yyyy') from dual
     
     union all select 03,'дом',to_date('01.01.2009', 'dd.mm.yyyy'),to_date('31.12.2009', 'dd.mm.yyyy') from dual -- !!!
     
     union all select 04,'дача',to_date('15.02.2009', 'dd.mm.yyyy'),to_date('15.03.2009', 'dd.mm.yyyy') from dual
     union all select 05,'дача',to_date('15.03.2009', 'dd.mm.yyyy'),to_date('16.03.2009', 'dd.mm.yyyy') from dual
     union all select 06,'дача',to_date('16.03.2009', 'dd.mm.yyyy'),to_date('17.04.2009', 'dd.mm.yyyy') from dual
     union all select 07,'дача',to_date('17.04.2009', 'dd.mm.yyyy'),to_date('18.04.2009', 'dd.mm.yyyy') from dual
     union all select 08,'дом',to_date('18.04.2009', 'dd.mm.yyyy'),to_date('29.04.2009', 'dd.mm.yyyy') from dual
     union all select 09,'дача',to_date('29.04.2009', 'dd.mm.yyyy'),to_date('30.04.2009', 'dd.mm.yyyy') from dual
     union all select 10,'дача',to_date('30.04.2009', 'dd.mm.yyyy'),to_date('01.05.2009', 'dd.mm.yyyy') from dual
     union all select 11,'дом',to_date('01.05.2009', 'dd.mm.yyyy'),to_date('02.05.2009', 'dd.mm.yyyy') from dual
     union all select 12,'дом',to_date('02.05.2009', 'dd.mm.yyyy'),to_date('03.05.2009', 'dd.mm.yyyy') from dual
   )

Zloxa

Дата 30.4.2009, 09:50 (ссылка)

(нет голосов)

Загрузка ...

Чо?

Профиль
Группа: Завсегдатай
Сообщений: 3473
Регистрация: 12.9.2008

Репутация: 53
Всего: 161

DimW,

Цитата(Zloxa @ 30.4.2009, 09:11

)

Однако работать должно только для случая, когда предыдущий date_out = текущий date_in

MadCoder, Прости, походу я ввел тебя в заблуждение. MS SQL не поддерживает выражения order by в предложении over для Aggregate Window Functions, только для Ranking Window Functions :(( Потому накапливающую суму посчитать на нем не удастя

Таки ему еще расти и расти до оракли.

Это сообщение отредактировал(а) Zloxa - 30.4.2009, 09:53

--------------------

Достоверно известно, что 89% людей доверяют статистике взятой с потолка smile

DimW

Дата 30.4.2009, 10:40 (ссылка)

(нет голосов)

Загрузка ...

Эксперт

Профиль
Группа: Завсегдатай
Сообщений: 1330
Регистрация: 24.2.2005
Где: Орёл

Репутация: 4
Всего: 44

Цитата(Zloxa @ 30.4.2009, 09:50

)

DimW,

Цитата(Zloxa @ 30.4.2009, 09:11 )
Однако работать должно только для случая, когда предыдущий date_out = текущий date_in

сори, необратил внимания.

1 Пользователей читают эту тему (1 Гостей и 0 Скрытых Пользователей)
0 Пользователей:
« Предыдущая тема \| Составление SQL-запросов \| Следующая тема »