Currently I use plain SQL statements to create reports which, for instance, show me the number of logins of all users on a 5 minutes scale.

One of those statements looks similar to this:

INSERT INTO report_item 
    (
        SELECT '2009-05-23 02:30:00+0200'::timestamp WITH time zone + (s.a || ' minute')::interval AS timestamp, 
               COUNT(source."created_on") AS value
        FROM generate_series(0,595,5) AS s(a)
 
        LEFT JOIN 
          (SELECT "created_on" FROM "session") AS source
          ON source."created_on" >= '2009-05-23 02:30:00+0200'::timestamp WITH time zone + (s.a  || ' minute')::interval 
             AND source."created_on" < '2009-05-23 02:30:00+0200'::timestamp WITH time zone + (s.a  + 5 || ' minute')::interval
 
        GROUP BY a
    );

This gives me the number of logins (or new sessions) per 5 minute time slot. Now it’s pretty easy to read the data from report_item and create a graph or do other nice things with it.

I was thinking about a more generic way to do these reports so that the user can choose, what kind of report he wants.

So I started to create a result class Report with the following columns:

  • group_by
    The column which is used to group rows together. This is always a timestamp column, e.g. created_on
  • interval_type
    Group by minutes, hours, days, weeks etc. (defaults to minutes)
  • interval_length
    Group by that amount of interval_type (defaults to 5)
  • aggregate_by
    Which SQL function should be used to aggregate data in a time slot (defaults to COUNT)
  • aggregate
    Which column should be aggregated
  • query
    Which data should be aggregated? This accepts a DBIC result set or a plain SQL statement

After you created a report in that table you call $report->create_report on that row. This will execute the query above with the correct values filled in and the result will be stored in a table result_item, which has a foreign key report_id to the report.

It does a decent job so far and I was wondering if anyone else did something like this before?

If anyone is interested in the code I can make a dist and upload it to github. Since the SQL is Postgres only I don’t want to push it to the CPAN.