通过过度语句:计算多个集合的GINI系数，用于复杂组的SQL语法

Question

我想计算多个集合的Gini系数，其中包含在两列表中（此处称为 #cits），其中包含一个值和一个set-id。我一直在尝试不同的Gini-coeff充足的计算，此处描述（Stackexchange查询）和此处（stackoverflow问题带有一些很好的答复）。两个示例仅计算一个表的一个系数，而我想使用GROUP BY子句进行。

#cits表分别包含两个列c和cid，分别为值和设置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的工作效果很好，但是下一行让我头疼。我可以看到语法是错误的，但是我无法弄清楚如何分配每个C每CID的row_number()。

编辑：根据建议，我使用了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

这个几乎有效。我得到了结果，但这是＆gt; 1，这是出乎意料的，因为gini-coeffity应该在0到1之间。一个主要问题。

Answer 1

您可以"分区"数据，因此每个ID的行都将重新开始...但是我不确定这就是您所追求的。

我假设您要按照它进行分组时显示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

如果我正确理解，则在CID集中需要每个C以及该集合内部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