очень-очень хитрый аналитический запрос

ShultZ
Дата: 06.11.2008 11:30:20
Абоненту задают вопросы, он отвечает да\нет.
нужно найти максимальное количество подряд идущих ответов "да" от абонента.
таблица вида:
номер вопроса | абонент | ответ (1 -да, 0-нет)
1 a1 1
2 a1 0
3 a1 1
4 a2 1
5 a2 0
6 a2 1
7 a2 1
8 a3 1
9 a3 1
10 a3 1
11 a3 1


Должно получиться:
a1 1 подряд ответ "да"
a2 2 подряд ответ "да"
a3 4 подряд ответ "да"

Сломал мозг =(
Elic
Дата: 06.11.2008 11:34:15
STFF start_of_group
ShultZ
Дата: 06.11.2008 12:06:03
спасибо. Работает. Правда как то сложновато получилось, но ладно

with tab as (
select to_date('30.12.2007', 'dd.mm.yyyy') dt, 'a1' ab, 1 val
from dual union all select to_date('31.12.2007', 'dd.mm.yyyy'), 'a1' ab, 1
from dual union all select to_date('01.01.2008', 'dd.mm.yyyy'), 'a1' ab, 1
from dual union all select to_date('02.01.2008', 'dd.mm.yyyy'), 'a2' ab, 1
from dual union all select to_date('03.01.2008', 'dd.mm.yyyy'), 'a2' ab, 1
from dual union all select to_date('04.01.2008', 'dd.mm.yyyy'), 'a2' ab, 0
from dual union all select to_date('05.01.2008', 'dd.mm.yyyy'), 'a2' ab, 0
from dual
)
select ab, max(yes_answers_count) from
(
SELECT dt,ab, SUM(val) OVER (PARTITION by group_no, ab) yes_answers_count FROM
(
select dt, val,ab, sum(start_of_group) over (order by dt desc) as group_no
from
( select t.*, decode(lag(sign(val)) over(order by dt desc), sign(val), 0, 1) as start_of_group
from tab t
)
)
GROUP BY DT, VAL,GROUP_NO,ab
)
group by ab
den-den
Дата: 06.11.2008 12:49:25
ShultZ
спасибо. Работает. Правда как то сложновато получилось, но ладно

with tab as (
select to_date('30.12.2007', 'dd.mm.yyyy') dt, 'a1' ab, 1 val
from dual union all select to_date('31.12.2007', 'dd.mm.yyyy'), 'a1' ab, 1
from dual union all select to_date('01.01.2008', 'dd.mm.yyyy'), 'a1' ab, 1
from dual union all select to_date('02.01.2008', 'dd.mm.yyyy'), 'a2' ab, 1
from dual union all select to_date('03.01.2008', 'dd.mm.yyyy'), 'a2' ab, 1
from dual union all select to_date('04.01.2008', 'dd.mm.yyyy'), 'a2' ab, 0
from dual union all select to_date('05.01.2008', 'dd.mm.yyyy'), 'a2' ab, 0
from dual
)
select ab, max(yes_answers_count) from
(
SELECT dt,ab, SUM(val) OVER (PARTITION by group_no, ab) yes_answers_count FROM
(
select dt, val,ab, sum(start_of_group) over (order by dt desc) as group_no
from
( select t.*, decode(lag(sign(val)) over(order by dt desc), sign(val), 0, 1) as start_of_group
from tab t
)
)
GROUP BY DT, VAL,GROUP_NO,ab
)
group by ab



а если такая ситуация:

select to_date('06.01.2008', 'dd.mm.yyyy') , 'a3' ab, 1 from dual union all
select to_date('07.01.2008', 'dd.mm.yyyy') , 'a3' ab, 1 from dual union all
select to_date('07.01.2008', 'dd.mm.yyyy') , 'a3' ab, 1 from dual

абонент a3 в один день '07.01.2008' позвонил два раза. То запрос для этого абонента вернет 2, хотя всего для него имеется 3 подряд идущих звонка. Такая ситуация приемлема для данной задачи?
Elic
Дата: 06.11.2008 12:50:57
ShultZ
Правда как то сложновато получилось
sign(val) - перебор :)
И partition by не хватает.
den-den
Дата: 06.11.2008 12:58:52
den-den
ShultZ
спасибо. Работает. Правда как то сложновато получилось, но ладно

with tab as (
select to_date('30.12.2007', 'dd.mm.yyyy') dt, 'a1' ab, 1 val
from dual union all select to_date('31.12.2007', 'dd.mm.yyyy'), 'a1' ab, 1
from dual union all select to_date('01.01.2008', 'dd.mm.yyyy'), 'a1' ab, 1
from dual union all select to_date('02.01.2008', 'dd.mm.yyyy'), 'a2' ab, 1
from dual union all select to_date('03.01.2008', 'dd.mm.yyyy'), 'a2' ab, 1
from dual union all select to_date('04.01.2008', 'dd.mm.yyyy'), 'a2' ab, 0
from dual union all select to_date('05.01.2008', 'dd.mm.yyyy'), 'a2' ab, 0
from dual
)
select ab, max(yes_answers_count) from
(
SELECT dt,ab, SUM(val) OVER (PARTITION by group_no, ab) yes_answers_count FROM
(
select dt, val,ab, sum(start_of_group) over (order by dt desc) as group_no
from
( select t.*, decode(lag(sign(val)) over(order by dt desc), sign(val), 0, 1) as start_of_group
from tab t
)
)
GROUP BY DT, VAL,GROUP_NO,ab
)
group by ab


сорри, не зонка, а ответов


а если такая ситуация:

select to_date('06.01.2008', 'dd.mm.yyyy') , 'a3' ab, 1 from dual union all
select to_date('07.01.2008', 'dd.mm.yyyy') , 'a3' ab, 1 from dual union all
select to_date('07.01.2008', 'dd.mm.yyyy') , 'a3' ab, 1 from dual

абонент a3 в один день '07.01.2008' позвонил два раза. То запрос для этого абонента вернет 2, хотя всего для него имеется 3 подряд идущих звонка. Такая ситуация приемлема для данной задачи?