Search code examples
mysqlaggregate-functionsolap

statistical aggregation over mysql table

In my application I have a table products_downloads where I'm storing information about each product download.

This is a structure of this table:

enter image description here

In my application I have a lot of product TOPs where I'm showing last 10 products sorted by total downloads(all time) and another one sorted by last week download count(sql query execution time minus 7 days)

For example at my homepage I need to show TOP product downloads for all products on my website. At some product category I need to show product downloads TOP with a products only related to this category and all descendant categories.

I feel that I need to perform some periodic data aggregation(over products_downloads table) but I don't understand what is a correct solution for this task right now.. Maybe OLAP ? Please advise.

Solution

  • This sort of thing uses the following basic pattern

     SELECT COUNT(*) hit_count,
        product_id
  FROM products_downloads 
  WHERE whatever-condition
  GROUP BY product_id
  ORDER BY COUNT(*) DESC
  LIMIT 10

    The aggregation you want is straightforward: COUNT(*). The trick is to pick suitable WHERE conditions.