将数字分组时如何计算平均值 [英] How do I calculate the average when the numbers are grouped

问题描述

我正在构建（在Rails中）用于抓取NBA数据的应用程序（主要用于我自己的教育和学习工具）。我从头开始使用了postgresql，因为如果要托管它，它会更有意义。完全根据我的需要在此处设置数据库模式。

I'm building (in Rails) an app to scrape NBA data (mostly for my own edification and a learning tool). I started with postgresql from scratch because it just makes more sense if I'm going to host with it. The database schema is set up exactly as I want it here.

参与者实例基于TEAM概念
Statistic实例基于PLAYER概念，因此许多Statistic实例构成了参与者的总计。

Participant instances are based on a TEAM concept Statistic instances are based on a PLAYER concept and thus many instances of Statistic make up the 'totals' for participant.

以下查询：

Select SUM(steals) from statistics
INNER JOIN participants on statistics.participant_id = participants.id
INNER JOIN teams on participants.team_id = teams.id 
WHERE teams.id = 15 
GROUP BY statistics.participant_id;

收益（在psql终端应用程序中）

yields (in the psql terminal app)

 sum 
-----
   8
   7
   5
   7
   6
   7
  10
   6
   7
   6
   7
   5
   9
  11
   4
   7
   8

我该如何编写sql查询以基于这17个计算平均值行

How do I write the sql query to calculate the average based on these 17 rows

（编辑以简化查询，仅询问一个问题）

(edit to simplify query and only ask one question)

推荐答案

使用以下查询来包装查询

Use the following query to wrap your query

SELECT sum, AVG(sum)
FROM (
  -- your query here
) x

在某些系统上，您也可以使用窗口该函数不需要外部查询。语法如下：

on some systems you can also use a windowing function which does not require the outer query. The syntax for that looks like this

AVG(sum) OVER () as avg

因为要查找的平均值是分组的结果，所以不能在此处使用。

since the average you are looking for is the result of a grouping you can't use it here.

这篇关于将数字分组时如何计算平均值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！