Группировка "непонятно каким методом"

Nrk
Дата: 18.11.2008 18:33:22
Доброго времени суток.

Помогите решить задачу одним запросом, пусть даже сложным.

Имеется неких ряд чисел. Задача состоит в том, чтобы каждому числу присвоить номер группы (начиная с 1) по следующему принципу:
последовательно (на каждом последующем шаге добавляется следующее в выборке число) считается среднее, как только отношение полученного среднего к предыдущему среднему превышает сколько-то %, получаем новую группу.

Например:
Условие: отклонение не может превышать 25% (точнее должно быть строго меньше).
значение|группа
10, 1
11, 1
12, 1
13, 1
14, 1
15, 1
16, 1
17, 1
18, 1
19, 1
20, 1
60, 2 - пошла 2я группа, т.к. здесь отношение среднего = 25%
61, 2 - отношение среднего - 0,0083 - отсюда среднее считается уже не с начала выборки, а с начала текущей группы
500, 3
501, 3

зы. числа, конечно же, упорядочиваются.

зыы. Очень не хочется писать процедуру, потому как это промежуточный этап: данные сюда будут приходить из другого запроса и дальше тоже где-то могут использоваться для дальнейшей группировки...


select 10 val from dual
union all
select 11 val from dual
union all
select 12 val from dual
union all
select 13 val from dual
union all
select 14 val from dual
union all
select 15 val from dual
union all
select 16 val from dual
union all
select 17 val from dual
union all
select 18 val from dual
union all
select 19 val from dual
union all
select 20 val from dual
union all
select 60 val from dual
union all
select 61 val from dual
union all
select 500 val from dual
union all
select 501 val from dual
andrey_anonymous
Дата: 18.11.2008 20:13:26
Nrk

значение|группа
10, 1
11, 1
12, 1
13, 1
14, 1
15, 1
16, 1
17, 1
18, 1
19, 1
20, 1
60, 2 - пошла 2я группа, т.к. здесь отношение среднего = 25%

По моим подсчетам это 19.07692308/15.66666667 ~= 1.217675941
Ошибка в данных, в описании задачи или у меня в голове?
AK-74U
Дата: 18.11.2008 20:30:03
andrey_anonymous

По моим подсчетам это 19.07692308/15.66666667 ~= 1.217675941

(10+11+12+13+14+15+16+17+18+19+20)/11 = 15
(10+11+12+13+14+15+16+17+18+19+20+60)/12 = 18,75
18,75/15 = 1,25

А вообще, задачка любопытная.
andrey_anonymous
Дата: 18.11.2008 20:34:07
AK-74U
andrey_anonymous

По моим подсчетам это 19.07692308/15.66666667 ~= 1.217675941

(10+11+12+13+14+15+16+17+18+19+20)/11 = 15
(10+11+12+13+14+15+16+17+18+19+20+60)/12 = 18,75
18,75/15 = 1,25

Да, проблема у меня в голове - я ввел элемент 23 :)
Задачка тривиально решается на модельке, на обычной же аналитике - действительно, забавно.
SY
Дата: 18.11.2008 21:01:46
SQL> with t as (
  2  select 10 val from dual
  3  union all
  4  select 11 val from dual
  5  union all
  6  select 12 val from dual
  7  union all
  8  select 13 val from dual
  9  union all
 10  select 14 val from dual
 11  union all
 12  select 15 val from dual
 13  union all
 14  select 16 val from dual
 15  union all
 16  select 17 val from dual
 17  union all
 18  select 18 val from dual
 19  union all
 20  select 19 val from dual
 21  union all
 22  select 20 val from dual
 23  union all
 24  select 60 val from dual
 25  union all
 26  select 61 val from dual
 27  union all
 28  select 500 val from dual
 29  union all
 30  select 501 val from dual
 31  )
 32  select  val,
 33          prev_avg_val,
 34          pct || '%' pct,
 35          sum(start_of_group) over(order by val) grp
 36    from  (
 37           select  val,
 38                   avg_val,
 39                   lag(avg_val) over(order by val) prev_avg_val,
 40                   (avg_val - lag(avg_val) over(order by val)) / lag(avg_val) over(order by val) * 100 pct,
 41                   case
 42                     when avg_val - lag(avg_val) over(order by val) < lag(avg_val) over(order by val) / 4 then 0
 43                     else 1
 44                   end start_of_group
 45             from  (
 46                    select  val,
 47                            avg(val) over(order by val rows between unbounded preceding and current row) avg_val
 48                      from  t
 49                   )
 50          )
 51  /

       VAL PREV_AVG_VAL PCT                                              GRP
---------- ------------ ----------------------------------------- ----------
        10              %                                                  1
        11           10 5%                                                 1
        12         10.5 4.76190476190476190476190476190476190476%          1
        13           11 4.54545454545454545454545454545454545455%          1
        14         11.5 4.34782608695652173913043478260869565217%          1
        15           12 4.16666666666666666666666666666666666667%          1
        16         12.5 4%                                                 1
        17           13 3.84615384615384615384615384615384615385%          1
        18         13.5 3.7037037037037037037037037037037037037%           1
        19           14 3.57142857142857142857142857142857142857%          1
        20         14.5 3.44827586206896551724137931034482758621%          1

       VAL PREV_AVG_VAL PCT                                              GRP
---------- ------------ ----------------------------------------- ----------
        60           15 25%                                                2
        61        18.75 17.3333333333333333333333333333333333333%          2
       500           22 155.194805194805194805194805194805194805%          3
       501   56.1428571 52.8244274809160305343511450381679389313%          4

15 rows selected.

SQL> 
SQL> 
SQL> 
SY
Дата: 18.11.2008 21:03:40
Never mind, missed "отсюда среднее считается уже не с начала выборки, а с начала текущей группы".

SY.
andrey_anonymous
Дата: 18.11.2008 21:07:08
SY
        61        18.75 17.3333333333333333333333333333333333333%          2
       500           22 155.194805194805194805194805194805194805%          3
       501   56.1428571 52.8244274809160305343511450381679389313%          4

Не сходиццо.
Для последней строки даже магия исходных данных и та не сработала ;)
SY
Дата: 18.11.2008 21:25:02
andrey_anonymous
SY
        61        18.75 17.3333333333333333333333333333333333333%          2
       500           22 155.194805194805194805194805194805194805%          3
       501   56.1428571 52.8244274809160305343511450381679389313%          4

Не сходиццо.
Для последней строки даже магия исходных данных и та не сработала ;)


Не сходиццо, since, as I already mentioned, I missed "отсюда среднее считается уже не с начала выборки, а с начала текущей группы". Counting average "с начала выборки" усе сходиццо.

SY.
andrey_anonymous
Дата: 18.11.2008 22:07:18
SY
Counting average "с начала выборки" усе сходиццо.

Отношение текущего среднего к предыдущему среднему элементарно получается и на первом уровне вложенности.
Попонятнее -как
     , avg(val) over (order by val)
     / avg(val) over (order by val rows between unbounded preceding and 1 preceding) X
позабавнее (а если есть номера строк, то и заметно "короче") -
(row_number() over(order by val) -1) 
/nullif((row_number() over(order by val) - val/avg(val) over (order by val)), 0) X2
Nrk
Дата: 18.11.2008 22:15:07
SY, andrey_anonymous

Так мне-то надо с начала группы среднее считать, когда новая группа начинается...
Для моей задачи, 500 и 501 - когда 500 начало группы - это "одно и то же", ну в смысле одна группа...

Собсна, что-то подобное получалось и у меня, а уперся я, когда понял, что в самом нижнем окне надо указать окну, что надо начинать по новой среднее считать, по результатам верхнего запроса. %)

Ну, всё равно спасибо за некоторые просветления - не до конца разбирался в параметрах order by для аналитики...

А есть ещё идеи?..