price_api/sql/spread.pg.sql

65 lines
1.4 KiB
MySQL
Raw Permalink Normal View History

2023-11-08 14:03:58 -05:00
DROP FUNCTION IF EXISTS rlarp.get_premium;
2023-11-09 08:07:03 -05:00
CREATE OR REPLACE FUNCTION rlarp.get_premium(
_mold text,
_season int,
_chan text,
_relevant text,
_quoted text
)
2023-11-08 14:03:58 -05:00
RETURNS jsonb
2023-11-09 08:07:03 -05:00
LANGUAGE plpgsql
AS $func$
2023-11-08 14:03:58 -05:00
DECLARE
_factor jsonb;
2023-11-09 08:07:03 -05:00
BEGIN
2023-11-08 12:42:46 -05:00
WITH
2023-11-09 08:07:03 -05:00
--target spreads--
2023-11-08 14:03:58 -05:00
bridge AS (
2023-11-08 12:42:46 -05:00
SELECT
2023-11-09 08:07:03 -05:00
_mold AS mold
,_season AS season
,_chan AS chan
,_relevant AS relevant
,_quoted AS quoted
),
ts AS (
2023-11-08 14:03:58 -05:00
SELECT
2023-11-08 12:42:46 -05:00
t.*
2023-11-09 08:07:03 -05:00
,t.target_price / min(t.target_price) over (partition by t.mold, t.chan, t.season) AS spread
2023-11-08 14:03:58 -05:00
,b.quoted
,b.relevant
2023-11-09 08:07:03 -05:00
,CASE
WHEN t.data_segment = b.quoted THEN 'get to here'
WHEN t.data_segment = b.relevant THEN 'from here'
END AS flag
2023-11-08 12:42:46 -05:00
FROM
pricequote.market_setavgprice t
2023-11-08 14:03:58 -05:00
INNER JOIN bridge b ON
2023-11-09 08:07:03 -05:00
t.mold = b.mold
AND t.season = b.season
AND t.chan = b.chan
AND t.data_segment LIKE 'v1%'
AND t.geo = 'ALL'
AND t.country = 'ALL'
2023-11-08 12:42:46 -05:00
)
2023-11-08 14:03:58 -05:00
SELECT
2023-11-09 08:07:03 -05:00
jsonb_build_object(
'bridgePremium',
ROUND(max(spread) FILTER (WHERE flag = 'get to here') / max(spread) FILTER (WHERE flag = 'from here'), 5)
)
2023-11-08 14:03:58 -05:00
INTO
_factor
FROM
ts
GROUP BY
2023-11-09 08:07:03 -05:00
mold, chan, season;
RAISE NOTICE 'relevant: %' , _relevant;
RAISE NOTICE 'quoted: %' , _quoted;
2023-11-08 14:03:58 -05:00
RETURN _factor;
END
2023-11-09 08:07:03 -05:00
$func$;