Синтаксис SQL для сложного GROUP BY с оператором OVER: вычисление коэффициента Джини для нескольких множеств
Я хочу рассчитать коэффициент Джини для ряда наборов, содержащихся в таблице из двух столбцов (здесь называется #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. Как указано в комментариях, я бы предпочел также решение с одним запросом, но это не главная проблема вообще.
2 ответа
Вы можете "разбить" данные так, чтобы нумерация строк начиналась заново для каждого идентификатора... но я не уверен, что это то, что вам нужно...
Я предполагаю, что вы хотите, чтобы 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
Если я правильно понимаю, вам нужно 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