在MySQL中,如何计算一组数据的中位数?,mysql中

作者:分分快三全天计划网站

建表语句:

在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个自定义变量的法子来促成这一指标:

SELECT (COUNT(*)   1) DIV 2
FROM income;
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
  name,
  income
FROM income
ORDER BY income DESC

时现今日,大家就找到了哪些从一组数据中收获中位数的议程。

 

小义务2的查询语句:

小职分3的查询语句:

下边,来介绍别的一种优化排名语句的点子。

小任务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;

  

那大家可不可以更进一竿,对查询出的结果加一列,这一列的多少为排行呢?

从那之后,大家找了三种办法来化解中位数的标题。撒花。

  • 首先个变量用来记录当前行数据的入账
  • 其次个变量用来记录上一行数据的进项
  • 其三个变量用来记录当前行数据的排行

    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

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)
  1. 将数据排序,并给每一行数据交到其在装有数据中的排行。
  2. 寻觅中位数的排行数字。
  3. 寻觅中间排行对应的值。

询问结果如下:

大家都晓得什么给一组数据做排序操作,在本例中,完成格局如下:

分分快三全天计划网站 3

分分快三全天计划网站 4

比如表明:

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

要博取一组数据的中位数(举个例子某些地点或某家公司的纯收入中位数),大家率先要将这一义务细分为3个小职务:

本文由分分快三计划发布,转载请注明来源

关键词: 分分快三计划