MySQL - 从事件列表聚合数据，查询优化

Question

我希望有人能够阅读这个场景和我创建的查询，并建议一种替代方法来获得相同的结果。我想查询中的某种条件逻辑会很有用 -

情况是，我只处理 1 个表“rto_events”，其中记录了详细说明“票证”在“解析器”或队列之间移动时所经历的更改。当创建票证、更改解析器或关闭票证时，系统会在此处存储一条详细说明更改的记录。 Incidentnbr 是票证的唯一标识符，但由于它们在此表中经历了多次更改，所以我还使用自动编号 ID。

其中的数据示例:

|  id  |   date_of_event   |  incidentnbr  |  event_type      | new_resovler_group  |
| 110  |   04/01/2014      |  1234000      |  Opened          | Bob's Queue         |
| 111  |   04/01/2014      |  1235323      |  Opened          | Jim's Queue         |
| 112  |   04/02/2014      |  1234000      |  Changed Resolver| Jim's Queue         |
| 113  |   04/02/2014      |  1235323      |  Closed          | Jim's Queue         |

表结构如下:

CREATE TABLE `rto_events` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`Date_of_event` datetime NOT NULL,
`IncidentNbr` int(11) NOT NULL,
`Event_Type` varchar(255) NOT NULL,
`New_Resolver_Group` varchar(255) NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB 

因此，我试图生成一份报告，显示给定日期每个队列的情况，这意味着要考虑 a.) 票证何时打开，b.) 票证何时关闭，以及 c.) 票证是什么时候该票证在给定日期之前的最新队列名称。

这是我想出的，它产生了所需的输出。我担心的是它有点慢，其中只有几千条记录，而且这个表可能会变得非常大，它有点丑陋和不方便，并且可能有一种更简单的方法来做到这一点。

我担心我已经对 SQL 犯下了罪行，我希望有人能给我指出更好的方向。任何特定的功能、新布局或方法的推荐、使用什么类型的条件逻辑——任何能让我开始优化它的东西我都会非常感激，我不会要求别人为我重写它。

首先，创建所有已打开票证和所有已关闭票证的集合，并添加“IsClosed”字段，然后将两者合并在一起:

(SELECT incidentnbr FROM rto_events 
        WHERE event_type = "Opened" AND date_of_event <= '$myDate') AS allcreated
LEFT JOIN 
(Select incidentnbr, TRUE AS isclosed FROM rto_events 
        WHERE event_type = "Closed" AND date_of_event <= '$myDate') AS allclosed
ON allcreated.incidentnbr = allclosed.incidentnbr

围绕这一点，我们从所有字段中选择没有 isclose 标签的事件，因此我们有一个在给定日期之前创建的所有未处理票证的列表。

(SELECT allcreated.incidentnbr FROM 
     #Above snippet is imbedded in here
WHERE isclosed IS NULL) AS allopened

现在，围绕这一点，我们提取关联的自动编号 ID(对事件进行排序以查找最新事件)和事件 nbr(因此我们可以立即对其进行分组，有效地将其折叠到为此创建的最新票证中)事件nbr)

(SELECT rto_events.id, allopened.incidentnbr FROM rto_events INNER JOIN
     #Above snippet imbedded here
ON rto_events.incidentnbr = allopened.incidentnbr 
WHERE rto_events.date_of_event <= '$myDate' 
ORDER BY id DESC) AS ordered

现在获取该结果并将其折叠到在给定日期打开的工单的给定日期之前的最新记录:

(SELECT ordered.id, ordered.incidentnbr, new_resolver_group FROM rto_events INNER JOIN 
    #Above snippet is embedded here
ON rto_events.id = ordered.id GROUP BY incidentnbr) as mostrecentrecord

现在我们有一个每个事件一条记录的列表，其中该记录是在给定日期之前创建的，在给定日期之前没有关闭，并且是截至给定日期(包括给定日期)但不是之后的最新记录。它看起来像——

id    |    incidentnbr    |   new_resolver_group
1001  |    1252000        |   Tom's Queue
3042  |    1352104        |   Bill's Queue
9181  |    1125412        |   Jim's Queue

所以剩下要做的最后一件事就是使用 COUNT() 函数聚合它。

SELECT '$myDate' as incidentdate, new_resolver_group, count(new_resolver_group) as openedtickets FROM 
    #Above segment inbedded here
GROUP BY incidentdate, new_resolver_group;

这就是所有 SQL - 已编译:

SELECT '$myDate' as incidentdate, new_resolver_group, count(new_resolver_group) as openedtickets FROM 
        #Most Recent Record for Open Tickets START#
        (SELECT ordered.id, ordered.incidentnbr, new_resolver_group FROM rto_events INNER JOIN 
                        #Ordered Open Ticket IDs START#
                        (SELECT rto_events.id, allopened.incidentnbr FROM rto_events INNER JOIN 
                                        #All Opened Ticket Incident Numbers START#
                                        (SELECT allcreated.incidentnbr FROM 
                                                        (SELECT incidentnbr FROM rto_events 
                                                                        WHERE event_type = "Opened" AND date_of_event <= '$myDate') AS allcreated
                                        LEFT JOIN 
                                                        (Select incidentnbr, TRUE AS isclosed FROM rto_events 
                                                                        WHERE event_type = "Closed" AND date_of_event <= '$myDate') AS allclosed
                                        ON allcreated.incidentnbr = allclosed.incidentnbr
                                        WHERE isclosed IS NULL) AS allopened
                                        #All Opened Ticket Incident Numbers END#
                        ON rto_events.incidentnbr = allopened.incidentnbr 
                        WHERE rto_events.date_of_event <= '$myDate' 
                        ORDER BY id DESC) AS ordered
                        #Ordered Open Ticket IDs END#
        ON rto_events.id = ordered.id GROUP BY incidentnbr) as mostrecentrecord
        #Most Recent Record for Open Tickets END#
GROUP BY incidentdate, new_resolver_group;

结果:

incidentdate | new_resolver_group | count_of
04/01/2014   | Bob's Queue        | 32
04/01/2014   | Jim's Qeueue       | 16

如果您有类似问题的经验并且可以提供一些指导，我将不胜感激。

Answer 1

回答您的问题:a.) 当票证被打开时，b.) 当票证被关闭时，以及 c.) 在给定日期之前该票证的最新队列名称是什么。

对于 A 和 B 有一个简单的解决方案，C 有点棘手(如果我正确理解了问题)。为了获得最佳结果(关于问题 C)，最好使用某种窗口函数(阅读有关使用 MySQL 模拟此类功能的信息，请从此处 - http://www.onlamp.com/pub/a/mysql/2007/03/29/emulating-analytic-aka-ranking-functions-with-mysql.html?page=2 )。

无论如何，这是我的解决方案(尽管没有问题 C 的正确窗口函数)。

SELECT DISTINCT
rt.incidentnbr
,dates.closed_date
,dates.open_date
,most_recent.new_resolver_group

FROM rto_events rt

LEFT JOIN (SELECT DISTINCT
    incidentnbr
    ,MAX(CASE WHEN event_type='Closed' THEN CAST(date_of_event AS DATE) END) closed_date -- given that there is only one closing date
    ,MAX(CASE WHEN event_type='Opened' THEN CAST(date_of_event AS DATE) END) open_date -- given that there is only one open date
    ,MAX(date_of_event) AS max_datetime
    FROM rto_events
    GROUP BY 1
    ) dates ON rt.incidentnbr=dates.incidentnbr

LEFT JOIN (SELECT DISTINCT
    incidentnbr
    ,date_of_event
    ,new_resolver_group
    FROM rto_events
    ) most_recent ON rt.incidentnbr=date.incidentnbr 
                  AND rt.date_of_event=dates.max_datetime

如果它不起作用，你可以尝试一下(只需删除第二个左连接部分和 new_resolver_group ，因为我有一种感觉，它可能不会像我预期的那样工作。在这种情况下，你应该阅读更多内容关于MySQL中的窗口函数。

在某些 SQL 语言中，这在查询末尾就足够了:QUALIFY (RANK() OVER(PARTITION BY rt.incidentnbr, CAST(date_of_event AS DATE) ORDER BY date_of_event DESC)=1