sql - 如何平均每个 SQL 组中的前 n 个

Question

我试图弄清楚如何平均每组中的前 N ​​个值。我有一个包含两列的表，组和值。我的目标是根据另一个表对每个组中 N 不同的前 N ​​个值进行平均。

对于 A 组，N 等于 3，并以红色突出显示。输出是前 3 个值的平均值。

对于 B 组，N 等于 2，并以绿色突出显示。因为我们只有 B 组的 1 个值 2.2，所以我们需要去填充表。B 组的填充值是 2.0，所以我们将平均 2.2 和 2.0。如果 N = 5，则 B 组的填充值将重复 4 次。

我最初的想法是：

1. 对每组中的值进行排名

2. 将其加入第二张桌子

3. 使用 where Rank <= N 在平均之前删除重复项

但是，我不确定如何合并填充表，因为 N 可能大于我拥有的值的数量。我确实需要使用 SQL Server 2008。

标签： sqlsql-serversql-server-2008

Answer 1

首先，我希望您使用更合适的名称而不是 Group 和 Value。这是一个示例代码，它首先定义顺序，然后定义将使用的 N 个值并从中获取平均值。该代码未经测试，因为您没有提供可消耗的示例数据。

WITH CTE AS(
    SELECT *,
        ROW_NUMBER() OVER( PARTITION BY [Group] ORDER BY [Value] DESC) AS rn,
        COUNT(*) OVER( PARTITION BY [Group]) ItemCount
    FROM TableWithValues
)
SELECT [Group],
    (SUM( [Value]) + CASE WHEN N.n > c.ItemCount 
                           THEN (N.n - c.ItemCount) * F.Filler
                           ELSE 0 END)/ N.n AS [Value]
FROM CTE c
JOIN TableWithN N ON c.[Group] = N.[Group] AND c.rn <= N.n
JOIN Fillers F ON c.[Group] = F.[Group]
GROUP BY [Group];