4

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

5

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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