pricing_health.sql

-- Create materialized view to store all price list prices as MD5 hash
CREATE MATERIALIZED VIEW prices AS select price_list_id, MD5(array_agg(pp.quantity::text || pp.unit_code || pp.value::text || pp.currency ORDER BY pp.quantity, pp.unit_code, pp.value::text, pp.currency)::text) as pmd5 FROM oro_price_product pp GROUP BY price_list_id;
-- Add index for md5 to improve performance
CREATE INDEX pmd5_idx ON prices(pmd5);
-- SELECT base price list, number of duplicated price lists and concatenated ids of duplicates
select p1.price_list_id, COUNT(p2.price_list_id), array_agg(p2.price_list_id ORDER BY p2.price_list_id) from prices p1 INNER JOIN prices p2 on p1.pmd5 = p2.pmd5 AND p1.price_list_id <> p2.price_list_id WHERE p1.price_list_id = (SELECT MIN(price_list_id) FROM prices WHERE p1.pmd5 = pmd5) GROUP BY p1.price_list_id ORDER BY p1.price_list_id;
-- SELECT overall number of unique price lists
SELECT COUNT(DISTINCT pmd5) from prices;
-- DROP VIEW
DROP MATERIALIZED VIEW prices;