MySQL limiting the number of records grouped within a time range

Question

I have a view that is used to generate reports every few hours. The view groups records into 5 minute intervals for display purposes. Due to the volume of data I want to restrict the number of records in each 5 minute interval.

For a report that is based on over an hour there will be 12 groups and I want to restrict the data in each group to only 100 records per group.

Ive tried using HAVING with a count on the time interval but its not restricting the total per group

Whats the best way to approach this

My view

SELECT 
    FROM_UNIXTIME((FLOOR((UNIX_TIMESTAMP(`r`.`created_at`) / 300)) * 300)) AS `time`,
    `u`.`picture_url` ,
    `u`.`username`,
    `rc`.`desc` ,
    `r`.`report_uid` 
FROM
    `records` `r`
    JOIN `record_content` `rc` ON `rc`.`record_id` = `r`.`id`
    JOIN `users` `u` ON `r`.`id` = `u`.`author_id`

GROUP BY `time` , `r`.`report_uid` , `u`.`picture_url` , `u`.`username` , `rc`.`desc`

Ive tried using LIMIT and HAVING after the group by but its not doing what I need. Ive been researching ways to achieve it with various joins but I haven't figure it out — briansexton, Feb 14 '20 at 18:07
Can you just do a row_number where row_number < 100 partitioning by your groupings — Kevin Johnson, Feb 14 '20 at 18:34
Enumerate records over partition by this 5min. intervals using ROW_NUMBER emulation with UDV, the select only those records which' record number in a group do not exceed 100. See, foir example, [this answer](https://stackoverflow.com/questions/854128/find-duplicate-records-in-mysql/854195#854195). — Akina, Feb 14 '20 at 19:25
I could be mistaken, but I thought MySQL 5.7 did not have windowing functions like ROW_NUMBER(). — Uueerdo, Feb 14 '20 at 19:41
@briansexton . . . MySQL imposes functionality restrictions on views. This is pretty hard to do in MySQL. And even harder in MySQL pre-v8.0. — Gordon Linoff, Feb 14 '20 at 19:42
hmm ok thanks for the feedback guys. I think ill assess my options on the back end code side instead — briansexton, Feb 14 '20 at 20:18

MySQL limiting the number of records grouped within a time range

0 Answers0