CODEWITHSUNDEEP
mysqlsqljoinsql-update
user1347026
user1347026

Reputation: 584

Update mySQL Table with Results of Aggregate Function

I have an aggregate table and a detail table that holds scores for many different games. The detail table is millions of records and the aggregate table just has each game and the average score for a different number of days.

Example:
Aggregate Table: 

| ID | Name  | AVG_SCORE_7_DAYS | AVG_SCORE_30_DAYS |
-----------------------------------------------------
| 1  | Game 1| 10.3             | 20.3              |
| 2  | Game 2| 14.3             | 26.3              |

Detail Table:

| ID | Name  | Date                | Score |
--------------------------------------------
| 1  | Game 1| 2015-07-12 01:00:00 | 20    |
| 2  | Game 2| 2015-07-12 01:00:00 | 26    |
| 3  | Game 1| 2015-07-12 01:00:00 | 14    |
| 4  | Game 2| 2015-07-12 01:00:00 | 9     |

I use code like the following to update my aggregate table nightly via a stored procedure: 

UPDATE `aggregate` aggr
INNER JOIN
(
    SELECT game_name, avg(score) AS avg_score
    FROM `detail`
    WHERE `date` BETWEEN (CURDATE() + INTERVAL -7 DAY) AND CURDATE()
    GROUP BY `game_name`
) detail7 ON aggr.`game_name` = detail7.`game_name`
INNER JOIN
(
    SELECT game_name, avg(score) AS avg_score
    FROM `detail`
    WHERE `date` BETWEEN (CURDATE() + INTERVAL -30 DAY) AND CURDATE()
    GROUP BY `game_name`
) detail30 ON aggr.`game_name` = detail30.`game_name`

The problem I have is that if there are no scores for some of the games in 7 days, 30 days, etc then those subqueries return no records and so if any one of them fails then none of the columns for that game get updated (due to inner join). Is there a way I can write my query to update the other columns even if the result of a subquery doesn't return any results?

Upvotes: 0

Views: 396

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

Use an outer join. You can also simplify the logic, so only one aggregation is needed:

UPDATE `aggregate` aggr LEFT JOIN
        (SELECT game_name,
                avg(case when `date` BETWEEN (CURDATE() + INTERVAL -7 DAY) AND CURDATE() then score end) AS avg_score_07,
                avg(case when `date` BETWEEN (CURDATE() + INTERVAL -30 DAY) AND CURDATE() then score end) AS avg_score_30
         FROM `detail`
         WHERE `date` BETWEEN (CURDATE() + INTERVAL -30 DAY) AND CURDATE()
         GROUP BY `game_name`
        ) detail
        ON aggr.`game_name` = detail.`game_name`
    SET . . . ;

Upvotes: 2

Related Questions