MySQL의 5번째 백분위수(MariaDB)

MySQL의 5번째 백분위수(MariaDB)

~30만 줄의 표에서 주문을 사용하여 아이템 가격의 95번째 백분위수(및 가장 높은 구매)를 찾고 있습니다.

95번째 백분위수 및 단일 항목 중 가장 높은 구매율을 찾는 데 성공했습니다.

 SELECT type_id,
       Max(price) AS buy,
       Min(price) AS '95th% buy'
FROM   (SELECT *,
               ( Row_number()
                   OVER (
                     partition BY type_id
                     ORDER BY price DESC) ) AS rownr
        FROM   orderbuffertest AS rownr
        WHERE  is_buy_order = 1
        ORDER  BY ( Row_number()
                      OVER (
                        partition BY type_id
                        ORDER BY price DESC) ) ASC) AS t1
WHERE  t1.type_id = 44992
       AND t1.rownr < (SELECT Count(*)
                       FROM   orderbuffertest
                       WHERE  is_buy_order = 1
                              AND type_id = 44992) * 0.05;  

하지만 지금은...GROUP BY type_id제 가치관을 엉망으로 만들고 있어요

어떻게 하면 좋을지 아는 사람?GROUP BY type_id이 질문이요?어쩌면 원래의 것을 개선할 수 있는 방법까지?

잘 부탁드립니다.

더조즐

추신. 데이터베이스 링크입니다.https://gofile.io/?c=Ga6ODR

이 쿼리는 원하는 결과를 제공합니다.이 명령어는ROW_NUMBER타고price각 행의 수를 세는 것 외에type_id및 주문 유형(is_buy_orderCTE에서 )를 선택하면,MAX로서 값을 매기다buy가격is_buy_order = 1> = 95번째 백분위수 가격으로서 행의 최소값.95번째 백분위수에 가장 높은 가격 이외의 행이 없을 경우 두 번째로 높은 가격을 반환합니다.같은 논리가 다음 명령어의 생성에도 적용됩니다.sell그리고.95th%sell가격:

WITH prices AS (
  SELECT type_id, price, is_buy_order,
         ROW_NUMBER() OVER (PARTITION BY type_id, is_buy_order ORDER BY price DESC) AS rownr,
         COUNT(*) OVER (PARTITION BY type_id, is_buy_order) AS num_rows
  FROM   orderbuffertest
)
SELECT type_id,
       MAX(CASE WHEN is_buy_order = 1 THEN price END) AS buy,
       COALESCE(MIN(CASE WHEN is_buy_order = 1 AND 100.0 * (rownr - 1) / num_rows <= 5 AND rownr != 1 THEN price END), 
                MAX(CASE WHEN is_buy_order = 1 AND rownr = 2 THEN price END)) AS `95th%buy`,
       MIN(CASE WHEN is_buy_order = 0 THEN price END) AS sell,
       COALESCE(MAX(CASE WHEN is_buy_order = 0 AND 100.0 * rownr / num_rows >= 95 AND rownr != num_rows THEN price END), 
                MAX(CASE WHEN is_buy_order = 0 AND rownr = num_rows - 1 THEN price END)) AS `95th%sell`
FROM prices
GROUP BY type_id

어떤 이유로 CTE를 사용할 수 없는 경우 CTE를 서브쿼리로 작성할 수 있습니다.

SELECT type_id,
       MAX(CASE WHEN is_buy_order = 1 THEN price END) AS buy,
       COALESCE(MIN(CASE WHEN is_buy_order = 1 AND 100.0 * (rownr - 1) / num_rows <= 5 AND rownr != 1 THEN price END), 
                MAX(CASE WHEN is_buy_order = 1 AND rownr = 2 THEN price END)) AS `95th%buy`,
       MIN(CASE WHEN is_buy_order = 0 THEN price END) AS sell,
       COALESCE(MAX(CASE WHEN is_buy_order = 0 AND 100.0 * rownr / num_rows >= 95 AND rownr != num_rows THEN price END), 
                MAX(CASE WHEN is_buy_order = 0 AND rownr = num_rows - 1 THEN price END)) AS `95th%sell`
FROM (
  SELECT type_id, price, is_buy_order,
         ROW_NUMBER() OVER (PARTITION BY type_id, is_buy_order ORDER BY price DESC) AS rownr,
         COUNT(*) OVER (PARTITION BY type_id, is_buy_order) AS num_rows
  FROM   orderbuffertest
) prices
GROUP BY type_id

dbfiddle 데모

언급URL : https://stackoverflow.com/questions/59905423/5th-percentile-on-mysql-mariadb