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

Откуда: СПб
Сообщений: 2965
Имеется таблица t тремя nullable столбцами a, b, c типа number. Требуется заменить в запросе:
select a, b, c, (select sum(c) from t t1 where t1.b = t.a) s from t
скалярный подзапрос на использование оконных функций, так чтобы результат остался прежним.

Пример данных

with t as
(
  select 1 a, 1 b, 1 c from dual union all
  select 14 a, 1 b, 2 c from dual union all
  select 4 a, 3 b, 3 c from dual union all
  select null a, 4 b, 4 c from dual union all
  select 3 a, 15 b, 5 c from dual union all
  select 9 a, 1 b, 6 c from dual union all
  select 2 a, 13 b, 7 c from dual union all
  select 13 a, null b, 8 c from dual union all
  select 3 a, 12 b, 9 c from dual union all
  select 1a, 11 b, 10 c from dual union all
  select 12 a, 3 b, 11 c from dual union all
  select 5 a, 12 b, null c from dual union all
  select 8 a, 8 b, 13 c from dual union all
  select 3 a, 3 b, 14 c from dual union all
  select 11 a, 12 b, 15 c from dual
)
select a, b, c, (select sum(c) from t t1 where t1.b = t.a) s from t

Решение несложное :)
12 дек 10, 00:44    [9923928]     Ответить | Цитировать Сообщить модератору
 Re: Пятничная задачка - оконные функции  [new]
august_petrovich
Member

Откуда:
Сообщений: 577
_Nikotin,

для какой версии оракла?
12 дек 10, 00:50    [9923944]     Ответить | Цитировать Сообщить модератору
 Re: Пятничная задачка - оконные функции  [new]
_Nikotin
Member

Откуда: СПб
Сообщений: 2965
august_petrovich,

хм, думаю и в 8-ке будет работать
12 дек 10, 00:52    [9923948]     Ответить | Цитировать Сообщить модератору
 Re: Пятничная задачка - оконные функции  [new]
_Nikotin
Member

Откуда: СПб
Сообщений: 2965
вот этого хватает
12 дек 10, 00:58    [9923955]     Ответить | Цитировать Сообщить модератору
 Re: Пятничная задачка - оконные функции  [new]
Maxim Demenko
Member

Откуда: Munich, Germany
Сообщений: 940
_Nikotin,

Было уже вообщето.
+
case
         when a > nvl(b, 0) then
          sum(c)
          over(order by nvl(b, 0)
               range between abs(a - nvl(b, 0))
               following and abs(a - nvl(b, 0)) following)
         else
          sum(c)
          over(order by nvl(b, 0)
               range between abs(nvl(b, 0) - a)
               preceding and abs(nvl(b, 0) - a) preceding)
end


Best regards

Maxim
12 дек 10, 01:28    [9924026]     Ответить | Цитировать Сообщить модератору
 Re: Пятничная задачка - оконные функции  [new]
_Nikotin
Member

Откуда: СПб
Сообщений: 2965
Maxim Demenko,

Спасибо, практически дословно:
case when a > nvl(b,666) then
         sum(c)over(order by nvl(b,666) range between greatest(a - nvl(b,666), 0) following and greatest(a - nvl(b,666), 0) following)
       else
         sum(c)over(order by nvl(b,666) range between greatest(nvl(b,666) - a, 0) preceding and greatest(nvl(b,666) - a, 0) preceding)
       end s2
Искал по ключевым словам self join window functions, не нашёл :(
12 дек 10, 08:27    [9924307]     Ответить | Цитировать Сообщить модератору
 Re: Пятничная задачка - оконные функции  [new]
xtender
Member

Откуда: Мск
Сообщений: 5704
Моделькой делается гораздо проще:
+
select *
from ( 
       select
         a, b, c, 
         (select sum(c) from t t1 where t1.b = t.a) s
       from t
     )
model
     dimension by (a,b)
     measures(c,s,0 s2)
     rules(
        s2[any,any]=sum(c)[any,cv(a)]
     )
12 дек 10, 09:10    [9924339]     Ответить | Цитировать Сообщить модератору
 Re: Пятничная задачка - оконные функции  [new]
_Nikotin
Member

Откуда: СПб
Сообщений: 2965
xtender,

ORA-32638: Non unique addressing in MODEL dimensions
12 дек 10, 09:16    [9924342]     Ответить | Цитировать Сообщить модератору
 Re: Пятничная задачка - оконные функции  [new]
xtender
Member

Откуда: Мск
Сообщений: 5704
_Nikotin, это потому что я счел что а и б уже агрегированные, если нет - то надо добавить любое уникальное измерение. Заодно приведу пример, если все таки не надо считать суммы где а=нулл:
+
select *
from ( 
       select
         a, b, c, 
         (select sum(c) from t t1 where t1.b = t.a) s
       from t
     )
model
     dimension by (rownum n,a,b)
     measures(c,s,cast(null as number) s2)
     rules(
        s2[any,a is not null,any]=sum(c)[any,any,cv(a)]
     )
12 дек 10, 09:21    [9924343]     Ответить | Цитировать Сообщить модератору
 Re: Пятничная задачка - оконные функции  [new]
_Nikotin
Member

Откуда: СПб
Сообщений: 2965
Изменим немного задачу. Теперь требуется получить такой же результат, но используя только возможности connect by.
+ решение
with t as
(
  select 1 a, 1 b, 1 c from dual union all
  select 14 a, 1 b, 2 c from dual union all
  select 4 a, 3 b, 3 c from dual union all
  select null a, 4 b, 4 c from dual union all
  select 3 a, 15 b, 5 c from dual union all
  select 9 a, 1 b, 6 c from dual union all
  select 2 a, 13 b, 7 c from dual union all
  select 13 a, null b, 8 c from dual union all
  select 3 a, 12 b, 9 c from dual union all
  select 1a, 11 b, 10 c from dual union all
  select 12 a, 3 b, 11 c from dual union all
  select 5 a, 12 b, null c from dual union all
  select 8 a, 8 b, 13 c from dual union all
  select 3 a, 3 b, 14 c from dual union all
  select 11 a, 12 b, 15 c from dual
)
select a,b,c,s 
from
(
  select connect_by_root a a, connect_by_root b b, connect_by_root c c, s, connect_by_isleaf il
  from
  (
    select level l, max(connect_by_root a) ra,
           max(a) a, max(b) b, max(c) c, max(r) r, 
           decode(level,2,sum(c)/count(distinct connect_by_root r)) s
    from (select a,b,c,rownum r from t)
    connect by level <= 2 and b = prior a
        and prior dbms_random.value is not null 
    group by level, decode(level,1,r,2,connect_by_root a)
  ) 
  start with l = 1
  connect by l = 2 and prior l = 1 and prior a = ra
)
where il = 1
12 дек 10, 12:27    [9924636]     Ответить | Цитировать Сообщить модератору
 Re: Пятничная задачка - оконные функции  [new]
dbms_photoshop
Member

Откуда: sqlmdx.net
Сообщений: 5151
_Nikotin
Решение несложное :)
Да, для меня самой тонкостью в решении явл. то, что обе ветки case выполняются независимо от условия. :) Но недавно это обсуждалось относительно агрегатов, в этом же случае аналитика.
_Nikotin
Изменим немного задачу. Теперь требуется получить такой же результат, но используя только возможности connect by.
Опиши словами что ты делал в своем решении. Сходу непонятно зачем там так много всего накручено.
SELECT a,
       b,
       c,
       (SELECT SUM(c) FROM t t1 WHERE t1.b = t.a) s,
       (SELECT SUM(c)
          FROM t t1
         START WITH t1.b = t.a
        CONNECT BY t1.b = PRIOR t1.b AND
                   LEVEL <= 1) s1,
       -- и немного "версионно-зависимого" бреда
       (SELECT SUM(c)
          FROM t t1
         START WITH t1.b = t.a
        CONNECT BY t1.b = PRIOR t1.b AND
                   t1.b = connect_by_root t1.b) s2,
       (SELECT SUM(c)
          FROM t t1
         START WITH t1.b = t.a
        CONNECT BY t1.b = PRIOR t1.b AND
                   dbms_random.VALUE IS NULL) s3
  FROM t
14 дек 10, 15:10    [9935876]     Ответить | Цитировать Сообщить модератору
 Re: Пятничная задачка - оконные функции  [new]
_Nikotin
Member

Откуда: СПб
Сообщений: 2965
dbms_photoshop
Опиши словами что ты делал в своем решении. Сходу непонятно зачем там так много всего накручено.

У меня смыл был чтобы только 1 раз использовать FROM t.
+
select a,b,c,s 
from
(
  select connect_by_root a a, connect_by_root b b, connect_by_root c c, s, 
         connect_by_isleaf il                                         -- 10.не для всякого "a" будут суммы, но такие "a" будут листовыми, 
                                                                      --    как и те для который надутся суммы
  from
  (
    select level l, 
           max(connect_by_root a) ra,                                 -- 5. важно для L=2, тут будут значения "a" для которых посчитаны суммы в 7.
           max(a) a, max(b) b, max(c) c, max(r) r,                    -- 6. важно для L=1, собственно сами a,b,c,r   
           decode(level,2,sum(c)/count(distinct connect_by_root r)) s -- 7. так как "a" неуникальный, то сумма будет посчитана для 
                                                                      --    каждой строки с одинаковым "a", надо поделить на их количество
    from (select a,b,c,rownum r from t)                               -- 1. r=rownum чтобы можно было идентифицировать строки, 
                                                                      --    так как нет уникальности
    connect by level <= 2 and b = prior a                             -- 2. идём по "дереву", аналог t t1 join t t2 on t1.a = t2.b
        and prior dbms_random.value is not null                       -- 3. защита от циклов (a=b)
    group by level, decode(level,1,r,2,connect_by_root a)             -- 4. группируем по уровню L, на L=1 будут исходные строки,
                                                                      --    на L=2 группируем по "a"
  ) 
  start with l = 1                                                    -- 8. начинаем с L=1, аналог left join
  connect by l = 2 and prior l = 1 and prior a = ra                   -- 9. присоединяем подсчитанные суммы, там где они есть
)
where il = 1
14 дек 10, 15:36    [9936135]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: Пятничная задачка - оконные функции  [new]
SvLite
Member

Откуда:
Сообщений: 5
Предлагаю попробовать решить исходную задачу, но не по всей таблице, а с произвольным ограничением:
select a, b, c, (select sum(c) from t t1 where t1.b = t.a) s from t where a>12

требуется написать запрос с одним чтением таблицы t исходные данные те же.
16 янв 12, 11:50    [11909562]     Ответить | Цитировать Сообщить модератору
 Re: Пятничная задачка - оконные функции  [new]
xtender
Member

Откуда: Мск
Сообщений: 5704
SvLite,

select a, b, c, sum(c) over(partition by b) s from t where a>12
21 янв 12, 15:31    [11944882]     Ответить | Цитировать Сообщить модератору
 Re: Пятничная задачка - оконные функции  [new]
xtender
Member

Откуда: Мск
Сообщений: 5704
тьфу, не так прочитал
21 янв 12, 15:32    [11944887]     Ответить | Цитировать Сообщить модератору
 Re: Пятничная задачка - оконные функции  [new]
сосед акцессник
Guest
SvLite
Предлагаю попробовать решить исходную задачу, но не по всей таблице, а с произвольным ограничением:
select a, b, c, (select sum(c) from t t1 where t1.b = t.a) s from t where a>12

требуется написать запрос с одним чтением таблицы t исходные данные те же.

что тут "пробовать".

Эквивалентного для произвольного варианта зполнения таблицы - в оконных функциях - решения нет.

Конкретно для заданного первоначально заполнения ( если считать, что оно несет в себе некоторые, не высказанные явно, зависимости данных) - годится любое решение из ранее показанных работоспособных.

PS
... здесь что-то не так - либо человек не сумел сформулировать свою идею, либо он совсем не понимает, что такое "оконные функции".
21 янв 12, 16:00    [11944960]     Ответить | Цитировать Сообщить модератору
 Re: Пятничная задачка - оконные функции  [new]
Elic
Member

Откуда:
Сообщений: 29988
сосед акцессник
что такое "оконные функции".
Во-во. И что это такое? Где официальное определение?
22 янв 12, 08:07    [11946891]     Ответить | Цитировать Сообщить модератору
 Re: Пятничная задачка - оконные функции  [new]
сосед акцессник
Guest
Elic
сосед акцессник
что такое "оконные функции".
Во-во. И что это такое? Где официальное определение?


Для "во-во" достаточно того, что выделено болдом в этом тексте:

[url= http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions001.htm ]Analytic functions compute an aggregate value based on a group of rows. They differ from aggregate functions in that they return multiple rows for each group. The group of rows is called a window and is defined by the analytic_clause. For each row, a sliding window of rows is defined. The window determines the range of rows used to perform the calculations for the current row. Window sizes can be based on either a physical number of rows or a logical interval such as time.

Analytic functions are the last set of operations performed in a query except for the final ORDER BY clause. All joins and all WHERE, GROUP BY, and HAVING clauses are completed before the analytic functions are processed. Therefore, analytic functions can appear only in the select list or ORDER BY clause[/url]

То, что выделено курсивом, вполне за "определение" сойдет, думаю - при любых степенях буквоедства.
22 янв 12, 21:25    [11948567]     Ответить | Цитировать Сообщить модератору
 Re: Пятничная задачка - оконные функции  [new]
Elic
Member

Откуда:
Сообщений: 29988
сосед акцессник
То, что выделено курсивом, вполне за "определение" сойдет, думаю - при любых степенях буквоедства.
Ни фига. Там определение окна, но не некой "оконной функции".
Может лучше сидеть там, где сосед?
22 янв 12, 21:45    [11948627]     Ответить | Цитировать Сообщить модератору
 Re: Пятничная задачка - оконные функции  [new]
Elic
Member

Откуда:
Сообщений: 29988
Elic
Может лучше сидеть там, где сосед?
По существу, наезд не на анонима, а на ТС. Даже не понимаю, как он год назад отскочил.
22 янв 12, 21:54    [11948657]     Ответить | Цитировать Сообщить модератору
Все форумы / Oracle Ответить