Re[9]: Помогите с запросом... :))) - Базы данных

Меня порадовало что на простые запросы многие быстро дают ответы. Приведеная ниже задача уже решена, но мне интересно, как вы с ней справитесь? Как оцените?

ЗЫ: На сложность задачи не претендую.

И так.

Есть таблица:
work_date | id_work
___________|_________
..........
01.01.2006 | 10
02.01.2006 | 10
........
........ Даты по порядку, причем id_work может быть null, но не меняться......
.......
04.02.2006 | 20
05.02.2006 | 20
.....
..... Тоже самое .....
.....
03.03.2006 | 50
04.03.2006 | 10
05.03.2006 | 10
.....
.....
.....

Т.о. надо найти все периоды по id_work и вывести в виде:
date_beg | date_end | id_work
___________|____________|________
....... | .... | ...
....... | ..... | ...
01.01.2006 | 02.01.2006 | 10
04.02.2006 | 05.02.2006 | 20
03.03.2006 | 03.03.2006 | 50
04.03.2006 | 05.03.2006 | 10
......
......

Ваши идеи.

Здравствуйте, FunnyRabbit, Вы писали:

Сервер может быть любым, но у меня решение под Oracle.

ЗЫ: Задача не очень сложная, просто интересно, как отреагирует народ.

Здравствуйте, FunnyRabbit, Вы писали:

FR> Приведеная ниже задача уже решена,

Причем неоднократно.

FR>Т.о. надо найти все периоды по id_work и вывести в виде:

Ну, во-первых, эта формулировка не совсем соответствует нарисованному Вами результату. Во-вторых, если отталкиваться от результата, то простейший путь, работающий практически в любой версии Oracle

group by id_work, work_date - rownum

Здравствуйте, Softwarer, Вы писали:

S>Здравствуйте, FunnyRabbit, Вы писали:

FR>> Приведеная ниже задача уже решена,

S>Причем неоднократно.

Я и не говорил, что она оригинальна и сложна.

FR>>Т.о. надо найти все периоды по id_work и вывести в виде:

S>Ну, во-первых, эта формулировка не совсем соответствует нарисованному Вами результату. Во-вторых, если отталкиваться от результата, то простейший путь, работающий практически в любой версии Oracle

S>

group by id_work, work_date - rownum

Может быть формулировка не верна. Но если отталкиваться от результата, то каким макаром должно работать по вашему:

group by id_work, work_date - rownum

У меня выдает ошибку (Error): ORA-00979: выражение не являеться выражением GROUP BY...

Здравствуйте, FunnyRabbit, Вы писали:

FR>Но если отталкиваться от результата, то каким макаром должно работать по вашему:

Правильно

FR>У меня выдает ошибку (Error): ORA-00979: выражение не являеться выражением GROUP BY...

Информация к размышлению: ORA-979 следует переводить примерно так: "неверно написаны выражения в SELECT".

Впрочем, если Вы не поняли основной идеи этой группировки, то просто так написать соответствующий запрос действительно не удастся.

Здравствуйте, Softwarer, Вы писали:

S>Впрочем, если Вы не поняли основной идеи этой группировки, то просто так написать соответствующий запрос действительно не удастся.

Ну так я же просил решения полностью. А в предложеном Вами варианте, ооооочень много вариаций.

Оно?

Здравствуйте, FunnyRabbit, Вы писали:

[...]

FR>Ваши идеи.

попробую — на идеальность решения не претендую:

    create table #work_dates (    work_date    datetime,
                                    id_work        int
                            )

    insert     into #work_dates (work_date, id_work)
    select    '01.02.2006', 10
    union
    select    '02.02.2006', 10
    union
    select    '03.02.2006', 50
    union
    select    '04.02.2006', 20
    union
    select    '05.02.2006', 20
    union
    select    '06.02.2006', 10
    union
    select    '07.02.2006', 10
    union
    select    '08.02.2006', 20
    union
    select    '09.02.2006', 20

    select     * 
    from     #work_dates

    select     innerSelect.work_date,
            case 
                when     innerSelect.id_next_for_single is null and id_next <> id_work then innerSelect.work_date
                else    innerSelect.date_end
            end,
            innerSelect.id_work
    from    (
                select     work_date, id_work,  
                    (    select     top 1 work_date
                        from    #work_dates WDTemp
                        where    WDTemp.work_date > WD.work_date
                    )    date_end,    
                    (    select     top 1 id_work
                        from    #work_dates WDTemp
                        where    WDTemp.work_date > WD.work_date
                    )    id_next,
                    (    select     top 1 WDTemp.id_work
                        from    #work_dates WDTemp
                        where    WDTemp.work_date < WD.work_date and WDTemp.id_work = WD.id_work
                    )    id_next_for_single

                from    #work_dates WD
            )    innerSelect
    where     id_next = id_work or id_next_for_single is null

    drop table #work_dates

Здравствуйте, DuШes, Вы писали:

[...]
вдогонку — полученный результат:

work_date                                                                                                     id_work     
------------------------------------------------------ ------------------------------------------------------ ----------- 
2006-01-02 00:00:00.000                                2006-02-02 00:00:00.000                                10
2006-03-02 00:00:00.000                                2006-03-02 00:00:00.000                                50
2006-04-02 00:00:00.000                                2006-05-02 00:00:00.000                                20
2006-06-02 00:00:00.000                                2006-07-02 00:00:00.000                                10
2006-08-02 00:00:00.000                                2006-09-02 00:00:00.000                                20

Здравствуйте, Hеmul, Вы писали:

H>Оно?

Тема то. Только ответов там нету.

Здравствуйте, DuШes, Вы писали:

DШ>Здравствуйте, FunnyRabbit, Вы писали:

DШ>[...]

Приблизительно оно.

Не проверял. Но красиво.

Здравствуйте, FunnyRabbit, Вы писали:

FR>

Ну так я же просил решения полностью. А в предложеном Вами варианте, ооооочень много вариаций.

Я не очень вижу вариации, но готов преодолеть свою лень

Connected to Oracle9i Enterprise Edition Release 9.2.0.6.0 
Connected as test

SQL> with
  2    work_data as (
  3      select to_date ( '01.01.2006', 'dd.mm.yyyy' ) work_date, 10 id_work from dual union all
  4      select to_date ( '02.01.2006', 'dd.mm.yyyy' ) work_date, 10 id_work from dual union all
  5      select to_date ( '04.02.2006', 'dd.mm.yyyy' ) work_date, 20 id_work from dual union all
  6      select to_date ( '05.02.2006', 'dd.mm.yyyy' ) work_date, 20 id_work from dual union all
  7      select to_date ( '03.03.2006', 'dd.mm.yyyy' ) work_date, 50 id_work from dual union all
  8      select to_date ( '04.03.2006', 'dd.mm.yyyy' ) work_date, 10 id_work from dual union all
  9      select to_date ( '05.03.2006', 'dd.mm.yyyy' ) work_date, 10 id_work from dual ),
 10    prepared as (
 11      select work_date, id_work, work_date - rownum grp_date from work_data order by id_work, work_date )
 12  select
 13    id_work, min ( work_date ), max ( work_date )
 14  from
 15    prepared
 16  group by
 17    id_work, grp_date
 18  order by
 19    2
 20  ;

   ID_WORK MIN(WORK_DATE) MAX(WORK_DATE)
---------- -------------- --------------
        10 01.01.2006     02.01.2006
        20 04.02.2006     05.02.2006
        50 03.03.2006     03.03.2006
        10 04.03.2006     05.03.2006

SQL>

Здравствуйте, Softwarer, Вы писали:

S>Здравствуйте, FunnyRabbit, Вы писали:

Спасибо. Вариаций действительно много, хоть вы их и не видите. Посмотрите здесь и я мог бы дать вам свой вариант, но, если честно, то ваш лучше. Спасибо.
Я пробовал аналитические функции + LEAST, GREATEST.

Здравствуйте, FunnyRabbit, Вы писали:

FR>Спасибо. Вариаций действительно много, хоть вы их и не видите.

Вариаций на тему указанного мной метода группировки. Ту же идею можно выразить через аналитические функции, но это будет менее удачно — поскольку результат потом все равно надо будет сжимать group by-ем. Вариации с другой исходной посылкой, разумеется, есть.

Здравствуйте, Softwarer, Вы писали:

S>Здравствуйте, FunnyRabbit, Вы писали:

FR>>Спасибо. Вариаций действительно много, хоть вы их и не видите.

S>Вариаций на тему указанного мной метода группировки. Ту же идею можно выразить через аналитические функции, но это будет менее удачно — поскольку результат потом все равно надо будет сжимать group by-ем. Вариации с другой исходной посылкой, разумеется, есть.

Согласен, что все сводиться в конечном итоге к группировке. Спасибо за урок. Надеюсь что не последний.

Зацените

declare @dm table( dt datetime, id int )

insert @dm values('2006-01-01', 10)
insert @dm values('2006-01-02', 10)
insert @dm values('2006-02-04', 20)
insert @dm values('2006-02-05', 20)
insert @dm values('2006-02-06', null)
insert @dm values('2006-03-03', 50)
insert @dm values('2006-03-04', 10)
insert @dm values('2006-03-05', 10)
insert @dm values('2006-03-06', 10)

select a.dt,min(b.dt),a.id from @dm a join @dm b on
a.dt <= b.dt and not exists(
    select * from @dm c where ( c.dt=b.dt+1 and isnull(c.id,0)=isnull(b.id,0)) 
    or (c.dt=a.dt-1 and isnull(c.id,0)=isnull(a.id,0))
)
group by a.dt, a.id
order by 1,2

Здравствуйте, Hеmul, Вы писали:

H>Зацените

H>

H>declare @dm table( dt datetime, id int )

H>insert @dm values('2006-01-01', 10)
H>insert @dm values('2006-01-02', 10)
H>insert @dm values('2006-02-04', 20)
H>insert @dm values('2006-02-05', 20)
H>insert @dm values('2006-02-06', null)
H>insert @dm values('2006-03-03', 50)
H>insert @dm values('2006-03-04', 10)
H>insert @dm values('2006-03-05', 10)
H>insert @dm values('2006-03-06', 10)

H>select a.dt,min(b.dt),a.id from @dm a join @dm b on
H>a.dt <= b.dt and not exists(
H>    select * from @dm c where ( c.dt=b.dt+1 and isnull(c.id,0)=isnull(b.id,0)) 
H>    or (c.dt=a.dt-1 and isnull(c.id,0)=isnull(a.id,0))
H>)
H>group by a.dt, a.id
H>order by 1,2
H>

ну план выполнения получше чем мой вариант, так что

Здравствуйте, Hеmul, Вы писали:

H>Зацените

H>

H>declare @dm table( dt datetime, id int )

H>select a.dt,min(b.dt),a.id from @dm a join @dm b on
H>a.dt <= b.dt and not exists(
H>    select * from @dm c where ( c.dt=b.dt+1 and isnull(c.id,0)=isnull(b.id,0)) 
H>    or (c.dt=a.dt-1 and isnull(c.id,0)=isnull(a.id,0))
H>)
H>group by a.dt, a.id
H>order by 1,2
H>

Поднимаю старый вопрос, так как нужна помощь.
При таких данных

insert @dm values('2006-08-16',    22)
insert @dm values('2006-09-04',    20)
insert @dm values('2006-09-05',    18)
insert @dm values('2006-09-05',    19)
insert @dm values('2006-09-05',    20)
insert @dm values('2006-09-05',    21)
insert @dm values('2006-09-06',    18)
insert @dm values('2006-09-06',    19)
insert @dm values('2006-09-06',    20)
insert @dm values('2006-09-06',    21)
insert @dm values('2006-09-07',    18)
insert @dm values('2006-09-07',    19)
insert @dm values('2006-09-07',    20)
insert @dm values('2006-09-07',    21)
insert @dm values('2006-09-08',    18)
insert @dm values('2006-09-08',    19)
insert @dm values('2006-09-08',    20)
insert @dm values('2006-09-08',    21)
insert @dm values('2006-09-11',    18)
insert @dm values('2006-09-11',    20)
insert @dm values('2006-09-11',    21)
insert @dm values('2006-09-12',    20)
insert @dm values('2006-09-12',    21)
insert @dm values('2006-09-13',    21)
insert @dm values('2006-09-14',    21)
insert @dm values('2006-09-15',    21)

результат не правильный, т.е. ничего после 2006-09-11 не выводится

Результат


2006-08-16 00:00:00.000    2006-08-16 00:00:00.000    22
2006-09-04 00:00:00.000    2006-09-08 00:00:00.000    20
2006-09-05 00:00:00.000    2006-09-08 00:00:00.000    18
2006-09-05 00:00:00.000    2006-09-08 00:00:00.000    19
2006-09-05 00:00:00.000    2006-09-08 00:00:00.000    21
2006-09-11 00:00:00.000    2006-09-11 00:00:00.000    21
2006-09-11 00:00:00.000    2006-09-11 00:00:00.000    18
2006-09-11 00:00:00.000    2006-09-11 00:00:00.000    20

Any ideas?

Спасибо

Здравствуйте, Holms, Вы писали:

H>результат не правильный, т.е. ничего после 2006-09-11 не выводится

H>Any ideas?

А самому разобраться? Ошибка же очевидная...

select a.dt,min(b.dt),a.id from @dm a join @dm b on
a.dt <= b.dt and a.id=b.id and not exists(
    select * from @dm c where ( c.dt=b.dt+1 and c.id=b.id ) 
    or (c.dt=a.dt-1 and c.id=a.id)
)
group by a.dt, a.id
order by 1,2

Здравствуйте, Хемуль, Вы писали:

Х>А самому разобраться? Ошибка же очевидная...

Спасибо

А насчет очевидно или нет...

Здравствуйте, FunnyRabbit, Вы писали:

S>>Вариаций на тему указанного мной метода группировки. Ту же идею можно выразить через аналитические функции, но это будет менее удачно — поскольку результат потом все равно надо будет сжимать group by-ем. Вариации с другой исходной посылкой, разумеется, есть.

FR>Согласен, что все сводиться в конечном итоге к группировке. Спасибо за урок. Надеюсь что не последний.

Можно и без группировок.


SQL> create table work_data as
  2  (
  3        select to_date ( '01.01.2006', 'dd.mm.yyyy' ) work_date, 10 id_work from dual union all
  4        select to_date ( '02.01.2006', 'dd.mm.yyyy' ) work_date, 10 id_work from dual union all
  5        select to_date ( '03.02.2006', 'dd.mm.yyyy' ) work_date, 20 id_work from dual union all
  6        select to_date ( '04.02.2006', 'dd.mm.yyyy' ) work_date, 20 id_work from dual union all
  7        select to_date ( '05.02.2006', 'dd.mm.yyyy' ) work_date, 20 id_work from dual union all
  8        select to_date ( '03.03.2006', 'dd.mm.yyyy' ) work_date, 50 id_work from dual union all
  9        select to_date ( '04.03.2006', 'dd.mm.yyyy' ) work_date, 10 id_work from dual union all
 10        select to_date ( '05.03.2006', 'dd.mm.yyyy' ) work_date, 10 id_work from dual
 11  );

Table created.

SQL>
SQL> select date_beg, date_end, id_work
  2    from (select wrn, id_work,
  3                 work_date date_beg,
  4                 lead(work_date, 1, work_date) over(partition by id_work order by id_work, work_date) date_end,
  5                 row_number() over(partition by id_work order by id_work, work_date) irn
  6            from (select wd.*, rownum wrn from work_data wd))
  7   where mod(irn, 2) = 1
  8   order by wrn;

DATE_BEG   DATE_END      ID_WORK
---------- ---------- ----------
01.01.2006 02.01.2006         10
03.02.2006 04.02.2006         20
05.02.2006 05.02.2006         20
03.03.2006 03.03.2006         50
04.03.2006 05.03.2006         10

SQL>
SQL> drop table  work_data;

Table dropped.

Здравствуйте, Diego, Вы писали:

D>Можно и без группировок.

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

SQL>> create table work_data as
....
SQL>> drop table  work_data;

Для таких целей во всех смыслах лучше использовать конструкцию WITH либо подзапрос во from.

	От:	FunnyRabbit
	Дата:	13.06.06 09:37
	Оценка:

	От:	FunnyRabbit
	Дата:	13.06.06 09:39
	Оценка:

От:	Softwarer	http://softwarer.ru
Дата:	13.06.06 09:44
Оценка:

	От:	FunnyRabbit
	Дата:	13.06.06 10:07
	Оценка:

От:	Softwarer	http://softwarer.ru
Дата:	13.06.06 10:15
Оценка:

	От:	Хемуль
	Дата:	05.09.06 07:01
	Оценка:	6 (1)

	От:	DuШes
	Дата:	14.06.06 07:57
	Оценка:

	От:	Holms
	Дата:	04.09.06 21:42
	Оценка:

	От:	Diego
	Дата:	13.09.06 15:58
	Оценка: