0

I am want to archive daily price statistics in MySQL 5.6 where I also use 25/50/75 percentiles. The formulas I found seem to work but are rounded to integers.

How can I get the 25/50/75 percentiles of price with 2 decimals precision?

INSERT INTO prices_daily
(DATE, ID, PRICE_MIN, PRICE_MAX, PRICE_AVG, `PRICE_25PZTL`, `PRICE 50PZTL`, `PRICE_75PZTL`, STRENGTH)
SELECT
    DATE,
    ID,
    ROUND(MIN(price),2) AS PRICE_MIN,
    ROUND(MAX(price),2) AS PRICE_MAX,
    ROUND(AVG(price),2) AS PRICE_AVG,
    CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(price ORDER BY price SEPARATOR ','), ',', 25/100 * COUNT(*) + 1), ',', -1) AS DECIMAL) AS 'PRICE_25PZTL',
    CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(price ORDER BY price SEPARATOR ','), ',', 50/100 * COUNT(*) + 1), ',', -1) AS DECIMAL) AS 'PRICE_50PZTL',
    CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(price ORDER BY price SEPARATOR ','), ',', 75/100 * COUNT(*) + 1), ',', -1) AS DECIMAL) AS 'PRICE_75PZTL',
    count(ID) AS STRENGTH
FROM
    `articles`
WHERE
    DATE = CURDATE()
GROUP BY
    DATE,
    ID
ORDER BY
    STRENGTH DESC
Ersoy
  • 8,816
  • 6
  • 34
  • 48
merlin
  • 2,717
  • 3
  • 29
  • 59
  • 2
    Does this answer your question? [Calculate Percentile Value using MySQL](https://stackoverflow.com/questions/19770026/calculate-percentile-value-using-mysql) – vhu May 26 '20 at 21:35
  • No, actually the formula i am using is from the second answer. – merlin May 26 '20 at 21:43

0 Answers0