[sql] MySQL로 중앙값을 계산하는 간단한 방법

Program / 글쓴이

MySQL로 중앙값을 계산하는 가장 간단한 방법은 무엇입니까? AVG(x)평균을 찾는 데 사용 했지만 중간 값을 계산하는 간단한 방법을 찾는 데 어려움을 겪고 있습니다. 지금은 모든 행을 PHP로 반환하고 정렬 한 다음 중간 행을 선택하지만 단일 MySQL 쿼리에서 간단한 행을 수행해야합니다.

데이터 예 :

id | val
--------
 1    4
 2    7
 3    2
 4    2
 5    9
 6    8
 7    3

에 정렬하면 val제공 2 2 3 4 7 8 9되므로 중앙값은 4대 SELECT AVG(val)= = 5입니다.

답변

MariaDB / MySQL에서 :

SELECT AVG(dd.val) as median_val
FROM (
SELECT d.val, @rownum:=@rownum+1 as `row_number`, @total_rows:=@rownum
  FROM data d, (SELECT @rownum:=0) r
  WHERE d.val is NOT NULL
  -- put some where clause here
  ORDER BY d.val
) as dd
WHERE dd.row_number IN ( FLOOR((@total_rows+1)/2), FLOOR((@total_rows+2)/2) );

Steve Cohen 은 첫 번째 패스 후에 @rownum에 총 행 수가 포함된다고 지적합니다. 이 값을 사용하여 중앙값을 결정할 수 있으므로 두 번째 패스 또는 조인이 필요하지 않습니다.

또한 AVG(dd.val)과 dd.row_number IN(...)레코드의 수가 짝수 인 경우 제대로 평균을 생성하기 위해 사용된다. 추리:

SELECT FLOOR((3+1)/2),FLOOR((3+2)/2); -- when total_rows is 3, avg rows 2 and 2
SELECT FLOOR((4+1)/2),FLOOR((4+2)/2); -- when total_rows is 4, avg rows 2 and 3

마지막으로 MariaDB 10.3.3+에는 MEDIAN 함수가 포함되어 있습니다

답변

방금 주석에서 온라인으로 다른 답변을 찾았습니다 .

거의 모든 SQL의 중앙값 :

SELECT x.val from data x, data y
GROUP BY x.val
HAVING SUM(SIGN(1-SIGN(y.val-x.val))) = (COUNT(*)+1)/2

열의 색인이 잘 작성되고 색인이 필터링 및 정렬에 사용되는지 확인하십시오. Explain 계획으로 확인하십시오.

select count(*) from table --find the number of rows

“중간”행 번호를 계산하십시오. 아마도 사용하십시오 : median_row = floor(count / 2).

그런 다음 목록에서 선택하십시오.

select val from table order by val asc limit median_row,1

원하는 값으로 하나의 행을 반환해야합니다.

야곱

답변

수용 된 솔루션이 MySQL 설치에서 작동하지 않고 빈 세트를 반환한다는 것을 알았지 만이 쿼리는 테스트 한 모든 상황에서 저에게 효과적이었습니다.

SELECT x.val from data x, data y
GROUP BY x.val
HAVING SUM(SIGN(1-SIGN(y.val-x.val)))/COUNT(*) > .5
LIMIT 1

답변

불행히도 TheJacobTaylor와 velcrow의 답변은 현재 버전의 MySQL에 대한 정확한 결과를 반환하지 않습니다.

위에서 벨크로의 대답은 가깝지만 짝수 행의 결과 집합에 대해서는 올바르게 계산되지 않습니다. 중앙값은 1) 홀수 번호 집합의 중간 숫자 또는 2) 짝수 숫자 집합의 두 중간 숫자의 평균으로 정의됩니다.

홀수 및 짝수 집합을 처리하기 위해 패치 된 벨크로 솔루션은 다음과 같습니다.

SELECT AVG(middle_values) AS 'median' FROM (
  SELECT t1.median_column AS 'middle_values' FROM
    (
      SELECT @row:=@row+1 as `row`, x.median_column
      FROM median_table AS x, (SELECT @row:=0) AS r
      WHERE 1
      -- put some where clause here
      ORDER BY x.median_column
    ) AS t1,
    (
      SELECT COUNT(*) as 'count'
      FROM median_table x
      WHERE 1
      -- put same where clause here
    ) AS t2
    -- the following condition will return 1 record for odd number sets, or 2 records for even number sets.
    WHERE t1.row >= t2.count/2 and t1.row <= ((t2.count/2) +1)) AS t3;

이를 사용하려면 다음 3 가지 간단한 단계를 수행하십시오.

  1. 위 코드에서 “median_table”(2 회 발생)을 테이블 이름으로 바꿉니다.
  2. “median_column”(3 회 발생)을 중앙값을 찾으려는 열 이름으로 바꿉니다.
  3. WHERE 조건이있는 경우 “WHERE 1″(2 회 발생)을 where 조건으로 바꾸십시오.

답변

더 빠른 방법을 제안합니다.

행 개수를 가져옵니다.

SELECT CEIL(COUNT(*)/2) FROM data;

그런 다음 정렬 된 하위 쿼리에서 중간 값을 가져옵니다.

SELECT max(val) FROM (SELECT val FROM data ORDER BY val limit @middlevalue) x;

나는 이것을 무작위 숫자의 5x10e6 데이터 세트로 테스트했으며 10 초 안에 중앙값을 찾을 것입니다.

답변

에 대한 의견 MySQL의 문서에서이 페이지는 다음과 같은 제안을 가지고 :

-- (mostly) High Performance scaling MEDIAN function per group
-- Median defined in http://en.wikipedia.org/wiki/Median
--
-- by Peter Hlavac
-- 06.11.2008
--
-- Example Table:

DROP table if exists table_median;
CREATE TABLE table_median (id INTEGER(11),val INTEGER(11));
COMMIT;


INSERT INTO table_median (id, val) VALUES
(1, 7), (1, 4), (1, 5), (1, 1), (1, 8), (1, 3), (1, 6),
(2, 4),
(3, 5), (3, 2),
(4, 5), (4, 12), (4, 1), (4, 7);



-- Calculating the MEDIAN
SELECT @a := 0;
SELECT
id,
AVG(val) AS MEDIAN
FROM (
SELECT
id,
val
FROM (
SELECT
-- Create an index n for every id
@a := (@a + 1) mod o.c AS shifted_n,
IF(@a mod o.c=0, o.c, @a) AS n,
o.id,
o.val,
-- the number of elements for every id
o.c
FROM (
SELECT
t_o.id,
val,
c
FROM
table_median t_o INNER JOIN
(SELECT
id,
COUNT(1) AS c
FROM
table_median
GROUP BY
id
) t2
ON (t2.id = t_o.id)
ORDER BY
t_o.id,val
) o
) a
WHERE
IF(
-- if there is an even number of elements
-- take the lower and the upper median
-- and use AVG(lower,upper)
c MOD 2 = 0,
n = c DIV 2 OR n = (c DIV 2)+1,

-- if its an odd number of elements
-- take the first if its only one element
-- or take the one in the middle
IF(
c = 1,
n = 1,
n = c DIV 2 + 1
)
)
) a
GROUP BY
id;

-- Explanation:
-- The Statement creates a helper table like
--
-- n id val count
-- ----------------
-- 1, 1, 1, 7
-- 2, 1, 3, 7
-- 3, 1, 4, 7
-- 4, 1, 5, 7
-- 5, 1, 6, 7
-- 6, 1, 7, 7
-- 7, 1, 8, 7
--
-- 1, 2, 4, 1

-- 1, 3, 2, 2
-- 2, 3, 5, 2
--
-- 1, 4, 1, 4
-- 2, 4, 5, 4
-- 3, 4, 7, 4
-- 4, 4, 12, 4


-- from there we can select the n-th element on the position: count div 2 + 1

답변

이 mysql 통계 함수를 설치하고 사용하십시오 : http://www.xarg.org/2012/07/statistical-functions-in-mysql/

그 후, 중앙값 계산은 쉽습니다.

SELECT median(val) FROM data;