时间戳

首页 > 解决方案 > 计算所有玩家权重的方差

sql - 计算所有玩家权重的方差

问题描述

我们从 nba 获得数据，其中每个表的描述如下：

  1. coaches_season，每个元组描述一位教练在一个赛季的表现；[cid, year, yr_order, year, season_win, season_loss, play_off_win, play_off_loss, tid]

  2. 团队，每个元组给出一个团队的基本信息；[tid，位置，名称，联赛]

  3. 玩家，每个元组给出一个玩家的基本信息；[ilkid，名字，姓氏，位置，first_season，last_season，h_feet，h_inches，体重，大学，生日]

  4. player_rs，每个元组给出了一名球员在一个常规赛中的详细表现；[ilkid，tid，pts，asts，of，ftm，tpa，tpm，fgm，fga，fta，blk，营业额，stl，dreb，oreb，reb，分钟，gp，联赛，姓氏，名字，年份]

  5. player_rs_career，每个元组都给出了一名球员在其职业生涯中的详细常规赛表现；[ilkid, firstname, lastname, fga, fgm, fta, ftm, tpa, tpm, pf, stl, oreb, minutes, gp, dreb , asts, 失误, blk, reb, 联赛]

  6. 选秀，每个元组显示NBA选秀的信息。[draft_year, firstname, lastname, draft_round, tid, selection, draft_from, ilkid, 联赛]

我发现了很多查询，但被这 3 个查询卡住了：

I) 对于每所大学，打印大学名称和他们发送给 NBA 的平均选秀数（每个赛季）。但是，只报告那些在至少 3 个赛季中发送选秀的大学。

II) 计算所有玩家权重的方差；

III) 打印职业生涯得分超过 12000 分或效力超过 12 个赛季的人的姓名。

标签： sqlpostgresqlnested-queries

解决方案

在这种情况下，尤其是每个表的 DDL，包括主键和外键定义。还包括样本数据，作为文本，而不是图像和该数据的预期输出。此外，在这种情况下，您可能希望包含列名的定义，因为并非每个人都熟悉 NBA 使用的首字母缩略词。

话虽如此，我会给它一个刺。请注意，由于您没有包含测试数据或表定义，因此尚未测试查询。

-- I)  For each college, print the college name and average number of drafts (per season) they sent to NBA. 
--     However, only report those colleges that sent drafts in at least 3 seasons.
-- assumptions: 
--    draft yr   integer specifying calendar year of draft
--    draft_from text    name of college
--    average number of drafts (per season)  ?? how many drafts are there per season
--                                           what is the difference between season and year  

with draft as
     (select max(draft_yr) dy from draft_year)
   , dy_last3 as
     (select distinct draft_from  df
        from draft_year
       where exists (select null from draft_year, draft where draft_yr = dy)
         and exists (select null from draft_year, draft where draft_yr = dy-1)
         and exists (select null from draft_year, draft where draft_yr = dy-2) 
     )      
 select draft_from, round(avg(drc),2) adv_drafts
   from (
         select draft_from, draft_yr, count(*) drc 
           from draft_year  
          where draft_from in (select df from dy_last3)
          group by draft_from,draft_yr
      ) t
 group by draft_from;   

-- II) Calculate the variance of the weights of all players;
-- assumption:  weight defined as float;

select var_samp(weight) from player;  
   OR 
select var_pop(weight) from player; 

-- III)Print the first and last names of those who either scored more than 12000 points in their careers or played for more than 12 seasons.
-- assumption fgm => field goals made  = 2 points each
--            ftm => free throws made  = 1 point  each
--            tpm => 3 point shot make = 3 points each
--            ilkid => Pk in player and FK in player_rs_career  
--            table player_rs_career does include last/current season
--            note player_rs_career does NOT contain year/season, unless hidden by undescribed column name

select distinct * 
  from (select p.firstname, p.lastname 
             , sum(ftm + (2*fgm) + (3*tpm)) over (partition by p.ilkid) points
             , (coalesce (p.last_season, extract (year from now())::integer) - p.first_season + 1) seasons 
          from player           p 
          join player_rs_career pc 
            on p.ilkid = pc.ilkid
       ) pp
  where points  > 12000
     or seasons > 12;

推荐阅读