SQL Server-запрос для расчета средневзвешенного значения на основе последних значений за каждый год

Предположим, у меня есть следующие столбцы из таблицы SQL для бейсбольных питчеров Высшей лиги: 



player_id   game_id             season_ip | season_whip |career_ip


И мы имеем следующие данные: 



502190  2013/04/18/miamlb-cinmlb-1  19      1.32    504

502190  2013/04/19/miamlb-cinmlb-1  19      1.32    504

502190  2013/06/11/cinmlb-chnmlb-1  73      1.32    558

502190  2013/06/13/cinmlb-chnmlb-1  81      1.24    566

502190  2013/09/29/pitmlb-cinmlb-1  192     1.25    677

502190  2014/04/22/cinmlb-pitmlb-1  28      0.99    705

502190  2014/05/26/cinmlb-lanmlb-1  71      1.06    748

502190  2014/09/28/pitmlb-cinmlb-1  214     1.25    891

502190  2015/03/25/texmlb-cinmlb-1  14      0.71    891

502190  2015/08/15/wasmlb-sfnmlb-1  143     1.17    1034



453286  2013/05/05/detmlb-houmlb-1  39      1.04    844

453286  2013/05/16/detmlb-texmlb-1  54      0.99    859

453286  2013/09/29/detmlb-miamlb-1  214     0.97    1019

453286  2014/06/18/kcamlb-detmlb-1  98      1.25    1117

453286  2014/07/15/nasmlb-aasmlb-1  126     1.17    1145

453286  2014/09/28/minmlb-detmlb-1  220     1.18    1239

453286  2015/03/22/wasmlb-detmlb-1  14      0.93    1239

453286  2015/08/15/wasmlb-sfnmlb-1  165     0.9     1404


Мне нужен SQL-запрос, чтобы получить средневзвешенное значение season_whip для каждого player_id на основе season_ip (подач).

Я хочу использовать только последнюю запись для каждого года (обратите внимание, что столбец game_id содержит год). Таким образом, для player_id 502190 последняя запись за 2015 год является game_id 2015/08/15/wasmlb-sfnmlb-1, 2014 год-это game_id 2014/09/28/pitmlb-cinmlb-1.



Формула для средневзвешенного значения такова: 



sum(season_ip * season_whip) / sum(season_ip)


Так, например, player_id 502190, его хлыст-это 



[192 * 1.25 + 214 * 1.25 + 143 * 1.17] / [192 + 214 + 143] = 1.229


Я включил career_ip, потому что это единственное значение, которое будет увеличиваться в течение всех лет. season_ip сбрасывается каждый год, а season_whip изменяется в течение года.



Любая помощь ценится. Я использую SQL Server 2014.

752   2  

2 ответов:

Попробуйте что-нибудь вроде этого

SQL FIDDLE DEMO 

With whip as (
    SELECT *, SUBSTRING(game_id, 1, 4) as year_id
    FROM testMLB
),
year_whip as (
    SELECT *, 
    ROW_NUMBER() OVER (PARTITION BY player_id, year_id ORDER BY game_id DESC) AS RowNumber
    FROM whip
)
SELECT 
    player_id, 
    SUM(season_ip * season_whip) / sum(season_ip) 
FROM year_whip
WHERE RowNumber = 1
GROUP BY player_id
1  
2015-08-19 07:35:47

Похоже, что вы хотите получить последнюю строку в год для каждого игрока, а затем сгруппировать эти строки, чтобы получить скорректированный вес для хлыста игрока. Если это так, вы можете использовать row_number(), чтобы получить только последнюю строку на игрока в год перед группировкой для средневзвешенного значения: 

  select player_id, sum(season_ip * season_whip) / sum(season_ip) 
  from (
    select * ,
      row_number() over (partition by player_id, left(game_id,4) order by game_id desc) rn 
    from
    mytable
  ) t1 where rn = 1
  group by player_id

Http://sqlfiddle.com/#!3/17724/1

1  
2015-08-19 07:09:39

Comments

    Ничего не найдено.
Click here to cancel reply.