在MySQL中,如何总括一组数据的中位数?彩民之家

2019-10-11 12:10 来源:未知
SELECT income AS median
FROM
  (SELECT
     a1.name,
     a1.income,
     count(*) AS rank
   FROM income AS a1, income AS a2
   WHERE a1.income < a2.income OR (a1.income = a2.income AND a1.name <= a2.name)
   GROUP BY a1.name, a1.income
   ORDER BY rank) a3

WHERE rank = (SELECT (COUNT(*)   1) DIV 2
              FROM income)

在MySQL中,如何总结一组数据的中位数?,mysql中位数

要获取一组数据的中位数(比如有个别地区或某家集团的纯收入中位数),大家第一要将这一任务细分为3个小职务:

比如表达:

彩民之家高手论坛 1

建表语句:

CREATE TABLE `income` (
  `name`   VARCHAR(10) NOT NULL DEFAULT '',
  `income` INT(11)     NOT NULL DEFAULT '0'
)
  ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

INSERT INTO test.income (name, income) VALUES ('麻子', 20000);
INSERT INTO test.income (name, income) VALUES ('李四', 12000);
INSERT INTO test.income (name, income) VALUES ('张三', 10000);
INSERT INTO test.income (name, income) VALUES ('王二', 16000);
INSERT INTO test.income (name, income) VALUES ('土豪', 40000);

  

小任务1的查询语句:

SELECT
  a1.name,
  a1.income,
  count(*) AS rank
FROM income AS a1, income AS a2
WHERE a1.income < a2.income OR (a1.income = a2.income AND a1.name <= a2.name)
GROUP BY a1.name, a1.income
ORDER BY rank;

小职分2的查询语句:

SELECT (COUNT(*)   1) DIV 2
FROM income;

小职务3的查询语句:

SELECT income AS median
FROM
  (SELECT
     a1.name,
     a1.income,
     count(*) AS rank
   FROM income AS a1, income AS a2
   WHERE a1.income < a2.income OR (a1.income = a2.income AND a1.name <= a2.name)
   GROUP BY a1.name, a1.income
   ORDER BY rank) a3

WHERE rank = (SELECT (COUNT(*)   1) DIV 2
              FROM income)

由来,大家就找到了什么从一组数据中获取中位数的情势。

上面,来介绍别的一种优化排行语句的点子。

笔者们都明白如何给一组数据做排序操作,在本例中,达成方式如下:

SELECT
  name,
  income
FROM income
ORDER BY income DESC

那大家可不得以更上一层楼,对查询出的结果加一列,这一列的数额为排行呢?

咱俩能够通过3个自定义变量的主意来兑现这一目的:

  • 先是个变量用来记录当前行数据的纯收入
  • 第三个变量用来记录上一行数据的低收入
  • 其多个变量用来记录当前行数据的排行
SET @curr_income := 0;
SET @prev_income := 0;
SET @rank := 0;

SELECT
  name,
  @curr_income := income                                      AS income,
  @rank := if(@prev_income != @curr_income, @rank   1, @rank) AS rank,
  @prev_income := @curr_income                                AS dummy
FROM income
ORDER BY income DESC

询问结果如下:

彩民之家高手论坛 2

下一场再搜索中位数的排行数字,进一步寻觅收入的中位数:

SET @curr_income := 0;
SET @prev_income := 0;
SET @rank := 0;

SELECT income AS median
FROM
  (SELECT
     name,
     @curr_income := income                                      AS income,
     @rank := if(@prev_income != @curr_income, @rank   1, @rank) AS rank,
     @prev_income := @curr_income                                AS dummy
   FROM income
   ORDER BY income DESC) AS a1
WHERE a1.rank = (SELECT (COUNT(*)   1) DIV 2
                 FROM income)

由来,大家找了二种艺术来消除中位数的标题。撒花。

 

要赢得一组数据的中位数(举例有个别地点或某家集团的收入中位数),大家先是要将...

建表语句:

咱俩能够通过3个自定义变量的方式来促成这一目的:

  

至此,大家就找到了什么从一组数据中赢得中位数的主意。

咱俩都理解如何给一组数据做排序操作,在本例中,实现情势如下:

由来,大家找了三种方法来缓慢解决中位数的主题材料。撒花。

彩民之家高手论坛 3

SELECT
  a1.name,
  a1.income,
  count(*) AS rank
FROM income AS a1, income AS a2
WHERE a1.income < a2.income OR (a1.income = a2.income AND a1.name <= a2.name)
GROUP BY a1.name, a1.income
ORDER BY rank;
CREATE TABLE `income` (
  `name`   VARCHAR(10) NOT NULL DEFAULT '',
  `income` INT(11)     NOT NULL DEFAULT '0'
)
  ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

INSERT INTO test.income (name, income) VALUES ('麻子', 20000);
INSERT INTO test.income (name, income) VALUES ('李四', 12000);
INSERT INTO test.income (name, income) VALUES ('张三', 10000);
INSERT INTO test.income (name, income) VALUES ('王二', 16000);
INSERT INTO test.income (name, income) VALUES ('土豪', 40000);

小任务2的查询语句:

 

  1. 将数据排序,并给每一行数据交由其在享有数据中的排名。
  2. 寻觅中位数的排名数字。
  3. 找寻中间排行对应的值。

彩民之家高手论坛 4

上面,来介绍别的一种优化排行语句的主意。

小职分1的查询语句:

这我们好还是倒霉更进一竿,对查询出的结果加一列,这一列的多寡为排名呢?

  • 率先个变量用来记录当前行数据的收益
  • 第2个变量用来记录上一行数据的受益
  • 其四个变量用来记录当前行数据的排名

    SET @curr_income := 0; SET @prev_income := 0; SET @rank := 0;

    SELECT name, @curr_income := income AS income, @rank := if(@prev_income != @curr_income, @rank 1, @rank) AS rank, @prev_income := @curr_income AS dummy FROM income ORDER BY income DESC

要赢得一组数据的中位数(举个例子有些地点或某家公司的受益中位数),大家率先要将这一职分细分为3个小职责:

小职务3的查询语句:

SELECT
  name,
  income
FROM income
ORDER BY income DESC

查询结果如下:

SET @curr_income := 0;
SET @prev_income := 0;
SET @rank := 0;

SELECT income AS median
FROM
  (SELECT
     name,
     @curr_income := income                                      AS income,
     @rank := if(@prev_income != @curr_income, @rank   1, @rank) AS rank,
     @prev_income := @curr_income                                AS dummy
   FROM income
   ORDER BY income DESC) AS a1
WHERE a1.rank = (SELECT (COUNT(*)   1) DIV 2
                 FROM income)
SELECT (COUNT(*)   1) DIV 2
FROM income;

比如表明:

下一场再找寻中位数的排名数字,进一步搜索收入的中位数:

TAG标签:
版权声明:本文由彩民之家高手论坛发布于彩民之家高手论坛,转载请注明出处:在MySQL中,如何总括一组数据的中位数?彩民之家