窗口功能的MySQL解决方法 [英] Mysql workaround for window functions

问题描述

我有一个事件表，其中包含以下字段:

I have an event table that has the following fields:

event_id
event_type 
event_time

给出一个持续时间D和一个数字k，我需要在持续时间D的任何相对时间窗口中，对具有多个K事件的所有event_type进行计数.这基本上需要针对每个事件的滑动窗口.例如，我希望所有在10分钟内具有超过5个事件活动的event_type.

Given a duration D and a number k, I need a count of all the event_type's that had more than K events in any relative time window of duration D. This basically requires a sliding window with respect to each event. For example, I want all the event_type's that had activity of more than 5 events in any 10 minute duration.

我不确定在没有窗口功能的情况下如何解决此问题.

I am not sure how to work around this without window functions.

(我在mysql 5.6上.我在说的是一百万行以下的数据集.)

(I am on mysql 5.6. I am talking about a dataset of under 1 million rows.)

推荐答案

MySQL不支持窗口功能，但是您可以在SELECT列表中使用相关子查询来精确检索一列:

MySQL has no window function support, but you can use a correlated subqueries in the SELECT list to retrieve exactly one column:

SELECT
  event_id,
  event_type, 
  event_time,
  (SELECT COUNT(*) FROM events EC WHERE EC.event_type = E.event_type AND EC.event_time > E.event_time) AS subsequent_event_count
FROM
  events E
WHERE ...

执行EXPLAIN.就执行逻辑而言，这与SQL Server中的CROSS APPLY相同.

Do EXPLAIN it. This is kinda the same in terms of execution logic as the CROSS APPLY in SQL Server.

另一种方法是自我联接:

Another approach is a self join:

SELECT
  E.event_id,
  E.event_type,
  E.event_time,
  COUNT(EC.event_id) AS subsequent_event_count
FROM
  events E
  LEFT JOIN events EC
    ON E.event_type = EC.event_type AND E.event_type < EC.event_type
GROUP BY
  E.event_id,
  E.event_type,
  E.event_time

请同时测试两种方法的性能.

Do test both approaches for performance.

您可以进行更多的创意连接，例如

You can do much more creative joins, like

EC.event_time > E.event_time AND EC.event_time < E.event_time + INTERVAL 1 DAY

这篇关于窗口功能的MySQL解决方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！