trying to create column with season-to-date averages of values of another column in same table using MySQL

Question

I'm struggling with determining the MySQL code to create a column (average park_factor) in my table "starting_pitcher_stats" that I'd like to contain the season-to-date in-season average values of the values in another column (park_factor). I'd like this in-season average to be grouped by pitcher and by date.

Ideally, the table would look like this:

    pitcher     park_fac   avg_park_fac       date
    aased001    94         94           1977-07-31
    aased001    100        97           1977-08-06
    aased001    108        100.666      1977-08-11
    aased001    108        102.5        1977-08-16
    aased001    96         101.2        1977-08-21
    aased001    108        102.33       1977-08-26
    aased001    108        103.14       1977-08-31
    aased001    104        103.25       1977-09-05
    aased001    108        103.77       1977-09-10
    aased001    92         102.6        1977-09-16
    aased001    106        102.9        1977-09-22
    aased001    108        103.33       1977-09-27

The code I'm using is:

SELECT Starting_Pitcher, full_park_factor, AVG(full_park_factor), Game_Date
FROM starting_pitcher_stats 
GROUP BY Starting_Pitcher, Game_Date, Game_Number

...and a sample of the resulting table looks like this:

pitcher     park_fac   avg_park_fac  date
aased001    94         94.0000      1977-07-31
aased001    100        100.0000     1977-08-06
aased001    108        108.0000     1977-08-11
aased001    108        108.0000     1977-08-16
aased001    96         96.0000      1977-08-21
aased001    108        108.0000     1977-08-26
aased001    108        108.0000     1977-08-31
aased001    104        104.0000     1977-09-05
aased001    108        108.0000     1977-09-10
aased001    92         92.0000      1977-09-16
aased001    106        106.0000     1977-09-22
aased001    108        108.0000     1977-09-27

Can someone help please?

Thank you in advance for help with this. Lee

Answer 1

You need to join your table on all previous results for the same pitcher from the same table.

I'm not quite sure how you define your season, but assuming it's by calendar year, the following query produces the desired output.

SELECT
    a.Starting_Pitcher, a.full_park_factor,
    AVG(b.full_park_factor), a.Game_Date, a.Game_Number
FROM starting_pitcher_stats a
INNER JOIN starting_pitcher_stats b
    ON a.Starting_Pitcher = b.Starting_Pitcher
    AND (b.Game_Date < a.Game_Date OR
         (b.Game_Date = a.Game_Date AND b.Game_Number <= a.Game_Number))
    AND YEAR(b.Game_Date) = YEAR(a.Game_Date)
GROUP BY a.Starting_Pitcher, a.Game_Date, a.Game_Number;

You seem to want to update a column in your table with the result of this calculation. This can be achieved with real-time updates with a trigger which updates the column whenever you insert or update existing data or using a view.

CREATE VIEW starting_pitcher_stats_with_average AS
SELECT
    a.Starting_Pitcher, a.full_park_factor,
    AVG(b.full_park_factor), a.Game_Date, a.Game_Number
FROM starting_pitcher_stats a
INNER JOIN starting_pitcher_stats b
    ON a.Starting_Pitcher = b.Starting_Pitcher
    AND (b.Game_Date < a.Game_Date OR
         (b.Game_Date = a.Game_Date AND b.Game_Number <= a.Game_Number))
    AND YEAR(b.Game_Date) = YEAR(a.Game_Date)
GROUP BY a.Starting_Pitcher, a.Game_Date, a.Game_Number;

In your own answer you create a procedure to update the average column for all records in the table in one go, so perhaps you don't want to have the column update as you insert data, but simply be able to add the average for all rows on demand. In this case, you can write an UPDATE statement which includes the SELECT query above as a subquery. Because MySQL cannot use the same table for the UPDATE and the subquery, you have to wrap the subquery in another SELECT so that MySQL generates a temporary table from your results.

UPDATE starting_pitcher_stats c
SET c.std_F_parkfactor = (
    SELECT d.std_F_parkfactor FROM (
        SELECT
            a.Starting_Pitcher,
            AVG(b.full_park_factor) std_F_parkfactor,
            a.Game_Date, a.Game_Number
        FROM starting_pitcher_stats a
        INNER JOIN starting_pitcher_stats b
            ON a.Starting_Pitcher = b.Starting_Pitcher
            AND (b.Game_Date < a.Game_Date OR
                 (b.Game_Date = a.Game_Date
                  AND b.Game_Number <= a.Game_Number))
            AND YEAR(b.Game_Date) = YEAR(a.Game_Date)
        GROUP BY a.Starting_Pitcher, a.Game_Date, a.Game_Number
    ) d
    WHERE c.Starting_Pitcher = d.Starting_Pitcher
    AND c.Game_Date = d.Game_Date
    AND c.Game_Number = d.Game_Number
);

Answer 2

UPDATE: Here's a way to store the season-to-date (in-season) park-factor average of a given season in a column, which in this case is the average of another column's values in the same table using a stored procedure. It essentially calculates the average by dividing by the row_number which corresponds to the number of rows of values that have been looped through in calculating this variable. This approach works if you have already-collected data that you want to update in one go or seldomly, but as implied by Matt Raines, may have to be run more frequently than his suggested approach(es). If the table will get updated frequently with data from successive days' games' results at least once daily, I think it will be less labor-intensive to use his approach(es). Please let me know what can be eliminated:

DROP PROCEDURE IF EXISTS std_park_factor_avg;
DELIMITER $$
CREATE PROCEDURE std_park_factor_avg()
BEGIN
    DECLARE pit_id CHAR(10);
    DECLARE lgID CHAR (2); 
    DECLARE YEARID INT;
    DECLARE gdate DATE;
    DECLARE seq INT;
    DECLARE F_park_factor INT;
    DECLARE RNUMBER INT;
    DECLARE accum_F_parkfactor REAL;
    DECLARE accum_row_number INT;
    DECLARE accum_avg_F_parkfactor REAL;
    DECLARE prev_year YEAR(4);
    DECLARE end_of_cursor BOOLEAN;

    DECLARE no_table CONDITION FOR SQLSTATE '42S02';

    DECLARE c1 CURSOR FOR
      SELECT Starting_Pitcher, lg_ID, YEAR_ID, Game_Date, Game_Number, full_park_factor, ROW_NUMBER 
        FROM starting_pitcher_stats
        GROUP BY Starting_Pitcher, lg_ID, YEAR_ID, Game_Date, Game_Number;

    DECLARE CONTINUE HANDLER FOR NOT FOUND
      SET end_of_cursor := TRUE;

    SET end_of_cursor := FALSE;  -- reset
    SET prev_year := 0;          -- reset control-break

    OPEN c1;

    fetch_loop: LOOP
      FETCH c1 INTO pit_id, lgID, YEARID, gdate,seq, F_park_factor, RNUMBER;
      IF end_of_cursor THEN
        LEAVE fetch_loop;
      END IF;

      -- check control-break conditions
      IF YEAR(gdate) != prev_year THEN
       SET accum_F_parkfactor := 0.0;
       SET RNUMBER:= 1.0;
       SET accum_avg_F_parkfactor := 0.0;
        SET prev_year := YEAR(gdate);
      END IF;

     SET accum_F_parkfactor := accum_F_parkfactor + F_park_factor;
     SET accum_avg_F_parkfactor := accum_F_parkfactor/RNUMBER;

      UPDATE starting_pitcher_stats
        SET std_F_parkfactor =accum_avg_F_parkfactor
        WHERE Starting_Pitcher = pit_id
          AND lg_ID = lgID 
          AND YEAR_ID = YEARID
          AND Game_Date = gdate
          AND Game_Number = seq;

    END LOOP;
    CLOSE c1;
  END
$$

DELIMITER ;