Агрегация в Oracle SQL

Question

Мне нужна помощь с проблемой агрегации в Oracle SQL. Я надеюсь, что это не слишком просто, но я действительно не знаю, как это сделать.

У меня есть следующие столбцы:

Customer_ID  (int)
Countract_ID (int)

• Каждый контракт может включать в себя несколько разных клиентов
• Каждый клиент может быть включен в несколько договоров.

Мне нужно добавить новый столбец со средним количеством контрактов, которые есть у каждого участника (включая текущий). Например:

ContractID  |CustomerID | "AVG sum of contracs per member in the contract"
123         | 11        |(3 + 2 + 1) / 3 = 2
123         | 22        |(3 + 2 + 1) / 3 = 2
123         | 33        |(3 + 2 + 1) / 3 = 2
321         | 11        |(3 + 2 + 2 + 1)  / 4  = 2
321         | 55        |(3 + 2 + 2 + 1)  / 4  = 2
321         | 22        |(3 + 2 + 2 + 1)  / 4  = 2
321         | 88        |(3 + 2 + 2 + 1)  / 4  = 2
987         | 11        |(3 + 2  + 1 + 1) / 4 = 1.75
987         | 55        |(3 + 2  + 1 + 1) / 4 = 1.75
987         | 99        |(3 + 2  + 1 + 1) / 4 = 1.75
987         | 77        |(3 + 2  + 1 + 1) / 4 = 1.75

Кто-нибудь знает, что такое запрос для таких как агрегация?

Answer 1

Вот один из подходов, где мы используем несколько аналитических функций: count() over() а также avg() over(),

-- sample of data
with t1(Contractid ,Customerid) as(
  select 123 , 11 from dual union all
  select 123 , 22 from dual union all
  select 123 , 33  from dual union all
  select 321 , 11  from dual union all
  select 321 , 55  from dual union all
  select 321 , 22  from dual union all
  select 321 , 88  from dual union all
  select 987 , 11  from dual union all
  select 987 , 55  from dual union all
  select 987 , 99  from dual union all
  select 987 , 77  from dual
 )
 -- the query
 -- analytic functions cannot be nested, thus inline vew
 select contractid
      , customerid
      , avg(cnt) over(partition by contractid) as average
  from ( select contractid
              , customerid    
              , count(contractid) over(partition by customerid) cnt
           from t1 )
 order by contractid, customerid

Результат:

CONTRACTID CUSTOMERID    AVERAGE
---------- ---------- ----------
       123         11          2
       123         22          2
       123         33          2
       321         11          2
       321         22          2
       321         55          2
       321         88          2
       987         11       1.75
       987         55       1.75
       987         77       1.75
       987         99       1.75

11 rows selected

Sqlfiddle demo