sql - Mysql workaround for window functions

Question

I have an event table that has the following fields:

event_id
event_type 
event_time

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.

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

Answer 1

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 ...

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