Query the top 30 URIs with largest PVs per minute

Use the date_format function to convert the format of the time field to minutes and use the group by clause to aggregate and calculate the number of page views (PVs) per minute. Use the calculated result as a subquery, and then use the rank() over(partition by time order by count desc) function to aggregate the data by time and sort the data by count. Filter out the top 30 URIs with largest PVs based on the condition rnk <= 30.

* |
select
  *
from(
    select
      time,
      request_uri,
      count,
      rank() over(
        partition by time
        order by
          count desc
      ) as rnk
    from(
        select
          date_format(__time__, '%Y-%m-%d %H:%i') as time,
          request_uri,
          count(*) as count
        from
          log
        group by
          time,
          request_uri
      )
  )
where
  rnk <= 30
order by
  time,
  count desc
limit
  1000

SQLSample query result: