在按语句分组后返回记录计数的平均值 [英] return average of counts of records after a group by statement

问题描述

我有一堆记录，我想针对这些记录计算特定时间单位(小时，天，周)的平均计数.因此，一种情况是我想计算每天在给定范围内的平均记录数.更清楚地说，这只是一个数字.

I have a bunch of records for which I want to compute the average count for over certain time units (hours, days, weeks). So one situation would be that I want to calculate an average number of records that I have per day in a given range. To be more clear, this would just be a single number.

我现在正在执行的方式(显然不起作用，因为它没有取平均值)，是以下sqlalchemy查询:

The way I'm doing it right now (which obviously does not work because it doesn't take the average), is the following sqlalchemy query:

db.query(MyClass).filter(MyClass.created.between(start_date, end_date)).group_by(func.HOUR(MyClass.created)).count()

这是SHOW CREATE TABLE yt_video的输出:

| yt_video | CREATE TABLE `yt_video` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `version` int(11) DEFAULT NULL,
  `created` datetime DEFAULT NULL,
  `published_date` date DEFAULT NULL,
  `yt_data` text,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |

我真正关心的只是创建的时间戳，但希望对您有所帮助.

All I really care about is the created timestamp, but hope this helps.

推荐答案

这是答案.

根据您的情况，使用提供的表定义:

In your case, using the table definition provided:

from sqlalchemy import cast, func
from sqlalchemy.types import TIME, DATE
from sqlalchemy.sql import between

time_from = ...  # get filter time maybe using datetime.time()
time_to = ...  # idem
counts = session.query(func.count('*').label('count')).\
    filter(between(cast(MyClass.created, TIME),
           time_from,
           time_to)).\
    group_by(cast(MyClass.created, DATE))

avg = session.query(func.avg(counts.subquery().columns.count)).scalar()

print avg

这篇关于在按语句分组后返回记录计数的平均值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！