我正在尝试编写一个查询来检索一段时间内的累积结果,该查询只获取每个相关id的每个间隔的结果集的最新示例。
例子:
假设有一个users表,每个用户都可以创建包含问题的报告。这些问题汇总在一个报表汇总表中,其中包含问题类别的总和。table可能看起来像这样
users
id, email
reports
id, user_id, date
report_totals
id, report_id, errors, alerts
这是我正在努力解决的问题,如果用户在当前时间间隔内没有提交报告,它应该用上一个时间间隔的总和来回填该数据。假设我们有这样的数据
reports
1, 1, 2018-1-1
2, 2, 2018-1-1
3, 1, 2018-1-4
4, 1, 2018-2-1
5, 1, 2018-3-1
6, 2, 2018-3-1
report_totals
1, 1, 5, 5
2, 2, 3, 0
3, 3, 2, 0
4, 4, 10, 2
5, 5, 30, 15
6, 6, 1, 2
我想编写一个查询来返回如下所示的结果
date, errors, alerts
2018-1-1, 5, 0
2018-2-1, 13, 2
2018-3-1, 31, 17
报告间隔为1个月,因此它只使用每个用户每月的最新结果,对它们求和,如果没有该用户的记录,则将从以前的间隔中回填。
在mysql中这样的事情是可能的吗?这是正确的方法吗?提前谢谢,如果这已经被回答了对不起,我还没有找到什么,完全完成我要找的。
2条答案
按热度按时间2uluyalo1#
这是一个棘手的问题,但不是用mysql无法解决的:-)它可以用不太详细的查询来解决,也可以用mysql版本8.0.2及更高版本中提供的带框架的窗口函数来执行。但是,我们也可以使用相关子查询解决这个问题,使用
CROSS JOIN
和派生表。我将分解这个问题,并尝试分步解释它。因为您要考虑前几个月的报告值,即使当前月份没有报告,所以我们的第一步是生成一个“主表”,它基本上包含所有可能的报告值组合
user_id
以及first date of a month
. 这可以在查询本身中完成。我们可以得到所有独特的
user_id
来自users
table。并且,可以使用以下查询确定所有报告月份的开始日期。现在,有可能某个月根本没有报告。在这种情况下,您将不得不使用主日历表。然而,就实际情况而言,一个月内完全没有报告的情况将极为罕见。
现在,我们可以用
CROSS JOIN
:现在,我们可以使用相关子查询来确定
errors
以及alerts
对于上面生成的表中的每一行。我们将从report_totals
table在哪里user_id
匹配项,并且报告月份小于或等于当前月份。为了errors
,子查询将如下所示:类似的子查询将用于确定
alerts
.最后,我们将把这个完整的结果集作为一个派生表,并进行
GROUP BY
,并计算SUM()
在alerts
以及errors
对于所有用户。最终(完整)查询如下所示:
db fiddle视图
结果:
编辑1:第一次优化
我不喜欢使用两个相似的相关子查询来获取
errors
以及alerts
分开。但是,这是mysql的一个限制,它不允许在这样的子查询中有多个操作数。因此,作为黑客,我们可以Concat()
使用一些分隔符,例如|
. 这将把要使用的子查询减少为一个。现在,在最外层的查询中,我们可以使用如下字符串函数
Substring_Index()
以及Cast()
以数字形式提取相应的值Sum()
相应的操作。查询#2
结果
db fiddle视图
qyswt5oh2#
我认为以下查询将起作用: