精华内容
下载资源
问答
  • 【Oracle】Oracle分析函数详解

    千次阅读 2019-05-25 22:45:09
    本篇将详细介绍Oracle数据库中的分析函数。 一 分析函数概述 所谓分析函数,是基于一组数据行计算聚合值,其与聚合函数的不同之处在于,它为每一组返回多个数据行。一组数据行称为一个窗口,由analytic_clause子句...

    Oracle数据库中的函数有多种,比如单行函数、聚合函数、对象引用函数、模型函数、OLAP函数等。本篇将详细介绍Oracle数据库中的分析函数。

    一 分析函数概述

    所谓分析函数,是基于一组数据行计算聚合值,其与聚合函数的不同之处在于,它为每一组返回多个数据行。一组数据行称为一个窗口,由analytic_clause子句进行定义,对于每一行,定义一个行移动窗口,窗口确定用于为当前行执行计算的行的范围,它的大小可以基于物理行或逻辑间隔(如时间)。

    二 分析函数语法

    说明:

    analytic_function

    指出分析函数的名称;

    arguments

    分析函数的参数,参数数量在0个到3个之间,该参数类型可以是任何数值数据类型,或者任何可以隐式转换为数值数据类型的非数值类型。

    analytic_clause

    使用OVER analytic_clause子句展示在查询结果集上所进行的函数操作,该子句是在FROM、WHERE、GROUP BY和HAVING子句后进行计算的。

     使用PARTITION BY子句可以基于一个或多个expr将查询结果集划分为多个组,如果忽略该子句,则函数会将整个查询结果集当做一个组。

    order_by_clause子句用于指定数据是在一个分组里是如何进行排序的。

    三 分析函数类型

    Oracle有以下类型的分析函数,其中,带有*号的函数可以使用上面的完全语句,包括窗口子句。

    • AVG *
    • COPR *
    • COUNT *
    • COVAR_POP *
    • COVAR_SAMP *
    • CUME_DIST
    • DENSE_RANK
    • FIRST
    • FIRST_VALUE *
    • LAG
    • LAST
    • LAST_VALUE *
    • LEAD
    • LISTAGG
    • MAX *
    • MEDIAN
    • MIN *
    • NTH_VALUE *
    • NTILE
    • PERCENT_RANK
    • PERCENTILE_COUNT
    • PERCENTILE_DISC
    • RANK
    • RATIO_TO_REPORT
    • REGR_(Linear Regression) Functions *
    • ROW_NUMBER
    • STDDEV *
    • STDDEV_POP *
    • STDDEV_SAMP *
    • SUM *
    • VAR_POP *
    • VAR_SAMP *
    • VARIANCE *

    四 分析函数详解

    1 演示数据库版本

    SQL> select * from v$version;
    BANNER
    --------------------------------------------------------------------------------
    Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
    PL/SQL Release 11.2.0.4.0 - Production
    CORE 11.2.0.4.0 Production
    TNS for Linux: Version 11.2.0.4.0 - Production
    NLSRTL Version 11.2.0.4.0 - Production

    2 AVG函数

    1)语法结构

    AVG函数用于返回expr的平均值,如果指定DISTINCT,那么只能指定analytic_clause中的query_partition_clause子句,不能指定order_by_clause andwindowing_clause子句。

    2)示例

    SQL> SELECT empno,
    ename,
    job,
    mgr,
    hiredate,
    deptno,
    sal,
    round(AVG(sal) over(), 2) avg_all_sal,
    round(AVG(sal) over(PARTITION BY deptno), 2) avg_dept_sal,
    round(AVG(sal) over(PARTITION BY deptno ORDER BY empno
    rows BETWEEN 1 preceding AND 1 following),2) avg_sal1,
    round(AVG(sal) over(PARTITION BY deptno ORDER BY empno
    rows BETWEEN CURRENT ROW AND 1 following),2) avg_sal2
    FROM emp;

     3 COUNT

    1)语法结构

    COUNT返回查询的行数,如果指定DISTINCT,那么只能指定analytic_clause中的query_partition_clause子句,不能指定order_by_clause andwindowing_clause子句。如果指定expr,COUNT返回expr非空的行数,如果指定*,则返回所有行数,包括重复行和空行。

    2)示例

    SQL> SELECT empno,
    ename,
    job,
    mgr,
    hiredate,
    deptno,
    sal,
    comm,
    COUNT(1) over() qty1,
    COUNT(*) over() qty2,
    COUNT(comm) over() qty3,
    COUNT(DISTINCT deptno) over() qty4,
    COUNT(1) over(PARTITION BY deptno) qty5
    FROM emp;

    4 RANK、DENSE_RANK、ROW_NUMBER

    1)语法结构

    2)示例

    SQL> SELECT empno,
    ename,
    job,
    mgr,
    hiredate,
    sal,
    deptno,
    --row_number() over(ORDER BY sal) rn1,
    row_number() over(PARTITION BY deptno ORDER BY sal) rn2,
    --rank()over(order by sal) rn3
    rank() over(PARTITION BY deptno ORDER BY sal) r4,
    dense_rank() over(PARTITION BY deptno ORDER BY sal) r5
    FROM emp;

    注:这三个函数主要用于进行排序,ROW_NUMBER函数排序的结果是连续的,而DENSE函数排序会出现跳号,DENSE_RANK函数排序,不会出现跳号。

    5 FIRST_VALUE

    1)语法结构

    FIRST_VALUE用于返回在一个排序结果集中的第一个值。

    2)示例

    SQL> SELECT empno,
    ename,
    job,
    mgr,
    hiredate,
    sal,
    deptno,
    first_value(sal) over(PARTITION BY deptno ORDER BY sal) sal,
    first_value(sal) over(PARTITION BY deptno ORDER BY sal DESC) sal2
    FROM emp;

    6 LAST_VALUE

    1)语法结构

    对于该函数,如果忽略了analytic_clause子句中的windowing_clause子句,默认是使用RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ,使用默认值有时可能返回不是预期的结果,因为LAST VALUE是窗口中的最后一个值,它不是固定的,而是随着当前行的改变而改变。为了得到与其的结果,可以使用RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING窗口,也可以使用RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING窗口。

    2)示例

    SQL> SELECT empno,
    ename,
    job,
    mgr,
    hiredate,
    sal,
    deptno,
    last_value(sal) over(PARTITION BY deptno ORDER BY sal) sal1,
    last_value(sal) over(PARTITION BY deptno ORDER BY sal RANGE BETWEEN unbounded preceding AND CURRENT ROW) sal2,
    last_value(sal) over(PARTITION BY deptno ORDER BY sal RANGE BETWEEN unbounded preceding AND unbounded following) sal3,
    last_value(sal) over(PARTITION BY deptno ORDER BY sal RANGE BETWEEN CURRENT ROW AND unbounded following) sal4,
    last_value(sal) over(PARTITION BY deptno ORDER BY sal DESC RANGE BETWEEN unbounded preceding AND unbounded following) sal5,
    last_value(sal) over(PARTITION BY deptno ORDER BY sal rows BETWEEN unbounded preceding AND unbounded following) sal6
    FROM emp;

    7 LAG

    1)语法结构

    该函数提供了在不使用自连接的情况下同时访问表的多行的能力,即将数据行根据偏移量靠后,offset参数指定偏移量,默认值为1,default参数指定超过窗口边界的默认值,没有指定,则为NULL。

    2)示例

    SQL> SELECT empno,
    ename,
    job,
    mgr,
    hiredate,
    sal,
    deptno,
    lag(sal) over(PARTITION BY deptno ORDER BY sal) sal1,
    lag(sal, 1) over(PARTITION BY deptno ORDER BY sal) sal2,
    lag(sal, 2, 0) over(PARTITION BY deptno ORDER BY sal) sal3
    FROM emp;

    8 LEAD

    1)语法结构

    该函数和LAG函数功能相反,主要用于将行数据提前。

    2)示例

    SQL> SELECT empno,
    ename,
    job,
    mgr,
    hiredate,
    sal,
    deptno,
    lead(sal) over(PARTITION BY deptno ORDER BY sal) sal1,
    lead(sal, 1) over(PARTITION BY deptno ORDER BY sal) sal2,
    lead(sal, 2, 0) over(PARTITION BY deptno ORDER BY sal) sal3
    FROM emp;

    9 LISTAGG

    1)语法结构

    该函数用于在分组内对数据进行排序,然后将对应的值进行拼接,参数delimilter用于指定拼接符。

    2)示例

    SQL> SELECT empno,
    ename,
    job,
    mgr,
    hiredate,
    sal,
    deptno,
    listagg(ename, ',') within GROUP(ORDER BY empno) over(PARTITION BY deptno) name1
    FROM emp;

    思考:如果拼接的列数据类型为varchar2,则有无长度限制,如果超过了该怎么处理?

    SQL> SELECT deptno,
    dbms_lob.substr(substr(rtrim(xmlcast(xmlagg(xmlelement(e,
    ename || ',')
    ORDER BY empno) AS CLOB),
    ','),
    1,
    100)) NAME
    FROM emp
    GROUP BY deptno;

    10 MAX、MIN、SUM

    1)语法结构

    这三个函数主要用于在分组内求最大值、最小值以及求和。

    2)示例

    SQL> SELECT empno,
    ename,
    job,
    mgr,
    hiredate,
    sal,
    deptno,
    MAX(sal) over() sal1,
    MAX(sal) over(PARTITION BY deptno) sal2,
    MAX(sal) over(PARTITION BY deptno ORDER BY empno rows BETWEEN unbounded preceding AND 1 following) sal3,
    MIN(sal) over() sal4,
    MIN(sal) over(PARTITION BY deptno) sal5,
    MIN(sal) over(PARTITION BY deptno ORDER BY empno rows BETWEEN unbounded preceding AND 1 following) sal6,
    SUM(sal) over() sal7,
    SUM(sal) over(PARTITION BY deptno) sal8,
    SUM(sal) over(PARTITION BY deptno ORDER BY empno rows BETWEEN unbounded preceding AND 1 following) sal9
    FROM emp;

    11 CUME_DIST

    1)语法结构

    该函数用于计算一个值在一组值中的累积分布,返回的值的范围是0到1。计算逻辑为:在一组值中某一个值的相对位置,即对于某一行R,总行数为N,值为R/N。

    2)示例

    SQL> SELECT empno,
    ename,
    job,
    mgr,
    hiredate,
    sal,
    deptno,
    cume_dist() over(ORDER BY sal) p1,
    cume_dist() over(PARTITION BY deptno ORDER BY sal) p2
    FROM emp
    ORDER BY deptno, sal;

    12 PERCENT_RANK

    1)语法结构

    该函数和CUME_DIST函数类似,返回值的范围是0到1,使用该函数的任何集合的第一行是0。计算逻辑为:对于某一行R,总行数为N,值为(R-1)/(N-1)。

    2)示例

    SQL> SELECT empno,
    ename,
    job,
    mgr,
    hiredate,
    sal,
    deptno,
    percent_rank() over(ORDER BY sal) p1,
    percent_rank() over(PARTITION BY deptno ORDER BY sal) p2
    FROM emp
    ORDER BY deptno, sal;

     

    展开全文
  • MySQL分析函数实现

    万次阅读 2018-10-29 10:09:08
    | MySQL分析函数实现 还好MySQL8.0已经实现了与Oracle相同的分析函数。 1. 实现rownum SET @rn:=0; SELECT @rn:=@rn+1 AS rownum ,e.* FROM emp e; 或者写成: SELECT @rn:=@rn + 1 AS rownum ,e.* FROM ...

    | MySQL分析函数实现

    还好MySQL8.0已经实现了与Oracle相同的分析函数。

    1. 实现rownum

    SET @rn:=0;
    SELECT @rn:=@rn+1 AS rownum ,e.* FROM emp e;

    或者写成:

    SELECT @rn:=@rn + 1 AS rownum ,e.* FROM emp e ,(SELECT @rn:=0) c

    2. 各种分析函数写法 (MySQL实现分析语句时可能遇到的各种计算问题)

    2.1 sum() 实现

    --SQL 执行顺序 ,FROM ,JOIN ,WHERE ,GROUP BY,HAVING ,ORDER BY ,SELECT,

    在Oracle中分页语句的原始语句如下:

    SELECT E.*, SUM(SAL) OVER(PARTITION BY DEPTNO) AS COUNTOVER FROM EMP E;

    SELECT E.*,
       (SELECT SUMOVER
          FROM (SELECT DEPTNO, SUM(SAL) AS SUMOVER
                  FROM EMP E1
                 GROUP BY DEPTNO) X
         WHERE X.DEPTNO = E.DEPTNO) AS COUNTOVER
    FROM EMP E
    ORDER BY DEPTNO;

    Mysql中也是这么实现的:

    SELECT E.*,
       (SELECT SUMOVER
          FROM (SELECT DEPTNO, SUM(SAL) AS SUMOVER
                  FROM emp E1
                 GROUP BY DEPTNO) X
         WHERE X.DEPTNO = E.DEPTNO) AS COUNTOVER
    FROM emp E
    ORDER BY DEPTNO;

    2.2 row_number () 实现

    select 
    e.* ,row_number() over(partition by deptno order by empno) as ROW_NUMBER from emp e;

    我们的默认规则是在from后初始化变量。

    SELECT E.*,
       IF(@DEPTNO = DEPTNO, @RN := @RN + 1, @RN := 1) AS ROW_NUMBER,
       @DEPTNO := DEPTNO AS VAR1
    FROM EMP E, (SELECT @DEPTNO := '', @RN := 0) C
    ORDER BY DEPTNO;

    SELECT E.*,
       IF(@DEPTNO = DEPTNO, @RN := @RN + 1, @RN := 1) AS ROW_NUMBER,
       @DEPTNO := DEPTNO AS VAR1
    FROM EMP E, (SELECT @DEPTNO := '', @RN := 0) C
    ORDER BY DEPTNO;

    这个语句首先执行order by

    2.3 求每个人员占他所在部门总工资的百分比

    在Oracle中实现:

    SELECT E.*,
       TRUNC(SAL / SUM(SAL) OVER(PARTITION BY DEPTNO), 3) AS SALPERCENT
    FROM EMP E
    ORDER BY DEPTNO;

    SELECT E.*,
       SAL / (SELECT SUMOVER
                FROM (SELECT DEPTNO, SUM(SAL) AS SUMOVER
                        FROM emp E1
                       GROUP BY DEPTNO) X
               WHERE X.DEPTNO = E.DEPTNO) AS SalPercent
    FROM emp E
    ORDER BY DEPTNO;

    2.4 求各个部门的总共工资

    Oracle:

    SELECT e.* ,SUM(sal) OVER(PARTITION BY deptno) FROM emp e;

    MySQL:

    SELECT A.*,
       ROUND(CAST(IF(@DEPTNO = DEPTNO, @MAX := @MAX, @MAX := SUMOVER) AS CHAR ),0) AS SUMOVER2,
       @DEPTNO := DEPTNO AS VAR2
    FROM (SELECT E.*,
               IF(@DEPTNO = DEPTNO, @SUM := @SUM + SAL, @SUM := SAL) AS SUMOVER,
               @DEPTNO := DEPTNO AS VAR1
          FROM emp E, (SELECT @DEPTNO := '', @SUM := 0, @MAX := 0) C
         ORDER BY DEPTNO) A
    ORDER BY DEPTNO, SUMOVER DESC;

    子查询的功能实现如下:

    下面是这个语句的结果

    2.5 拿部门第二的工资的人

    首先我们拿第二名的,用Oracle很好实现,不论是第一还是第二。

    SELECT *
    FROM (SELECT E.*,
               ROW_NUMBER() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) AS RN
          FROM EMP E)
    WHERE RN = 2;

    Mysql中第一这么实现:

    在5.6版本,sql_mode非only_full_group_by的情况,我们可以使用如下方式实现

    set global sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
    SELECT * FROM (SELECT e.* FROM emp e ORDER BY deptno,sal ) a GROUP BY deptno;

    在SQL_MODE非only_full_group_by时,MySQL中的group by是只取第一行的,下面我们看取第二行的SQL。

    SELECT *
    FROM (SELECT E.*,
               IF(@DEPTNO = DEPTNO, @RN := @RN + 1, @RN := 1) AS RN,
               @DEPTNO := DEPTNO
          FROM EMP E, (SELECT @RN := 0, @DEPTNO := 0) C
         ORDER BY DEPTNO, SAL DESC) X
    WHERE X.RN = 2;

    2.6 dense_rank()

    dense_rank函数返回一个唯一的值,除非当碰到相同数据时,此时所有相同数据的排名都一样。

    SELECT empno,
    ename,
    sal,
    deptno,
    rank() OVER(PARTITION BY deptno ORDER BY sal desc) as rank,
    dense_rank() OVER(PARTITION BY deptno ORDER BY sal desc) as dense_rank
    FROM emp e;

    MySQL的写法:

    select 
    empno,ename,sal,deptno, 
    if(@deptno = deptno,if(@sal=sal,@rn:=@rn,@rn3:=@rn3+1),@rn:=1) as "RANK() OVER", 
    if(@sal =sal,@rn2:=@rn2 ,if(@deptno = deptno,@rn2:=@rn2+1,@rn2:=1)) as "DENSE_RANK() OVER", 
    if(@deptno = deptno,@rn:=@rn+1,@rn:=1) as "ROW_NUMBER() OVER" 
    , @deptno:=deptno,@sal:=sal 
    from 
    (select empno,ename,sal,deptno from emp a ,(select @rn:=1,@deptno:=0,@rn2:=0,@rn3:=0,@sal:=0,@i:=0) b order by deptno,sal desc) c;

    2.7 连续获得冠军的有哪些

    --请写出一条SQL语句,查询出在此期间连续获得冠军的有哪些,其连续的年份的起止时间是多少,结果如下:

    create table  nba as 
    SELECT '公牛' AS TEAM, '1991' AS Y FROM DUAL UNION ALL
    SELECT '公牛' AS TEAM, '1992' AS Y FROM DUAL UNION ALL
    SELECT '公牛' AS TEAM, '1993' AS Y FROM DUAL UNION ALL
    SELECT '活塞' AS TEAM, '1990' AS Y FROM DUAL UNION ALL
    SELECT '火箭' AS TEAM, '1994' AS Y FROM DUAL UNION ALL
    SELECT '火箭' AS TEAM, '1995' AS Y FROM DUAL UNION ALL
    SELECT '公牛' AS TEAM, '1996' AS Y FROM DUAL UNION ALL
    SELECT '公牛' AS TEAM, '1997' AS Y FROM DUAL UNION ALL
    SELECT '公牛' AS TEAM, '1998' AS Y FROM DUAL UNION ALL
    SELECT '马刺' AS TEAM, '1999' AS Y FROM DUAL UNION ALL
    SELECT '湖人' AS TEAM, '2000' AS Y FROM DUAL UNION ALL
    SELECT '湖人' AS TEAM, '2001' AS Y FROM DUAL UNION ALL
    SELECT '湖人' AS TEAM, '2002' AS Y FROM DUAL UNION ALL
    SELECT '马刺' AS TEAM, '2003' AS Y FROM DUAL UNION ALL
    SELECT '活塞' AS TEAM, '2004' AS Y FROM DUAL UNION ALL
    SELECT '马刺' AS TEAM, '2005' AS Y FROM DUAL UNION ALL
    SELECT '热火' AS TEAM, '2006' AS Y FROM DUAL UNION ALL
    SELECT '马刺' AS TEAM, '2007' AS Y FROM DUAL UNION ALL
    SELECT '凯尔特人' AS TEAM, '2008' AS Y FROM DUAL UNION ALL
    SELECT '湖人' AS TEAM, '2009' AS Y FROM DUAL UNION ALL
    SELECT '湖人' AS TEAM, '2010' AS Y FROM DUAL;

    Oracle实现:

    SELECT TEAM, MIN(Y), MAX(Y)
    FROM (SELECT E.*,
               ROWNUM,
               ROW_NUMBER() OVER(PARTITION BY TEAM ORDER BY Y) AS RN,
               ROWNUM - ROW_NUMBER() OVER(PARTITION BY TEAM ORDER BY Y) AS DIFF
          FROM NBA E
         ORDER BY Y)
    GROUP BY TEAM, DIFF
    HAVING MIN(Y) != MAX(Y)
    ORDER BY 2;

    MySQL实现:

    SELECT TEAM, MIN(Y), MAX(Y)
    FROM (SELECT TEAM,
               Y,
               IF(@TEAM = TEAM, @RN := @RN + 1, @RN := 1) AS RWN,
               @RN1 := @RN1 + 1 AS RN,
               @TEAM := TEAM
          FROM nba N, (SELECT @RN := 0, @TEAM := '', @RN1 := '') C) A
    GROUP BY RN - RWN
    HAVING MIN(Y) != MAX(Y)
    ORDER BY 2

     

    | UDF插件

    Userdefined Function,用户定义函数。我们知道,MySQL本身支持很多内建的函数,此外还可以通过创建存储方法来定义函数。UDF为用户提供了一种更高效的方式来创建函数。

    UDF与普通函数类似,有参数,也有输出。分为两种类型:单次调用型和聚集函数。前者能够针对每一行数据进行处理,后者则用于处理Group By这样的情况。

    UDF自定义函数,在MySQL basedir/include

    [root@test12c include]# pwd
    /usr/local/mysql/include
    [root@test12c include]# cat rownum.c 
    #include <my_global.h>
    #include <my_sys.h>
    
    #if defined(MYSQL_SERVER)
    #include <m_string.h>        /* To get strmov() */
    #else
    /* when compiled as standalone */
    #include <string.h>
    #define strmov(a,b) stpcpy(a,b)
    #endif
    
    #include <mysql.h>
    #include <ctype.h>
    
    /*
    gcc -fPIC -Wall -I/usr/local/mysql/include -I. -shared rownum.c -o rownum.so
    DROP FUNCTION IF EXISTS rownum;
    CREATE FUNCTION rownum RETURNS INTEGER SONAME 'rownum.so';
    */
    
    C_MODE_START;
    
    my_bool rownum_init(UDF_INIT *initid, UDF_ARGS *args, char *message);
    void rownum_deinit(UDF_INIT *initid);
    chong rownum(UDF_INIT *initid, UDF_ARGS *args, char *is_null,char *error);
    
    C_MODE_END;
    
    
    
    /*
    Simple example of how to get a sequences starting from the first argument
    or 1 if no arguments have been given
    */
    
    my_bool rownum_init(UDF_INIT *initid, UDF_ARGS *args, char *message)
    {
    if (args->arg_count > 1)
    {
    strmov(message,"This function takes none or 1 argument");
    return 1;
    }
    if (args->arg_count)
    args->arg_type[0]= INT_RESULT;        /* Force argument to int */
    
    if (!(initid->ptr=(char*) malloc(sizeof(chong))))
    {
    strmov(message,"Couldn't allocate memory");
    return 1;
    }
    memset(initid->ptr, 0, sizeof(chong));
    initid->const_item=0;
    return 0;
    }
    
    void rownum_deinit(UDF_INIT *initid)
    {
    if (initid->ptr)
    free(initid->ptr);
    }
    
    chong rownum(UDF_INIT *initid __attribute__((unused)), UDF_ARGS *args,char *is_null __attribute__((unused)),char *error __attribute__((unused)))
    {
    uchong val=0;
    if (args->arg_count)
    val= *((chong*) args->args[0]);
    return ++*((chong*) initid->ptr) + val;
    }

    生成动态链接库

    gcc rownum.c -fPIC -shared -o ../lib/plugin/rownum.so

     

    |  作者简介

    姚崇·沃趣科技高级数据库技术专家

    熟悉Oracle、MySQL数据库内部机制,丰富的Oracle、MySQL故障诊断、性能调优、数据库备份恢复、复制、高可用方案及迁移经验。

    展开全文
  • oracle分析函数汇总

    万次阅读 多人点赞 2018-07-03 15:00:47
    一、分析函数语法 function_name(,<argument>...) over(<partition_Clause><order by_Clause>); function_name():函数名称 argument:参数 over( ):开窗函数 partition_Clause:分区子句,数据记录集分组,group ...

    一、分析函数语法

    function_name(<argument>,<argument>...) over(<partition_Clause><order by_Clause><windowing_Clause>);

    function_name():函数名称

    argument:参数

    over( ):开窗函数

    partition_Clause:分区子句,数据记录集分组,group by...

    order by_Clause:排序子句,数据记录集排序,order by...

    windowing_Clause:开窗子句,定义分析函数在操作行的集合,三种开窗方式:rows、range、Specifying

    注:使用开窗子句时一定要有排序子句!!!

    本篇未涉及开窗子句,开窗子句在另外的文章中单独说明


    二、分析函数汇总

    1、count() over()  :统计分区中各组的行数,partition by 可选,order by 可选

    select ename,esex,eage,count(*) over() from emp; --总计数
    select ename,esex,eage,count(*) over(order by eage) from emp; --递加计数
    select ename,esex,eage,count(*) over(partition by esex) from emp; --分组计数
    select ename,esex,eage,count(*) over(partition by esex order by eage) from emp;--分组递加计数

    2、sum() over()  :统计分区中记录的总和,partition by 可选,order by 可选

    select ename,esex,eage,sum(salary) over() from emp; --总累计求和
    select ename,esex,eage,sum(salary) over(order by eage) from emp; --递加累计求和
    select ename,esex,eage,sum(salary) over(partition by esex) from emp; --分组累计求和
    select ename,esex,eage,sum(salary) over(partition by esex order by eage) from emp; --分组递加累计求和

    3、avg() over()  :统计分区中记录的平均值,partition by 可选,order by 可选

    select ename,esex,eage,avg(salary) over() from emp; --总平均值
    select ename,esex,eage,avg(salary) over(order by eage) from emp; --递加求平均值
    select ename,esex,eage,avg(salary) over(partition by esex) from emp; --分组求平均值
    select ename,esex,eage,avg(salary) over(partition by esex order by eage) from emp; --分组递加求平均值

    4、min() over() :统计分区中记录的最小值,partition by 可选,order by 可选

         max() over() :统计分区中记录的最大值,partition by 可选,order by 可选

    select ename,esex,eage,salary,min(salary) over() from emp; --求总最小值
    select ename,esex,eage,salary,min(salary) over(order by eage) from emp; --递加求最小值
    select ename,esex,eage,salary,min(salary) over(partition by esex) from emp; --分组求最小值
    select ename,esex,eage,salary,min(salary) over(partition by esex order by eage) from emp; --分组递加求最小值
    select ename,esex,eage,salary,max(salary) over() from emp; --求总最大值
    select ename,esex,eage,salary,max(salary) over(order by eage) from emp; --递加求最大值
    select ename,esex,eage,salary,max(salary) over(partition by esex) from emp; --分组求最大值
    select ename,esex,eage,salary,max(salary) over(partition by esex order by eage) from emp; --分组递加求最大值

    5、rank() over()  :跳跃排序,partition by 可选,order by 必选

    select ename,eage,rank() over(partition by job order by eage) from emp;
    select ename,eage,rank() over(order by eage) from emp;

    6、dense_rank() :连续排序,partition by 可选,order by 必选

    select ename,eage,dense_rank() over(partition by job order by eage) from emp;
    select ename,eage,dense_rank() over(order by eage) from emp;

    7、row_number() over() :排序,无重复值,partition by 可选,order by 必选

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

    8、ntile(n) over() :partition by 可选,order by 必选

        n表示将分区内记录平均分成n份,多出的按照顺序依次分给前面的组

    select ename,salary,ntile(3) over(order by salary desc) from emp;
    select ename,salary,ntile(3) over(partition by job order by salary desc) from emp;

    9、first_value() over() :取出分区中第一条记录的字段值,partition by 可选,order by 可选

         last_value() over() :取出分区中最后一条记录的字段值,partition by 可选,order by 可选

    select ename,first_value(salary) over() from emp;
    select ename,first_value(salary) over(order by salary desc) from emp;
    select ename,first_value(salary) over(partition by job) from emp;                                                           
    select ename,first_value(salary) over(partition by job order by salary desc) from emp;
    select ename,last_value(ename) over() from emp;
    select ename,last_value(ename) over(order by salary desc) from emp;
    select ename,last_value(ename) over(partition by job) from emp;
    select ename,last_value(ename) over(partition by job order by salary desc) from emp;

    10、first :从DENSE_RANK返回的集合中取出排在最前面的一个值的行

          last :从DENSE_RANK返回的集合中取出排在最后面的一个值的行

    select job,max(salary) keep(dense_rank first order by salary desc),
    max(salary) keep(dense_rank last order by salary desc) from emp
    group by job;

    11、lag() over() :取出前n行数据,partition by 可选,order by 必选

          lead() over() :取出后n行数据,partition by 可选,order by 必选

    select ename,eage,lag(eage,1,0) over(order by salary), 
    lead(eage,1,0) over(order by salary) from emp;
    
    select ename,eage,lag(eage,1) over(partition by esex order by salary),
    lead(eage,1) over(partition by esex order by salary) from emp;

    12、ratio_to_report(a) over(partition by b) :求按照b分组后a的值在所属分组中总值的占比,a的值必须为数值或数值型字段

          partition by 可选,order by 不可选

    select ename,job,salary,ratio_to_report(1) over() from emp; --给每一行赋值1,求当前行在总值的占比,总是0.1
    select ename,job,salary,ratio_to_report(salary) over() from emp; --当前行的值在所有数据中的占比
    select ename,job,salary,ratio_to_report(1) over(partition by job) from emp; --给每一行赋值1,求当前行在分组后的组内总值的占比
    select ename,job,salary,ratio_to_report(salary) over(partition by job) from emp; --当前行的值在分组后组内总值占比

    13、percent_rank() over()  :partition by 可选,order by 必选

         所在组排名序号-1除以该组所有的行数-1,排名跳跃排序

    select ename,job,salary,percent_rank() over(order by salary) from emp;
    select ename,job,salary,percent_rank() over(partition by job order by salary) from emp;

    14、cume_dist() over() :partition by 可选,order by必选

    所在组排名序号除以该组所有的行数,注意对于重复行,计算时取重复行中的最后一行的位置

    select ename,job,salary,cume_dist() over(order by salary) from emp;
    select ename,job,salary,cume_dist() over(partition by job order by salary) from emp;

    15、precentile_cont( x ) within group(order by ...) over()    :over()中partition by可选,order by 不可选

    x为输入的百分比,是0-1之间的一个小数,返回该百分比位置的数据,若没有则返回以下计算值(r):

    a=1+( x *(N-1) )  x为输入的百分比,N为分区内的记录的行数

    b=ceil ( a )  向上取整

    c = floor( a ) 向下取整

    r=a * 百分比位置上一条数据 + b * 百分比位置下一条数据

    select ename,job,salary,percentile_cont(0.5) within group(order by salary) over() from emp;
    select ename,job,salary,percentile_cont(0.5) within group(order by salary) over(partition by job) from emp;

    16、precentile_disc( x ) within group(order by ...) over()   :over()中partition by可选,order by 不可选

    x为输入的百分比,是0-1之间的一个小数,返回百分比位置对应位置上的数据值,若没有对应数据值,就取大于该分布值的下一个值

    select ename,job,salary,percentile_disc(0.5) within group(order by salary) over()from emp;
    select ename,job,salary,percentile_disc(0.5) within group(order by salary) over(partition by job) from emp;

    17、stddev() over():计算样本标准差,只有一行数据时返回0,partition by 可选,order by 可选

          stddev_samp() over():计算样本标准差,只有一行数据时返回null,partition by 可选,order by 可选

          stddev_pop() over():计算总体标准差,partition by 可选,order by 可选

    select stddev(stu_age) over() from student; --计算所有记录的样本标准差
    select stddev(stu_age) over(order by stu_age) from student; --计算递加的样本标准差
    select stddev(stu_age) over(partition by stu_major) from student; --计算分组的样本标准差
    select stddev(stu_age) over(partition by stu_major order by stu_age) from student; --计算分组递加的样本标准差
    
    
    select stddev_samp(stu_age) over() from student; --计算所有记录的样本标准差
    select stddev_samp(stu_age) over(order by stu_age) from student; --计算递加的样本标准差
    select stddev_samp(stu_age) over(partition by stu_major) from student; --计算分组的样本标准差
    select stddev_samp(stu_age) over(partition by stu_major order by stu_age) from student; --计算分组递加的样本标准差
    
    
    select stddev_pop(stu_age) over() from student; --计算所有记录的总体标准差
    select stddev_pop(stu_age) over(order by stu_age) from student; --计算递加的总体标准差
    select stddev_pop(stu_age) over(partition by stu_major) from student; --计算分组的总体标准差
    select stddev_pop(stu_age) over(partition by stu_major order by stu_age) from student;--计算分组递加的总体标准差

    18、variance() over():计算样本方差,只有一行数据时返回0,partition by 可选,order by 可选

           var_samp() over():计算样本方差,只有一行数据时返回null,partition by 可选,order by 可选

           var_pop() over():计算总体方差,partition by 可选,order by 可选

    select variance(stu_age) over() from student; --计算所有记录的样本方差
    select variance(stu_age) over(order by stu_age) from student; --计算递加的样本方差
    select variance(stu_age) over(partition by stu_major) from student; --计算分组的样本方差
    select variance(stu_age) over(partition by stu_major order by stu_age) from student; --计算分组递加的样本方差
    
    
    select var_samp(stu_age) over() from student; --计算所有记录的样本方差
    select var_samp(stu_age) over(order by stu_age) from student; --计算递加的样本方差
    select var_samp(stu_age) over(partition by stu_major) from student; --计算分组的样本方差
    select var_samp(stu_age) over(partition by stu_major order by stu_age) from student; --计算分组递加的样本方差
    
    
    select var_pop(stu_age) over() from student; --记录所有就的总体方差
    select var_pop(stu_age) over(order by stu_age) from student; --计算递加的总体方差
    select var_pop(stu_age) over(partition by stu_major) from student; --计算分组的总体方差
    select var_pop(stu_age) over(partition by stu_major order by stu_age) from student;--计算分组递加的样本方差

    stddev()=sqrt( variance() )     sqrt()--求开方

    stddev_samp()=sqrt( var_samp() )

    stddec_pop=sqrt( var_pop() )


    19、covar_samp over():返回一对表达式的样本协方差,partition by 可选,order by 可选

           covar_pop over(): 返回一堆表达式的总体协方差,partition by 可选,order by 可选

    select covar_samp(stu_age,line) over() from student; --计算所有记录的样本协方差
    select covar_samp(stu_age,line) over(order by stu_age) from student; --计算递加的样本协方差
    select covar_samp(stu_age,line) over(partition by stu_major) from student; --计算分组的样本协方差
    select covar_samp(stu_age,line) over(partition by stu_major order by stu_age) from student; --计算分组递加的样本协方差
    
    
    select covar_pop(stu_age,line) over() from student; --计算所有记录的总体协方差
    select covar_pop(stu_age,line) over(order by stu_age) from student; --计算递加的总体协方差
    select covar_pop(stu_age,line) over(partition by stu_major) from student; --计算分组的总体协方差
    select covar_pop(stu_age,line) over(partition by stu_major order by stu_age) from student; --计算分组递加的总体协方差

    20、corr() over() :返回一对表达式的相关系数,partition by 可选,order by 可选

    select corr(stu_age,line) over() from student; --计算所有记录的相关系数
    select corr(stu_age,line) over(order by stu_age) from student; --计算递加的相关系数
    select corr(stu_age,line) over(partition by stu_major) from student; --计算分组的相关系数
    select corr(stu_age,line) over(partition by stu_major order by stu_age) from student; --计算分组递加的相关系数

    21、REGR_ (Linear Regression) Functions:这些线性回归函数适合最小二乘法回归线,有9个不同的回归函数可使用


    展开全文
  • HIVE 窗口及分析函数 应用场景

    千次阅读 2019-02-28 23:14:06
    窗口函数应用场景: (1)用于分区排序 (2)动态Group By (3)Top N (4)累计计算 (5)层次查询 hive中一般取top n时,row_number(),rank,dense_rank()这三个函数就派上用场了, 先简单说下这三函数都是...

    窗口函数应用场景:

    (1)用于分区排序

    (2)动态Group By

    (3)Top N

    (4)累计计算

    (5)层次查询

    hive中一般取top n时,row_number(),rank,dense_rank()这三个函数就派上用场了,

    先简单说下这三函数都是排名的,不过呢还有点细微的区别。

    通过代码运行结果一看就明白了。

    ROW_NUMBER() OVER函数的基本用法

    语法:ROW_NUMBER() OVER(PARTITION BY COLUMNORDER BY COLUMN)

    详解:

    row_number() OVER (PARTITION BY COL1 ORDERBY COL2)表示根据COL1分组,在分组内部根据COL2排序,而此函数计算的值就表示每组内部排序后的顺序编号(该编号在组内是连续并且唯一的)。

    场景描述:

    在Hive中employee表包括empid、depid、salary三个字段,根据部门分组,显示每个部门的工资等级

    SELECT empid、depid、salary, Row_Number() OVER (partition by deptid ORDER BY salary desc) rank FROM employee

    参考:https://blog.csdn.net/wiborgite/article/details/80521593 
    一、分析函数

    用于等级、百分点、n分片等。

    新增加序号列NTILE, ROW_NUMBER(), RANK(), DENSE_RANK()

    函数说明
    RANK()返回数据项在分组中的排名,排名相等会在名次中留下空位
    DENSE_RANK()返回数据项在分组中的排名,排名相等会在名次中不会留下空位
    NTILE()返回n分片后的值
    ROW_NUMBER()为每条记录返回一个数字
    • Rank、DENSE_RANK

    RANK()在出现等级相同的元素时预留为空,DENSE_RANK()不会。

    Eg:某产品类型有两个并列第一

    RANK():第一二为1,第三位3

    DENSE_RANK():第一二为1,第三位2

    select *,rank() over (order by deptid desc) as rank, dense_rank() over (order by deptid desc) as dense_rank from employee;

    OVER 需要,括号内为编号顺序。

    结果如下:

    注意:order by 时,desc NULL 值排在首位,ASC时NULL值排在末尾

    可以通过NULLS LAST、NULLS FIRST 控制

          RANK() OVER (ORDER BY column_name DESC NULLS LAST)  

     PARTITION BY 分组排列顺序

         RANK() OVER(PARTITION BY month ORDER BY column_name DESC)  

     这样,就会按照month 来分,即所需要排列的信息先以month 的值来分组,在分组中排序,各个分组间不干涉

    • NTILE

    按层次查询,如一年中,统计出工资前1/4之的部门信息,使用NTILE分析函数,把所有工资分为4份,为1的哪一份就是我们想要的结果:

    select deptid,sum(salary),ntile(4) over (order by sum(salary) desc nulls last) til_col from employee group by deptid;

    按层次查询,如一年中,统计出工资前1/2之的部门信息,使用NTILE分析函数,把所有工资分为2份,为1的哪一份就是我们想要的结果:

    select deptid,sum(salary),ntile(2) over (order by sum(salary) desc nulls last) til_col from employee group by deptid;

    • ROW_NUMBER

    ROW_NUMBER()从1开始,为每条记录返回一个数字

    SELECT ROW_NUMBER() OVER(ORDER BY column_name DESC) AS row_name FROM table_name;

    二、窗口函数

    可以计算一定范围内、一定值域内、或者一段时间内的累积和以及移动平均值等。

    可以结合聚集函数SUM() 、AVG() 等使用。

    可以结合FIRST_VALUE() 和LAST_VALUE(),返回窗口的第一个和最后一个值

    1)计算累计和

    eg:统计1-12月的累积销量,即1月为1月份的值,2月为1.2月份值的和,3月为123月份的和,12月为1-12月份值的和

    SELECT  
    month,SUM(amount) month_amount,  
    SUM( SUM(amount)) OVER (ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_amount  
    FROM table_name  
    GROUP BY month  
    ORDER BY month;  

    其中:

    SUM( SUM(amount)) 内部的SUM(amount)为需要累加的值,在上述可以换为 month_amount

    ORDER BY month 按月份对查询读取的记录进行排序,就是窗口范围内的排序

    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 定义起点和终点,UNBOUNDED PRECEDING 为起点,表明从第一行开始, CURRENT ROW为默认值,就是这一句等价于:

    ROWS UNBOUNDED PRECEDING

    PRECEDING:在前 N 行的意思。

    FOLLOWING:在后 N 行的意思。

    例如,按照部门ID大小排升序,依次统计所有工资部门的累计工资支出。

    SELECT deptid,SUM(salary) salary_amount,SUM(SUM(salary)) OVER( ORDER BY deptid ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_amount FROM employee GROUP BY deptid ORDER BY deptid;

    例如,按照部门ID大小排升序,依次统计当前部门和之前1个部门的工资部门的累计工资支出。

    SELECT deptid,SUM(salary) salary_amount,SUM(SUM(salary)) OVER( ORDER BY deptid ASC ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS cumulative_amount FROM employee GROUP BY deptid ORDER BY deptid;

    计算前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 3 PRECENDING) AS cumulative_amount  

     前后一个月之间的和

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

    窗体第一条和最后一条的值

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

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

    SUM(), MIN(),MAX(),AVG()等聚合函数

    对一定窗口期内的数据进行聚合

    SELECT *, 
    SUM(a.pv) OVER (PARTITION BY cookieid ORDER BY create_time ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS pv1,
    SUM(a.pv) OVER (PARTITION BY cookieid ORDER BY create_time ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING) AS pv2
    FROM lxy AS a;

    在这里根据cookieid进行分组,然后按照create_time进行分组,选择不同的窗口进行一定函数的聚合运算。
    基本的语法是ROWS BETWEEN 一个时间点 AND 一个时间点
    时间点分别可以是以当前行作为参考系,前面几行n PRECEDING或者是后面几行n FOLLOWING,也可以是当前行CURRENT ROW。总之可以想象有一个滑动窗口,我们可以规定一个滑动窗口的中心位置和大小,然后每次画过一个步长,计算一次窗口内的值。

    例子如下:

    SELECT *, 
    SUM(a.salary) OVER (PARTITION BY deptid ORDER BY empid ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS total_salary1,
    SUM(a.salary) OVER (PARTITION BY deptid ORDER BY empid ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS total_salary2
    FROM employee AS a;


    三、LAG、LEAD

    获得相对于当前记录指定距离的那条记录的数据

    LAG()为向前、LEAD()为向后

      LAG(column_name1,1) OVER(ORDER BY column_name2) 

      LEAD(column_name1,1) OVER(ORDER BY column_name2)  

    这样就获得前一条、后一条的数据

    select *,LAG(empid,1) over (order by empid asc) from employee;

    select *,LEAD(empid,1) over (order by empid asc) from employee;

    四、FIRST、LAST

    获得一个排序分组中的第一个值和组后一个值。可以与分组函数结合

    SELECT  
    MIN(month) KEEP(DENSE_RANK FIRST ORDER BY SUM(amount)) AS highest_sales_month,  
    MIN(month) KEEP(DENSE_RANK LAST ORDER BY SUM(amount)) AS lows_sales_month  
    FROM table_name  
    GROUP BY month  
    ORDER BY month; 

    这样就可以求得一年中销量最高和最低的月份。

    输出的是月份,但是用SUM(amount)来判断。

    参考:https://yugouai.iteye.com/blog/1908121
               https://www.jianshu.com/p/9fda829b1ef1
               https://www.jianshu.com/p/9fda829b1ef1?from=timeline
               https://blog.csdn.net/scgaliguodong123_/article/details/60135385 

    展开全文
  • postgresql分析函数

    千次阅读 2018-10-24 11:08:23
    SELECT uid, odate, num, ...ORDER BY子句:分析函数中ORDER BY的存在将添加一个默认的开窗子句,这意味着计算中所使用的行的集合是当前分区中当前行和前面所有行;没有ORDER BY时,默认的窗口是全部的分区。  
  • Hive分析函数

    千次阅读 2018-08-09 20:36:53
    分析函数 1、窗口函数(开窗函数),关键字:over (0)基础知识 2 preceding ====== 前两行 2 following ====== 后两行 current row ====== 当前行 unbounded preceding ====== 无上限 unbounded following ===...
  • SQL分析函数,看这一篇就够了

    千次阅读 2019-07-25 15:06:53
    分析函数主要分为四类: 1.聚合分析函数 2.排名分析函数 3.数学分析函数 4.行比较分析函数 1. 一.聚合分析函数 SUM :该函数计算组中表达式的累积和 COUNT :对一组内发生的事情进行累积计数 MIN :在一个组中...
  • Oracle 分析函数与聚合函数(重点理解)

    万次阅读 多人点赞 2017-04-22 21:14:06
    一、Oracle分析函数的语法与作用:   rank ( ) over ( [query_partition_clause]order_by_clause ) dense_rank ( ) over ([query_partition_clause] order_by_clause ) rownumber ( ) over ( [query_partition
  • hive 窗口函数,分析函数的一些理解

    千次阅读 2018-03-30 14:33:12
    先解释什么是窗口函数首先,我们要知道什么是窗口子句:需要指定一个窗口的边界,语法是这样的:ROWS betweenCURRENT ROW | UNBOUNDED PRECEDING | [num] PRECEDING AND UNBOUNDED FOLLOWING | [num] FOLLOWING| ...
  • Oracle over()分析函数 ---- 去重数据

    千次阅读 2018-04-12 09:22:59
    分析函数的形式分析函数带有一个开窗函数over(),包含三个分析子句:分组(partition by), 排序(order by), 窗口(rows) ,他们的使用形式如下:over(partition by xxx order by yyy rows between zzz)。业务背景:三张...
  • oracle------分析函数和开窗函数over( )

    千次阅读 多人点赞 2018-09-23 12:52:19
    一 什么是分析函数  1 概念  分析函数是Oracle专门用于解决复杂报表统计需求的功能强大的函数,它可以在数据中进行分组然后计算基于组的某种统计值,并且每一组的每一行都可以返回一个统计值。  2 和聚合函数...
  • MySQL不使用分析函数实现排名

    千次阅读 2021-08-09 09:45:11
    MySQL8.0开始支持分析函数,MySQL8.0之前如果有排名需求,不使用分析函数的情况下会比较麻烦。 我们可以通过变量、子查询、表的自连接来实现排名的功能。 数据准备: scott建表及录入数据sql脚本 二.解决方案 此处我...
  • 大型项目中通常会涉及到统计分析相关的功能,今天笔者把oracle常用的分析函数用法和区别通过详细的案例整理出来,希望对各位同学有帮助,也算是对自己的一个重新温故。常用的分析函数如下所列:row_number() over...
  • Oracle SQL 之 分析函数(窗口函数)

    千次阅读 2018-05-04 09:49:45
    概述分析函数是以一定的方法在一个与当前行相关的结果子集中进行计算,也称为窗口函数。一般结构为:Function(arg1 , arg2 ……) over(partition by clause order by clause windowing clause )Windowing clause : ...
  • 窗口函数(分析函数)的使用方法

    千次阅读 2018-06-12 11:32:14
    一本书上讲到3个窗口函数【ROW_NUMBER()、RANK()、DENSE_RANK()】的使用方法和区别,感觉讲的不错。选摘如下:总结:3个函数最主要的差别在对重复数据的编号规则上。row_number()重复数据按统一不重复编号;rank()...
  • HIVE--hive的窗口函数(开窗函数,分析函数

    万次阅读 多人点赞 2019-06-05 21:30:45
    本文详细介绍了hive窗口函数,即包括开窗函数和分析函数两部分。
  • oracle分析函数——keep

    千次阅读 2015-10-10 10:59:09
    keep是Oracle下的另一个分析函数,他的用法不同于通过over关键字指定的分析函数,可以用于这样一种场合下:取同一个分组下以某个字段排序后,对指定字段取最小或最大的那个值。   从这个前提出发,我们可以看到...
  • 分析函数之求占比函数

    千次阅读 2019-05-09 20:47:59
    分析函数之求占比函数 RATIO_TO_REPORT     定义:RATIO_TO_REPORT是一个分析函数。它计算值与一组值之和的比率。如果expr求值为null,则ratio-to-report值也将计算为null。这组值由query_...
  • Oracle-分析函数之sum(...) over(...)

    万次阅读 多人点赞 2016-10-31 20:20:28
    http://blog.csdn.net/yangshangwei/article/details/51425458,分析函数并没有包括,这里作为单独的开篇来介绍一下 分析函数分析函数是Oracle专门用于解决复杂报表统计需求的功能强大的函数,它可以在数据中进行...
  • 今天有个同事给我打电话问我题目列出的这个问题,在日常开发中,确实它们俩都是与分组有关的,但是需要注意的是一个是分组函数另一个是分析函数,讲解用到的表就以Oracle中schema的scott的EMP表和DEPT表为例做讲解:...
  • oracle分析函数技术详解(配上开窗函数over())

    万次阅读 多人点赞 2013-11-11 11:36:00
    一、Oracle分析函数入门 分析函数是什么? 分析函数是Oracle专门用于解决复杂报表统计需求的功能强大的函数,它可以在数据中进行分组然后计算基于组的某种统计值,并且每一组的每一行都可以返回一个统计值。   ...
  • 分析函数的语法结构一般是: 分析函数名(参数) OVER (PARTITION BY子句 ORDER BY子句 ROWS/RANGE子句)。 即由以下三部分组成: 分析函数名:如sum、max、min、count、avg等聚集函数以及lead、lag行比较函数等; ...
  • 分析函数 的东西,之前学 oracle 时没有印象,现在感觉其分析函数的功能相当强大、神奇, 就特意去找到了 chm 文档研究了一下,想要的朋友在本文末尾自行下载。 本文的例子都来源于 chm 文档,本人只是执行了...
  • 概述ORACLE函数系列:Oracle常见函数大全Oracle-分析函数之连续求和sum(…) over(…)Oracle-分析函数之排序值rank()和dense_rank()Oracle-分析函数之排序后顺序号row_number()Oracle-分析函数之取上下行数据lag()和...
  • 一般而言,在数据库里面,用来做排序的函数主要有四个: row_number、renk、dense_rank、ntile 此处以sqlserser操作方法作解析,此处我把该表放在tmp库下的goosinfo表里,使用数据可移步:数据库练习数据   1、...
  • R语言 回归分析函数说明

    万次阅读 2018-11-02 21:19:56
    回归分析相关的函数 1、一元线性回归 lm() #计算beta0,beta1参数 summary() # 提取lm()参数信息 anovn() #方差分析 predict() # 根据给出自变量预测因变量的值 例: a=lm(y~1+x,data=…) #对x,y回归分析 ...
  • rollup分析函数

    万次阅读 2017-03-15 17:51:21
    如果显示“1”表示GROUPING函数对应的列(例如JOB字段)是由于ROLLUP函数所产生的空值对应的信息,即对此列进行汇总计算后的结果。  如果显示“0”表示此行对应的这列参未与ROLLUP函数分组汇总活动 。 ...
  • matlab 主成分分析函数(PC A)

    千次阅读 2017-09-11 20:17:17
    pcacov函数 使用条件原始数据标准化之后,得到协方差矩阵 运用协方差矩阵进行的操作 案例:  g= 7.3700 0.0680 282.0000 20.0000 3.0000 0.9700 2.0000 10....
  • Oracle的分析函数over(...)

    万次阅读 2017-04-28 10:23:43
    最近在用SQL实现一个查询功能时接触到了分析函数,如同发现了新大陆,很多功能可以用分析函数轻松搞定,速度还快,真是一个超级牛叉的功能。 先说一下要实现的功能吧,总部下发的结算数据居然有重复,悲催的是他们...
  • [Hive]窗口函数与分析函数

    千次阅读 2017-02-22 14:24:52
    本文介绍了用于窗口函数和分析函数的Hive QL增强功能。所有窗口和分析函数操作都按照SQL标准。 当前版本支持以下窗口函数和分析函数:1 窗口函数LEAD 返回分区中当前行后面行(可以指定第几行)的值。 如果没有行,...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 1,778,016
精华内容 711,206
关键字:

分析函数