当前位置: 澳门新濠3559 > 数据库 > 正文

建表语句,小任务2的查询语句澳门新濠3559

时间:2019-10-06 19:49来源:数据库
建表语句,小任务2的查询语句澳门新濠3559。要得到一组数据的中位数(例如某个地区或某家公司的收入中位数),我们首先要将这一任务细分为3个小任务: 在MySQL中,如何计算一组数

建表语句,小任务2的查询语句澳门新濠3559。要得到一组数据的中位数(例如某个地区或某家公司的收入中位数),我们首先要将这一任务细分为3个小任务:

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

要得到一组数据的中位数(例如某个地区或某家公司的收入中位数),我们首先要将这一任务细分为3个小任务:

举例说明:

澳门新濠3559 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

查询结果如下:

澳门新濠3559 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)

至此,我们找了两种方法来解决中位数的问题。撒花。

 

要得到一组数据的中位数(例如某个地区或某家公司的收入中位数),我们首先要将...

分析函数是什么?
分析函数是Oracle专门用于解决复杂报表统计需求的功能强大的函数,它可以在数据中进行分组然后计算基于组的某种统计值,并且每一组的每一行都可以返回一个统计值。

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

          

举例说明:

分析函数和聚合函数的不同之处是什么?
普通的聚合函数用group by分组,每个分组返回一个统计值,而分析函数采用partition by分组,并且每组每行都可以返回一个统计值。

澳门新濠3559 3

              

建表语句:

分析函数的形式
分析函数带有一个开窗函数over(),包含三个分析子句:分组(partition by), 排序(order by), 窗口(rows) ,他们的使用形式如下:over(partition by xxx order by yyy rows between zzz)。
注:窗口子句在这里我只说rows方式的窗口,range方式和滑动窗口也不提

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

    

  

分析函数例子(在scott用户下模拟)

小任务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 E.DEPTNO,
       E.EMPNO,
       E.ENAME,
       E.SAL,
       LAST_VALUE(E.SAL) 
       OVER(PARTITION BY E.DEPTNO 
            ORDER BY E.SAL ROWS 
            --unbounded preceding and unbouned following针对当前所有记录的前一条、后一条记录,也就是表中的所有记录
            --unbounded:不受控制的,无限的
            --preceding:在...之前
            --following:在...之后
            BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) MAX_SAL
  FROM EMP E;
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)

澳门新濠3559 4

至此,我们就找到了如何从一组数据中获得中位数的方法。

示例目的:按照deptno分组,然后计算每组值的总和

下面,来介绍另外一种优化排名语句的方法。

 

我们都知道如何给一组数据做排序操作,在本例中,实现方法如下:

SELECT EMPNO,
       ENAME,
       DEPTNO,
       SAL,
       SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY ENAME) max_sal
  FROM SCOTT.EMP;
SELECT
  name,
  income
FROM income
ORDER BY income DESC

 

那我们可不可以更进一步,对查询出的结果加一列,这一列的数据为排名呢?

 运行结果:

我们可以通过3个自定义变量的方法来实现这一目标:

澳门新濠3559 5

  • 第一个变量用来记录当前行数据的收入
  • 第二个变量用来记录上一行数据的收入
  • 第三个变量用来记录当前行数据的排名

    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

示例目的:对各部门进行分组,并附带显示第一行至当前行的汇总

查询结果如下:

 

澳门新濠3559 6

SELECT EMPNO,
       ENAME,
       DEPTNO,
       SAL,
       --注意ROWS BETWEEN unbounded preceding AND current row  是指第一行至当前行的汇总
       SUM(SAL) OVER(PARTITION BY DEPTNO 
                     ORDER BY ENAME 
                     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) max_sal
  FROM SCOTT.EMP;

然后再找出中位数的排名数字,进一步找出收入的中位数:

 

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)

 运行结果:

至此,我们找了两种方法来解决中位数的问题。撒花。

澳门新濠3559 7

 

示例目标:当前行至最后一行的汇总

SELECT EMPNO,
       ENAME,
       DEPTNO,
       SAL,
       --注意ROWS BETWEEN current row AND unbounded following 指当前行到最后一行的汇总
       SUM(SAL) OVER(PARTITION BY DEPTNO 
                     ORDER BY ENAME 
                     ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) max_sal
  FROM SCOTT.EMP;

 运行结果:

澳门新濠3559 8

 示例目标:当前行的上一行(rownum-1)到当前行的汇总

 

SELECT EMPNO,
       ENAME,
       DEPTNO,
       SAL,
       --注意ROWS BETWEEN 1 preceding AND current row 是指当前行的上一行(rownum-1)到当前行的汇总 
       SUM(SAL) OVER(PARTITION BY DEPTNO 
                     ORDER BY ENAME ROWS 
                     BETWEEN 1 PRECEDING AND CURRENT ROW) max_sal
  FROM SCOTT.EMP;

 

运行结果:

澳门新濠3559 9

示例目标:   当前行的上一行(rownum-1)到当前行的下辆行(rownum 2)的汇总

 

SELECT EMPNO,
       ENAME,
       DEPTNO,
       SAL,
       --注意ROWS BETWEEN 1 preceding AND 1 following 是指当前行的上一行(rownum-1)到当前行的下辆行(rownum 2)的汇总
       SUM(SAL) OVER(PARTITION BY DEPTNO 
                     ORDER BY ENAME 
                     ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING) max_sal
  FROM SCOTT.EMP;

 

运行结果:

澳门新濠3559 10

评级函数

常见评级函数如下:

  • RANK():返回数据项在分组中的排名,在排名相等时会在名次中留下空位,造成排名不连续。
  • DENSE_RANK():同样返回数据项在分组中排名,不过在排名相等时不会留下名位空位。
  • CUME_DIST():返回特定值相对于一组值的位置,是累积分布(cumulative distribution)的简写。
  • PERCENT_RANK():返回某个值相对于一组值的百分比排名。
  • NTILE():返回n分片后的值,如三分片、四分片等。
  • ROW_NUMBER():为每一条分组记录返回一个数字,注意不同于rownum伪列。

RANK()和DENSE_RANK()

rank()和dense_rank()函数都可用于计算数据项在分组中(在不使用partition by时以所有数据为一个分组)的排名。它们的区别在于rank()在排名相等时,如:有3个第1名时,则下一个排名为第4名,没有2、3名;而dense_rank()则在有3个第1名时,下一个排名为第2名。即,rank()会出现排名间隔,而dense_rank()则不会出现排名间隔。

这两个函数多用于select子句中,在不进行分组的情况下,可以不使用partition by子句。其使用举例如,找出公司所有人工资排名:

select ename,

rank() over (order by sal desc) rank,

dense_rank() over (order by sal desc) dense_rank

from emp;

从语句中可以看出,rank()函数需要有关键字over和order by。而且rank()是一个单值函数,而不是聚合函数。若需要找出每种工作的最高工资在所有工作最高工资中的排名:

澳门新濠3559,select job,

rank() over (order by max(sal) desc) rank,

dense_rank() over (order by max(sal) desc) dense_rank

from emp

group by job;

在排名中,会出现NULL值在前在后的问题,可以在ORDER BY子句之后使用关键字NULLS FIRST/LAST来控制。

PARTITION BY子句

当需要进行获得分组后各组内的排名,则需要使用partition by子句。它不同于group by的分组,这种分组不“合并聚合”,它相当于把值分组后计算,然后重复每个值。

最常见的例子如:在table表中有name(姓名)、class(班级)和score(分数)三个字段,求每个班级里前三名姓名、班级及分数,SQL语句为:

select name,class,score

from (select name,

class,

score,

rank() over(partition by class order by score desc) rank

from table)

where rank <= 3;

在SCOTT用户中测试,求每个部门工资前3名的人姓名、部门、工作和工资,如:

select *

from (select ename,

deptno,

job,

sal,

dense_rank() over(partition by deptno order by sal desc) rank

from emp)

where rank <= 3;

ROW_NUMBER()

row_number为每一行返回一个数字,在分组中较常用(rownum在非分组中常用)。如,给emp表中每种工作工资由高到低进行排序:

select ename,job,sal,row_number() over (partition by job order by sal desc) from emp;

窗口函数(累计和、移动平均值等)

窗口函数可用来计算累计和、移动平均值和中心平均值等,具体如下:

计算累计和

查询从2003年1月到12月的累计销量,SQL语句如下:

SELECT month,

SUM(amount) AS month_amount,

SUM(SUM(amount)) OVER (ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_amount

FROM all_sales

where year = 2003

GROUP BY month

ORDER BY month;

对于累计部分SUM(SUM(amount)) OVER (ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)解析如下:

  • SUM(SUM(amount))中内部的SUM(amount)用于计算月销量总和,外部的SUM()用于计算累计销量。
  • ORDER BY month 按月份对查询读取的记录进行排序。
  • ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW定义了窗口的起点和终点,起点为UNBOUNDED PRECEDING,意味着起点为固定的查询结果集的第一行;终点为CURRENT ROW表示终点为处理结果集的当前行。当外部SUM函数计算返回当前的累计销量后,窗口的终点便向下移动一行。PRECEDING表示向上累计数,若将UNBOUNDED换成数字如1,则表示跟之前一条记录做累积;同时还可以向后,使用关键字FOLLOWING,指定向后累积数只需要在该关键字前加数字即可,该数字为向后累积的行数(从这里也可以看出排序的重要性)。

如:

若要计算指定月份如6月到12月的累积销量,则只需要在where子句中再增加条件month between 6 and 12即可。

计算当月跟前3个月累积销量,窗口语句:

SUM(SUM(amount)) OVER (ORDER BY month ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS cumulative_amount

计算前一个月和后一个月累积销量,窗口语句:

SUM(SUM(amount)) OVER (ORDER BY month ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS cumulative_amount

计算移动平均值

计算本月与前3个月之间销量的移动平均值,SQL语句如下:

SELECT month,

SUM(amount) AS month_amount,

AVG(SUM(amount)) OVER (ORDER BY month ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS moving_average

FROM all_sales

where year = 2003

GROUP BY month

ORDER BY month;

对移动平均值部分AVG(SUM(amount)) OVER (ORDER BY month ROWS BETWEEN 3 PRECEDING AND CURRENT ROW)解析如下:

  • AVG(SUM(amount))内部的sum(amount)计算月销量和,外部的avg()计算平均值。
  • ORDER BY month 按月份对查询读取的记录进行排序(这是必须的,因为只有排序后才能做累积或前后求平均值)。
  • ROWS BETWEEN 3 PRECEDING AND CURRENT ROW定义了窗口的起点为当前记录的前3条记录,窗口的终点为当前记录。

计算中心平均值

计算当前月份前、后各一个月的销量移动平均值,SQL语句如下:

SELECT month,

SUM(amount) AS month_amount,

AVG(SUM(amount)) OVER (ORDER BY month ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS moving_average

FROM all_sales

where year = 2003

GROUP BY month

ORDER BY month;

对中心平均值部分AVG(SUM(amount)) OVER (ORDER BY month ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)解析如下:

  • AVG(SUM(amount))内部的sum(amount)计算月销量和,外部的avg()计算平均值。
  • ORDER BY month 按月份对查询读取的记录进行排序(这是必须的,因为只有排序后才能做累积或前后求平均值)。
  • ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING定义了窗口的起点是当前记录之前的那条记录,窗口的终点是当前记录之后的那条记录。

窗口第一条和最后一条记录

FIRST_VALUE()和LAST_VALUE()函数可用于获取窗口中的第一行和最后一行数据,如,可用于获取当前月前一个月和后一个月的销量:

SELECT month,

SUM(amount) AS month_amount,

FIRST_VALUE(SUM(amount)) OVER (ORDER BY month ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS pre_month_amount,

LAST_VALUE(SUM(amount)) OVER (ORDER BY month ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS next_month_amount

FROM all_sales

where year = 2003

GROUP BY month

ORDER BY month;

其中,窗口定义了起点为前一个月终点为后一个月,故而first_value(sum(amount))为前一个月销量而last_value()为后一个月销量。

 

编辑:数据库 本文来源:建表语句,小任务2的查询语句澳门新濠3559

关键词: 澳门新濠3559