Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Oracle Новый топик    Ответить
 Dynamically reset running sum  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10041
This was posted on OTN:

user629197

Hi all,

suppose we have TABLE1 with one column DS (dynamical sum) as following:

TABLE1
-----------
DS
-----------
30
40
20
80

We also have TABLE2

ID, VAL
-------
1 10
2 20
3 20
4 10
5 5
6 20
7 20
8 60
9 20

As result we need to group those values in TABLE2. For grouping we go from above. As soon as sum of VAL is >= of current value in TABLE1 we start new group. Now we need to find next group which sum is just greater then NEXT value in TABLE1.

For TABLE1 and TABLE2 results must be the following.

ID, VAL, GRP
----------
1 10 1
2 20 1
3 20 2
4 10 2
5 5 2
6 20 2
7 20 3
8 60 4
9 20 4


Have you any clues how can it be done with sql query (<= 9.2i) without stored procedures, except of small functions for calculations?


I do not know if user629197 and RomaHagen are the same person - post sounds like a next level of Возможно ли решить запросом?. And this is my solution:

SQL> with t1 as (
  2              select 1 grp,30 as ds from dual union all
  3              select 2,40 from dual union all
  4              select 3,20 from dual union all
  5              select 4,80 from dual
  6             ),
  7       t2 as (
  8              select 1 id, 10 val from dual union all
  9              select 2, 20 from dual union all
 10              select 3, 20 from dual union all
 11              select 4, 10 from dual union all
 12              select 5, 5 from dual union all
 13              select 6, 20 from dual union all
 14              select 7, 20 from dual union all
 15              select 8, 60 from dual union all
 16              select 9, 20 from dual
 17             ),
 18       t3 as (
 19              select  id,
 20                      val,
 21                      sum(val) over(order by id) running_sum
 22                from  t2
 23             ),
 24       t4 as (
 25              select  id,
 26                      val,
 27                      running_sum,
 28                      running_sum - val prev_running_sum,
 29                      case
 30                        when val >= ds then id + 1
 31                        else max(id) over(order by running_sum - val range between current row and ds - 1e-38 following) + 1
 32                      end start_of_next_grp,
 33                      grp,
 34                      ds
 35                from  t3,
 36                      t1
 37             ),
 38       t5 as (
 39              select  id,
 40                      val,
 41                      running_sum,
 42                      prev_running_sum,
 43                      start_of_next_grp,
 44                      grp,
 45                      ds
 46                from  t4
 47                start with id = 1
 48                  and grp = 1
 49                connect by id = prior start_of_next_grp
 50                       and grp = prior grp + 1
 51             ),
 52       t6 as (
 53              select  t3.id,
 54                      t3.val,
 55                      t5.grp
 56                from  t3,
 57                      t5
 58                where t3.id >= t5.id
 59                  and t3.id < t5.start_of_next_grp
 60             )
 61  select  id,
 62          val,
 63          grp
 64    from  t6
 65    order by id
 66  /

        ID        VAL        GRP
---------- ---------- ----------
         1         10          1
         2         20          1
         3         20          2
         4         10          2
         5          5          2
         6         20          2
         7         20          3
         8         60          4
         9         20          4

9 rows selected.

SQL> 

SY.
9 апр 08, 01:06    [5521847]     Ответить | Цитировать Сообщить модератору
 Re: Dynamically reset running sum  [new]
Кобанчег
Member

Откуда: Рахів
Сообщений: 837
SY
This was posted on OTN:
user629197
...

I do not know if user629197 and RomaHagen are the same person - post sounds like a next level of Возможно ли решить запросом?. And this is my solution:
...
SY.


Да, user629197 and RomaHagen - это один и тот же человек. Как он здесь честно и признался :)
Хоть мне и не понятно зачем он так настойчиво пытается решить эту задачу на чистом SQL версии не выше 9.2, но пищу для размышлений он подкинул неплохую. Пусть и раширить решение, думаю, особого труда не составляет.

Как бы там ни было, приступим к обсуждению решения.
Основное логическое отличие этой задачки от Возможно ли решить запросом? даже не столько в изменяющейся границе обнуления суммы, а в обнулении по условию >= а не >.
Как я понял "case":
 29                      case
 30                        when val >= ds then id + 1
 31                        else max(id) over(order by running_sum - val range between current row and ds - 1e-38 following) + 1
 32                      end start_of_next_grp,
был обусловлен потерей точности при сравнении.
То есть:
SQL>  with t1 as (
  2                select 1 grp,20 as ds from dual
  3               ),
  4         t2 as (
  5                select 1 id, 10 val from dual union all
  6                select 2, 20 from dual union all
  7                select 3, 20 from dual union all
  8                select 4, 10 from dual union all
  9                select 5, 5 from dual union all
 10                select 6, 20 from dual union all
 11                select 7, 20 from dual union all
 12                select 8, 60 from dual union all
 13                select 9, 20 from dual
 14               ),
 15         t3 as (
 16                select  id,
 17                        val,
 18                        sum(val) over(order by id) running_sum
 19                  from  t2
 20               ),
 21         t4 as (
 22                select  id,
 23                        val,
 24                        running_sum,
 25                        running_sum - val prev_running_sum,
 26                        case
 27                          when val >= ds then id + 1
 28                          else max(id) over(order by running_sum - val range between current row and ds - 1e-38 following) + 1
 29                        end start_of_next_grp,
 30                        max(id) over(order by running_sum - val range between current row and ds - 1e-36 following) + 1 start_36,
 31                        max(id) over(order by running_sum - val range between current row and ds - 1e-38 following) + 1 start_38, 
 32                        grp,
 33                        ds
 34                  from  t3,
 35                        t1
 36               )
 37    select  *
 38      from  t4
 39    order by id
 40  /

        ID        VAL RUNNING_SUM PREV_RUNNING_SUM START_OF_NEXT_GRP   START_36   START_38        GRP         DS
---------- ---------- ----------- ---------------- ----------------- ---------- ---------- ---------- ----------
         1         10          10                0                 3          3          3          1         20
         2         20          30               10                 3          3          3          1         20
         3         20          50               30                 4          4          4          1         20
         4         10          60               50                 7          7          7          1         20
         5          5          65               60                 7          7          7          1         20
         6         20          85               65                 7          7          7          1         20
         7         20         105               85                 8          8          9          1         20
         8         60         165              105                 9          9          9          1         20
         9         20         185              165                10         10         10          1         20

9 rows selected.
Это произошло во-первых т.к. range between current row and ds - 1e-38 following превысил 1e+2, а во-вторых т.к. VAL = ds.
Соответственно фрагмент кода можно изменить так:
 29                      case
 30                        when val = ds then id + 1
 31                        else max(id) over(order by running_sum - val range between current row and ds - 1e-38 following) + 1
 32                      end start_of_next_grp,
Далее можно заметить, что при использовании константы 1e-36 результат получился верный, однако, и для нее при определенном значении PREV_RUNNING_SUM будет выдаваться неверный результат.
Подробнее:
SQL> with vals as
  2  (select 1e+1 val from dual union all
  3  select 1e+2 val from dual union all
  4  select 1e+3 val from dual union all
  5  select 1e+4 val from dual union all
  6  select 1e+5 val from dual union all
  7  select 1e+6 val from dual union all
  8  select 1e+7 val from dual union all
  9  select 1e+8 val from dual union all
 10  select 1e+9 val from dual union all
 11  select 1e+10 val from dual)
 12  select vals.* ,
 13    case when val > val - 1e-38 then 1 else 0 end e38,
 14    case when val > val - 1e-37 then 1 else 0 end e37,
 15    case when val > val - 1e-36 then 1 else 0 end e36,
 16    case when val > val - 1e-35 then 1 else 0 end e35,
 17    case when val > val - 1e-34 then 1 else 0 end e34,
 18    case when val > val - 1e-33 then 1 else 0 end e33,
 19    case when val > val - 1e-32 then 1 else 0 end e32,
 20    case when val > val - 1e-31 then 1 else 0 end e31
 21  from vals
 22  /

       VAL        E38        E37        E36        E35        E34        E33        E32        E31
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
        10          1          1          1          1          1          1          1          1
       100          1          1          1          1          1          1          1          1
      1000          0          0          1          1          1          1          1          1
     10000          0          0          1          1          1          1          1          1
    100000          0          0          0          0          1          1          1          1
   1000000          0          0          0          0          1          1          1          1
  10000000          0          0          0          0          0          0          1          1
 100000000          0          0          0          0          0          0          1          1
1000000000          0          0          0          0          0          0          0          0
1,0000E+10          0          0          0          0          0          0          0          0

10 rows selected.
То есть при увеличения порядка чисел снижается точность сравнения.
Итого, от case можно избавиться, если известно какой порядок чисел будет использоваться и какая точность неоходима.
Например, в запросе, где фигурируют числа с точностью до двух знаков после запятой и PREV_RUNNING_SUM не превышает миллион, с головой хватило бы константы 1e-10.

P.S. А решение действительно хорошо демонстрирует мощь аналитики и иерархических запросов в Oracle.
P.P.S. Может в будущем на форуме приемчик для reset_running_total станет таким же популярным как сейчас start_of_group.
9 апр 08, 06:23    [5521943]     Ответить | Цитировать Сообщить модератору
 Re: Dynamically reset running sum  [new]
Кобанчег
Member

Откуда: Рахів
Сообщений: 837
Кобанчег

P.P.S. Может в будущем на форуме приемчик для reset_running_total станет таким же популярным как сейчас start_of_group.

То есть, хотел сказать reset_running_sum.
9 апр 08, 06:27    [5521945]     Ответить | Цитировать Сообщить модератору
 Re: Dynamically reset running sum  [new]
SY
Member

Откуда: Middlebury, CT USA
Сообщений: 10041
Кобанчег
Как я понял "case":
 29                      case
 30                        when val >= ds then id + 1
 31                        else max(id) over(order by running_sum - val range between current row and ds - 1e-38 following) + 1
 32                      end start_of_next_grp,
был обусловлен потерей точности при сравнении.


CASE itself HE обусловлен потерей точности при сравнении. And I would not call it потерей точности. As you correctly noticed, issue here is в обнулении по условию >= а не >. Unfortunately, windowing does not allow to specify open-ended value ranges. So we are forced to mimic it by subtracting smallest precision number value of 1e-38. Now about the analytic max. Actually, it is wrong, even though it works (could be data magic). What I missed, is that since t4 is a join to all groups, we should calculate max(id) per group. Also, since author was not interested in running sum values, we do not need prev_running_sum. So correct code should be:

with t1 as (
            select 1 grp,30 as ds from dual union all
            select 2,40 from dual union all
            select 3,20 from dual union all
            select 4,80 from dual
           ),
     t2 as (
            select 1 id, 10 val from dual union all
            select 2, 20 from dual union all
            select 3, 20 from dual union all
            select 4, 10 from dual union all
            select 5, 5 from dual union all
            select 6, 20 from dual union all
            select 7, 20 from dual union all
            select 8, 60 from dual union all
            select 9, 20 from dual
           ),
     t3 as (
            select  id,
                    val,
                    sum(val) over(order by id) running_sum
              from  t2
           ),
     t4 as (
            select  id,
                    val,
                    running_sum,
                    case
                      when val >= ds then id + 1
                      else max(id) over(partition by grp order by running_sum - val range between current row and ds - 1e-38 following) + 1
                    end start_of_next_grp,
                    grp,
                    ds
              from  t3,
                    t1
           ),
     t5 as (
            select  id,
                    val,
                    running_sum,
                    start_of_next_grp,
                    grp,
                    ds
              from  t4
              start with id = 1
                and grp = 1
              connect by id = prior start_of_next_grp
                     and grp = prior grp + 1
           ),
     t6 as (
            select  t3.id,
                    t3.val,
                    t5.grp
              from  t3,
                    t5
              where t3.id >= t5.id
                and t3.id < t5.start_of_next_grp
           )
select  id,
        val,
        grp
  from  t6
  order by id
/

SY.
9 апр 08, 16:49    [5525908]     Ответить | Цитировать Сообщить модератору
 Re: Dynamically reset running sum  [new]
Кобанчег
Member

Откуда: Рахів
Сообщений: 837
SY

CASE itself HE обусловлен потерей точности при сравнении. And I would not call it потерей точности. As you correctly noticed, issue here is в обнулении по условию >= а не >. Unfortunately, windowing does not allow to specify open-ended value ranges. So we are forced to mimic it by subtracting smallest precision number value of 1e-38.

Согласен, я не совсем корректно выразился. И причина CASE в том, что в функции поиска максимального можно указывать только закрытый диапазон. Просто я рассуждал на тему, что CASE можно не использовать если известен порядок чисел (порядок не в смысле упорядочености а в смысле размера :)). И никоим образом не хотел сказать, что использование CASE здесь неверно.

SY
Now about the analytic max. Actually, it is wrong, even though it works (could be data magic).

В данной задаче analytic max отрабатывает правильно и без партиционирования т.к. во всех группах наборы значений id, running_sum - val одни и те же.

В общем, думаю, тема себя исчерпала.
SY, было интересно обсуждать, если Вам еще будут встречаться интересные задачки на forums.oracle.com публикуйте здесь, думаю не только у меня не хватает времени на чтение нескольких форумов.
10 апр 08, 11:31    [5528942]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить