Гость
Войти | Профиль | Очистить
Действия ...
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Группировка "непонятно каким методом" / 19 сообщений из 19, страница 1 из 1
18.11.2008, 18:33
    #35661481
Nrk
Nrk
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Группировка "непонятно каким методом"
Доброго времени суток.

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

Имеется неких ряд чисел. Задача состоит в том, чтобы каждому числу присвоить номер группы (начиная с 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

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

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


Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
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
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.11.2008, 20:13
    #35661682
andrey_anonymous
andrey_anonymous
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Группировка "непонятно каким методом"
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
Ошибка в данных, в описании задачи или у меня в голове?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.11.2008, 20:30
    #35661703
AK-74U
AK-74U
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Группировка "непонятно каким методом"
andrey_anonymous
По моим подсчетам это 19.07692308/15.66666667 ~= 1.217675941
Код: plaintext
1.
2.
( 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

А вообще, задачка любопытная.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.11.2008, 20:34
    #35661707
andrey_anonymous
andrey_anonymous
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Группировка "непонятно каким методом"
AK-74Uandrey_anonymous
По моим подсчетам это 19.07692308/15.66666667 ~= 1.217675941
Код: plaintext
1.
2.
( 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 :)
Задачка тривиально решается на модельке, на обычной же аналитике - действительно, забавно.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.11.2008, 21:01
    #35661749
SY
SY
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Группировка "непонятно каким методом"
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
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>
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.11.2008, 21:03
    #35661753
SY
SY
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Группировка "непонятно каким методом"
Never mind, missed "отсюда среднее считается уже не с начала выборки, а с начала текущей группы".

SY.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.11.2008, 21:07
    #35661767
andrey_anonymous
andrey_anonymous
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Группировка "непонятно каким методом"
SY
Код: plaintext
1.
2.
3.
         61          18 . 75   17 . 3333333333333333333333333333333333333 %           2 
        500             22   155 . 194805194805194805194805194805194805 %           3 
        501     56 . 1428571   52 . 8244274809160305343511450381679389313 %           4

Не сходиццо.
Для последней строки даже магия исходных данных и та не сработала ;)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.11.2008, 21:25
    #35661785
SY
SY
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Группировка "непонятно каким методом"
andrey_anonymousSY
Код: plaintext
1.
2.
3.
         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.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.11.2008, 22:07
    #35661832
andrey_anonymous
andrey_anonymous
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Группировка "непонятно каким методом"
SY Counting average "с начала выборки" усе сходиццо.
Отношение текущего среднего к предыдущему среднему элементарно получается и на первом уровне вложенности.
Попонятнее -как
Код: plaintext
1.
2.
     , avg(val) over (order by val)
     / avg(val) over (order by val rows between unbounded preceding and  1  preceding) X
позабавнее (а если есть номера строк, то и заметно "короче") -
Код: plaintext
1.
(row_number() over(order by val) - 1 ) 
/nullif((row_number() over(order by val) - val/avg(val) over (order by val)),  0 ) X2
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.11.2008, 22:15
    #35661844
Nrk
Nrk
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Группировка "непонятно каким методом"
SY, andrey_anonymous

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

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

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

А есть ещё идеи?..
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.11.2008, 23:28
    #35661958
andrey_anonymous
andrey_anonymous
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Группировка "непонятно каким методом"
NrkА есть ещё идеи?..
Сервер какой?
На 10g достаточно просто реализуется моделькой.
На аналитике 9i решить - интерес скорее спортивный нежели чем практический (она плохо приспособлена для организации рекурсивных связей), в реальной жизни проще всего pipelined присобачить.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.11.2008, 23:35
    #35661966
xymbo
xymbo
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Группировка "непонятно каким методом"
andrey_anonymousAK-74Uandrey_anonymous
По моим подсчетам это 19.07692308/15.66666667 ~= 1.217675941
Код: plaintext
1.
2.
( 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 :)
Задачка тривиально решается на модельке, на обычной же аналитике - действительно, забавно.
А можете показать решение на model?Хотелось бы поизучать данную фичу.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
18.11.2008, 23:59
    #35661982
Nrk
Nrk
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Группировка "непонятно каким методом"
andrey_anonymousNrkА есть ещё идеи?..
Сервер какой?
На 10g достаточно просто реализуется моделькой.
На аналитике 9i решить - интерес скорее спортивный нежели чем практический (она плохо приспособлена для организации рекурсивных связей), в реальной жизни проще всего pipelined присобачить.

сервер 9ка, про pipelined почитаю...
Спасибо.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.11.2008, 01:27
    #35662043
SY
SY
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Группировка "непонятно каким методом"
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
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       t1 as (
  33              select  row_number() over(order by val) rn,
  34                      val
  35                from  t
  36             )
  37   select  t1.val,
  38           t4.grp
  39     from  t1,
  40           (
  41            select  t3.*,
  42                    level grp
  43              from  (
  44                     select  start_rn,
  45                             min(case when avg_val - prev_avg_val >= prev_avg_val /  4  then end_rn else null end) -  1  end_rn
  46                       from  (
  47                              select  t1_1.rn start_rn,
  48                                      t1_2.rn end_rn,
  49                                      avg(t1_2.val) over(partition by t1_1.rn order by t1_2.rn rows between unbounded preceding and current row) avg_val,
  50                                      avg(t1_2.val) over(partition by t1_1.rn order by t1_2.rn rows between unbounded preceding and  1  preceding) prev_avg_val
  51                                from  t1 t1_1,
  52                                      t1 t1_2
  53                                where t1_2.rn >= t1_1.rn
  54                             ) t2
  55                       group by start_rn
  56                    ) t3
  57              start with start_rn =  1 
  58              connect by start_rn = prior end_rn +  1 
  59           ) t4
  60     where t1.rn between t4.start_rn and nvl(t4.end_rn,t1.rn)
  61     order by t1.rn
  62   /

       VAL        GRP
---------- ----------
         10            1 
         11            1 
         12            1 
         13            1 
         14            1 
         15            1 
         16            1 
         17            1 
         18            1 
         19            1 
         20            1 

       VAL        GRP
---------- ----------
         60            2 
         61            2 
        500            3 
        501            3 

 15  rows selected.

SQL>

Explanations:

t1 - simply numbering rows by ascending val.
t2 - for each row (start_rn) calculating average and pervious average for all rows starting row start_rn through the rest of the table (another word for rows >= start_rn) .
t3 - for each row (start_rn) calculate last consecutive row (end_rn) when average change is < 25%. If all rows are within 25% end_rn is null.
t4 - get first row for each group. Remember, end_rn for each row points us to the last row (or null for last group) in a group that starts with start_rn. This ensures "среднее считается уже не с начала выборки, а с начала текущей группы".


SY.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.11.2008, 09:12
    #35662248
Elic
Elic
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Группировка "непонятно каким методом"
SY
Код: plaintext
1.
2.
3.
SQL> ...
   2   ...
   3   ...
Соломон, почему бы тебе не взять пример, например, с меня и постить исходники в виде, пригодном к copy'n'paste, чтобы каждому желающему потестить не приходилось каждый раз вырезать эту грёбаную нумерацию?!
Не первый же год замужем...
P.S. Не за себя радею, а за дело общее :)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.11.2008, 10:41
    #35662551
Nrk
Nrk
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Группировка "непонятно каким методом"
SY, спасибо

Снимаю шляпу...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.11.2008, 14:57
    #35663473
SY
SY
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Группировка "непонятно каким методом"
Elicвырезать эту грёбаную нумерацию?!
P.S. Не за себя радею, а за дело общее :)

Well, it is like warming up before the game . On a bit more serious note, "I'll take that under advisement":

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
with t as (
            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
         ),
   t1 as (
          select  row_number() over(order by val) rn,
                  val
            from  t
         )
select  t1.val,
        t4.grp
  from  t1,
        (
         select  t3.*,
                 level grp
           from  (
                  select  start_rn,
                          min(case when avg_val - prev_avg_val >= prev_avg_val /  4  then end_rn else null end) -  1  end_rn
                    from  (
                           select  t1_1.rn start_rn,
                                   t1_2.rn end_rn,
                                   avg(t1_2.val) over(partition by t1_1.rn order by t1_2.rn rows between unbounded preceding and current row) avg_val,
                                   avg(t1_2.val) over(partition by t1_1.rn order by t1_2.rn rows between unbounded preceding and  1  preceding) prev_avg_val
                             from  t1 t1_1,
                                   t1 t1_2
                             where t1_2.rn >= t1_1.rn
                          ) t2
                    group by start_rn
                 ) t3
           start with start_rn =  1 
           connect by start_rn = prior end_rn +  1 
        ) t4
  where t1.rn between t4.start_rn and nvl(t4.end_rn,t1.rn)
  order by t1.rn
/

SY.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Период между сообщениями больше года.
21.02.2020, 02:54
    #39929247
Кобанчег
Кобанчег
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Группировка "непонятно каким методом"
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
select *
from t
match_recognize
(
order by val
measures
  match_number() grp
all rows per match
pattern (x+)
define x as
  decode(count(*), 1, 1, avg(val)/((sum(val)-val)/(sum(1)-1))) < 1.25
  /*
  case when last(val,1) is not null
       then avg(val)/((sum(val)-val)/(sum(1)-1))
       else 1
  end < 1.25
  */
  -- nvl2 causes ORA-01476: divisor is equal to zero
  -- nvl2(last(val,1), avg(val)/((sum(val)-val)/(sum(1)-1)), 1) < 1.25
);

       VAL        GRP
---------- ----------
        10          1
        11          1
        12          1
        13          1
        14          1
        15          1
        16          1
        17          1
        18          1
        19          1
        20          1
        60          2
        61          2
       500          3
       501          3

15 rows selected.


Первый элемент в группе можно определить как "last(val,1) is null" - то есть нет предыдущего либо как "count(*) = 1" либо иными способами.

nvl2 вместо case не работает.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
21.02.2020, 02:57
    #39929248
Кобанчег
Кобанчег
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Группировка "непонятно каким методом"
andrey_anonymous
Задачка тривиально решается на модельке
Итеративной?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Группировка "непонятно каким методом" / 19 сообщений из 19, страница 1 из 1
Целевая тема:
Создать новую тему:
Автор:
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 169ms
Закрыть
start [/forum/topic.php?fid=52&tablet=1&tid=1881535]:
 0ms
get settings:
 8ms
get forum list:
 12ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 51ms
get topic data:
 12ms
get forum data:
 2ms
get page messages:
 58ms
get tp. blocked users:
 2ms
others: 16ms
total:  169ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]