ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Переписать запрос. Количество уникальных значений с накоплением.
23 сообщений из 23, страница 1 из 1
Переписать запрос. Количество уникальных значений с накоплением.
    #39633444
JDS
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
JDS
Участник
Необходимо для каждой записи исходного набора указать количество уникальных id,
у которых сумма SM накопительным итогом > 0.
Запрос ниже дает верный результат, но может есть вариант проще, короче (искомый результат - id_count).
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
with t as (-- исходные данные
                                select 1 rn, 1 id, 100 sm from dual union all
           select 2 rn, 1 id, -100 sm from dual union all
           select 3 rn, 1 id, 100 sm from dual union all
           select 4 rn, 2 id, 100 sm from dual union all
           select 5 rn, 2 id, 150 sm from dual union all
           select 6 rn, 2 id, -100 sm from dual union all
           select 7 rn, 2 id, -100 sm from dual union all
           select 8 rn, 1 id, -100 sm from dual),
t1 as (select rn, id, sm, sum(sm) over(partition by id order by rn) sm_over
         from t),
t3 as (select t1.rn, t1.id, t1.sm, t2.id t2_id,
              max(t2.sm_over) keep(dense_rank last order by t2.rn) last_sum_ver
         from t1, 
              t1 t2
        where t1.rn >= t2.rn
        group by t1.rn, t1.id, t1.sm, t2.id)
-- результат
select rn, id, sm, 
       count(distinct case when last_sum_ver > 0 then t2_id end) id_count
  from t3
 group by rn, id, sm


Например для записи с rn = 5
видим, что на этот момент у нас накоп. итог sm для id = 1 будет равен 100,
накоп. итог для id = 2 будет равен 250.
Итого для записи с rn = 5 id_count будет равен 2 (2 id с накоп. суммой > 0).
...
Рейтинг: 0 / 0
19.04.2018, 22:57
| Ответить | Цитировать | Написать  
Переписать запрос. Количество уникальных значений с накоплением.
    #39633516
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
JDS,

Ваш запрос правильно работает?
почему для ід8 = 0?

ps
задачка наверняка для match_recognize

.....
stax
...
Рейтинг: 0 / 0
20.04.2018, 08:55
| Ответить | Цитировать | Написать  
Переписать запрос. Количество уникальных значений с накоплением.
    #39633544
JDS
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
JDS
Участник
Stax, для rn=8 id_count=0, т.к. сумма с накоплением по id=1 и id=2 вышла в ноль к этой записи с rn=8, т.е. не осталось id с положительным остатком.
...
Рейтинг: 0 / 0
20.04.2018, 09:33
| Ответить | Цитировать | Написать  
Переписать запрос. Количество уникальных значений с накоплением.
    #39633562
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
JDS,

пока я пойму что надо, наверняка сотни решений предложат

where t1.rn >= t2.rn как-бы намекает на start_of_group

ps
непонима
Например для записи с rn = 8
видим, что на этот момент у нас накоп. итог sm для id = 1 будет равен 0,
накоп. итог для id = 2 будет равен 50.
Итого для записи с rn = 8 id_count будет равен 1 (1 id с накоп. суммой > 0).
.....
stax
...
Рейтинг: 0 / 0
20.04.2018, 09:52
| Ответить | Цитировать | Написать  
Переписать запрос. Количество уникальных значений с накоплением.
    #39633568
JDS
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
JDS
Участник
Stax, точно. Будет 1 (в пятой строке после вставки вместо 100 изменил на 150)
Значит все верно поняли )
...
Рейтинг: 0 / 0
20.04.2018, 10:03
| Ответить | Цитировать | Написать  
Переписать запрос. Количество уникальных значений с накоплением.
    #39633631
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
JDS,

я так понимаю надо сэмитировать
count( distinct (case when sm_over>0 then id end)) over (order by rn)

.....
stax
...
Рейтинг: 0 / 0
20.04.2018, 11:18
| Ответить | Цитировать | Написать  
Переписать запрос. Количество уникальных значений с накоплением.
    #39633641
JDS
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
JDS
Участник
Stax, именно так )
...
Рейтинг: 0 / 0
20.04.2018, 11:30
| Ответить | Цитировать | Написать  
Переписать запрос. Количество уникальных значений с накоплением.
    #39633658
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
JDSStax, именно так )

імхо, надо спецов по match_recognize

Ваш вариант в другой форме записи

Код: 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.
with t as (-- исходные данные
                                select 1 rn, 1 id, 100 sm from dual union all
           select 2 rn, 1 id, -100 sm from dual union all
           select 3 rn, 1 id, 100 sm from dual union all
           select 4 rn, 2 id, 100 sm from dual union all
           select 5 rn, 2 id, 150 sm from dual union all
           select 6 rn, 2 id, -100 sm from dual union all
           select 7 rn, 2 id, -100 sm from dual union all
           select 8 rn, 1 id, -100 sm from dual union all
           select 9 rn, 1 id, 25 sm from dual union all
           select 10 rn, 1 id, -30 sm from dual union all
           select 11 rn, 2 id, -50 sm from dual
)
,t1 as (select rn, id, sm, sum(sm) over(partition by id order by rn) sm_over from t)
select t1.*
,(select
 count(case when max(t2.sm_over) keep(dense_rank last order by t2.rn)>0 then t2.id end ) s
 from t1 t2
 where t2.rn<=t1.rn
 group by t2.id
) id_count
from t1
order by 1
SQL> /

        RN         ID         SM    SM_OVER   ID_COUNT
---------- ---------- ---------- ---------- ----------
         1          1        100        100          1
         2          1       -100          0          0
         3          1        100        100          1
         4          2        100        100          2
         5          2        150        250          2
         6          2       -100        150          2
         7          2       -100         50          2
         8          1       -100          0          1
         9          1         25         25          2
        10          1        -30         -5          1
        11          2        -50          0          0

11 rows selected.



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

.....
stax
...
Рейтинг: 0 / 0
20.04.2018, 11:50
| Ответить | Цитировать | Написать  
Переписать запрос. Количество уникальных значений с накоплением.
    #39633809
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
JDS,

если ид мало (varchar2(4000))
вот такой уродец для примера

Код: 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.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
with t as (-- исходные данные
           select 1 rn, 1 id, 100 sm from dual union all
           select 2 rn, 1 id, -100 sm from dual union all
           select 3 rn, 1 id, 100 sm from dual union all
           select 4 rn, 2 id, 100 sm from dual union all
           select 5 rn, 2 id, 150 sm from dual union all
           select 6 rn, 2 id, -100 sm from dual union all
           select 7 rn, 2 id, -100 sm from dual union all
           select 8 rn, 1 id, -100 sm from dual union all
           select 9 rn, 1 id, 25 sm from dual union all
           select 10 rn, 1 id, -30 sm from dual union all
           select 11 rn, 2 id, -50 sm from dual
)
,t1 as (select rn, id, sm, sum(sm) over(partition by id order by rn) sm_over from t)
,t11  (rn,id,sm,sm_over,id_count,list_id) as (
select rn,id,sm,sm_over
   ,case when sm>0 then 1 else 0 end id_count 
   ,case when sm>0 then ','||id||',' else '' end list_id 
from t1 where rn=1
union all
select t2.rn,t2.id,t2.sm,t2.sm_over
,case when t2.sm_over>0 
   then case when instr(t11.list_id,','||t2.id||',')>0 
              then t11.id_count
              else t11.id_count+1 end 
   else case when instr(t11.list_id,','||t2.id||',')=0 
              then t11.id_count
              else t11.id_count-1 end 
end id_count
,case when t2.sm_over>0 
   then case when instr(t11.list_id,','||t2.id||',')>0 
              then t11.list_id
              else t11.list_id||t2.id||',' end 
   else case when instr(t11.list_id,','||t2.id||',')=0 
              then t11.list_id
              else replace(t11.list_id,t2.id||',') end 
end list_id
from t1 t2,t11 where t2.rn=t11.rn+1)
select *
from t11
order by 1
SQL> /

        RN         ID         SM    SM_OVER   ID_COUNT LIST_ID
---------- ---------- ---------- ---------- ---------- --------------------
         1          1        100        100          1 ,1,
         2          1       -100          0          0 ,
         3          1        100        100          1 ,1,
         4          2        100        100          2 ,1,2,
         5          2        150        250          2 ,1,2,
         6          2       -100        150          2 ,1,2,
         7          2       -100         50          2 ,1,2,
         8          1       -100          0          1 ,2,
         9          1         25         25          2 ,2,1,
        10          1        -30         -5          1 ,2,
        11          2        -50          0          0 ,



ID_COUNT можно считать по запятых в LIST_ID, но уже набрал и жалко было выбрасывать

....
stax
...
Рейтинг: 0 / 0
20.04.2018, 15:12
| Ответить | Цитировать | Написать  
Переписать запрос. Количество уникальных значений с накоплением.
    #39633814
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
Stax
ID_COUNT можно считать по запятых в LIST_ID, но уже набрал и жалко было выбрасывать

....
stax

схалявил

,case when sm>0 then ','||id||',' else ' , ' end list_id

.....
stax
...
Рейтинг: 0 / 0
20.04.2018, 15:16
| Ответить | Цитировать | Написать  
Переписать запрос. Количество уникальных значений с накоплением.
    #39633826
Фотография andrey_anonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andrey_anonymous
Участник
Тестить лень.
Код: 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.
SQL> 
with t as (-- исходные данные
                                select 1 rn, 1 id, 100 sm from dual union all
           select 2 rn, 1 id, -100 sm from dual union all
           select 3 rn, 1 id, 100 sm from dual union all
           select 4 rn, 2 id, 100 sm from dual union all
           select 5 rn, 2 id, 150 sm from dual union all
           select 6 rn, 2 id, -100 sm from dual union all
           select 7 rn, 2 id, -100 sm from dual union all
           select 8 rn, 1 id, -100 sm from dual),
t1 as (select rn, id, sm
            , case
               when 0 >= nvl(sum(sm) over(partition by id order by rn rows between unbounded preceding and 1 preceding),0)
                and 0 < sm + nvl(sum(sm) over(partition by id order by rn rows between unbounded preceding and 1 preceding),0)
                then 1
               when 0 < nvl(sum(sm) over(partition by id order by rn rows between unbounded preceding and 1 preceding),0)
                and 0 >= sm + nvl(sum(sm) over(partition by id order by rn rows between unbounded preceding and 1 preceding),0)
                then -1
              end ss
         from t)
select rn, id, sm, sum(ss) over(order by rn) c
from t1
;
        RN         ID         SM          C
---------- ---------- ---------- ----------
         1          1        100          1
         2          1       -100          0
         3          1        100          1
         4          2        100          2
         5          2        150          2
         6          2       -100          2
         7          2       -100          2
         8          1       -100          1
8 rows selected
...
Рейтинг: 0 / 0
20.04.2018, 15:57
| Ответить | Цитировать | Написать  
Переписать запрос. Количество уникальных значений с накоплением.
    #39633855
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
andrey_anonymous,

імхо, должно работать (переход с -,0 на + и наоборот)

ps
sm + nvl(sum(sm) over(partition by id order by rn rows between unbounded preceding and 1 preceding),0)
==
sum(sm) over(partition by id order by rn)
?

.....
stax
...
Рейтинг: 0 / 0
20.04.2018, 16:29
| Ответить | Цитировать | Написать  
Переписать запрос. Количество уникальных значений с накоплением.
    #39633857
Фотография andrey_anonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andrey_anonymous
Участник
Staxsm + nvl(sum(sm) over(partition by id order by rn rows between unbounded preceding and 1 preceding),0)
==
sum(sm) over(partition by id order by rn)
?
Как бы да, но спецификация окна отличается - а так я даю оракелю шанс не считать окно дважды.
...
Рейтинг: 0 / 0
20.04.2018, 16:32
| Ответить | Цитировать | Написать  
Переписать запрос. Количество уникальных значений с накоплением.
    #39633862
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
andrey_anonymousStaxsm + nvl(sum(sm) over(partition by id order by rn rows between unbounded preceding and 1 preceding),0)
==
sum(sm) over(partition by id order by rn)
?
Как бы да, но спецификация окна отличается - а так я даю оракелю шанс не считать окно дважды.

спасибо, понятно

думаете будет over считать раз, я сильно сомневаюсь

хотя на будущее, мож и так

.....
stax
...
Рейтинг: 0 / 0
20.04.2018, 16:36
| Ответить | Цитировать | Написать  
Переписать запрос. Количество уникальных значений с накоплением.
    #39633872
Фотография andrey_anonymous
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
andrey_anonymous
Участник
Staxдумаете будет over считать раз, я сильно сомневаюсь
Вообще Вы правы, ему по барабану - в конкретном случае считает один раз для окон unbounded preceding-current и unbounded preceding - 1 preceding (по крайней мере, 12с), можно самовыражаться яснее.

Ну и если включить мозг, то можно и подсократить код:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
t1 as (select rn, id, sm
            , case
               when 0 >= sum(sm) over(partition by id order by rn)-sm
                and 0 < sum(sm) over(partition by id order by rn)
                then 1
               when 0 < sum(sm) over(partition by id order by rn)-sm
                and 0 >= sum(sm) over(partition by id order by rn)
                then -1
              end ss
         from t)
select rn, id, sm, sum(ss) over(order by rn) c
from t1;
...
Рейтинг: 0 / 0
20.04.2018, 16:44
| Ответить | Цитировать | Написать  
Переписать запрос. Количество уникальных значений с накоплением.
    #39633886
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
andrey_anonymousНу и если включить мозг, то можно и подсократить код:




.....
stax
...
Рейтинг: 0 / 0
20.04.2018, 16:58
| Ответить | Цитировать | Написать  
Переписать запрос. Количество уникальных значений с накоплением.
    #39634004
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
Staxзадачка наверняка для match_recognizeИмеется в виду отметить выход в плюс и обнуление как сделал Андрей?

Тут загвоздка в том, что все строки должны быть заматчены ибо агрегат, используемый в define clause, вычисляется в рамках каждого match.

Код: 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.
SQL> with t as (-- исходные данные
  2             select 1 rn, 1 id, 100 sm from dual union all
  3             select 2 rn, 1 id, -100 sm from dual union all
  4             select 3 rn, 1 id, 100 sm from dual union all
  5             select 4 rn, 2 id, 100 sm from dual union all
  6             select 5 rn, 2 id, 150 sm from dual union all
  7             select 6 rn, 2 id, -100 sm from dual union all
  8             select 7 rn, 2 id, -100 sm from dual union all
  9             select 8 rn, 1 id, -100 sm from dual)
 10  select *
 11  from t
 12  match_recognize
 13  (
 14    partition by id
 15    order by rn
 16    measures
 17      match_number() match,
 18      classifier() cls
 19    all rows per match
 20    pattern (("+"|"-"|" ")+)
 21    define
 22      "+" as sum(sm) > 0 and sum(sm) - sm <= 0,
 23      "-" as sum(sm) <= 0 and sum(sm) - sm > 0
 24  )
 25  order by rn;

        ID         RN      MATCH CLS         SM
---------- ---------- ---------- --- ----------
         1          1          1 +          100
         1          2          1 -         -100
         1          3          1 +          100
         2          4          1 +          100
         2          5          1            150
         2          6          1           -100
         2          7          1           -100
         1          8          1 -         -100

8 rows selected.



В подобных выкрутасах особого смысла нет ввиду тривиальности решения с аналитикой.
...
Рейтинг: 0 / 0
20.04.2018, 22:16
| Ответить | Цитировать | Написать  
Переписать запрос. Количество уникальных значений с накоплением.
    #39634279
JDS
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
JDS
Участник
Спасибо всем, отличное решение.
match_recognize надо бы отдельно изучать, но он начиная с 12-го работает.
...
Рейтинг: 0 / 0
22.04.2018, 02:06
| Ответить | Цитировать | Написать  
Переписать запрос. Количество уникальных значений с накоплением.
    #39634488
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
dbms_photoshop
В подобных выкрутасах особого смысла нет ввиду тривиальности решения с аналитикой.
спасибо

для меня решение стало тривильным после andrey_anonymous,
хотя я изначально 1 preceding рассматривал, не додул что надо в разрезе partition by id

match_recognize пригодится как пример

.....
stax
...
Рейтинг: 0 / 0
23.04.2018, 08:37
| Ответить | Цитировать | Написать  
Переписать запрос. Количество уникальных значений с накоплением.
    #39634522
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
dbms_photoshop,

вопрос не дописал

для суммирования +-, я так понимаю, из-за partition by id надо в подзапрос, или можно извратится?

.....
stax
...
Рейтинг: 0 / 0
23.04.2018, 09:39
| Ответить | Цитировать | Написать  
Переписать запрос. Количество уникальных значений с накоплением.
    #39634574
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
Staxmatch_recognize пригодится как примерБыло недавно аналогичное Можно ли аналитикой? .
Там match_recognize более уместно (ты там тоже участвовал).
Staxдля суммирования +-, я так понимаю, из-за partition by id надо в подзапрос, или можно извратится?Я не вижу варианта без подзапроса.
...
Рейтинг: 0 / 0
23.04.2018, 11:07
| Ответить | Цитировать | Написать  
Переписать запрос. Количество уникальных значений с накоплением.
    #39634575
Фотография dbms_photoshop
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
dbms_photoshop
Участник
dbms_photoshopStaxдля суммирования +-, я так понимаю, из-за partition by id надо в подзапрос, или можно извратится?Я не вижу варианта без подзапроса.Точнее подзапрос там не нужен, аналитика просто добавляется в select-list.
Но все равно это двухходовочка.
...
Рейтинг: 0 / 0
23.04.2018, 11:13
| Ответить | Цитировать | Написать  
Переписать запрос. Количество уникальных значений с накоплением.
    #39634597
Фотография Stax
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Stax
Участник
dbms_photoshopdbms_photoshopпропущено...
Я не вижу варианта без подзапроса.Точнее подзапрос там не нужен, аналитика просто добавляется в select-list.
Но все равно это двухходовочка.

понятно
питался в measures "просуммировать"

ps
імхо двухходовочка, но без подзапросов
....
stax
...
Рейтинг: 0 / 0
23.04.2018, 11:40
| Ответить | Цитировать | Написать  
23 сообщений из 23, страница 1 из 1
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Переписать запрос. Количество уникальных значений с накоплением.
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=52&msg=39633444&tid=1884111]:
 0ms
get settings:
 7ms
get forum list:
 16ms
check forum access:
 2ms
check topic access:
 2ms
track hit:
 45ms
get topic data:
 8ms
get forum data:
 2ms
get page messages:
 40ms
get tp. blocked users:
 1ms
others: 239ms
total:  362ms
созд. / загр.: 362ms
x
x
Закрыть


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