Multidimensional aggregates Suppose one wanted to cache simple aggregate data over a table or grouped parts of a table, one could simply make a table with the aggregated results. If the original table is only appended to, it is simple to keep the aggregate-table up to date (let's assume an aggregate like SUM()).

However, I am wondering if there is a method to keep/cache aggregate data, over different attributes from the original table.

For example.

games(username text, win bool, t timestamp);

--- attribute, grouping
aggregate_games(wins int, username text);

SELECT * FROM aggregate_games
WHERE username=&lt;...&gt;


But it gets more complicated when I want to keep the aggregate data for many different possible attributes. For example.

games(win bool, username text, map text, revision int, mode int, team int, server inet);

--- attribute, group-combinations
aggregate_games(wins int, username text, map text, revision int, mode int, team int, server inet);


Ultimately, what I want to be able to do is something like:

SELECT sum(wins) 
WHERE username={*,{username}}
  AND map={*,{mapname}}
  AND revision={*,{revision}}
  AND mode={*,{mode}}
  AND team={*,{team}}
  AND server={*,{server}}


Is there a good way to do this without:


re-aggregating all the data
without keeping an insane unscalable number of combinations of aggregate data around




To extend it a bit further, I want to actually keep the aggregate data itself over time, so essentially my aggregate data would have a timestamp (and so, for example, it could be graphed over time).
MS Analysis Services (Multidimensional)'s [almost] whole reason for being is to abstract away aggregations so you can simply query tour OLAP data without having to worry about how the aggregate value is being retrieved.  In fact, in Analysis Services Multidimensional, you point and click to create your aggregation combinations.

I'm not sure what database server you're running, but if you're running SQL Server 2014 you also have the ability to build a clustered columnstore index on your fact table.  Doing this will, in a most peoples' cases, essentially eliminate the need for you to perform aggregations entirely.  I'd definitely look into that as an option if you're on SQL Server and your data and resources are a good fit.

In that similar vein, if you're not running SQL Server, there are a lot of columnar databases out there which service OLAP workloads quite well, such as Tableau ($$$).  I'm not sure what the open-source space has to offer, but that's worth checking out as well.