下面的语句不起作用,但我似乎不明白的原因
select AVG(delay_in_seconds) from A_TABLE ORDER by created_at DESC GROUP BY row_type limit 1000;
我想得到每个row_type最近1000行的平均值。created_at的类型为DATETIME,row_type的类型为VARCHAR
如果只想要1000个最近的行,而不考虑row_type,然后获得每个row_type的delay_in_seconds的平均值,这是一个相当简单的查询。例如:
SELECT t.row_type
, AVG(t.delay_in_seconds)
FROM (
SELECT r.row_type
, r.delay_in_seconds
FROM A_table r
ORDER BY r.created_at DESC
LIMIT 1000
) t
GROUP BY t.row_type
但是,我怀疑这个查询不满足指定的要求。(我知道它不符合我所理解的规范。)
如果我们想要的是每个row_type的最近1000行的平均值,那也相当简单。。。如果我们使用的是支持分析函数的数据库。
不幸的是,MySQL不支持分析函数。但是可以在MySQL中模拟一个,但语法有点复杂,而且它依赖于而不是所保证的行为。
例如:
SELECT s.row_type
, AVG(s.delay_in_seconds)
FROM (
SELECT @row_ := IF(@prev_row_type = t.row_type, @row_ + 1, 1) AS row_
, @prev_row_type := t.row_type AS row_type
, t.delay_in_seconds
FROM A_table t
CROSS
JOIN (SELECT @prev_row_type := NULL, @row_ := NULL) i
ORDER BY t.row_type DESC, t.created_at DESC
) s
WHERE s.row_ <= 1000
GROUP
BY s.row_type
注意:
内联视图查询对于大型集合来说将是昂贵的。有效的做法是为每一行指定一个行号。"排序依据"是按created_at
按降序对行进行排序,我们希望最近的行被分配值1,下一个最近的行分配值2,等等。对于row_type
的每个不同值,都会重复这种行编号。
为了提高性能,我们希望有一个合适的索引,其前导列为(row_type,created_at,delay_seconds)
,以避免昂贵的"使用文件排序"操作。为此,我们至少需要前两列,包括delay_seconds使其成为覆盖索引(完全可以从索引中满足查询)
然后,外部查询根据视图查询返回的结果集("派生表")运行。WHERE中的谓词过滤掉所有被分配了大于1000的行号的行,其余的是一个Straighborward GROUP BY和AVG聚合。
LIMIT子句是完全没有必要的。可以合并一些额外的谓词来增强性能。。。比如,如果我们指定了最近的1000行,但只有在过去30或90天内创建的行呢?
(我不完全确定这是否回答了OP提出的问题。答案是:是否有一个查询可以使用AVG
聚合和GROUP BY
、ORDER BY
和LIMIT
子句返回指定的结果集。)
N.B.此查询依赖于MySQL用户定义变量的行为,而不保证该行为。
上面的查询显示了一种方法,但也有另一种方法。可以使用(a_table与a_table的)"联接"操作来获得指定的行号(获得比每一行"最近"的行数的COUNT)。然而,对于大集合,如果我们不小心限制它,可能会产生巨大的中间结果。
在语句的最后编写ORDER BY。
SELECT AVG(delay_in_seconds) from A_TABLE GROUP BY row_type ORDER by created_at DESC limit 1000;
有关详细信息,请阅读mysql-dev网站。