问题描述
我在这里设置了一个小提琴:https://www.db-fiddle.com/f/snDGExYZgoYASvWkDGHKDC/2
I've setup a fiddle here: https://www.db-fiddle.com/f/snDGExYZgoYASvWkDGHKDC/2
还有:
架构:
查询 1:
查询 2:
查询 3:
第三个查询正确执行,但在我的测试中,当扩展到数百万行时非常慢.我认为这是因为它是一个相关的子查询并且运行了数百万次.
The third query executes correctly but in my testing has been very slow when scaling to millions of rows. I think this is because it is a correlated subquery and runs millions of times.
前两次尝试是我尝试创建以使用 MySQL 8 Window Functions 对平均计算进行分区.然而,这些正在产生意想不到的结果.给定日期的 average_score
总数应该加起来为 100,就像在第三个查询中一样.
The first two attempts are me trying to created to use MySQL 8 Window Functions to partition the average calculation. However, these are giving unexpected results. The total average_score
s for a given day should add up to 100, like it does in the 3rd query.
有人知道更有效的计算方法吗?
Does anyone know of a more efficient way to calculate this?
还值得注意的是,在现实中,查询中也会有一个 WHERE IN
以按特定的 shift_id
进行过滤.给定的 shift_ids
数量可以是几十万,也可以是一百万.
It's also worth noting that in reality, there will also be a WHERE IN
on the queries to filter by specific shift_id
s. The number of shift_ids
given could be in the hundreds of thousands, up to a million.
正在考虑的另一件事是 ElasticSearch.是否有助于更快地计算这些?
One other thing being considered is ElasticSearch. Would it help with calculating these in a quicker way?
推荐答案
您可以使用窗口函数.诀窍是取每个员工每天总分的窗口总和,如下所示:
You can use window functions. The trick is to take a window sum of the total score per employee for each day, like so:
在你的数据库小提琴中,这个产量:
In your DB Fiddle, this yields:
这篇关于MySQL 8 按日期分区计算平均值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!