CW_Marcels

CREATE TABLE batting_posASSELECT 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 PAFROM	( 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 mWHERE 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) > 0GROUP BY yearID, playerID;CREATE VIEW average_posASSELECT 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 PAFROM batting_pos bGROUP BY yearID;CREATE VIEW player_league_averageASSELECT 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 PAFROM batting_pos b, average_pos aWHERE b.yearID = a.yearIDGROUP BY playerID;CREATE VIEW player_league_average_proratedASSELECT 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 PAFROM player_league_average;CREATE VIEW player_Age_2009ASSELECT 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 CurveFROM batting_posGROUP BY playerID;CREATE TABLE player_pa_2008AS SELECT playerID	, ROUND( COALESCE(PA1,0) * .5 + COALESCE(PA2,0) * .1 + 200 ) AS PAFROM (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) bGROUP BY playerIDORDER BY PA Desc;CREATE VIEW hitter_marcels_2009ASSELECT 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 RFROM batting_pos b, player_league_average_prorated w, player_Age_2009 c, player_pa_2009 pWHERE b.playerID = w.playerID	AND b.playerID = p.playerID	AND b.playerID = c.playerIDGROUP BY playerID;