时间戳

首页 > 解决方案 > 在 SQL 中查询派生表的平均值

mysql - 在 SQL 中查询派生表的平均值

问题描述

在我的数据库中，我正在跟踪几个网页以及他们收到的访问者数量。

表格中的每一页在sites表格中都有几行观察值readings。

我想维护所有观察行，以便跟踪每个站点上的访问者随着时间的推移如何增长，但当然要在给定时刻获取快照，我只想查看最近的观察。

当我试图获取一组网站当前的平均访问者数量时，我运行以下查询：

SELECT Avg(o.visitors)
  FROM
     (
        SELECT sites.id site_id, 
               r.visitors, 
               Max(r.observation_batch_id) observation_batch_id
        FROM sites s
             JOIN readings r ON s.id = r.site_id
        GROUP BY s.id
     ) o

我没有太多的 SQL 经验，所以我想知道这个两步过程是否最好，我在哪里

  1. 通过选择每个站点和与该站点对应的最高站点来创建一个表observation_batch_id，因为这是最新的观察结果，然后

  2. 获取该派生表中值的平均值

一步完成整个事情会更好/可能吗？如果是这样，我会怎么做？

标签： mysqlsql

解决方案

首先，您的查询格式不正确。您按一列（站点 ID）聚合，但visitors在SELECT. 您的数据库应该返回错误；较新版本的 MySQL 会生成此错误。

您不能一步轻松地做到这一点。但是您至少可以编写一个准确的查询。令人惊讶的是，您不需要此sites表。首先，要获得最新的读数，一种方法是相关子查询：

select r.*
from readings r
where r.observation_batch_id = (select max(r2.observation_batch_id)
                                from readings r2
                                where r2.site_id = r.site_id
                               );

然后你可以在没有另一个子查询的情况下做你想做的一切：

select sum(r.visitors) / count(distinct r.site_id)
from readings r
where r.observation_batch_id = (select max(r2.observation_batch_id)
                                from readings r2
                                where r2.site_id = r.site_id
                               );

推荐阅读