Синтаксис SQL для сложного GROUP BY с оператором OVER: вычисление коэффициента Джини для нескольких множеств

Question

Я хочу рассчитать коэффициент Джини для ряда наборов, содержащихся в таблице из двух столбцов (здесь называется #cits) содержащий значение и идентификатор набора. Я экспериментировал с различными вычислениями коэффициента Джини, описанными здесь (запрос StackExchange) и здесь (вопрос Stackru с некоторыми хорошими ответами). Оба примера рассчитывают только один коэффициент для одной таблицы, тогда как я хотел бы сделать это с GROUP BY пункт.

#cits таблица содержит две колонки, c а также cid, являясь значением и set-ID соответственно.

Вот моя текущая попытка (неполная):

select count(c) as numC, 
sum(c) as totalC, 
(select row_number() over(order by c asc, cid) id, c from #cits) as a 
from #cits group by cid

Конечно, выбор numC и totalC работает хорошо, но следующая строка вызывает у меня головную боль. Я вижу, что синтаксис неправильный, но я не могу понять, как назначить row_number() per c за cid.

РЕДАКТИРОВАТЬ: на основе предложений, я использовал partition, вот так:

select cid,sumC = sum(a.id * a.c) 
into #srep 
from (
   select cid,row_number() over (partition by cid order by c asc) id, 
   c 
   from #cits
) as a 
group by a.cluster_id1 


select count(c) as numC, 
    sum(c) as totalC, b.sumC 
into #gtmp 
from #cits a
    join #srep b 
        on a.cid = b.cid 
group by a.cid,b.sumC


select 
    gini = 2 * sumC / (totalC * numC) - (numC - 1) / numC 
from #gtmp

Это почти работает. Я получаю результат, но это>1, что является неожиданным, так как коэффициент Джини должен быть между 0 и 1. Как указано в комментариях, я бы предпочел также решение с одним запросом, но это не главная проблема вообще.

Answer 1

Вы можете "разбить" данные так, чтобы нумерация строк начиналась заново для каждого идентификатора... но я не уверен, что это то, что вам нужно...

Я предполагаю, что вы хотите, чтобы CID отображался во время группировки.

select count(c) as numC
     , sum(c) as totalC
     , row_number() over(partition by cID order by c asc) as a
     , cid 
from #cits group by cid

Обратите внимание, что вам не нужен подзапрос.

Да, это вряд ли верно.

выход

NumC TotalC A CID
24   383    1  1
15   232    1  2

Answer 2

Если я правильно понимаю, вам нужно numC и totalC для каждого C в наборе cid, а также положение c внутри этого набора. Это должно дать вам то, что вам нужно:

select
    rn.cid,
    rn.c,
    row_number() over (partition by rn.cid order by rn.c) as id,
    agg.numC,
    agg.totalC
from #cits rn
    left outer join
    (
        select
            cid,
            count(c) as numC,
            sum(c) as totalC
        from #cits
        group by cid
    ) agg
        on rn.cid = agg.cid