복붙노트

[SQL] MySQL의와 계산의 중간에 간단한 방법

SQL

MySQL의와 계산의 중간에 간단한 방법

MySQL의와 평균을 계산하는 가장 간단한 (잘하면 너무 느린되지 않음) 방법은 무엇입니까? 나는 평균을 찾는 AVG (X)를 사용했지만, 나는 평균을 계산하는 간단한 방법을 찾는 힘든 시간을 보내고 있습니다. 지금, 나는, PHP에 모든 행을 반환 정렬을하고, 다음 가운데 행을 따기, 그러나 확실하게 하나의 MySQL의 쿼리에서 그것을하는 간단한 방법이있을거야.

예 데이터 :

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

중앙값은 SELECT AVG (발) == 5 대 4이어야하므로 발을 정리하면, 2 3 4 7 8 9 (2)을 제공한다.

해결법

  1. ==============================

    1.MariaDB / MySQL은 :

    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) );

    스티브 코헨은 첫 번째 패스 후, @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 함수를 포함

  2. ==============================

    2.난 그냥 코멘트에 온라인 또 다른 해답을 발견 :

    난 그냥 코멘트에 온라인 또 다른 해답을 발견 :

    있는지 확인 열이 잘 색인 및 인덱스 필터링 및 정렬에 사용됩니다. Explain 스 계획을 확인합니다.

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

    은 "중간"행 번호를 계산합니다. 아마 사용 median_row = 바닥 (수 / 2).

    그런 다음 목록의 그것을 선택 :

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

    이것은 당신이 원하는 단지 값으로 당신에게 하나 개의 행을 반환해야합니다.

    야곱

  3. ==============================

    3.나는 허용 솔루션 내 MySQL을 설치, 빈 집합을 반환에 작동하지 않았다 찾았지만,이 쿼리는 내가 그것을 테스트하는 모든 상황에서 나를 위해 일한 :

    나는 허용 솔루션 내 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
  4. ==============================

    4.불행하게도,도 TheJacobTaylor의도 벨크로의 대답은 MySQL의 현재 버전에 대한 정확한 결과를 반환합니다.

    불행하게도,도 TheJacobTaylor의도 벨크로의 대답은 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 단계를 수행 :

  5. ==============================

    5.나는 빠른 방법을 제안한다.

    나는 빠른 방법을 제안한다.

    행 수를 가져옵니다 :

    데이터로부터 SELECT CEIL (COUNT (*) / 2);

    그런 다음 정렬 된 서브 쿼리의 중간 값을 :

    SELECT 맥스 (값 제한 @middlevalue BY 데이터 ORDER FROM SELECT 값) × FROM (발);

    나는 임의의 숫자의 5x10e6 데이터 세트와 함께이 테스트는 10 초 이상에서 중간을 찾을 수 있습니다.

  6. ==============================

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

    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
  7. ==============================

    7.작업 위의 솔루션의 대부분은 테이블의 한 필드를 들어, 쿼리에 많은 분야에 대한 평균 (50 백분위 수)를 얻을 필요가 있습니다.

    작업 위의 솔루션의 대부분은 테이블의 한 필드를 들어, 쿼리에 많은 분야에 대한 평균 (50 백분위 수)를 얻을 필요가 있습니다.

    나는 이것을 사용

    SELECT CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(
 GROUP_CONCAT(field_name ORDER BY field_name SEPARATOR ','),
  ',', 50/100 * COUNT(*) + 1), ',', -1) AS DECIMAL) AS `Median`
FROM table_name;

    당신은이 "50"어떤 백분위 수에 위의 예에서 매우 효율적으로 대체 할 수 있습니다.

    그냥 당신이 GROUP_CONCAT에 대한 충분한 메모리를 가지고 있는지 확인, 당신이 그것을 변경할 수 있습니다 :

    SET group_concat_max_len = 10485760; #10MB max length

    자세한 내용 : http://web.performancerasta.com/metrics-tips-calculating-95th-99th-or-any-percentile-with-single-mysql-query/

  8. ==============================

    8.나는 HackerRank에서 발견 코드를 아래이 있고 그것은 매우 간단하고 경우 각각의 모든 작동합니다.

    나는 HackerRank에서 발견 코드를 아래이 있고 그것은 매우 간단하고 경우 각각의 모든 작동합니다.

    SELECT M.MEDIAN_COL FROM MEDIAN_TABLE M WHERE  
  (SELECT COUNT(MEDIAN_COL) FROM MEDIAN_TABLE WHERE MEDIAN_COL < M.MEDIAN_COL ) = 
  (SELECT COUNT(MEDIAN_COL) FROM MEDIAN_TABLE WHERE MEDIAN_COL > M.MEDIAN_COL );
  9. ==============================

    9.설치하고이 MySQL의 통계 기능을 사용 http://www.xarg.org/2012/07/statistical-functions-in-mysql/

    설치하고이 MySQL의 통계 기능을 사용 http://www.xarg.org/2012/07/statistical-functions-in-mysql/

    그 후, 계산의 중간 값은 간단합니다 :

    SELECT median(val) FROM data;
  10. ==============================

    10.다른 매개 변수에 의해 그룹화 뭔가 떨어져 평균을해야하는 사람들을 위해, 벨크로의 대답의 오프 건물 :

    다른 매개 변수에 의해 그룹화 뭔가 떨어져 평균을해야하는 사람들을 위해, 벨크로의 대답의 오프 건물 :

    SELECT grp_field, FROM t1.val (    SELECT grp_field, @rownum = ROW_NUMBER AS IF (@s = grp_field, @rownum + 1, 0),    @s = IF (@s = grp_field, @s, grp_field) AS 초 d.val   (= 0 @s = 0 SELECT @rownum)의 R의 데이터 (D), FROM   ORDER BY의 grp_field, d.val )가 T1으로 가입 (   TOTAL_ROWS 같은 SELECT grp_field, 수 (*)   데이터 D FROM   GROUP BY의 grp_field )로서 T2 t1.grp_field = t2.grp_field ON WHERE t1.row_number = 층 (TOTAL_ROWS / 2) +1]

  11. ==============================

    11.당신은 여기있어 사용자 정의 함수를 사용할 수 있습니다.

    당신은 여기있어 사용자 정의 함수를 사용할 수 있습니다.

  12. ==============================

    12.홀수 값 (count)에 대해 처리한다 -이 경우 중간의 두 값의 평균을 제공한다.

    홀수 값 (count)에 대해 처리한다 -이 경우 중간의 두 값의 평균을 제공한다.

    SELECT AVG(val) FROM
  ( SELECT x.id, x.val from data x, data y
      GROUP BY x.id, x.val
      HAVING SUM(SIGN(1-SIGN(IF(y.val-x.val=0 AND x.id != y.id, SIGN(x.id-y.id), y.val-x.val)))) IN (ROUND((COUNT(*))/2), ROUND((COUNT(*)+1)/2))
  ) sq
  13. ==============================

    13.테이블 또는 추가 변수없이 내 코드, 효율적인

    테이블 또는 추가 변수없이 내 코드, 효율적인

    SELECT
((SUBSTRING_INDEX(SUBSTRING_INDEX(group_concat(val order by val), ',', floor(1+((count(val)-1) / 2))), ',', -1))
+
(SUBSTRING_INDEX(SUBSTRING_INDEX(group_concat(val order by val), ',', ceiling(1+((count(val)-1) / 2))), ',', -1)))/2
as median
FROM table;
  14. ==============================

    14.선택적으로, 당신은 또한 저장 프로 시저에서이 작업을 수행 할 수 있습니다 :

    선택적으로, 당신은 또한 저장 프로 시저에서이 작업을 수행 할 수 있습니다 :

    DROP PROCEDURE IF EXISTS median;
DELIMITER //
CREATE PROCEDURE median (table_name VARCHAR(255), column_name VARCHAR(255), where_clause VARCHAR(255))
BEGIN
  -- Set default parameters
  IF where_clause IS NULL OR where_clause = '' THEN
    SET where_clause = 1;
  END IF;

  -- Prepare statement
  SET @sql = CONCAT(
    "SELECT AVG(middle_values) AS 'median' FROM (
      SELECT t1.", column_name, " AS 'middle_values' FROM
        (
          SELECT @row:=@row+1 as `row`, x.", column_name, "
          FROM ", table_name," AS x, (SELECT @row:=0) AS r
          WHERE ", where_clause, " ORDER BY x.", column_name, "
        ) AS t1,
        (
          SELECT COUNT(*) as 'count'
          FROM ", table_name, " x
          WHERE ", where_clause, "
        ) 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
    ");

  -- Execute statement
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
END//
DELIMITER ;


-- Sample usage:
-- median(table_name, column_name, where_condition);
CALL median('products', 'price', NULL);
  15. ==============================

    15.내 솔루션 테이블, 변수 또는 하위 쿼리를 생성하지 않고 하나의 쿼리에서 작품 아래에 제시했다. 게다가, 그것은 당신이 그룹에 의해 쿼리에서 각 그룹의 평균 얻을 수 있습니다 (이것은 내가 필요한 것입니다!)

    내 솔루션 테이블, 변수 또는 하위 쿼리를 생성하지 않고 하나의 쿼리에서 작품 아래에 제시했다. 게다가, 그것은 당신이 그룹에 의해 쿼리에서 각 그룹의 평균 얻을 수 있습니다 (이것은 내가 필요한 것입니다!)

    SELECT `columnA`, 
SUBSTRING_INDEX(SUBSTRING_INDEX(GROUP_CONCAT(`columnB` ORDER BY `columnB`), ',', CEILING((COUNT(`columnB`)/2))), ',', -1) medianOfColumnB
FROM `tableC`
-- some where clause if you want
GROUP BY `columnA`;

    이 때문에 GROUP_CONCAT 및 SUBSTRING_INDEX의 스마트 사용의 작동합니다.

    그러나, 큰 GROUP_CONCAT 수 있도록, 당신은 더 높은 값 (기본적으로 1024 문자)에 group_concat_max_len 설정해야합니다. 당신은 (현재 SQL 세션에 대해) 그런 식으로 설정할 수 있습니다 :

    SET SESSION group_concat_max_len = 10000; 
-- up to 4294967295 in 32-bits platform.

    group_concat_max_len에 대한 더 많은 정보를 정기적으로 : https://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_group_concat_max_len

  16. ==============================

    16.또 다른 Velcrow의 대답에 리프,하지만 하나의 중간 테이블을 사용하고 오히려 그것을 계산하는 별도의 쿼리를 수행하는 것보다, 수를 얻을 번호 행에 사용되는 변수를 활용합니다. 또한 첫 번째 행이 중간 행을 선택하기 위해 바닥 및 천장을 만들다를 사용하여 간단히 할 수 있도록 로우 0이되도록 카운트를 시작한다.

    또 다른 Velcrow의 대답에 리프,하지만 하나의 중간 테이블을 사용하고 오히려 그것을 계산하는 별도의 쿼리를 수행하는 것보다, 수를 얻을 번호 행에 사용되는 변수를 활용합니다. 또한 첫 번째 행이 중간 행을 선택하기 위해 바닥 및 천장을 만들다를 사용하여 간단히 할 수 있도록 로우 0이되도록 카운트를 시작한다.

    SELECT Avg(tmp.val) as median_val
    FROM (SELECT inTab.val, @rows := @rows + 1 as rowNum
              FROM data as inTab,  (SELECT @rows := -1) as init
              -- Replace with better where clause or delete
              WHERE 2 > 1
              ORDER BY inTab.val) as tmp
    WHERE tmp.rowNum in (Floor(@rows / 2), Ceil(@rows / 2));
  17. ==============================

    17.

    SELECT 
    SUBSTRING_INDEX(
        SUBSTRING_INDEX(
            GROUP_CONCAT(field ORDER BY field),
            ',',
            ((
                ROUND(
                    LENGTH(GROUP_CONCAT(field)) - 
                    LENGTH(
                        REPLACE(
                            GROUP_CONCAT(field),
                            ',',
                            ''
                        )
                    )
                ) / 2) + 1
            )),
            ',',
            -1
        )
FROM
    table

    위의 나를 위해 작동하는 것 같다.

  18. ==============================

    18.나는 두 개의 쿼리 접근 방식을 사용 :

    나는 두 개의 쿼리 접근 방식을 사용 :

    모든 값이 하나의 호출에서 반환 할 수 있도록 이러한, 함수 defn이에 싸여있다.

    당신의 범위는 정적이며 데이터가 자주 변경되지 않는 경우 / 미리 계산에보다 효율적으로이 값을 저장하고 대신 처음부터 때마다 쿼리의 저장된 값을 사용할 수 있습니다.

  19. ==============================

    19.난 그냥 평균과 백분위 솔루션을 필요에 따라, 나는이 글의 결과를 바탕으로 간단하고 매우 유연한 기능을했다. 나는 신속하게 공유하기로 결정 그래서 나는, 내가 찾아 내 프로젝트에 포함 쉬운 기능을 "기성품"경우 자신 행복 것을 알고있다 :

    난 그냥 평균과 백분위 솔루션을 필요에 따라, 나는이 글의 결과를 바탕으로 간단하고 매우 유연한 기능을했다. 나는 신속하게 공유하기로 결정 그래서 나는, 내가 찾아 내 프로젝트에 포함 쉬운 기능을 "기성품"경우 자신 행복 것을 알고있다 :

    function mysql_percentile($table, $column, $where, $percentile = 0.5) {

    $sql = "
            SELECT `t1`.`".$column."` as `percentile` FROM (
            SELECT @rownum:=@rownum+1 as `row_number`, `d`.`".$column."`
              FROM `".$table."` `d`,  (SELECT @rownum:=0) `r`
              ".$where."
              ORDER BY `d`.`".$column."`
            ) as `t1`, 
            (
              SELECT count(*) as `total_rows`
              FROM `".$table."` `d`
              ".$where."
            ) as `t2`
            WHERE 1
            AND `t1`.`row_number`=floor(`total_rows` * ".$percentile.")+1;
        ";

    $result = sql($sql, 1);

    if (!empty($result)) {
        return $result['percentile'];       
    } else {
        return 0;
    }

}

    사용법은 내 현재 프로젝트에서 아주 쉽게 예입니다 :

    ...
$table = DBPRE."zip_".$slug;
$column = 'seconds';
$where = "WHERE `reached` = '1' AND `time` >= '".$start_time."'";

    $reaching['median'] = mysql_percentile($table, $column, $where, 0.5);
    $reaching['percentile25'] = mysql_percentile($table, $column, $where, 0.25);
    $reaching['percentile75'] = mysql_percentile($table, $column, $where, 0.75);
...
  20. ==============================

    20.여기 내 방법입니다. 물론, 당신은 프로 시저에 넣을 수 있습니다 :-)

    여기 내 방법입니다. 물론, 당신은 프로 시저에 넣을 수 있습니다 :-)

    SET @median_counter = (SELECT FLOOR(COUNT(*)/2) - 1 AS `median_counter` FROM `data`);

SET @median = CONCAT('SELECT `val` FROM `data` ORDER BY `val` LIMIT ', @median_counter, ', 1');

PREPARE median FROM @median;

EXECUTE median;

    당신이 그것을 대체 할 경우, 변수 @median_counter을 피할 수 :

    SET @median = CONCAT( 'SELECT `val` FROM `data` ORDER BY `val` LIMIT ',
                      (SELECT FLOOR(COUNT(*)/2) - 1 AS `median_counter` FROM `data`),
                      ', 1'
                    );

PREPARE median FROM @median;

EXECUTE median;
  21. ==============================

    21.이 방법은 부질없이 모두 짝수 및 홀수 수를 포함 보인다.

    이 방법은 부질없이 모두 짝수 및 홀수 수를 포함 보인다.

    SELECT AVG(t1.x)
FROM table t1, table t2
GROUP BY t1.x
HAVING SUM(SIGN(t1.x - t2.x)) = 0
  22. ==============================

    22.@ 밥의 답변에 따라,이 몇 가지 기준에 따라 분류 다수의 중간 값을 반환 할 수있는 능력을 가지고 쿼리를 일반화.

    @ 밥의 답변에 따라,이 몇 가지 기준에 따라 분류 다수의 중간 값을 반환 할 수있는 능력을 가지고 쿼리를 일반화.

    생각, 예를 들어, 올해 월별로 그룹화 자동차 주차장에 중고차에 대한 평균 판매 가격.

    SELECT 
    period, 
    AVG(middle_values) AS 'median' 
FROM (
    SELECT t1.sale_price AS 'middle_values', t1.row_num, t1.period, t2.count
    FROM (
        SELECT 
            @last_period:=@period AS 'last_period',
            @period:=DATE_FORMAT(sale_date, '%Y-%m') AS 'period',
            IF (@period<>@last_period, @row:=1, @row:=@row+1) as `row_num`, 
            x.sale_price
          FROM listings AS x, (SELECT @row:=0) AS r
          WHERE 1
            -- where criteria goes here
          ORDER BY DATE_FORMAT(sale_date, '%Y%m'), x.sale_price
        ) AS t1
    LEFT JOIN (  
          SELECT COUNT(*) as 'count', DATE_FORMAT(sale_date, '%Y-%m') AS 'period'
          FROM listings x
          WHERE 1
            -- same where criteria goes here
          GROUP BY DATE_FORMAT(sale_date, '%Y%m')
        ) AS t2
        ON t1.period = t2.period
    ) AS t3
WHERE 
    row_num >= (count/2) 
    AND row_num <= ((count/2) + 1)
GROUP BY t3.period
ORDER BY t3.period;
  23. ==============================

    23.종종, 우리는 전체 테이블에 대한,하지만 우리의 ID에 대한 집계를 위해 단지 중간 값을 계산해야 할 수도 있습니다. 즉, 각각의 ID가 많은 레코드가 우리 테이블의 각 ID에 대한 계산의 중간. (이상의 서로 다른 중간-방법의 성능 https://sqlperformance.com/2012/08/t-sql-queries/median에 대해도 및 확률의 많은 SQL + 수정 문제에서 좋은 성능과 작품)

    종종, 우리는 전체 테이블에 대한,하지만 우리의 ID에 대한 집계를 위해 단지 중간 값을 계산해야 할 수도 있습니다. 즉, 각각의 ID가 많은 레코드가 우리 테이블의 각 ID에 대한 계산의 중간. (이상의 서로 다른 중간-방법의 성능 https://sqlperformance.com/2012/08/t-sql-queries/median에 대해도 및 확률의 많은 SQL + 수정 문제에서 좋은 성능과 작품)

    SELECT our_id, AVG(1.0 * our_val) as Median
FROM
( SELECT our_id, our_val, 
  COUNT(*) OVER (PARTITION BY our_id) AS cnt,
  ROW_NUMBER() OVER (PARTITION BY our_id ORDER BY our_val) AS rn
  FROM our_table
) AS x
WHERE rn IN ((cnt + 1)/2, (cnt + 2)/2) GROUP BY our_id;

    희망이 도움이

  24. ==============================

    24.MySQL은 ROW_NUMBER가있는 경우, 다음 MEDIAN (이 SQL Server 쿼리에 의해 영감을)한다 :

    MySQL은 ROW_NUMBER가있는 경우, 다음 MEDIAN (이 SQL Server 쿼리에 의해 영감을)한다 :

    WITH Numbered AS 
(
SELECT *, COUNT(*) OVER () AS Cnt,
    ROW_NUMBER() OVER (ORDER BY val) AS RowNum
FROM yourtable
)
SELECT id, val
FROM Numbered
WHERE RowNum IN ((Cnt+1)/2, (Cnt+2)/2)
;

    당신이 항목의 짝수 번호가 경우에 IN이 사용됩니다.

    당신은 그룹 별 평균, 당신의 OVER 절에 그룹 BY 그럼 그냥 파티션을 찾으려면.

    롭

  25. ==============================

    25.내가 어떤 절차 나 복잡한 문장을 필요로하지 않는 내 자신의 경로를 구현하므로 이전의 모든 것들을 읽고 나면 그들은 단지 내가 중간을 얻기 위해 원하는 열에서 모든 값을 GROUP_CONCAT와 COUNT의 DIV를 적용, 내 실제 요구 사항과 일치하지 않습니다 2 나는 다음과 같은 쿼리가하는 것처럼 목록의 중간에서 값을 추출 :

    내가 어떤 절차 나 복잡한 문장을 필요로하지 않는 내 자신의 경로를 구현하므로 이전의 모든 것들을 읽고 나면 그들은 단지 내가 중간을 얻기 위해 원하는 열에서 모든 값을 GROUP_CONCAT와 COUNT의 DIV를 적용, 내 실제 요구 사항과 일치하지 않습니다 2 나는 다음과 같은 쿼리가하는 것처럼 목록의 중간에서 값을 추출 :

    (POS 내가 그 중간을 얻을 열의 이름입니다)

    (query) SELECT
SUBSTRING_INDEX ( 
   SUBSTRING_INDEX ( 
       GROUP_CONCAT(pos ORDER BY CAST(pos AS SIGNED INTEGER) desc SEPARATOR ';') 
    , ';', COUNT(*)/2 ) 
, ';', -1 ) AS `pos_med`
FROM table_name
GROUP BY any_criterial

    나는이 다른 의견의 대부분은이 웹 사이트에서 나를 위해했던 방법으로 누군가를 위해 도움이 될 수 있기를 바랍니다.

  26. ==============================

    26.정확한 행 개수를 알면 당신은이 쿼리를 사용할 수 있습니다 :

    정확한 행 개수를 알면 당신은이 쿼리를 사용할 수 있습니다 :

    SELECT <value> AS VAL FROM <table> ORDER BY VAL LIMIT 1 OFFSET <half>

    여기서 <반> = 천장 (<사이즈> / 2.0) - 1

  27. ==============================

    27.나는 우리가 세트의 평균 연령을 결정하기 위해 필요로하는 10 억에 대한 행을 포함하는 데이터베이스가 있습니다. 억 행을 정렬하는 것은 어렵다,하지만 당신은 (나이가 0 ~ 100의 범위)를 찾을 수있는 고유 한 값을 집계 경우,이 목록을 정렬하고 다음과 같이 백분위 어떤 원하는 찾기 위해 몇 가지 산술 마법을 사용할 수 있습니다 :

    나는 우리가 세트의 평균 연령을 결정하기 위해 필요로하는 10 억에 대한 행을 포함하는 데이터베이스가 있습니다. 억 행을 정렬하는 것은 어렵다,하지만 당신은 (나이가 0 ~ 100의 범위)를 찾을 수있는 고유 한 값을 집계 경우,이 목록을 정렬하고 다음과 같이 백분위 어떤 원하는 찾기 위해 몇 가지 산술 마법을 사용할 수 있습니다 :

    with rawData(count_value) as
(
    select p.YEAR_OF_BIRTH
        from dbo.PERSON p
),
overallStats (avg_value, stdev_value, min_value, max_value, total) as
(
  select avg(1.0 * count_value) as avg_value,
    stdev(count_value) as stdev_value,
    min(count_value) as min_value,
    max(count_value) as max_value,
    count(*) as total
  from rawData
),
aggData (count_value, total, accumulated) as
(
  select count_value, 
    count(*) as total, 
        SUM(count(*)) OVER (ORDER BY count_value ROWS UNBOUNDED PRECEDING) as accumulated
  FROM rawData
  group by count_value
)
select o.total as count_value,
  o.min_value,
    o.max_value,
    o.avg_value,
    o.stdev_value,
    MIN(case when d.accumulated >= .50 * o.total then count_value else o.max_value end) as median_value,
    MIN(case when d.accumulated >= .10 * o.total then count_value else o.max_value end) as p10_value,
    MIN(case when d.accumulated >= .25 * o.total then count_value else o.max_value end) as p25_value,
    MIN(case when d.accumulated >= .75 * o.total then count_value else o.max_value end) as p75_value,
    MIN(case when d.accumulated >= .90 * o.total then count_value else o.max_value end) as p90_value
from aggData d
cross apply overallStats o
GROUP BY o.total, o.min_value, o.max_value, o.avg_value, o.stdev_value
;

    이 쿼리는 (이전 UNBOUNDED ROWS 포함) DB를 지원하는 윈도우 함수에 의존하지만 당신은 그 자체로 aggData CTE에 참여를 결정하는 데 사용되는 '축적'컬럼에 이전의 모든 합계를 집계하는 간단한 문제라고하지 않은 경우 어떤 값은 지정된 precentile가 포함되어 있습니다. 상기 샘플의 calcuates P10, P25, P50 (중앙값), P75 및 P90.

    크리스

  28. ==============================

    28.에서 발췌 : http://mdb-blog.blogspot.com/2015/06/mysql-find-median-nth-element-without.html

    에서 발췌 : http://mdb-blog.blogspot.com/2015/06/mysql-find-median-nth-element-without.html

    내가 가입하지 않고, 다른 방법을 제안 하지만 문자열 작업

    나는 큰 데이터 테이블을 선택하지 않았다 하지만 작은 / 매체 테이블은 잘 작동합니다.

    여기에 좋은 것, 그것은 그룹화하여도 작동은 몇 가지 항목에 대한 평균을 반환 할 수 있습니다.

    여기에 테스트 테이블에 대한 테스트 코드는 다음과 같습니다

    DROP TABLE test.test_median
CREATE TABLE test.test_median AS
SELECT 'book' AS grp, 4 AS val UNION ALL
SELECT 'book', 7 UNION ALL
SELECT 'book', 2 UNION ALL
SELECT 'book', 2 UNION ALL
SELECT 'book', 9 UNION ALL
SELECT 'book', 8 UNION ALL
SELECT 'book', 3 UNION ALL

SELECT 'note', 11 UNION ALL

SELECT 'bike', 22 UNION ALL
SELECT 'bike', 26

    각 그룹에 대한 평균을 찾기위한 코드 :

    SELECT grp,
         SUBSTRING_INDEX( SUBSTRING_INDEX( GROUP_CONCAT(val ORDER BY val), ',', COUNT(*)/2 ), ',', -1) as the_median,
         GROUP_CONCAT(val ORDER BY val) as all_vals_for_debug
FROM test.test_median
GROUP BY grp

    산출:

    grp | the_median| all_vals_for_debug
bike| 22        | 22,26
book| 4         | 2,2,3,4,7,8,9
note| 11        | 11
  29. ==============================

    29.다음과 같이 어떤 경우에는 중간 계산됩니다 :

    다음과 같이 어떤 경우에는 중간 계산됩니다 :

    그들은 값으로 정렬 할 때 "중간"는 번호 목록에서 "중간"값입니다. 심지어 계수 세트의 중앙값은 두 개의 중간 값의 평균이다. 그에 대한 간단한 코드를 만들었습니다 :

    $midValue = 0;
$rowCount = "SELECT count(*) as count {$from} {$where}";

$even = FALSE;
$offset = 1;
$medianRow = floor($rowCount / 2);
if ($rowCount % 2 == 0 && !empty($medianRow)) {
  $even = TRUE;
  $offset++;
  $medianRow--;
}

$medianValue = "SELECT column as median 
               {$fromClause} {$whereClause} 
               ORDER BY median 
               LIMIT {$medianRow},{$offset}";

$medianValDAO = db_query($medianValue);
while ($medianValDAO->fetch()) {
  if ($even) {
    $midValue = $midValue + $medianValDAO->median;
  }
  else {
    $median = $medianValDAO->median;
  }
}
if ($even) {
  $median = $midValue / 2;
}
return $median;

    반환 $의 평균은 필요한 결과가 될 것입니다 :-)

  30. ==============================

    30.차원별로 그룹화 중간 값 :

    차원별로 그룹화 중간 값 :

    SELECT your_dimension, avg(t1.val) as median_val FROM (
SELECT @rownum:=@rownum+1 AS `row_number`,
   IF(@dim <> d.your_dimension, @rownum := 0, NULL),
   @dim := d.your_dimension AS your_dimension,
   d.val
   FROM data d,  (SELECT @rownum:=0) r, (SELECT @dim := 'something_unreal') d
  WHERE 1
  -- put some where clause here
  ORDER BY d.your_dimension, d.val
) as t1
INNER JOIN  
(
  SELECT d.your_dimension,
    count(*) as total_rows
  FROM data d
  WHERE 1
  -- put same where clause here
  GROUP BY d.your_dimension
) as t2 USING(your_dimension)
WHERE 1
AND t1.row_number in ( floor((total_rows+1)/2), floor((total_rows+2)/2) )

GROUP BY your_dimension;

    31. from https://stackoverflow.com/questions/1291152/simple-way-to-calculate-median-with-mysql by cc-by-sa and MIT license

'SQL' 카테고리의 다른 글

[SQL] 어떻게 2005 SQL 서버에 하나 개의 문장에서 두 테이블을 업데이트?  (0) 2020.03.11
[SQL] 선택 열에 대 선택 *  (0) 2020.03.11
[SQL] 엄격한 관계 모델 전자 상거래 대 법인 속성 값 데이터베이스  (0) 2020.03.11
[SQL] CASE 및 GROUP BY와 피벗에 동적 대안  (0) 2020.03.11
[SQL] SQL가 null = NULL [중복]  (0) 2020.03.10
,

티스토리툴바