sql - 计算和使用子查询的列平均值

Question

我有一个团队统计数据数据库，我想对某个统计数据的总和进行归一化。例如，我有一个这样的冗余记录表:

team_name | team_code | player | steal | block
Team A    | TA        | PA     | 100   | 100
Team A    | TA        | PA     | 100   | 100
Team A    | TA        | PA     | 100   | 100
Team B    | TB        | PB     | 200   | 200
Team C    | TC        | PC     | 300   | 300
Team C    | TC        | PC     | 300   | 300

我想得到所有球员的球队(抢断 + 盖帽)除以所有球队的平均值(抢断 + 盖帽)。

例如，我需要这样的输出(注意:我在表达式中添加了 norm_def_total 所需的表达式):

team_name | team_code | steal_sum | block_sum | def_total | norm_def_total
Team A    | TA        | 300       | 300       | 600       | 600/(avg(def_total))
Team B    | TB        | 200       | 200       | 400       | 400/(avg(def_total))
Team C    | TC        | 600       | 600       | 1200      | 1200/(avg(def_total))

这是我的尝试:

SELECT b.team_name, b.team_code, b.def_total/AVG(b.def_total) normalized_def FROM(    SELECT  team_name,        team_code,        SUM(steal) steal_sum,        SUM(block) block_sum,        CAST(SUM(steal) + SUM(block) AS double precision) def_total    FROM gamelog    WHERE team_name IN (SELECT DISTINCT (team_name) FROM gamelog)    GROUP BY team_name,             team_code    ORDER BY def_total DESC) bGROUP BY    b.team_name,    b.team_code,    b.def_total

从上面看，我的问题是 b.def_total/AVG(b.def_total) 总是返回 1。我认为它只为那个特定的团队获取 AVG，但我想要 b 中所有团队的 AVG。如何获得 b 的实际平均值以用于计算？

Answer 1

试试这个。

SELECT b.team_name,
       b.team_code,
       Sum(def_total) / Avg(b.def_total)OVER(partition BY NULL)
FROM   (SELECT team_name,
               team_code,
               Sum(steal)                                        steal_sum,
               Sum(block)                                        block_sum,
               Cast(Sum(steal) + Sum(block) AS DOUBLE PRECISION) def_total
        FROM   gamelog
        WHERE  team_name IN (SELECT DISTINCT ( team_name )
                             FROM   gamelog)
        GROUP  BY team_name,
                  team_code
        ORDER  BY def_total DESC) b
GROUP  BY b.team_name,
          b.team_code