Marcels

Based on Tom Tango's Marcels projection system.

MySQL

CREATE TABLE batting_pos
AS
SELECT b.playerID
        , CAST(b.yearID AS SIGNED) AS yearID
        , POW(0.9994,(2009-CAST(b.yearID AS SIGNED))*365.25) AS weight
        , (CASE WHEN m.birthMonth < 7
                THEN ( b.yearID - m.BirthYear )
                ELSE ( b.yearID - m.BirthYear - 1 ) END) AS Age
        , SUM(b.G) AS G
        , SUM(b.H) AS H
        , SUM(b.2B) AS 2B
        , SUM(b.3B) AS 3B
        , SUM(b.HR) AS HR
        , SUM(b.BB) AS BB
        , SUM(b.SO) AS SO
        , SUM(b.IBB) AS IBB
        , SUM(b.HBP) AS HBP
        , SUM(b.SB) AS SB
        , SUM(b.CS) AS CS
        , SUM(b.GIDP) AS GIDP
        , SUM(b.AB) AS AB
        , SUM(b.AB+b.BB+b.SH+b.SF+b.HBP) AS PA
FROM
        ( SELECT * FROM bdb.batting b
        LEFT JOIN ( SELECT playerID AS pplayerID, yearID AS pyearID, BFP
                FROM bdb.pitching p WHERE p.yearID > 2005 ) p
                ON b.playerID = p.pplayerID AND b.yearID = p.pyearID
                WHERE b.yearID > 2005 AND AB IS NOT NULL) b, bdb.master m
WHERE b.playerID = m.playerID
        AND ( (b.AB+b.BB+b.SH+b.SF+b.HBP) > (b.BFP) OR b.BFP IS NULL )
        AND (b.AB+b.BB+b.SH+b.SF+b.HBP) > 0
GROUP BY yearID, playerID;
 
CREATE VIEW average_pos
AS
SELECT yearID
        , POW(0.9994,(2009-CAST(b.yearID AS SIGNED))*365.25) AS weight
        , SUM(G) AS G
        , SUM(H) AS H
        , SUM(2B) AS 2B
        , SUM(3B) AS 3B
        , SUM(HR) AS HR
        , SUM(BB) AS BB
        , SUM(SO) AS SO
        , SUM(IBB) AS IBB
        , SUM(HBP) AS HBP
        , SUM(SB) AS SB
        , SUM(CS) AS CS
        , SUM(GIDP) AS GIDP
        , SUM(AB) AS AB
        , SUM(PA) AS PA
FROM batting_pos b
GROUP BY yearID;
 
CREATE VIEW player_league_average
AS
SELECT b.playerID
        , (SUM(b.weight*a.H*b.PA)) / (SUM(b.weight*b.PA)) AS H
        , (SUM(b.weight*a.2B*b.PA)) / (SUM(b.weight*b.PA)) AS 2B
        , (SUM(b.weight*a.3B*b.PA)) / (SUM(b.weight*b.PA)) AS 3B
        , (SUM(b.weight*a.HR*b.PA)) / (SUM(b.weight*b.PA)) AS HR
        , (SUM(b.weight*a.BB*b.PA)) / (SUM(b.weight*b.PA)) AS BB
        , (SUM(b.weight*a.SO*b.PA)) / (SUM(b.weight*b.PA)) AS SO
        , (SUM(b.weight*a.IBB*b.PA)) / (SUM(b.weight*b.PA)) AS IBB
        , (SUM(b.weight*a.HBP*b.PA)) / (SUM(b.weight*b.PA)) AS HBP
        , (SUM(b.weight*a.SB*b.PA)) / (SUM(b.weight*b.PA)) AS SB
        , (SUM(b.weight*a.CS*b.PA)) / (SUM(b.weight*b.PA)) AS CS
        , (SUM(b.weight*a.GIDP*b.PA)) / (SUM(b.weight*b.PA)) AS GIDP
        , (SUM(b.weight*a.AB*b.PA)) / (SUM(b.weight*b.PA)) AS AB
        , (SUM(b.weight*a.PA*b.PA)) / (SUM(b.weight*b.PA)) AS PA
FROM batting_pos b, average_pos a
WHERE b.yearID = a.yearID
GROUP BY playerID;
 
CREATE VIEW player_league_average_prorated
AS
SELECT playerID
        , ( H / PA * 214 ) AS H
        , ( 2B / PA * 214 ) AS 2B
        , ( 3B / PA * 214 ) AS 3B
        , ( HR / PA * 214 ) AS HR
        , ( BB / PA * 214 ) AS BB
        , ( SO / PA * 214 ) AS SO
        , ( GIDP / PA * 214 ) AS GIDP
        , ( IBB / PA * 214 ) AS IBB
        , ( HBP / PA * 214 ) AS HBP
        , ( SB / PA * 214 ) AS SB
        , ( CS / PA * 214 ) AS CS
        , ( AB / PA * 214 ) AS AB
        , 214 AS PA
FROM player_league_average;
 
CREATE VIEW player_Age_2009
AS
SELECT playerID
        , yearID
        , MAX(Age)
        , ( CASE
                WHEN (29 - MAX(CAST(Age AS SIGNED))+(2009-CAST(yearID AS SIGNED))) > 29
                THEN 1 + (29 - MAX(CAST(Age AS SIGNED))+(2009-CAST(yearID AS SIGNED)))*0.003
                ELSE 1 + (29 - MAX(CAST(Age AS SIGNED))+(2009-CAST(yearID AS SIGNED))) *0.006 END ) AS Curve
FROM batting_pos
GROUP BY playerID;
 
CREATE TABLE player_pa_2009
AS 
SELECT playerID
        , ROUND( COALESCE(PA1,0) * .5 + COALESCE(PA2,0) * .1 + 200 ) AS PA
FROM (SELECT * FROM (SELECT DISTINCT playerID FROM batting_pos) b
        LEFT JOIN (SELECT playerID AS playerID1, PA AS PA1 FROM batting_pos
                WHERE yearID = "2008") b1
                ON b.playerID = b1.playerID1
        LEFT JOIN (SELECT playerID AS playerID2, PA AS PA2 FROM batting_pos
                WHERE yearID = "2007") b2
                ON b.playerID = b2.playerID2) b
GROUP BY playerID
ORDER BY PA DESC;
 
CREATE VIEW hitter_marcels_2009
AS
SELECT b.playerID
        , ROUND(( SUM(b.weight*b.H) + w.H ) / ( SUM(b.weight*b.PA) + 214 ) * p.PA * c.Curve) AS H
        , ROUND(( SUM(b.weight*b.2B) + w.2B ) / ( SUM(b.weight*b.PA) + 214 ) * p.PA * c.Curve) AS 2B
        , ROUND(( SUM(b.weight*b.3B) + w.3B ) / ( SUM(b.weight*b.PA) + 214 ) * p.PA * c.Curve) AS 3B
        , ROUND(( SUM(b.weight*b.HR) + w.HR ) / ( SUM(b.weight*b.PA) + 214 ) * p.PA * c.Curve) AS HR
        , ROUND(( SUM(b.weight*b.BB) + w.BB ) / ( SUM(b.weight*b.PA) + 214 ) * p.PA * c.Curve) AS BB
        , ROUND(( SUM(b.weight*b.SO) + w.SO ) / ( SUM(b.weight*b.PA) + 214 ) * p.PA * c.Curve) AS SO
        , ROUND(( SUM(b.weight*b.GIDP) + w.GIDP ) / ( SUM(b.weight*b.PA) + 214 ) * p.PA * c.Curve) AS GIDP
        , ROUND(( SUM(b.weight*b.IBB) + w.IBB ) / ( SUM(b.weight*b.PA) + 214 ) * p.PA * c.Curve) AS IBB
        , ROUND(( SUM(b.weight*b.HBP) + w.HBP ) / ( SUM(b.weight*b.PA) + 214 ) * p.PA * c.Curve) AS HBP
        , ROUND(( SUM(b.weight*b.SB) + w.SB ) / ( SUM(b.weight*b.PA) + 214 ) * p.PA * c.Curve) AS SB
        , ROUND(( SUM(b.weight*b.CS) + w.CS ) / ( SUM(b.weight*b.PA) + 214 ) * p.PA * c.Curve) AS CS
        , ROUND(( SUM(b.weight*b.AB) + w.AB ) / ( SUM(b.weight*b.PA) + 214 ) * p.PA * c.Curve) AS AB
        , p.PA AS PA
        , (SUM(b.weight*b.PA)) / ( SUM(b.weight*b.PA) + 214 ) AS R
FROM batting_pos b, player_league_average_prorated w, player_Age_2009 c, player_pa_2009 p
WHERE b.playerID = w.playerID
        AND b.playerID = p.playerID
        AND b.playerID = c.playerID
GROUP BY playerID;