Databases
StackOverflow2013
Postsdbo
POMySQL: Initialize a summary table with placeholders for non-existent data
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMySQL: Initialize a summary table with placeholders for non-existent data
    primarykey
    data
    text
    <p>I'll try to give this as generically as I can so it's reusable.</p> <p>I am running a site with a fairly large MySQL database which has grown to need some summary/rollup tables initialized. For the example's sake, let's say it's soccer statistics. Since I handle multiple soccer leagues in the same database, many of them play games of different lengths - for instance, indoor soccer leagues play four quarters while most outdoor leagues play halves.</p> <p>I have three tables important to this exercise. I've redacted all of the fields that I don't consider significant to the answer I'm looking for.</p> <pre><code>GAME `game`.id `game`.home_team_id `game`.away_team_id `game`.number_of_periods GOAL // Records for each goal scored in the game `goal`.id `goal`.game_id `goal`.team_id `goal`.period_number `goal`.player_id `goal`.assist_player_id PERIOD_SUMMARY `period`.id `period`.game_id `period`.team_id `period`.number `period`.goals_scored </code></pre> <p>Ultimately I should have records for EVERY period played in the period summary table, regardless of whether or not a goal was scored. This table only needs to be initialized once, as it's fairly easy to add the appropriate zero-filled records via a trigger on game creation and fire on insert/update requests to update the period_summary table.</p> <p>It is also fairly easy for me to group all of the goals and initialize the period summary table with the SUM(), what I am having a bit of trouble figuring out an efficient way to "fill" any periods that don't have a goal scored with a 0.</p> <p>What I am trying to figure out is if it's easier/more efficient to:</p> <ol> <li>Write the trigger and prefill the entire period_summary table with 0-filled values, then run the query I already know to update the appropriate records for periods in which goals were scored.</li> <li>Use some other method (perhaps a temporary stored procedure?) that will only 0-fill records where there is not a match in the goals table.</li> </ol>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USAvatarKava
    1. USAvatarKava
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POMySQL: Initialize a summary table with placeholders for non-existent data
      1. USAvatarKava
      1. VTBountyStart
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload