sql - 报告一组记录的分组平均值

Question

我的目标是生成一份报告，显示车库在一周中的特定日期和/或一天中的特定时间的平均占用率(y 轴)。我的数据模型如下:

• 车库 has_many 汽车和车库 has_many 约会，through: :cars
• 汽车 has_many 约会
• 约会有如下字段:
  • picked_up_at(日期时间)
  • 返回时间(日期时间)

此外，Garage 有一个字段capacity(整数)，这是车库中可容纳的最大汽车数量。

如果我有一个过去 6 个月的约会列表，我想生成一个线图，其中 x 轴显示一周中的每一天，分割为 4 小时的时间间隔，而 y-轴显示给定天/小时间隔内 6 个月内的平均占用率(车库中的汽车数量/容量)，我如何才能收集这些数据以进行报告？

例如一辆车从一个约会的返回时间到下一个约会的取车时间为In，从约会的取车时间为Out，直到它的returned_at时间。

我在将这些数据点与有意义地报告并将它们呈现给最终用户的最佳方式联系起来时遇到了很多麻烦。

我正在使用 Rails 4.1 和 Ruby 2.0。

编辑:SQL Fiddle - http://sqlfiddle.com/#!9/a72fe/1

Answer 1

此查询将完成所有操作(适应您添加的 fiddle ):

SELECT a.ts, g.*, round((a.ct * numeric '100') / g.capacity, 2) AS pct
FROM  (
   SELECT ts, c.garage_id, count(*) AS ct
   FROM   generate_series(timestamp '2015-06-01 00:00'  -- lower and
                        , timestamp '2015-12-01 00:00'  -- upper bound of range
                        , interval  '4h') ts
   JOIN   appointment a ON a.picked_up_at <= ts     -- incl. lower
                       AND (a.returned_at >  ts OR
                            a.returned_at IS NULL)  -- excl. upper bound
   JOIN   car c ON c.id = a.car_id
   GROUP  BY 1, 2
   ) a
JOIN   garage g ON g.id = a.garage_id
ORDER  BY 1, 2;

SQL Fiddle.

如果 returned_at IS NULL，则此查询假定汽车仍在使用中。所以 NULL 不应该出现在其他情况下或者你在计算中有错误。

首先，我使用方便的 generate_series() 构建时间序列功能。

然后加入时间戳落在预订内的约会。
我假设每个约会都包含较低的时间戳和排除较高的时间戳，因为这是普遍的惯例。

在我们加入车库之前进行聚合和计数(这种方式更快)。比较:

• Aggregate a single column in query with many columns

在外部 SELECT 中进行百分比计算。
我将 bigint 数字与 numeric(或可选的 real 或 float)相乘以保留小数位，这将是在整数除法中切断。然后我四舍五入到两位小数。

请注意，这并不是每个 4 小时周期的平均百分比，而只是每个时间点的当前百分比，这是真实平均值的近似值。您可能会以一个奇怪的时间戳开始，例如“2015-06-01 01:17”，这样就不会陷入可能会在整点或其他时间翻过来的预订之间，这可能会增加近似值的平均误差。

您也可以对 4 小时的时间段进行精确计算，但这更复杂。一种简单的技术是将间隔减少到 10 分钟或足够详细以捕获完整图片的某种粒度。

相关(附精确计算示例):

• Calculate working hours between 2 dates in PostgreSQL
• Average stock history table