SQL中的合并的聚合查询和非聚合的查询 [英] Combined aggregated and non-aggregate query in SQL

问题描述

不确定如何表达此问题，但我希望将聚合查询应用于多行。希望有一个例子可以使这个过程更容易。假设我有以下数据：

Not sure how to phrase this question, but I want an aggregate query applied to multiple rows. Hopefully an example should make this easier. Assuming I have the following data:

  player  | year | games
-------------------------
ausmubr01 | 2006 | 139
ausmubr01 | 2007 | 117
bondsba01 | 2006 | 130
bondsba01 | 2007 | 126
stairma01 | 2006 | 26
stairma01 | 2006 | 77
stairma01 | 2006 | 14
stairma01 | 2007 | 125

对于每个球员，我想计算他们的职业生涯，即他们玩过的年数：

And for each player in each year, I want to calculate their "career year", i.e. the number of years they've been playing:

  player  | year | games | cyear
 --------------------------------
ausmubr01 | 2006 | 139   |  1
ausmubr01 | 2007 | 117   |  2
bondsba01 | 2006 | 130   |  1
bondsba01 | 2007 | 126   |  2
stairma01 | 2006 | 26    |  1
stairma01 | 2006 | 77    |  2
stairma01 | 2006 | 14    |  3
stairma01 | 2007 | 125   |  4

很自然地将此转换表示为 SELECT播放器，年份，游戏，年-分钟（年）+1作为cyear（按球员分组从棒球组中获得），但由于汇总查询的规则，该表达式对于每个组仅计算一次：

It would be natural to express this transformation as SELECT player, year, games, year - min(year) + 1 as cyear FROM baseball GROUP by player but because of the rules for aggregate queries the expression is only evaluated once for each group:

  player  | year | games | cyear
 --------------------------------
ausmubr01 | 2006 | 139   |  1
bondsba01 | 2006 | 130   |  1
stairma01 | 2006 | 26    |  1

我一般如何克服这个问题（即不仅针对这种情况，而且无论何时我想进行算术运算，将现有的列和使用汇总函数计算出的单个每组数字结合起来）？

How can I overcome this problem in general (i.e. not just for this case but whenever I want to perform an arithmetic operation combining an existing column and a single per-group number computed with an aggregate function)?

推荐答案

您可以使用 ROW_NUMBER ：职业年：

You can use ROW_NUMBER for the career-year:

SELECT player, year, games,
       cyear = ROW_NUMBER () OVER (PARTITION BY player ORDER BY year),
       gamesPerMax = 1.0 * games / MAX(games) OVER (PARTITION BY player)
FROM dbo.TableName

演示

看看功能强大的 OVER 子句。

Have a look at the powerful OVER clause.

这篇关于SQL中的合并的聚合查询和非聚合的查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！