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

Question

我有一个表中有一个名为"IP“的列，我想在同一个表"YTD_IP”中创建另一列，它将给出一年中任何给定游戏的累积年迄今为止的"IP“之和，反映在同一季之前的游戏(行/事件)中的投注数之和。一旦一年有变化，我想把总数再从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

是否有办法确保它适应未来的游戏将被玩？

任何帮助都会有很大帮助。

全表屏幕截图

你好达尔文，

您能否共享可用于临时调用列的代码以及可创建累积和字段的永久列的代码？特别是，我想在一个名为"YTD_IP“的表格中创建另一个专栏，它将给出从赛季开始到那个赛季中任何一场比赛的累积年迄今为止的"IP”的总和，反映出在给定的游戏(行/事件)中，一个投手在游戏之前投出的局数之和+在那个游戏中投的局。一旦季节发生变化，我希望从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来重新创建这个列(我认为这是为了说明双头&c)。我还将W和L转换为一个列，用于保存值"W“(win)、"L”(丢失)和"T“(tie)。

teams表为3 char团队ids提供了一个查找表。它可以扩展为保存任何其他您想要的团队数据。(注意，它是用来描述团队本身的；团队活动将放在另一个表中。)

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

现在实际回答你原来的问题。我可以在MySQL上找到的最佳解决方案是一个scratch表和一个存储过程，如下所示：

-- 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。