Nikk
Reputation: 7921
Optimizing a cumulative price calculation query
I have a "pricing engine" query that is getting the cost (per source country) of a product variant, and is calculating taxes & margins in a cumulative fashion (one on top of the other and so forth ex: ((cost * tariff)*tariff) etc...) according to the order in which they need to be applied, in order to calculate the final price per channel: wholesale, b2b etc.
The query does work using recursion but it's rather heavy. I've tried to accomplish the same thing using window functions. But I just do not get the correct result at the end.
Any ideas/suggestions on how I can optimize and make it way more performant?
WITH RECURSIVE __static AS (
SELECT NOW() AS __t_now
),
raw AS (
SELECT
pp.p_var_id,
pp.r_ch_id,
_ch.channel,
pp.r_pl_c_id, -- source country
_c.source_r_pl_c_id,
_c.source_r_ccy_id,
_c.splr_acct_id,
_c.source_cost,
_br.bkng_rt_id,
_br.rate AS rate, -- default to 1 -- no rate defined
_c.source_cost * COALESCE(_br.rate, 1) AS target_cost,
_ch.r_ccy_id AS target_r_ccy_id,
_pt.position,
pp.p_pr_id,
COALESCE(pp.p_pr_ty_id, _pc.p_pr_ty_id) AS p_pr_ty_id,
COALESCE(pp.tariff, _pc.tariff, 0) AS tariff_normalized,
COALESCE(pp.fixed, _pc.fixed, 0) AS fixed_normalized,
COALESCE(pp.tariff, _pc.tariff) AS tariff,
COALESCE(pp.fixed, _pc.fixed) AS fixed,
ROW_NUMBER()
OVER (
PARTITION BY
pp.p_var_id,
pp.r_pl_c_id,
_c.source_cost,
_c.source_r_pl_c_id,
_c.source_r_ccy_id,
_c.splr_acct_id,
pp.r_ch_id,
_br.bkng_rt_id,
_br.rate
ORDER BY _pt.position DESC
) AS row_number
FROM prices pp
CROSS JOIN __static
LEFT JOIN price_components _pc on _pc.p_pr_cmp_id = pp.p_pr_cmp_id
LEFT JOIN price_types _pt on _pt.p_pr_ty_id = COALESCE(pp.p_pr_ty_id, _pc.p_pr_ty_id)
LEFT JOIN channels _ch ON pp.r_ch_id = _ch.r_ch_id AND _ch.active IS TRUE
LEFT JOIN LATERAL (
SELECT DISTINCT ON (c.p_var_id, c.splr_acct_id)
c.p_var_id,
c.splr_acct_id,
c.cost AS source_cost,
c.bkng_rt_src_id,
c.r_ccy_id AS source_r_ccy_id,
c.r_pl_c_id AS source_r_pl_c_id
FROM costs c
WHERE
c.r_pl_c_id = pp.r_pl_c_id -- match cost source country to price source country (new)
AND __static.__t_now BETWEEN c.t_from AND c.t_until
ORDER BY c.p_var_id, c.splr_acct_id, c.t DESC
) _c ON pp.p_var_id = _c.p_var_id
LEFT JOIN LATERAL (
SELECT DISTINCT ON (br.bkng_rt_src_id, br.source_r_ccy_id, br.target_r_ccy_id)
br.bkng_rt_id,
br.bkng_rt_src_id,
br.rate
FROM rates br
WHERE
_c.source_r_ccy_id <> _ch.r_ccy_id -- Only join if conversion is needed
AND br.source_r_ccy_id = _c.source_r_ccy_id --cost source ccy
AND br.target_r_ccy_id = _ch.r_ccy_id --channel target ccy
AND br.bkng_rt_src_id = _c.bkng_rt_src_id
AND __static.__t_now >= br.t_from
AND br.deleted IS FALSE
ORDER BY br.bkng_rt_src_id, br.source_r_ccy_id, br.target_r_ccy_id, br.t_from DESC
) _br ON _c.bkng_rt_src_id = _br.bkng_rt_src_id
WHERE __static.__t_now BETWEEN pp.t_from AND pp.t_until
GROUP BY
__static.__t_now,
_c.p_var_id, _c.source_cost,
pp.r_pl_c_id, _c.source_r_pl_c_id,
_c.source_r_ccy_id, _c.splr_acct_id, _ch.r_ccy_id,
pp.p_var_id, pp.r_ch_id,
_ch.r_ch_id, _ch.channel, _br.bkng_rt_id, _br.rate,
_pt.position,
pp.p_pr_ty_id, _pc.p_pr_ty_id,
pp.p_pr_id,
pp.tariff, _pc.tariff,
pp.fixed, _pc.fixed
),
calc AS (
SELECT *,
target_cost + (target_cost * tariff_normalized) + fixed_normalized AS cumulative, -- Apply first tariff
jsonb_build_array(
jsonb_build_object(
'p_pr_id', p_pr_id,
'p_pr_ty_id', p_pr_ty_id,
'tariff', trim_scale(tariff),
'fixed', trim_scale(fixed),
'subtotal', trim_scale((target_cost * tariff_normalized) + fixed_normalized)
)
) AS components
FROM raw
WHERE row_number = 1 -- Start with the highest position tariff
UNION ALL
SELECT raw.*,
cc.cumulative + (cc.cumulative * raw.tariff_normalized) + raw.fixed_normalized AS cumulative, -- Apply each subsequent tariff
cc.components || jsonb_build_object(
'p_pr_id', raw.p_pr_id,
'p_pr_ty_id', raw.p_pr_ty_id,
'tariff', trim_scale(raw.tariff),
'fixed', trim_scale(raw.fixed),
'subtotal', trim_scale((cc.cumulative * raw.tariff_normalized) + raw.fixed_normalized)
) AS components
FROM calc cc
JOIN raw ON
cc.p_var_id = raw.p_var_id
AND cc.r_pl_c_id = raw.r_pl_c_id
AND cc.source_cost = raw.source_cost
AND cc.source_r_pl_c_id = raw.source_r_pl_c_id
AND cc.source_r_ccy_id = raw.source_r_ccy_id
AND cc.splr_acct_id IS NOT DISTINCT FROM raw.splr_acct_id
AND cc.r_ch_id = raw.r_ch_id
AND cc.bkng_rt_id IS NOT DISTINCT FROM raw.bkng_rt_id
AND cc.rate IS NOT DISTINCT FROM raw.rate
AND cc.row_number + 1 = raw.row_number -- Join on the next lower tariff
)
SELECT *
FROM calc c
WHERE row_number = (
SELECT MAX(raw.row_number)
FROM raw
WHERE
p_var_id = c.p_var_id
AND r_pl_c_id = c.r_pl_c_id
AND source_cost = c.source_cost
AND source_r_pl_c_id = c.source_r_pl_c_id
AND source_r_ccy_id = c.source_r_ccy_id
AND splr_acct_id IS NOT DISTINCT FROM c.splr_acct_id
AND r_ch_id = c.r_ch_id
AND bkng_rt_id IS NOT DISTINCT FROM c.bkng_rt_id
AND rate IS NOT DISTINCT FROM c.rate
)
;
Please find a live version here: https://www.db-fiddle.com/f/vnM3o5RZnhvyNgSqr57w66/0
PS. This is meant to go into a materialized view (thats why it's calculating everything). But I would still like to optimize the heck out of it, because I will need to readapt it in order to get the price for a single product.
Correct result:
+--------+-------+--------+---------+----------------+---------------+------------+-----------+----------+---------+-----------+---------------+--------+-------+----------+-----------------+----------------+------+-----+----------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|p_var_id|r_ch_id|r_pl_c_id|source_r_pl_c_id|source_r_ccy_id|splr_acct_id|source_cost|bkng_rt_id|rate |target_cost|target_r_ccy_id|position|p_pr_id|p_pr_ty_id|tariff_normalized|fixed_normalized|tariff|fixed|row_number|cumulative |components |
+--------+-------+--------+---------+----------------+---------------+------------+-----------+----------+---------+-----------+---------------+--------+-------+----------+-----------------+----------------+------+-----+----------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|1 |4 |807 |807 |978 |1 |100.000000 |null |null |100 |978 |70 |33 |4 |0.35 |0 |0.3500|null |2 |152.55 |[{"fixed": null, "tariff": 0.13, "p_pr_id": 34, "subtotal": 13, "p_pr_ty_id": 2}, {"fixed": null, "tariff": 0.35, "p_pr_id": 33, "subtotal": 39.55, "p_pr_ty_id": 4}] |
|1 |4 |807 |807 |807 |null |2000.000000|6 |0.016129 |32.258 |978 |70 |33 |4 |0.35 |0 |0.3500|null |2 |49.209579 |[{"fixed": null, "tariff": 0.13, "p_pr_id": 34, "subtotal": 4.19354, "p_pr_ty_id": 2}, {"fixed": null, "tariff": 0.35, "p_pr_id": 33, "subtotal": 12.758039, "p_pr_ty_id": 4}] |
|1 |1 |807 |807 |978 |1 |100.000000 |1 |61.696400|6169.64 |807 |1 |19 |1 |0.18 |0 |0.1800|null |4 |11110.0372676|[{"fixed": null, "tariff": 0.13, "p_pr_id": 28, "subtotal": 802.0532, "p_pr_ty_id": 2}, {"fixed": null, "tariff": 0.35, "p_pr_id": 26, "subtotal": 2440.09262, "p_pr_ty_id": 4}, {"fixed": 3.5, "tariff": null, "p_pr_id": 27, "subtotal": 3.5, "p_pr_ty_id": 3}, {"fixed": null, "tariff": 0.18, "p_pr_id": 19, "subtotal": 1694.7514476, "p_pr_ty_id": 1}]|
|1 |2 |807 |807 |978 |1 |100.000000 |1 |61.696400|6169.64 |807 |1 |31 |1 |0.18 |0 |0.1800|null |4 |11932.6970652|[{"fixed": null, "tariff": 0.13, "p_pr_id": 32, "subtotal": 802.0532, "p_pr_ty_id": 2}, {"fixed": null, "tariff": 0.45, "p_pr_id": 29, "subtotal": 3137.26194, "p_pr_ty_id": 4}, {"fixed": 3.5, "tariff": null, "p_pr_id": 30, "subtotal": 3.5, "p_pr_ty_id": 3}, {"fixed": null, "tariff": 0.18, "p_pr_id": 31, "subtotal": 1820.2419252, "p_pr_ty_id": 1}]|
|1 |1 |807 |807 |807 |null |2000.000000|null |null |2000 |807 |1 |19 |1 |0.18 |0 |0.1800|null |4 |3604.31 |[{"fixed": null, "tariff": 0.13, "p_pr_id": 28, "subtotal": 260, "p_pr_ty_id": 2}, {"fixed": null, "tariff": 0.35, "p_pr_id": 26, "subtotal": 791, "p_pr_ty_id": 4}, {"fixed": 3.5, "tariff": null, "p_pr_id": 27, "subtotal": 3.5, "p_pr_ty_id": 3}, {"fixed": null, "tariff": 0.18, "p_pr_id": 19, "subtotal": 549.81, "p_pr_ty_id": 1}] |
|1 |2 |807 |807 |807 |null |2000.000000|null |null |2000 |807 |1 |31 |1 |0.18 |0 |0.1800|null |4 |3870.99 |[{"fixed": null, "tariff": 0.13, "p_pr_id": 32, "subtotal": 260, "p_pr_ty_id": 2}, {"fixed": null, "tariff": 0.45, "p_pr_id": 29, "subtotal": 1017, "p_pr_ty_id": 4}, {"fixed": 3.5, "tariff": null, "p_pr_id": 30, "subtotal": 3.5, "p_pr_ty_id": 3}, {"fixed": null, "tariff": 0.18, "p_pr_id": 31, "subtotal": 590.49, "p_pr_ty_id": 1}] |
+--------+-------+--------+---------+----------------+---------------+------------+-----------+----------+---------+-----------+---------------+--------+-------+----------+-----------------+----------------+------+-----+----------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Table definitions (foreign keys and identity removed):
create table if not exists rate_sources
(
bkng_rt_src_id serial,
hidden boolean default false not null,
active boolean default false not null,
position integer,
bkng_bnk_id integer,
ent_acct_id integer,
r_pl_c_id integer,
name jsonb
);
create table if not exists rates
(
bkng_rt_id serial,
t timestamp default CURRENT_TIMESTAMP(6) not null,
deleted boolean default false not null,
by_e_usr_id integer not null,
bkng_rt_src_id integer not null,
t_from timestamp default '-infinity'::timestamp without time zone not null,
source_r_ccy_id integer,
target_r_ccy_id integer,
rate numeric(14, 6) not null
);
create table if not exists costs
(
p_cst_id serial,
t timestamp default CURRENT_TIMESTAMP(6) not null,
by_e_usr_id integer not null,
p_var_id integer not null,
r_pl_c_id integer not null,
splr_acct_id integer,
r_ccy_id integer not null,
bkng_rt_src_id integer,
cost numeric(14, 6) not null,
t_from timestamp default '-infinity'::timestamp without time zone not null,
t_until timestamp default 'infinity'::timestamp without time zone not null
);
create table if not exists price_components
(
p_pr_cmp_id serial,
hidden boolean default false not null,
p_pr_ty_id integer not null,
r_pl_c_id integer not null,
tariff numeric(7, 4),
fixed numeric(14, 6)
);
create table if not exists price_types
(
p_pr_ty_id serial,
hidden boolean default false not null,
position integer,
name jsonb not null,
r_pl_c_id integer
);
create table if not exists channels
(
r_ch_id serial,
hidden boolean default false not null,
active boolean default false not null,
position integer,
r_pl_c_id integer not null,
r_ccy_id integer not null,
channel varchar not null,
name jsonb not null
);
create table if not exists prices
(
p_pr_id serial,
t timestamp default CURRENT_TIMESTAMP not null,
e_usr_id integer not null,
p_var_id integer not null,
r_ch_id integer not null,
p_pr_ty_id integer,
p_pr_cmp_id integer,
tariff numeric(7, 4),
fixed numeric(14, 6),
t_from date default '-infinity'::date not null,
t_until date default 'infinity'::date not null,
r_pl_c_id integer
);
Query planner output: https://codefile.io/f/MHC9ZVhb91
Upvotes: 0
Views: 43
Answers (0)
Related Questions
- How do I get the query builder to output its raw SQL query as a string?
- How do I limit the number of rows returned by an Oracle query after ordering?
- SQL update query using joins
- How to create a MySQL hierarchical recursive query?
- How to query MongoDB with "like"
- MySQL Query GROUP BY day / month / year
- Calculating Cumulative Sum in PostgreSQL
- Update multiple rows in same query using PostgreSQL
- Join vs. sub-query