跨列和平均值选择最上面的n个值

I have a table that has 13 columns, one with type varchar(25) and the rest with type `int (holding values for each month of the year).

我有一个有13列的表，其中一个列的类型是varchar(25)，其余的列的类型是' int(保持一年中的每个月的值)。

For each row, I would like to pick the top 6 int values from the 12 columns and calculate the average of those values.

对于每一行，我想从12列中选出前6个int值，然后计算这些值的平均值。

I know how to select the top n from a given column, but how do you do it across multiple columns?

我知道如何从给定列中选择最上面的n，但是如何跨多个列进行呢?

3 个解决方案

#1

select ID,
       (
       select avg(C)
       from (
            select top(6) C
            from (values(C1),(C2),(C3),(C4),(C5),(C6),(C7),
                        (C8),(C9),(C10),(C11),(C12)) as T(C)
            order by C desc
            ) as T
       ) as C
from YourTable

SQL Fiddle

SQL小提琴

For SQL Server 2005 it would look like this since you can't use the Table Value Constructor

对于SQL Server 2005，它看起来是这样的，因为您不能使用表值构造函数

select ID,
       (
       select avg(C)
       from (
            select top(6) C
            from (select C1 union all
                  select C2 union all
                  select C3 union all
                  select C4 union all
                  select C5 union all
                  select C6 union all
                  select C7 union all
                  select C8 union all
                  select C9 union all
                  select C10 union all
                  select C11 union all
                  select C12) as T(C)
            order by C desc
            ) as T
       ) as C
from YourTable

SQL Fiddle

SQL小提琴

And for SQL Server 2000 this could work for you.

对于SQL Server 2000，这可能对你有用。

select T1.ID,
       avg(C) as C
from (
     select ID, C1 as C from YourTable union all
     select ID, C2  from YourTable union all
     select ID, C3  from YourTable union all
     select ID, C4  from YourTable union all
     select ID, C5  from YourTable union all
     select ID, C6  from YourTable union all
     select ID, C7  from YourTable union all
     select ID, C8  from YourTable union all
     select ID, C9  from YourTable union all
     select ID, C10 from YourTable union all
     select ID, C11 from YourTable union all
     select ID, C12 from YourTable
     ) as T1
where (
      select count(*)
      from (
           select ID, C1 as C from YourTable union all
           select ID, C2  from YourTable union all
           select ID, C3  from YourTable union all
           select ID, C4  from YourTable union all
           select ID, C5  from YourTable union all
           select ID, C6  from YourTable union all
           select ID, C7  from YourTable union all
           select ID, C8  from YourTable union all
           select ID, C9  from YourTable union all
           select ID, C10 from YourTable union all
           select ID, C11 from YourTable union all
           select ID, C12 from YourTable
           ) as T2
      where T1.ID = T2.ID and
            T1.C <= T2.C
      ) <= 6
group by T1.ID

SQL Fiddle

SQL小提琴

I would not expect this to be particularly fast. Perhaps a better option is to store an intermediate result in a temp table.

我不认为这会特别快。也许更好的选择是在临时表中存储中间结果。

create table #T
(
  ID varchar(25),
  C int
)

insert into #T
select ID, C1 as C from YourTable union all
select ID, C2  from YourTable union all
select ID, C3  from YourTable union all
select ID, C4  from YourTable union all
select ID, C5  from YourTable union all
select ID, C6  from YourTable union all
select ID, C7  from YourTable union all
select ID, C8  from YourTable union all
select ID, C9  from YourTable union all
select ID, C10 from YourTable union all
select ID, C11 from YourTable union all
select ID, C12 from YourTable

select T1.ID,
       avg(C) as C
from #T as T1
where (
      select count(*)
      from #T as T2
      where T1.ID = T2.ID and
            T1.C <= T2.C
      ) <=  6 
group by T1.ID  

drop table #T

#2

Firstly, it is important to understand that using TOP in conjunction with aggregates will not limit the aggregation but rather the result set. Look at this example:

首先，重要的是要理解，将TOP与aggregates结合使用不会限制聚合，而是限制结果集。

SELECT TOP 2 SUM(col) FROM
(SELECT 1 AS col
 UNION
 SELECT 2
 UNION
 SELECT 3)sq

The result is still "6".

结果仍然是“6”。

Secondly, aggregation does NOT work across columns, only rows. You would need to evaluate them manually. Probably the most efficient way would be to create a table from the row like this:

其次，聚合不是跨列工作，而是跨行工作。您需要手动评估它们。可能最有效的方法就是从这一行创建一个表:

SELECT
    (SELECT MAX(myval) FROM (values (col1), (col2), (col3), (col4)) as all_values(myval))
FROM (SELECT 1 as col1, 2 as col2, 3 as col3, 4 as col4)sq

#3

If you are using SQL Server 2005 or later you can unpivot the table, then rank the values, and last calculate the averages the for the top 6 values per identifier.

如果您正在使用SQL Server 2005或更高版本，您可以取消对表的数据透视，然后对值进行排序，最后计算每个标识符前6个值的平均值。

something like this:

是这样的:

;WITH UnPivoted AS (
    SELECT pk, MonthID, MonthNumber, MonthValue
    FROM 
       (SELECT pk, Month1, Month2, Month3, Month4, Month5, Month6, Month7, Month8, Month9, Month10, Month11, Month12
       FROM pvt) p
    UNPIVOT
       (pk FOR MonthNumber IN 
          (Month1, Month2, Month3, Month4, Month5, Month6, Month7, Month8, Month9, Month10, Month11, Month12)
    )AS unpvt
),
UnPivotedRanked AS (
    SELECT pk, MonthValue, RANK() OVER(PARTITION BY pk ORDER BY MonthValue DESC) AS pkRanked
    FROM UnPivoted
    GROUP BY pk
)
SELECT pk, AVG(MonthValue) AS Top6Average
FROM UnPivotedRanked
WHERE pkRanked < 6
GROUP BY pk