Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 3 down vote favorite
1

I have a parent row and then many child rows, and I must aggregate statistics from these child rows.

For a more concrete example let's imagine I have one round of golf (parent row) and then child rows with the score of each hole. I need to aggregate statistics on these rows, like number of birdies, pars, bogies, putts and so forth. And then put these numbers in a round_detail table.

What would be the best solution to this?

I'm using postgres if it matters, but this is probably just a general sql propblem.

EDIT: As requested http://sqlfiddle.com/#!1/6549c

share|improve this question
2  
Please post the CREATE TABLE for the tables involved and some sample data. Ideally as a runnable exampel on sqlfiddle.com – a_horse_with_no_name Feb 7 at 16:11
Have edited with sql statements – Iceman Feb 7 at 18:36
I want to clarify that I know how to group by and all that stuff, what I'm looking for is a best practice solution to do this efficiently. It could be done with a trigger, that updates a round_detail table with the necessary stats, or maybe a stored proc. Alternative solutions, like using mongodb for the stats are also welcome. But it must run on Linux. – Iceman Feb 7 at 18:38
So you aren't really looking for a query that returns the results? – a_horse_with_no_name Feb 7 at 18:42
Not necessarily, it could be done that way or done with a trigger. I'm just trying to figure out what would be the best way. I thought doing it just once would provide better performance later, because rounds are rarely edited afterwards. – Iceman Feb 7 at 18:45

1 Answer

up vote 4 down vote accepted

The concept you consider is known as materialized view. Since you comment:

rounds are rarely edited afterwards

.. I think this should be good idea for your case. Some RDBMS' have distinct objects for that. In Postgres it's just a matter of writing the results from a view or SRF (set returning function) to a table. You can find a basic code example for refreshing MVs in the manual here.

Refer to this related answer on SO for the more sophisticated regime I use for that purpose.

Your query to (re-)create a materialized view could look something like this:

SELECT c.name AS course
      ,r.user_id
      ,s.round_id
      ,COUNT(NULLIF(s.strokes = h.par, FALSE))     AS pars
      ,COUNT(NULLIF(s.strokes = h.par - 1, FALSE)) AS birdies
      -- more columns ..
FROM   score  s
JOIN   round  r ON r.id = s.round_id
JOIN   course c ON c.id = r.course_id
JOIN   hole   h ON h.id = s.hole_id
GROUP  BY 1,2,3

I simplified your test case and added the query: ->sqlfiddle.

share|improve this answer
3  
I wish SQL Server had a true Boolean data type like this instead of just Boolean-like bit values! So you could for example do WHERE IsNumeric(a) instead of WHERE IsNumeric(a) = 1. – ErikE Feb 9 at 6:29
Thanks for this great answer. – Iceman Feb 9 at 19:51
Have been playing around with this a bit, and this is great stuff, thanks again. – Iceman Feb 9 at 20:55

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.