如何从无限数量的组中聚合信息

Question

如何在TSQL中聚合来自不定数量组的信息？例如，我们有一个包含2列的表——客户和地区。

Clients Regions
client1 45
client1 45
client1 45
client1 45
client1 43
client1 42
client1 41
client2 45
client2 45
client3 43
client3 43
client3 41
client3 41
client3 41
client3 41

每个客户端都可以有任意数量的区域。

在下面的示例中：client1有4组区域，第2-1组，第3-2组。

我想为每个客户计算基尼杂质，即计算客户中的区域有多不同。

为此，我想将以下公式应用于每个客户：

1 - ((% of region1 among all the regions in the client) ^ 2 + 
(% of region2 among all the regions in the client) ^ 2 + 
… (% of regionN among all the regions in the client) ^ 2)

但是区域的数量是不确定的(每个客户可能不同(。

应计算：

client1 = 1 - ((4 / 7 ) ^ 2 + (1 / 7 ) ^ 2 + (1 / 7 ) ^ 2  + (1 / 7 ) ^ 2)
client2 = 1 - ((2 / 2 ) ^ 2)
client3 = 1 - ((2 / 6 ) ^ 2 +  (4 / 6 ) ^ 2)

这是理想的输出：

Clients Impurity
client1 61%
client2 0%
client3 44%

你能告诉我解决这个问题的方法吗。

Answer 1

我认为公式可以用表示为一组

WITH cte AS (
SELECT Clients
, CAST(COUNT(*) AS DECIMAL(10, 0)) / SUM(COUNT(*)) OVER(PARTITION BY Clients) AS tmp
FROM t
GROUP BY Clients, Regions
)
SELECT Clients
, 100 * (1 - SUM(tmp * tmp)) AS GI
FROM cte
GROUP BY Clients

数据库<>小提琴似乎和预期的产量相符。

Answer 2

以下是我的方法：

• 在子子查询中，执行count(*) as cnt ... group by clients, regions
• 在子查询中，执行cast(cnt as float)/sum(cnt) over(partition by clients) as pcnt并将其平方
• 在外部查询中执行1 - sum(pcnt) ... group by clients

有一些方法可以将其压缩为不使用2个子查询，但它们可能不会使其更可读或更易于理解。我不完全清楚你的意思是百分比(满分100(还是比率(满分1(，所以你可能必须在适当的点添加*100