mysql - GROUP BY 和 2 个限制

Question

想象一下这张 table

Name | Date | Value
--------------------
Olli | 2019 | 2
Olli | 2018 | 2
Olli | 2017 | 2
Olli | 2016 | 1
Olli | 2015 | 1
Olli | 2014 | 1
Pete | 2019 | 3
Pete | 2018 | 3
Pete | 2017 | 3
Pete | 2016 | 2
Pete | 2015 | 9

我想选择短期平均值(最新 3 行)与每个名称的历史平均值的商:

Olli: 2 / 1.5 = 1.3333
Pete: 3 / 4 = 0.75

没有按名称分组，我可以轻松选择所有组件:

SELECT MEAN(value) FROM tbl WHERE name = 'Olli' -- all time mean, used directly in final query 

-- for the short term mean I use a CTE:
WITH short_term_tbl AS (SELECT * FROM tbl WHERE name = 'Olli' ORDER BY Date DESC LIMIT 3)
    SELECT MEAN(short_term_tbl.value) / MEAN(table.value)
    FROM short_term_tbl, tbl

但我怎样才能将其推广到所有名称呢？

我很乐意找到一个适用于 mysql 和 postgresql 的解决方案。

Answer 1

在 MySQL 8.0 中(您似乎正在使用它，因为您使用的是 CTE，它仅从该版本的 MySQL 开始可用)，这可以使用 row_number() 完成，如下所示:

select name, avg(case when rn <= 3 then value end) / avg(value)
from (
    select t.*, row_number() over(partition by name order by date desc) rn from mytable t
) t
group by name

子查询为共享相同 name 的组中的每条记录分配一个等级，按 date 降序排列。然后外部查询做条件聚合。