TSQL 模式(如均值、中值、模式) [英] TSQL mode (as in mean, median,mode)
本文介绍了TSQL 模式(如均值、中值、模式)的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在尝试计算表中一系列 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屋!
查看全文