TSQL 模式(如均值、中值、模式) [英] TSQL mode (as in mean, median,mode)

问题描述

我正在尝试计算表中一系列 idsofInterest 的模式，每个 idsofInterest 都有一个伴随的 valueOfInterest，例如:

I'm trying to calculate the mode of a series of idsofInterest in a table, each with an accompanying valueOfInterest such:

idsOfInterest | valueOfInterest  
2             | 1A  
2             | 1A  
2             | 3B  
1             | 2A  
1             | 2C  
1             | 2A  
4             | 3B  
4             | 3B  
4             | 4C  

但有数百万行.
每个 idOfInterest 列表都足够长，多模式不是问题.理想情况下，我想要类似的东西

but with millions of rows.
Each list of idOfInterest is sufficiently long that multimodes are not a problem. Ideally, I would like something like

idsOfInterest | modeValueOfInterest  
1             | 2A  
2             | 1A  
3             | 3C  
4             | 3B

任何帮助表示赞赏.(使用 MS SQL Server 2008)

Any help appreciated. (Using MS SQL Server 2008)

推荐答案

mode 是最常见的值.您可以通过聚合和 row_number() 获得:

The mode is the most common value. You can get this with aggregation and row_number():

select idsOfInterest, valueOfInterest
from (select idsOfInterest, valueOfInterest, count(*) as cnt,
             row_number() over (partition by idsOfInterest order by count(*) desc) as seqnum
      from table t
      group by idsOfInterest, valueOfInterest
     ) t
where seqnum = 1;

这篇关于TSQL 模式(如均值、中值、模式)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！