отсортировать в порядке убывания «количества дней нулевых продаж идущих подряд»

Жираф Подвальный
Дата: 24.03.2011 11:24:23
Привет всем!
Помогите, пожалуйста. Задача вроде бы на первый взгляд не сложная, но что-то не могу никак допетрить. Чувствую, что надо использовать аналитические функции, но сижу как в басне крылова про мартышку и очки и ничего не получается.
Есть товар - id_wares
Есть даты продаж -date_sale
и сумма продаж за этот день - sum_sale
пара товар - дата уникальная
товар - дата - сумма идет всегда без пропусков, т.е. если нет продаж, то sum_sale = 0
нулевая продажа - сумма продаж за день равная 0, т.е. товар не продавался

"отсортировать в порядке убывания «количества дней нулевых продаж идущих подряд» и «сумме продаж этого товара за предыдущие 4 недели» в порядке убывания."

по сумме, вопросов вроде бы нет, а вот «количества дней нулевых продаж идущих подряд»

внизу пример для наглядности
если кому не тяжело натолкните на мысль
спасибо

drop table test.wares_sale;
create table test.wares_sale (id_wares number, date_sale date, sum_sale number);
create unique index id_wares_date on test.WARES_SALE (id_wares, date_sale);

insert into test.wares_sale (id_wares, date_sale, sum_sale)
values (1, to_date('01.01.2011','DD.MM.YYYY'), 0);                      
insert into test.wares_sale (id_wares, date_sale, sum_sale)
values (2, to_date('01.01.2011','DD.MM.YYYY'), 15.4);
insert into test.wares_sale (id_wares, date_sale, sum_sale)
values (3, to_date('01.01.2011','DD.MM.YYYY'), 15.4);

insert into test.wares_sale (id_wares, date_sale, sum_sale)
values (1, to_date('02.01.2011','DD.MM.YYYY'), 20);
insert into test.wares_sale (id_wares, date_sale, sum_sale)
values (2, to_date('02.01.2011','DD.MM.YYYY'), 10);
insert into test.wares_sale (id_wares, date_sale, sum_sale)
values (3, to_date('02.01.2011','DD.MM.YYYY'), 0);                        

insert into test.wares_sale (id_wares, date_sale, sum_sale)
values (1, to_date('03.01.2011','DD.MM.YYYY'), 0);                        
insert into test.wares_sale (id_wares, date_sale, sum_sale)
values (2, to_date('03.01.2011','DD.MM.YYYY'), 10);
insert into test.wares_sale (id_wares, date_sale, sum_sale)
values (3, to_date('03.01.2011','DD.MM.YYYY'), 0);

insert into test.wares_sale (id_wares, date_sale, sum_sale)
values (1, to_date('04.01.2011','DD.MM.YYYY'), 10);
insert into test.wares_sale (id_wares, date_sale, sum_sale)
values (2, to_date('04.01.2011','DD.MM.YYYY'), 10);
insert into test.wares_sale (id_wares, date_sale, sum_sale)
values (3, to_date('04.01.2011','DD.MM.YYYY'), 0);

insert into test.wares_sale (id_wares, date_sale, sum_sale)
values (1, to_date('05.01.2011','DD.MM.YYYY'), 0);
insert into test.wares_sale (id_wares, date_sale, sum_sale)
values (2, to_date('05.01.2011','DD.MM.YYYY'), 10);
insert into test.wares_sale (id_wares, date_sale, sum_sale)
values (3, to_date('05.01.2011','DD.MM.YYYY'), 10);

insert into test.wares_sale (id_wares, date_sale, sum_sale)
values (1, to_date('06.01.2011','DD.MM.YYYY'), 10);
insert into test.wares_sale (id_wares, date_sale, sum_sale)
values (2, to_date('06.01.2011','DD.MM.YYYY'), 0);
insert into test.wares_sale (id_wares, date_sale, sum_sale)
values (3, to_date('06.01.2011','DD.MM.YYYY'), 10);

select * from test.wares_sale

Elic
Дата: 24.03.2011 11:34:05
Жираф Подвальный
«количества дней нулевых продаж идущих подряд»
STFF start_of_group
Жираф Подвальный
Дата: 24.03.2011 15:49:04
Грусть печаль
все, что пока смог, это определить что между прошлым и предыдущим значением 1 день, т.е. "подряд"
И разбить на группы.
Но что дальше, пока никак...
посмотрел и примеры, но аналога что-то не нашел
чувствую что где-то рядом уже вот вот, но млин ничего не получается
может кто напишет? а? кому это раз плюнуть?
буду дальше бурить...
select id_wares, date_sale, sum_sale, days, 
count(days) OVER (partition by id_Wares, date_sale ORDER BY  id_wares,date_sale ) day_interval
from
(
select id_wares, date_sale, sum_sale, day_interval, day_interval2, least(day_interval, day_interval2) days from
(
select ws.id_wares, ws.date_sale, ws.sum_sale, 
        nvl(date_sale  - lag(date_sale,1)
        OVER (partition by id_Wares, sum_sale ORDER BY  id_wares,date_sale ),1) day_interval,
        nvl(lead(date_sale,1)
        OVER (partition by id_Wares, sum_sale ORDER BY  id_wares,date_sale ) - date_sale,1) day_interval2
from test.wares_sale ws
where sum_sale=0
)
)
Добрый Э - Эх
Дата: 25.03.2011 05:34:09
Вот тебе небольшая "подсказка":
select id_wares, date_sale, sum_sale,
       sum(decode(sum_sale,0,1)) over(partition by id_wares, grp_id, decode(sum_sale,0,1)) as "Кол-во дней подряд"
  from (
         select id_wares, date_sale, sum_sale,
                row_number() over(partition by id_wares, decode(sum_sale,0,0) order by date_sale) -
                row_number() over(partition by id_wares order by date_sale) as grp_id
           from wares_sale
       )
 order by id_wares, date_sale
докури её самостоятельно...
Жираф Подвальный
Дата: 25.03.2011 12:13:37
Добрый Э - Эх
Вот тебе небольшая "подсказка":
select id_wares, date_sale, sum_sale,
       sum(decode(sum_sale,0,1)) over(partition by id_wares, grp_id, decode(sum_sale,0,1)) as "Кол-во дней подряд"
  from (
         select id_wares, date_sale, sum_sale,
                row_number() over(partition by id_wares, decode(sum_sale,0,0) order by date_sale) -
                row_number() over(partition by id_wares order by date_sale) as grp_id
           from wares_sale
       )
 order by id_wares, date_sale
докури её самостоятельно...



не большая подсказка? o_O НЕ БОЛЬШАЯ ПОДСКАЗКА!!?? Фак мой моск!!!!! ))) Врот мне ноги!!!!!!!! Спасибо!!!

СПАСИБО * 9^9999999999 !!!!!!!
Жираф Подвальный
Дата: 25.03.2011 12:20:51
ну а допилил уже так

select id_wares, max(cnt_days)
from
(
select id_wares, date_sale, sum_sale,
       sum(decode(sum_sale,0,1)) over(partition by id_wares, grp_id, decode(sum_sale,0,1)) as cnt_days
  from (
         select id_wares, date_sale, sum_sale,
                row_number() over(partition by id_wares, decode(sum_sale,0,0) order by date_sale) -
                row_number() over(partition by id_wares order by date_sale) as grp_id
           from test.wares_sale
           
       )
 order by id_wares, date_sale
)
where sum_sale=0
group by id_wares

спасибо еще раз Э-Эх!!!