mysql - 如何添加在同一表中的另一列上递增的列？

Question

我有一个表，其中有一列名为“IP”，我想在同一个表“YTD_IP”中创建另一列，它会给出“IP”(投球局数)的累计年初至今总和一年中的任何给定比赛反射(reflect)了同一赛季之前的比赛(行/事件)中投球局数的总和。一旦年份发生变化，我希望总和再次从 0 开始。

这是“PIT_ID”、“YEAR_ID”、“IP”列的数据示例，以及我希望看到的“YTD_IP”列:

Pit_ID  YEAR_ID  IP      YTD_IP
aased001      1977  9.0000    9
aased001      1977  9.0000    18
aased001      1977  7.0000    25
aased001      1977  5.0000    30
aased001      1977  6.3333    36.3333
aased001      1977  6.3333    42.6666
aased001      1977  7.0000    49.6666
aased001      1977  6.0000    55.6666
aased001      1977  9.0000    64.6666
aased001      1977  9.0000    73.6666
aased001      1977  5.0000    78.6666
aased001      1977  6.3333    84.9999
aased001      1977  7.3333    92.3333
aased001      1978  4.3333     4.3333
aased001      1978  7.0000    11.3333
aased001      1978  6.3333    17.6666
aased001      1978  3.3333    20.9999

我整理了以下代码:

ALTER TABLE starting_pitcher_game_log ADD COLUMN YTD_IP VARCHAR(255);
    SET YTD_IP:= 0;
    SELECT a.IP,  b.YEAR_ID
      IF(@prevYEAR_ID != YEAR_ID,IP,@YTD_IP+IP) AS IP,
      @prevYEAR_ID=YEAR_ID
    FROM starting_pitcher_game_log

但这是不对的，因为它得到以下错误:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF(@prevYEAR_ID != YEAR_ID,IP,@YTD_IP+IP) AS IP,
          @prevYEAR_ID=YEAR_ID
' at line 2

有没有办法确保它适应 future 将玩的游戏？

任何帮助都会大有帮助。

screen shot of full table

嗨， Darwin ，

您能否分享可用于临时调用列的代码以及用于可创建累积总和字段的永久列的代码？特别是，我想在名为“YTD_IP”的表中创建另一列，该列将给出从赛季开始到该赛季任何给定比赛的“IP”(投球局数)的累计年初至今总和反射(reflect)给定投手在给定比赛之前的比赛(行/事件)中投出的局数 + 该比赛中投出的局数的总和。一旦季节发生变化，我希望总和再次从 0 开始。

预先感谢您的帮助。

Answer 1

我最好的建议是，不要这样做。存储可以从数据库中的其他信息派生的信息通常被认为是非常糟糕的设计，并且尝试依赖于数据库中的行的顺序肯定会导致疯狂。

这是标准化表格的第一步:

-- Table: teams

-- DROP TABLE teams;

CREATE TABLE teams
(
  team_id character(3) primary key,
  team_name varchar(255),
  team_city varchar(255)
) engine=innodb;

-- Table: starting_pitchers_game_log

-- DROP TABLE starting_pitchers_game_log;

CREATE TABLE starting_pitchers_game_log
(
  pitcher_id character(10) NOT NULL,
  game_date date NOT NULL,
  opposing_team character(3),
  game_seq integer NOT NULL,
  outcome character(1),
  innings_pitched real,
  bfp integer,
  hits integer,
  runs integer,
  errors integer,
  homeruns integer,
  bb integer,
  k integer,
  ibb integer,
  hbp integer,
  wp integer,
  balks integer,
  CONSTRAINT starting_pitcher_log_pk
      PRIMARY KEY (pitcher_id , game_date , game_seq ),
  CONSTRAINT team_fk FOREIGN KEY (opposing_team)
      REFERENCES teams (team_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
) engine=innodb;

(我不关注棒球，所以我只能猜测一些列名称。)请注意 year_id、month_id 和 day_id 列消失了，因为这些值可以从 game_date 列重新创建，正如我在评论中指出的那样。您的 game_id 列也不见了；这可以通过连接 opposing_team、game_date 和 game_seq 来重新创建(我认为这是为了解释双标题等)我也将 W 和 L 转换为一个列，用于保存值“W”(赢)、“L”(输)和“T”(平)。

teams 表为 3 个字符的团队 ID 提供了一个查找表。它可以扩展以保存您想要的任何其他团队数据。 (请注意，它旨在描述团队本身；团队事件将放在另一个表中。)

要回答有关“约束”子句的问题，第一个子句(CONSTRAINT starting_pitcher_log_pk 及其下方的缩进线)指定这三列的串联用作每个子句的主要唯一标识符表中的行。第二个(CONSTRAINT team_fk FOREIGN KEY (opposing_team) 及其下方的缩进线)意味着要将值放置在 opposing_team 列中，它必须已经存在于 teams.team_id 列中；您无法与不存在的球队比赛。

现在开始实际回答您的原始问题。我能想到的关于 MySQL 的最佳解决方案是一个临时表和一个存储过程，如下所示:

-- Table: ip_subtotal

-- DROP TABLE ip_subtotal;

CREATE TABLE ip_subtotal
(
  pitcher_id char(10) NOT NULL,
  game_date date NOT NULL,
  game_seq int(11) NOT NULL,
  innings_pitched double,
  ip_total double DEFAULT '0.0',
  CONSTRAINT ip_subtotal_pk
      PRIMARY KEY (pitcher_id , game_date , game_seq )
) ENGINE=InnoDB;

存储过程:

------------------------------------------------------------------------------    --
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE PROCEDURE accumulate_innings()
BEGIN
    DECLARE pit_id CHAR(10);
    DECLARE gdate DATE;
    DECLARE seq INT;
    DECLARE in_pit REAL;
    DECLARE accum REAL;
    DECLARE prev_year YEAR(4);
    DECLARE end_of_cursor BOOLEAN;

    DECLARE c1 CURSOR FOR
        SELECT pitcher_id, game_date, game_seq, innings_pitched
            FROM ip_subtotal
            ORDER BY pitcher_id, game_date, game_seq;

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

    TRUNCATE TABLE ip_subtotal;
    INSERT INTO ip_subtotal
        SELECT pitcher_id, game_date, game_seq, innings_pitched, 0.0
            FROM starting_pitchers_game_log;

    SET prev_year := 0;
    OPEN c1;

    fetch_loop: LOOP
        FETCH c1 INTO pit_id, gdate, seq, in_pit;
        IF end_of_cursor THEN
            LEAVE fetch_loop;
        END IF;
        IF YEAR(gdate) != prev_year THEN
            SET accum := 0.0;
            SET prev_year := YEAR(gdate);
        END IF;
        SET accum := accum + in_pit;
        UPDATE ip_subtotal
            SET ip_total = accum
            WHERE pitcher_id = pit_id
              AND game_date = gdate
              AND game_seq = seq;
    END LOOP;
    CLOSE c1;
END

此过程清除表 ip_subtotal，从主表填充它，然后汇总投球局数的总计。它还使用简单的控制中断在年初重置累加器。通过执行运行该过程后

CALL accumulate_innings();

您可以根据需要查询 ip_subtotal 表或将其连接回 starting_pitchers_game_log 表。

该程序还可以扩展以接受开始和结束日期；我将其作为练习留给读者。

希望这对您有所帮助；这很有趣，迫使我学习了一点 MySQL。