101 lines
2.2 KiB
SQL
101 lines
2.2 KiB
SQL
DROP TABLE IF EXISTS uomc;
|
|
|
|
CREATE TEMP TABLE uomc AS (
|
|
WITH
|
|
uom AS (
|
|
SELECT
|
|
uom.p part
|
|
,uom.f fu
|
|
,uom.t tu
|
|
,uom.nm/uom.dm conv
|
|
FROM
|
|
(
|
|
SELECT
|
|
jsonb_agg(row_to_json(d)::jsonb) jdoc
|
|
FROM
|
|
(
|
|
select distinct
|
|
jcpart partn
|
|
, jcunit fu
|
|
, 'PC' tu
|
|
from
|
|
lgdat.iprcc
|
|
WHERE
|
|
jcpart <> ''
|
|
) d
|
|
) c
|
|
JOIN LATERAL rlarp.uom_array(c.jdoc) uom ON TRUE
|
|
)
|
|
SELECT * FROM uom
|
|
) WITH DATA;
|
|
|
|
CREATE INDEX uom_idx ON uomc (part, fu, tu);
|
|
|
|
|
|
-- Clear the output table
|
|
TRUNCATE TABLE pricequote.pricelist_ranged;
|
|
|
|
--DROP TABLE pricequote.pricelist_ranged;
|
|
|
|
-- Compute normalized volume/price and ranges
|
|
--CREATE TABLE pricequote.pricelist_ranged AS (
|
|
WITH
|
|
conv AS (
|
|
SELECT
|
|
p.jcplcd,
|
|
p.jcpart,
|
|
p.jcunit,
|
|
p.jcvoll,
|
|
p.jcpric,
|
|
u.conv,
|
|
(p.jcvoll * u.conv) AS vol_pc,
|
|
(p.jcpric / NULLIF(u.conv, 0)) AS price_pc
|
|
FROM
|
|
lgdat.iprcc p
|
|
INNER JOIN uomc u
|
|
ON u.part = p.jcpart
|
|
AND u.fu = p.jcunit
|
|
AND u.tu = 'PC'
|
|
),
|
|
--SELECT * FROM conv LIMIT 1000
|
|
sorted AS (
|
|
SELECT
|
|
*,
|
|
ROW_NUMBER() OVER (PARTITION BY jcplcd, jcpart ORDER BY vol_pc ASC) AS rn
|
|
FROM conv
|
|
),
|
|
ranged AS (
|
|
SELECT
|
|
curr.jcplcd,
|
|
curr.jcpart,
|
|
curr.jcunit,
|
|
curr.jcvoll,
|
|
curr.jcpric,
|
|
curr.vol_pc,
|
|
curr.price_pc price,
|
|
curr.vol_pc AS vb_from,
|
|
COALESCE(next.vol_pc, 9999999.0) AS vb_to
|
|
FROM
|
|
sorted curr
|
|
LEFT JOIN sorted next
|
|
ON curr.jcplcd = next.jcplcd
|
|
AND curr.jcpart = next.jcpart
|
|
AND curr.rn + 1 = next.rn
|
|
)
|
|
--SELECT * FROM ranged
|
|
INSERT INTO pricequote.pricelist_ranged (
|
|
jcplcd, jcpart, jcunit, jcvoll, jcpric, vb_from, vb_to, price
|
|
)
|
|
SELECT
|
|
jcplcd,
|
|
jcpart,
|
|
jcunit,
|
|
jcvoll,
|
|
jcpric,
|
|
vb_from,
|
|
vb_to,
|
|
price
|
|
FROM ranged;
|
|
|
|
|
|
CREATE INDEX pricelist_ranged_idx ON pricequote.pricelist_ranged ( jcpart ASC , jcplcd ASC , vb_from ASC , vb_to ASC ) ; |