ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Пятничная задачка. Смотрим назад.
20 сообщений из 20, страница 1 из 1
Пятничная задачка. Смотрим назад.
    #39469133
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
Написать запрос, который для каждого adjusted value находит
1) предыдущее original (ordered by id)
2) original со сдвигом, который задается переменной (предыдущее считается со сдвигом 0)
3) число предыдущих original так чтоб их сумма не превышала заданный лимит

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
var shift number
var limit number
exec :shift := 2;

PL/SQL procedure successfully completed.

exec :limit := 2000;

PL/SQL procedure successfully completed.

Код: 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.
with t(id, type, value) as
(select rownum * 10, decode(column_value, 'o', 'original', 'adjusted'), rownum * 100
   from table(sys.odcivarchar2list('o', 'o', 'a', 'o', 'a', 'o', 'o', 'a', 'a', 'o', 'a', 'o', 'a', 'o', 'a')))
select ...
 order by id;

        ID TYPE          VALUE PREV_O_VALUE PREV_SHIFT_N_O_VALUE    COUNT_O
---------- -------- ---------- ------------ -------------------- ----------
        10 original        100
        20 original        200
        30 adjusted        300          200                               2
        40 original        400
        50 adjusted        500          400                  100          3
        60 original        600
        70 original        700
        80 adjusted        800          700                  400          5
        90 adjusted        900          700                  400          5
       100 original       1000
       110 adjusted       1100         1000                  600          2
       120 original       1200
       130 adjusted       1300         1200                  700          1
       140 original       1400
       150 adjusted       1500         1400                 1000          1

15 rows selected.


Соединения и подзапросы не использовать.

Правда мне надо было решить задачу для Impala 2.7, но может здесь кому-нибудь будет интересно.
Первые два столбца считаются достаточно тривиально, третий чуть хитрее.

Никакой закономерности в value а также зависимости value от id нет, это просто тестовые данные. ID уникальное.
...
Рейтинг: 0 / 0
09.06.2017, 08:12
| Ответить | Цитировать | Написать  
Пятничная задачка. Смотрим назад.
    #39469161
ORA__SQL
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ORA__SQL
Участник
dbms_photoshop, пока осилил 1 и 2
 

Код: plsql
1.
2.
case when t.type = 'adjusted' then lag(decode(t.type,'original',t.value) ignore nulls)    over(order by t.id) end,
case when t.type = 'adjusted' then lag(decode(t.type,'original',t.value) ignore nulls, 3) over(order by t.id) end


...
Рейтинг: 0 / 0
09.06.2017, 09:30
| Ответить | Цитировать | Написать  
Пятничная задачка. Смотрим назад.
    #39469178
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
ORA__SQL,

Да, зачетно. В Impala не поддерживается ignore nulls в lag, поэтому у меня было.

Код: plsql
1.
2.
3.
4.
5.
6.
select id, type, value,
       decode(type, 'adjusted', last_value(decode(type, 'original', value) ignore nulls) over(order by id)) prev_o_value,
       decode(type, 'adjusted', last_value(decode(type, 'original', shift_n) ignore nulls) over(order by id)) prev_shift_n_o_value
  from (select t.*, decode(type, 'original', lag(value, 2) over(order by decode(type, 'original', 0, 1), id)) shift_n
          from t) t
 order by id;



Можно обойтись и без last_value ignore nulls, но не было необходимости.
...
Рейтинг: 0 / 0
09.06.2017, 09:51
| Ответить | Цитировать | Написать  
Пятничная задачка. Смотрим назад.
    #39469219
Фотография Ozornitca
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ozornitca
Участник
это же аналитические функции ? для решения ?
...
Рейтинг: 0 / 0
09.06.2017, 10:42
| Ответить | Цитировать | Написать  
Пятничная задачка. Смотрим назад.
    #39470656
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
Что-то не особо энтузиазма.

Открою карты. Для Oracle решается через pattern matching.
Но решение чуть менее эффективное чем для PL/SQL поскольку в последнем случае сложность алгоритма линейная.

Код: 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.
41.
42.
43.
44.
45.
SQL> declare
  2    limit int := 2000;
  3    queue numbers := numbers();
  4    total int := 0;
  5    j     int;
  6  begin
  7    for i in (select * from t order by id) loop
  8      if i.type = 'original' then
  9        queue.extend();
 10        queue(queue.last) := i.value;
 11        total := total + i.value;
 12      elsif i.type = 'adjusted' then
 13        j := queue.first;
 14        while total > limit loop
 15          total := total - queue(j);
 16          queue.delete(j);
 17          j := queue.next(j);
 18        end loop;
 19      end if;
 20      dbms_output.put_line(rpad(i.id, 5, ' ') || rpad(i.type, 10, ' ') ||
 21                           rpad(i.value, 5, ' ') ||
 22                           rpad(case when i.type = 'adjusted' then
 23                                queue.count end,
 24                                5,
 25                                ' '));
 26    end loop;
 27  end;
 28  /
10   original  100
20   original  200
30   adjusted  300  2
40   original  400
50   adjusted  500  3
60   original  600
70   original  700
80   adjusted  800  5
90   adjusted  900  5
100  original  1000
110  adjusted  1100 2
120  original  1200
130  adjusted  1300 1
140  original  1400
150  adjusted  1500 1

PL/SQL procedure successfully completed.


На чистом SQL предлагается решить больше из спортивного интереса (и все-таки этот подход значительно лучше чем self join).
...
Рейтинг: 0 / 0
13.06.2017, 02:21
| Ответить | Цитировать | Написать  
Пятничная задачка. Смотрим назад.
    #39470731
Vint
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Vint
Участник
dbms_photoshop,
так и знал что ты смухлевал через pl\sql))
покажи через pattern matching решение, пожалуйста. у меня опять нет под рукой 12ой, а решение хочется осознать)))
...
Рейтинг: 0 / 0
13.06.2017, 10:41
| Ответить | Цитировать | Написать  
Пятничная задачка. Смотрим назад.
    #39470921
dwp
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dwp
Участник
не использовал этот инструмент, потому было интересно подумать над ним. у меня получилось вот так:
не смог избавиться от склеивание лишних паттернов, через distinct , так как подсчитывается несколько вариантов сумма меньше
заданной.

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
with t(id,type,value) as
 (select rownum * 10,decode(column_value, 'o', 'original', 'adjusted'),rownum * 100
  from table(sys.odcivarchar2list('o','o','a','o','a','o','o','a','a','o','a','o','a','o','a')))
select distinct t.id, max(sc) Keep(dense_Rank last order by sc) Over(Partition by id) c
 from t
 match_recognize(
 order by id
 measures sum(1*decode(pr.type,'original',1,0)) as sc
all rows PER MATCH  
AFTER MATCH SKIP to next row 
 pattern (pr+)
 define pr as ((prev(pr.id) < pr.id) or prev(pr.id) is null) and sum(pr.value*decode(pr.type,'original',1,0)) <= 2000
 ) t
 where t.type = 'adjusted'
 order by t.id



с удовольствием посмотрю на авторское решение
...
Рейтинг: 0 / 0
13.06.2017, 13:26
| Ответить | Цитировать | Написать  
Пятничная задачка. Смотрим назад.
    #39471066
Будто бы вроде как должно бы
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Будто бы вроде как должно бы
Гость
Код: 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.
SQL> with t(id,type,value) as
  2   (select power(2,rownum-1),decode(column_value, 'o', 'original', 'adjusted'),rownum * 100
  3    from table(sys.odcivarchar2list('o','o','a','o','a','o','o','a','a','o','a','o','a','o','a')))
  4  select
  5      t.id,
  6      t.type,
  7      t.value,
  8      case when type = 'adjusted' then
  9              count(decode(type, 'original', id)) over(order by orig_val_runing_sum - current_orig_val range between 2000 preceding and 1 preceding)
 10      end COUNT_O
 11      --,to_bin(sum(id) over(order by orig_val_runing_sum - current_orig_val range between 2000 preceding and 1 preceding))
 12  from
 13  (
 14      select
 15         t.*,
 16         decode(type, 'original', value, 0) current_orig_val,
 17         sum(decode(type, 'original', value)) over (order by id) orig_val_runing_sum
 18      from t
 19  ) t
 20  order by id
 21  /
        ID TYPE          VALUE    COUNT_O
---------- -------- ---------- ----------
         1 original        100 
         2 original        200 
         4 adjusted        300          2
         8 original        400 
        16 adjusted        500          3
        32 original        600 
        64 original        700 
       128 adjusted        800          5
       256 adjusted        900          5
       512 original       1000 
      1024 adjusted       1100          2
      2048 original       1200 
      4096 adjusted       1300          1
      8192 original       1400 
     16384 adjusted       1500          1
15 rows selected
...
Рейтинг: 0 / 0
13.06.2017, 16:48
| Ответить | Цитировать | Написать  
Пятничная задачка. Смотрим назад.
    #39471086
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
Будто бы вроде как должно бы,

Диапазон по нарастающей сумме весьма изящно.

Для импалы пока не допилили window clause как следует
Код: plaintext
AnalysisException: RANGE is only supported with both the lower and upper bounds UNBOUNDED or one UNBOUNDED and the other CURRENT ROW.
а в Оракле возвращает что и положено.
...
Рейтинг: 0 / 0
13.06.2017, 17:29
| Ответить | Цитировать | Написать  
Пятничная задачка. Смотрим назад.
    #39471087
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
Vintтак и знал что ты смухлевал через pl\sqlЯ смухлевал через другой язык программирования потому что надо было решать не для Оракла,
а решение паттерн матчинг просто возникло в голове потому и создал тему.

Вон ниже симпатично решили аналитикой.

Вечером опубликую.
...
Рейтинг: 0 / 0
13.06.2017, 17:33
| Ответить | Цитировать | Написать  
Пятничная задачка. Смотрим назад.
    #39471173
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
Oracle настолько крут, что эта задача могла быть решена еще в конце прошлого века с выходом 8i.

Код: 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.
SQL> select * from v$version where rownum = 1;

BANNER
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production

SQL> select t.id,
  2         t.type,
  3         t.value,
  4         decode(type, 'adjusted', max(decode(type, 'original', value)) over(partition by grp)) prev_o_value,
  5         decode(type, 'adjusted', max(decode(type, 'original', shift_n)) over(partition by grp)) prev_shift_n_o_value,
  6         decode(type, 'adjusted', count(decode(type, 'original', id))
  7                over(order by orig_val_running_sum range between 2000 preceding and 1 preceding)) count_o
  8    from (select t.*,
  9                 sum(decode(type, 'original', value)) over(order by id) - decode(type, 'original', value, 0) orig_val_running_sum,
 10                 decode(type, 'original', lag(value, 2) over(order by decode(type, 'original', 0, 1), id)) shift_n,
 11                 sum(decode(type, 'original', 1)) over(order by id) grp
 12            from t) t
 13   order by id;

        ID TYPE                                VALUE PREV_O_VALUE PREV_SHIFT_N_O_VALUE    COUNT_O
---------- ------------------------------ ---------- ------------ -------------------- ----------
        10 original                              100
        20 original                              200
        30 adjusted                              300          200                               2
        40 original                              400
        50 adjusted                              500          400                  100          3
        60 original                              600
        70 original                              700
        80 adjusted                              800          700                  400          5
        90 adjusted                              900          700                  400          5
       100 original                             1000
       110 adjusted                             1100         1000                  600          2
       120 original                             1200
       130 adjusted                             1300         1200                  700          1
       140 original                             1400
       150 adjusted                             1500         1400                 1000          1

15 rows selected.
...
Рейтинг: 0 / 0
13.06.2017, 21:36
| Ответить | Цитировать | Написать  
Пятничная задачка. Смотрим назад.
    #39471177
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
Возвращаясь к теме pattern matching.

Первые два столбца могут быть посчитаны линейным проходом, когда все строки попадают под единственное совпадение. match_number = 1.
Код: 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.
41.
SQL> with t(id, type, value) as
  2  (select rownum * 10, decode(column_value, 'o', 'original', 'adjusted'), rownum * 100
  3     from table(sys.odcivarchar2list('o', 'o', 'a', 'o', 'a', 'o', 'o', 'a', 'a', 'o', 'a', 'o', 'a', 'o', 'a')))
  4  select *
  5    from t
  6  match_recognize
  7  (
  8    order by id
  9    measures
 10      decode(type, 'adjusted', last(o.value)) as prev_o_value,
 11      decode(type, 'adjusted', last(o.value, 2))
 12        as prev_shift_n_o_value,
 13      classifier() cls,
 14      match_number() match
 15    all rows per match
 16    pattern((a|o)+)
 17    define
 18      a as type = 'adjusted',
 19      o as type = 'original'
 20  ) mr
 21  order by id;

        ID PREV_O_VALUE PREV_SHIFT_N_O_VALUE CLS        MATCH TYPE          VALUE
---------- ------------ -------------------- ----- ---------- -------- ----------
        10                                   O              1 original        100
        20                                   O              1 original        200
        30          200                      A              1 adjusted        300
        40                                   O              1 original        400
        50          400                  100 A              1 adjusted        500
        60                                   O              1 original        600
        70                                   O              1 original        700
        80          700                  400 A              1 adjusted        800
        90          700                  400 A              1 adjusted        900
       100                                   O              1 original       1000
       110         1000                  600 A              1 adjusted       1100
       120                                   O              1 original       1200
       130         1200                  700 A              1 adjusted       1300
       140                                   O              1 original       1400
       150         1400                 1000 A              1 adjusted       1500

15 rows selected.

Предыдущие оригинальные просто смотрятся путем применения функции last к правилу o.

Можно пойти более извилистым путем (и менее эффективным) и строить совпадение для каждой adjusted строки пока не найдем три оригинальных. При таком подходе число совпадений будет равно числу строк.

Код: 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.
41.
42.
43.
44.
45.
SQL> with t(id, type, value) as
  2  (select rownum * 10, decode(column_value, 'o', 'original', 'adjusted'), rownum * 100
  3     from table(sys.odcivarchar2list('o', 'o', 'a', 'o', 'a', 'o', 'o', 'a', 'a', 'o', 'a', 'o', 'a', 'o', 'a')))
  4  select *
  5    from t
  6  match_recognize
  7  (
  8    order by id desc
  9    measures
 10      first(id) as id,
 11      first(type) as type,
 12      first(value) as value,
 13      first(o.value) as prev_o_value,
 14      case when count(o.id) = 3 then last(o.value) end
 15        as prev_shift_n_o_value,
 16      classifier() cls,
 17      match_number() match
 18    one row per match
 19    after match skip to next row
 20    pattern(a+ (a|o)+|dummy)
 21    define
 22      a as type = 'adjusted' and count(o.id) < 3,
 23      o as type = 'original' and count(o.id) <= 3
 24  ) mr
 25  order by id;

        ID TYPE          VALUE PREV_O_VALUE PREV_SHIFT_N_O_VALUE CLS        MATCH
---------- -------- ---------- ------------ -------------------- ----- ----------
        10 original        100                                   DUMMY         15
        20 original        200                                   DUMMY         14
        30 adjusted        300          200                      O             13
        40 original        400                                   DUMMY         12
        50 adjusted        500          400                  100 O             11
        60 original        600                                   DUMMY         10
        70 original        700                                   DUMMY          9
        80 adjusted        800          700                  400 O              8
        90 adjusted        900          700                  400 O              7
       100 original       1000                                   DUMMY          6
       110 adjusted       1100         1000                  600 O              5
       120 original       1200                                   DUMMY          4
       130 adjusted       1300         1200                  700 O              3
       140 original       1400                                   DUMMY          2
       150 adjusted       1500         1400                 1000 O              1

15 rows selected.



Этот же подход можно применить для получения третьего столбца. Только совпадение строим не по фиксированному числу строк, а по достижении необходимой суммы.

Код: 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.
41.
42.
43.
SQL> with t(id, type, value) as
  2  (select rownum * 10, decode(column_value, 'o', 'original', 'adjusted'), rownum * 100
  3     from table(sys.odcivarchar2list('o', 'o', 'a', 'o', 'a', 'o', 'o', 'a', 'a', 'o', 'a', 'o', 'a', 'o', 'a')))
  4  select *
  5    from t
  6  match_recognize
  7  (
  8    order by id desc
  9    measures
 10      first(id) as id,
 11      first(type) as type,
 12      first(value) as value,
 13      count(o.id) count_o,
 14      classifier() cls,
 15      match_number() match
 16    one row per match
 17    after match skip to next row
 18    pattern(a+ (a|o)+|dummy)
 19    define
 20      a as type = 'adjusted' and nvl(sum(o.value),0) < 2000,
 21      o as type = 'original' and sum(o.value) <= 2000
 22  ) mr
 23  order by id;

        ID TYPE          VALUE    COUNT_O CLS        MATCH
---------- -------- ---------- ---------- ----- ----------
        10 original        100          0 DUMMY         15
        20 original        200          0 DUMMY         14
        30 adjusted        300          2 O             13
        40 original        400          0 DUMMY         12
        50 adjusted        500          3 O             11
        60 original        600          0 DUMMY         10
        70 original        700          0 DUMMY          9
        80 adjusted        800          5 O              8
        90 adjusted        900          5 O              7
       100 original       1000          0 DUMMY          6
       110 adjusted       1100          2 O              5
       120 original       1200          0 DUMMY          4
       130 adjusted       1300          1 A              3
       140 original       1400          0 DUMMY          2
       150 adjusted       1500          1 A              1

15 rows selected.
...
Рейтинг: 0 / 0
13.06.2017, 22:01
| Ответить | Цитировать | Написать  
Пятничная задачка. Смотрим назад.
    #39471179
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
dwpне использовал этот инструмент, потому было интересно подумать над ним. у меня получилось вот так:
не смог избавиться от склеивание лишних паттернов, через distinct , так как подсчитывается несколько вариантов сумма меньше
заданной.

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
with t(id,type,value) as
 (select rownum * 10,decode(column_value, 'o', 'original', 'adjusted'),rownum * 100
  from table(sys.odcivarchar2list('o','o','a','o','a','o','o','a','a','o','a','o','a','o','a')))
select distinct t.id, max(sc) Keep(dense_Rank last order by sc) Over(Partition by id) c
 from t
 match_recognize(
 order by id
 measures sum(1*decode(pr.type,'original',1,0)) as sc
all rows PER MATCH  
AFTER MATCH SKIP to next row 
 pattern (pr+)
 define pr as ((prev(pr.id) < pr.id) or prev(pr.id) is null) and sum(pr.value*decode(pr.type,'original',1,0)) <= 2000
 ) t
 where t.type = 'adjusted'
 order by t.id



с удовольствием посмотрю на авторское решениеТут довольно много уточнений.
В выделенном нет необходимости ибо оно всегда верно согласно сортировке.
Вместо all rows per match лучше использовать one row per match и ключевые слова running/final в measures вместо distinct + keep и проч.
...
Рейтинг: 0 / 0
13.06.2017, 22:07
| Ответить | Цитировать | Написать  
Пятничная задачка. Смотрим назад.
    #39471184
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
Ну и про баги. nullif возвращает значение в два раза большее, хотя в 10053 он преобразован в case, который работает корректно.

Код: 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.
41.
42.
43.
44.
SQL> with t(id, type, value) as
  2  (select rownum * 10, decode(column_value, 'o', 'original', 'adjusted'), rownum * 100
  3     from table(sys.odcivarchar2list('o', 'o', 'a', 'o', 'a', 'o', 'o', 'a', 'a', 'o', 'a', 'o', 'a', 'o', 'a')))
  4  select *
  5    from t
  6  match_recognize
  7  (
  8    order by id desc
  9    measures
 10      first(id) as id,
 11      first(type) as type,
 12      first(value) as value,
 13      count(o.id) count_o,
 14      nullif(final count(o.id), 0) count_o1,
 15      decode(final count(o.id), 0, to_number(null), final count(o.id)) count_o2,
 16      CASE COUNT("O"."ID") WHEN 0 THEN NULL ELSE COUNT("O"."ID") END  AS "10053"
 17    one row per match
 18    after match skip to next row
 19    pattern(a+ (a|o)+|dummy)
 20    define
 21      a as type = 'adjusted' and nvl(sum(o.value),0) < 2000,
 22      o as type = 'original' and sum(o.value) <= 2000
 23  ) mr
 24  order by id;

        ID TYPE          VALUE    COUNT_O   COUNT_O1   COUNT_O2      10053
---------- -------- ---------- ---------- ---------- ---------- ----------
        10 original        100          0
        20 original        200          0
        30 adjusted        300          2          4          2          2
        40 original        400          0
        50 adjusted        500          3          6          3          3
        60 original        600          0
        70 original        700          0
        80 adjusted        800          5         10          5          5
        90 adjusted        900          5         10          5          5
       100 original       1000          0
       110 adjusted       1100          2          4          2          2
       120 original       1200          0
       130 adjusted       1300          1          2          1          1
       140 original       1400          0
       150 adjusted       1500          1          2          1          1

15 rows selected.
...
Рейтинг: 0 / 0
13.06.2017, 22:16
| Ответить | Цитировать | Написать  
Пятничная задачка. Смотрим назад.
    #39471484
Vint
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Vint
Участник
dbms_photoshop,
ты написал select ... order by id; - я прочитал как решение одним селектом. поэтому и было интересно.
а магия данных меня не интересует...
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
WITH t(id,
TYPE,
VALUE) AS
 (SELECT  rownum, decode(column_value, 'o', 'original', 'adjusted'), case when mod(rownum,2)=1 then 100 else -10 end
    FROM TABLE(sys.odcivarchar2list('o', 'o', 'a', 'o', 'a', 'o', 'o', 'a', 'a', 'o', 'a', 'o', 'a', 'o', 'a')))
SELECT t.id
      ,t.type
      ,t.value
      ,current_orig_val
      ,orig_val_runing_sum
      ,orig_val_runing_sum - current_orig_val
      ,CASE
         WHEN TYPE = 'adjusted' THEN
          COUNT(decode(TYPE, 'original', id)) over(ORDER BY orig_val_runing_sum - current_orig_val RANGE BETWEEN 2000 preceding AND 1 preceding)
       END COUNT_O
  FROM (SELECT t.*, decode(TYPE, 'original', VALUE, 0) current_orig_val, SUM(decode(TYPE, 'original', VALUE)) over(ORDER BY id) orig_val_runing_sum
          FROM t) t
 ORDER BY id


это типовая задача в кредитных организациях на сумму остатка. и она не решается на чистом скуле даже двойной аналитикой) ну вернее я решения не знаю. за решения на match_recognize спасибо. буду учиться))
...
Рейтинг: 0 / 0
14.06.2017, 13:30
| Ответить | Цитировать | Написать  
Пятничная задачка. Смотрим назад.
    #39471489
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
Vint,

Помимо уникальности ID мне стоило упомянуть положительность value.
...
Рейтинг: 0 / 0
14.06.2017, 13:33
| Ответить | Цитировать | Написать  
Пятничная задачка. Смотрим назад.
    #39471496
Vint
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Vint
Участник
dbms_photoshop,
с постоянно нарастающим итогом эта задача уже не так интересна)
...
Рейтинг: 0 / 0
14.06.2017, 13:38
| Ответить | Цитировать | Написать  
Пятничная задачка. Смотрим назад.
    #39471498
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
Vint,

Как обычно детали решают какой в итоге инструмент использовать. :))
...
Рейтинг: 0 / 0
14.06.2017, 13:42
| Ответить | Цитировать | Написать  
Пятничная задачка. Смотрим назад.
    #39471504
Vint
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Vint
Участник
dbms_photoshop,
ессесно))) главное это как поставлена задача, а то я по привычке искал универсальное решение и вспомнил задачу о показе периодов овердрафта на карте. которую кстати так и не решил на чистом sql. хотя у меня в производстве и нет похожих задач. но чисто академически интересно было порешать.
...
Рейтинг: 0 / 0
14.06.2017, 13:46
| Ответить | Цитировать | Написать  
Пятничная задачка. Смотрим назад.
    #39471519
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
Vint,

Для общего случая (если без match recognize) - self join с предыдущими строками для каждой строки + аналитика, просто по производительности не вариант. :)
...
Рейтинг: 0 / 0
14.06.2017, 14:05
| Ответить | Цитировать | Написать  
20 сообщений из 20, страница 1 из 1
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Пятничная задачка. Смотрим назад.
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=52&msg=39470921&tid=1885758]:
 0ms
get settings:
 6ms
get forum list:
 14ms
check forum access:
 2ms
check topic access:
 2ms
track hit:
 170ms
get topic data:
 10ms
get forum data:
 2ms
get page messages:
 72ms
get tp. blocked users:
 2ms
others: 203ms
total:  483ms
созд. / загр.: 483ms
x
x
Закрыть


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