пятничная задачка

Vladimir Sitnikov
Дата: 02.03.2007 17:28:21
Дано:
namevalue
a1
b1
c2
d2
e5
f6
g9
h12


Нужно для каждой строки вычилить количетсво строк, следующих за ней (текущая строка тоже учитывается), и в которых value отличается от value текущей строки не больше, чем на 1.
value не убывает.

namevaluedesired_result
a14
b13
c22
d21
e52
f61
g91
h121

Т.е. 3 для b получается потому, что за строкой b следуют ровно 2 строки, в которых value отличается от value для b не больше чем на 1.

Решать можно, например, для Oracle 9.

with t as (
  select 'a' name, 1 value, 4 result from dual union all
  select 'b' name, 1 value, 3 result from dual union all
  select 'c' name, 2 value, 2 result from dual union all
  select 'd' name, 2 value, 1 result from dual union all
  select 'e' name, 5 value, 2 result from dual union all
  select 'f' name, 6 value, 1 result from dual union all
  select 'g' name, 9 value, 1 result from dual union all
  select 'h' name, 12 value, 1 result from dual
)
select t.* from t
Elic
Дата: 02.03.2007 17:41:53
Ты ещё не отчитался за неправильность "скучного" стандарного решения", а уже лезешь задачки задавать

Это обыкновенная вариация на тему start_of_group + count по окну до конца группы
dmidek
Дата: 02.03.2007 17:42:29
SQL> with t as (
  2    select 'a' name, 1 value, 4 result from dual union all
  3    select 'b' name, 1 value, 3 result from dual union all
  4    select 'c' name, 2 value, 2 result from dual union all
  5    select 'd' name, 2 value, 1 result from dual union all
  6    select 'e' name, 5 value, 2 result from dual union all
  7    select 'f' name, 6 value, 1 result from dual union all
  8    select 'g' name, 9 value, 1 result from dual union all
  9    select 'h' name, 12 value, 1 result from dual
 10  )
 11  select name,
 12         value,
 13         count(*) over(partition by group_no) - count(*) over(partition by group_no order by name) + 1
 14    from (select name, value, sum(group_no) over(order by name) group_no
 15            from (select name,
 16                         value,
 17                         CASE
 18                           WHEN value - lag(value) over(order by name) <= 1 THEN
 19                            0
 20                           ELSE
 21                            1
 22                         END group_no
 23                    from t))
 24  /

NAME      VALUE COUNT(*)OVER(PARTITIONBYGROUP_
---- ---------- ------------------------------
a             1                              4
b             1                              3
c             2                              2
d             2                              1
e             5                              2
f             6                              1
g             9                              1
h            12                              1

8 rows selected

SQL> 
dmidek
Дата: 02.03.2007 17:44:13
+ order by name
Vladimir Sitnikov
Дата: 02.03.2007 17:48:48
Elic
Ты ещё не отчитался за неправильность "скучного" стандарного решения", а уже лезешь задачки задавать

Это обыкновенная вариация на тему start_of_group + count по окну до конца группы


Да, за сухой бамбуковый лес придётся отчитаться :), но, после того, как решат эту задачу.


Задачу нужно решить без подзапросов
zirex
Дата: 02.03.2007 17:51:11
еще вариант, через декартово произведение

with t as (
  select 'a' name, 1 value, 4 result from dual union all
  select 'b' name, 1 value, 3 result from dual union all
  select 'c' name, 2 value, 2 result from dual union all
  select 'd' name, 2 value, 1 result from dual union all
  select 'e' name, 5 value, 2 result from dual union all
  select 'f' name, 6 value, 1 result from dual union all
  select 'g' name, 9 value, 1 result from dual union all
  select 'h' name, 12 value, 1 result from dual
)


select t.name, t.value, count(1) as result from 
(
       select t.name, t.value, row_number() over (order by name) rn from t
) t,
(
       select t.name, t.value, row_number() over (order by name) rn from t
) t2
where 
      (t.value = t2.value or t.value - 1 = t2.value or t.value + 1 = t2.value)
      and t.rn <= t2.rn
group by t.name, t.value
order by t.name      

   	NAME	VALUE	RESULT
1	a	1	4
2	b	1	3
3	c	2	2
4	d	2	1
5	e	5	2
6	f	6	1
7	g	9	1
8	h	12	1
Elic
Дата: 02.03.2007 18:24:46
Vladimir Sitnikov
Задачу нужно решить без подзапросов
Ну-ну :)
with t as (
  select 'a' name,  1 value from dual union all
  select 'b' name,  1 value from dual union all
  select 'c' name,  2 value from dual union all
  select 'd' name,  2 value from dual union all
  select 'e' name,  5 value from dual union all
  select 'f' name,  6 value from dual union all
  select 'g' name,  9 value from dual union all
  select 'h' name, 12 value from dual
)
select name, value, max(result) as result
  from
  ( select 
        replace(sys_connect_by_path(decode(level, 1, name ), '~'), '~') as name,
        replace(sys_connect_by_path(decode(level, 1, value), '~'), '~') as value,
        length(sys_connect_by_path('', '+')) as result
      from t
      connect by name > prior name and (value - prior value) between 0 and 1
  )
  group by name, value
;

NAME       VALUE             RESULT
---------- ---------- -------------
a          1                      4
b          1                      3
c          2                      2
d          2                      1
e          5                      2
f          6                      1
g          9                      1
h          12                     1

8 rows selected.
Vladimir Sitnikov
Дата: 02.03.2007 18:29:40
Elic
Vladimir Sitnikov
Задачу нужно решить без подзапросов
Ну-ну :)

Да, да. Без подзапросов, без иерархии, без decode и без case. И, разумеется, без pl/sql :-)
zirex
Дата: 02.03.2007 18:30:07
глядя на решение Elic-a, оптимизнул свое :)

with t as (
  select 'a' name, 1 value, 4 result from dual union all
  select 'b' name, 1 value, 3 result from dual union all
  select 'c' name, 2 value, 2 result from dual union all
  select 'd' name, 2 value, 1 result from dual union all
  select 'e' name, 5 value, 2 result from dual union all
  select 'f' name, 6 value, 1 result from dual union all
  select 'g' name, 9 value, 1 result from dual union all
  select 'h' name, 12 value, 1 result from dual
)

select 
       t.name, t.value, count(1) as result from t, t t2
where 
      (t.value = t2.value or t.value - t2.value between 0 and 1)
      and t.name <= t2.name
group by t.name, t.value
order by t.name    

   	NAME	VALUE	RESULT
1	a	1	2
2	b	1	1
3	c	2	2
4	d	2	1
5	e	5	1
6	f	6	1
7	g	9	1
8	h	12	1
zirex
Дата: 02.03.2007 18:34:23
оптимизнул и промахнулся
with t as (
  select 'a' name, 1 value, 4 result from dual union all
  select 'b' name, 1 value, 3 result from dual union all
  select 'c' name, 2 value, 2 result from dual union all
  select 'd' name, 2 value, 1 result from dual union all
  select 'e' name, 5 value, 2 result from dual union all
  select 'f' name, 6 value, 1 result from dual union all
  select 'g' name, 9 value, 1 result from dual union all
  select 'h' name, 12 value, 1 result from dual
)

select 
       t.name, t.value, count(1) as result from t, t t2
where 
      (t.value = t2.value or t2.value - t.value between 0 and 1)
      and t.name <= t2.name
group by t.name, t.value
order by t.name