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

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)?

回答1:

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

Demo

Have a look at the powerful OVER clause.

回答2:

One straightforward method is to compute each player's starting year as an aggregate query, and join the data with the original. These kinds of "sequence based" queries are usually tricky to express in a set based language :(

WITH tmp as (
  select player, min(year) as minyear 
  from table 
  group by player
);

select t.*, t.year - t.minyear + 1 as cyear
from table as t, tmp
where t.player = tmp.player;

回答3:

If you don't have WITH or OVER, then ... get a real database. Failing that, you can do it with a subquery:

SELECT t.*, t.year - subtable.minyear + 1 AS cyear
FROM table AS t
JOIN (
  select player, min(year) as minyear 
  from table 
  group by player
) AS SubTable
ON T.player = SubTable.player

回答4:

Just use multiple group by... and sum on the desired field

GROUP BY player, year