|
sbox_last_n_days
|
public
|
CREATE OR REPLACE FUNCTION public.sbox_last_n_days(n integer)
RETURNS SETOF sbox_efr_row
LANGUAGE plpgsql
STABLE
AS $function$
BEGIN
RETURN QUERY
WITH S AS (
SELECT
MIN(PUBLIC.SBOX_CY.NAME) AS NAME,
PUBLIC.SBOX_CY.MLBID,
MIN(PUBLIC.SBOX_CY.DATE) AS START_DATE,
MAX(PUBLIC.SBOX_CY.DATE) AS END_DATE,
SUM(PUBLIC.SBOX_CY.AB) AS AB,
SUM(PUBLIC.SBOX_CY.BB) AS BB,
SUM(PUBLIC.SBOX_CY.HBP) AS HBP,
SUM(PUBLIC.SBOX_CY.PA) AS PA,
SUM(PUBLIC.SBOX_CY.H) AS H,
SUM(PUBLIC.SBOX_CY.H1B) AS H1B,
SUM(PUBLIC.SBOX_CY.H2B) AS H2B,
SUM(PUBLIC.SBOX_CY.H3B) AS H3B,
SUM(PUBLIC.SBOX_CY.HR) AS HR,
SUM(PUBLIC.SBOX_CY.R) AS R,
SUM(PUBLIC.SBOX_CY.RBI) AS RBI,
-- AVG = H / AB
ROUND(
SUM(PUBLIC.SBOX_CY.H)::numeric /
NULLIF(SUM(PUBLIC.SBOX_CY.AB)::numeric, 0),
4
) AS AVG,
-- WOBA = SUM(woba * pa) / SUM(pa)
ROUND(
SUM(PUBLIC.SBOX_CY.WOBA * PUBLIC.SBOX_CY.PA) /
NULLIF(SUM(PUBLIC.SBOX_CY.PA), 0),
4
) AS WOBA,
-- OBP = SUM(obp * pa) / SUM(pa)
ROUND(
SUM(PUBLIC.SBOX_CY.OBP * PUBLIC.SBOX_CY.PA) /
NULLIF(SUM(PUBLIC.SBOX_CY.PA), 0),
4
) AS OBP,
-- SLG = SUM(slg * ab) / SUM(ab)
ROUND(
SUM(PUBLIC.SBOX_CY.SLG * PUBLIC.SBOX_CY.AB) /
NULLIF(SUM(PUBLIC.SBOX_CY.AB), 0),
4
) AS SLG,
SUM(PUBLIC.SBOX_CY.SB) AS SB,
SUM(PUBLIC.SBOX_CY.CS) AS CS
FROM
PUBLIC.SBOX_CY
LEFT OUTER JOIN PUBLIC.EFR ON PUBLIC.SBOX_CY.MLBID = PUBLIC.EFR.MLBID
WHERE
DATE >= TO_CHAR(
CURRENT_DATE - (n || ' days')::interval,
'YYYYMMDD'
)::integer
GROUP BY
PUBLIC.SBOX_CY.MLBID
)
SELECT
S.*,
FRAN,
WB,
BELT,
PRAC,
BOHM,
PP,
TM,
AGE,
STS,
POS,
PUBLIC.EFR.AVG AS AVGYR,
SBP,
E,
EP,
-- EP_NTILE: NTILE never divides, so no NULLIF needed
100 - NTILE(100) OVER (ORDER BY EP) AS EP_NTILE
FROM
S
LEFT OUTER JOIN PUBLIC.EFR ON S.MLBID = PUBLIC.EFR.MLBID
WHERE
S.PA > 0
ORDER BY
S.PA * S.WOBA DESC;
END;
$function$
|