精华内容
下载资源
问答
  • 直接上图 select * from tab select B,ROW_NUMBER()over(order by B) from tab 当碰到相同数据时,排名按照记录集中记录的顺序依次递增。 遇到相同数据 不影响排序 序列号 ...select B,rank()over(order by B) ...

    直接上图

    select * from tab

    select B,ROW_NUMBER()over(order by B) from tab

    当碰到相同数据时,排名按照记录集中记录的顺序依次递增。  遇到相同数据 不影响排序 序列号

    select B,rank()over(order by B) from tab

    当碰到相同的数据时,此时所有相同数据的排名是一样的,同时会在最后一条相同记录和下一条不同记录的排名之间空出排名。

     遇到相同数据 影响排序   值相同 排序序号也相同,下一条数据 指在整张表的排名

     

    select B,dense_rank()over(order by B) from tab

    当碰到相同数据时,此时所有相同数据的排名都是一样的。 

      值相同 排序序号也相同,下一条数据 跟着排名排

     

    select B,D,first_value(D)over(partition by B order by B) from tab

    根据B分组   再排序 再取组里 第一条

     

    select B,D,last_value(D)over(partition by B order by B) from tab

    根据B分组   再排序 再取组里 最后一条

     

     

     

    切记一下

    select D,sum(D)over(order by D) from tab

    新增的列 根据排序字段 D  第一组到当前组 执行sum(D)

     

    select B,D,sum(D)over(order by B) from tab

     

    select B,D,sum(D)over() from tab

     

    转载于:https://www.cnblogs.com/cjm123/p/8043779.html

    展开全文
  • 数据库-分析函数

    千次阅读 2018-07-19 10:31:39
    分析函数基本语法函数名称([参数]) VOER (PARTITION BY 子句 字段,....[ORDER BY 子句 字段,..[ASC][DESC][NULLS FIRST][NULLS LAST]][WINDOWING 子句]); 使用PARTITION BY子句 SELECT deptno,ename,sal,job, SUM...

    分析函数
    基本语法
    函数名称([参数]) VOER (
    PARTITION BY 子句 字段,....
    [ORDER BY 子句 字段,..[ASC][DESC][NULLS FIRST][NULLS LAST]]
    [WINDOWING 子句]);
    使用PARTITION BY子句

    SELECT deptno,ename,sal,job,
    SUM(sal) OVER(PARTITION BY deptno) sum部门工资总和
    FROM emp;


    如果不加PARTITION BY

    SELECT deptno,ename,sal,job,
    SUM(sal) OVER() sum全公司工资总和
    FROM emp;


    使用PARTITION BY子句加多个分区

    SELECT deptno,ename,sal,job,
    SUM(sal) OVER(PARTITION BY deptno,job)sum
    FROM emp;

    ORDER BY 子句
    查看ORDER BY子句

    SELECT deptno,ename,sal,hiredate,
    RANK()OVER (PARTITION BY deptno ORDER BY sal,hiredate DESC) rk
    FROM emp;


    直接用ORDER BY排序

    SELECT deptno,ename,sal,hiredate,
    SUM(sal) OVER(ORDER BY ename)sum
    FROM emp;


    NULLS FIRST 出现NULL值的数据放在前面
    NULLS LAST 出现NULL值的数据放在后面

    SELECT deptno,ename,sal,comm,
    RANK() OVER(ORDER BY comm DESC NULLS LAST) RK,
    SUM(sal) OVER(ORDER BY comm DESC NULLS LAST) sum
    FROM emp;

    WINDOWING子句
    RNGGE子句
    在sal上设置偏移量

    SELECT deptno,ename,sal,
    SUM(sal) OVER (PARTITION BY deptno ORDER BY sal RANGE 300 PRECEDING) sum
    FROM emp;


    向上N匹配
    下面是向下N匹配

    SELECT deptno,ename,sal,
    SUM(sal) OVER (PARTITION BY deptno ORDER BY sal RANGE 
    BETWEEN 0 PRECEDING AND 300 FOLLOWING) sum
    FROM emp;


    匹配当前行数据

    SELECT deptno,ename,sal,
    SUM(sal) OVER (PARTITION BY deptno ORDER BY sal RANGE 
    BETWEEN 0 PRECEDING AND CURRENT ROW) sum
    FROM emp;


    此处用了CURRENT ROW选项,表示比当前行数相同,所以只有相同的数据才会使用SUM计算总和
    使用UNBOUNDED不设置边界

    SELECT deptno,ename,sal,
    SUM(sal) OVER (PARTITION BY deptno ORDER BY sal RANGE 
    BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) sum
    FROM emp;

    ROWS子句
    设置两行偏移

    SELECT deptno,ename,sal,
    SUM(sal) OVER (PARTITION BY deptno ORDER BY sal ROWS 2 PRECEDING) sum
    FROM emp;


    查询行的范围

    SELECT deptno,ename,sal,
    SUM(sal) OVER (PARTITION BY deptno ORDER BY sal ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) sum
    FROM emp;


    分析函数的范例
    数据统计函数
    SUM
    MIN
    MAX
    AVG
    COUNT
    这些是数据统计函数
    查询编号是7369的员工姓名,职位,基本工资,部门编号,部门人数,平均工资,最高工资,最低工资,总工资

     

    SELECT * FROM(
    SELECT empno,ename,job,sal,deptno,
    COUNT(empno) OVER(PARTITION BY deptno) count,
    ROUND(AVG(sal) OVER(PARTITION BY deptno),2) avg,
    MAX(sal) OVER(PARTITION BY deptno) max,
    MIN(sal) OVER(PARTITION BY deptno) min,
    SUM(sal) OVER(PARTITION BY deptno) sum
    FROM emp
    ) temp
    WHERE temp.empno=7369;

     


    查询每个员工的编号,姓名,基本工资,所在部门名称,部门位置,以及此部门的平均工资,最高和最低工资
    emp和dept表

     

    SELECT e.empno,e.ename,e.sal,d.dname,d.loc,
    ROUND(AVG(sal) OVER (PARTITION BY e.deptno ORDER BY sal RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING),2) avg, 
    MAX(sal) OVER(PARTITION BY e.deptno ORDER BY sal RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) max,
    MIN(sal) OVER(PARTITION BY e.deptno ORDER BY sal RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) min,
    SUM(sal) OVER(PARTITION BY e.deptno ORDER BY sal RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) sum
    FROM emp e,dept d
    WHERE e.deptno=d.deptno;

     

    等级函数
    记录标记函数
    RANK()和DENSE_RANK()
    观察RANK()和DENSE_RANK()函数

    SELECT deptno,ename,sal,
    RANK() OVER(PARTITION BY deptno ORDER BY sal) rank_result,
    DENSE_RANK() OVER (PARTITION BY deptno ORDER BY sal) dense_rank_resut
    FROM emp;


    可以看出RANK是有重复是当前值未变,但其它已经+1会跳号,下个不同的就+2了和DENSE_RANK()每次只+1,重复也不会增加
    行标记函数
    ROW_NUMBER()自动成功行记录号,并且不管内容是否重复都可以连接编号

    SELECT deptno,ename,sal,
    ROW_NUMBER() OVER(PARTITION BY deptno ORDER BY sal) row_result_deptno,
    ROW_NUMBER() OVER(ORDER BY sal) row_result_sal
    FROM emp;
    使用KEEP语句保留

    满足条件的数据

    SELECT deptno,
    MAX(sal) KEEP (DENSE_RANK FIRST ORDER BY sal) max_sal,
    MIN(sal) KEEP (DENSE_RANK LAST ORDER BY sal) min_sal
    FROM emp
    GROUP BY deptno;


    取出首行和尾行的记录

     

    FIRST_VALUE() LAST_VALUE()
    SELECT deptno,empno,ename,sal,
    FIRST_VALUE(sal) OVER(PARTITION BY deptno ORDER BY sal
    RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) first_result,
    LAST_VALUE(sal) OVER (PARTITION BY deptno ORDER BY sal
    RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) last_result
    
    FROM emp
    WHERE deptno=10;

     

    比较相邻记录
    LAG()取得之前所列数据行的第N行记录显示,如果没有则使用默认值,不设置默认值返回NULL
    LEAD()取重之后所列数据的第N行记录显示,如果没有则使用默认值,不设置默认值返回NULL

    SELECT deptno,empno,ename,sal,
    LAG(sal,2,0) OVER (PARTITION BY deptno ORDER BY sal) lag_result,
    LEAD(sal,2,0) OVER (PARTITION BY deptno ORDER BY sal)lead_result
    FROM emp
    WHERE deptno=20;

    报表函数
    CUME_DIST()计算在分区中的相对位置
    NTILE(数字)将一个分区分为“表达式”的散列表示
    RATIO_TO_TEPORT(表达式)该函数计算expression/(sum(expression))的值,它给出的相对于总数据的百分比
    验证CUME_DIST()函数
    假设分区有5行测0.2,0.4,0.6,0.8,1.0进行划分

    SELECT deptno,ename,sal, 
    CUME_DIST() OVER(PARTITION BY deptno ORDER BY sal) cume
    FROM emp
    WHERE deptno IN(10,20);

     

    NTILE函数
    对一个数据区有序结果进行划分,并为每个小组分配唯一的组编号

    SELECT deptno,sal,
    SUM(sal) OVER (PARTITION BY deptno ORDER BY sal) sum,
    NTILE(3) OVER (PARTITION BY deptno ORDER BY sal) ntile_a,
    NTILE(6) OVER (PARTITION BY deptno ORDER BY sal) ntile_b
    FROM emp;

    RATIO_TO_REPORT函数可以将需要统计的数据按照整体数据的百分比进行显示

    SELECT deptno,SUM(sal),
    ROUND(RATIO_TO_REPORT(SUM(sal)) OVER(),5) rate,
    ROUND(RATIO_TO_REPORT(SUM(sal)) OVER(),5)*100 || '%'precent
    FROM emp
    GROUP BY deptno;


    行列转换

    查询每个部门中各个职位的总工资

    SELECT deptno,job,SUM(sal)
    FROM emp
    GROUP BY deptno,job;


    第2种实现
    用DECODE显示

     

    SELECT deptno, 
    SUM(DECODE(job,'PRESIDENT',sal,0)) PRESIDENT_JOB,
    SUM(DECODE(job,'MANAGER',sal,0)) MANAGER_JOB,
    SUM(DECODE(job,'ANALYST',sal,0)) ANALYST_JOB, 
    SUM(DECODE(job,'CLERK',sal,0)) CLERK_JOB, 
    SUM(DECODE(job,'SALESMAN',sal,0)) SALESMAN_JOB
    FROM emp
    GROUP BY deptno;

     


    不使用DECODE来实现

     

    SELECT temp.dno,SUM(PRESIDENT_JOB),SUM(MANAGER_JOB),SUM(ANALYST_JOB),SUM(CLERK_JOB),SUM(SALESMANJOB)
    FROM(
    SELECT deptno dno, 
    (SELECT SUM(sal) FROM emp WHERE job='PRESIDENT'AND empno=e.empno) PRESIDENT_JOB,
    (SELECT SUM(sal) FROM emp WHERE job='MANAGER'AND empno=e.empno) MANAGER_JOB,
    (SELECT SUM(sal) FROM emp WHERE job='ANALYST'AND empno=e.empno) ANALYST_JOB,
    (SELECT SUM(sal) FROM emp WHERE job='CLERK'AND empno=e.empno) CLERK_JOB,
    (SELECT SUM(sal) FROM emp WHERE job='SALESMAN'AND empno=e.empno) SALESMANJOB
    FROM emp e) temp
    GROUP BY temp.dno
    ORDER BY temp.dno DESC;

     

    PIVOT函数和UNPIVOT函数

     

    SELECT * FROM (SELECT deptno,job,sal FROM emp)
    PIVOT(
    SUM(sal)
    FOR job IN(
    'PRESIDENT' AS PRESIDENT_JOB,
    'MANAGER' AS MANAGER_JOB,
    'ANALYST' AS ANALYST_JOB,
    'CLERK' AS CLERK_JOB,
    'SALESMAN' AS SALESMANJOB
    )
    )ORDER BY deptno;

     


    使用XML与ANY
    如果在PIVOT中增加了XML显示,可以利用ANY设置所要操作的所有数据

    SELECT * FROM (SELECT deptno,job,sal FROM emp)
    PIVOT XML(
    SUM(sal)
    FOR job IN(ANY)
    )ORDER BY deptno;


    查询更多统计信息

     

    SELECT * FROM 
    (
    SELECT deptno,job,sal,
    SUM(sal) OVER (PARTITION BY deptno) sumsal,
    MAX(sal) OVER (PARTITION BY deptno) maxsal,
    MIN(sal) OVER (PARTITION BY deptno) minsal
    FROM emp
    )
    PIVOT(
    SUM(sal)
    FOR job IN(
    'PRESIDENT' AS PRESIDENT_JOB,
    'MANAGER' AS MANAGER_JOB,
    'ANALYST' AS ANALYST_JOB,
    'CLERK' AS CLERK_JOB,
    'SALESMAN' AS SALESMANJOB
    )
    )ORDER BY deptno;

     

     

    设置多个统计函数,查询每个部门不同职位的总工资,以及每个部门不同职位的最高工资

     

    SELECT * FROM 
    (
    SELECT deptno,job,sal FROM emp
    )
    PIVOT(
    SUM(sal) AS sum, MAX(sal) AS max
    FOR job IN(
    'PRESIDENT' AS PRESIDENT_JOB,
    'MANAGER' AS MANAGER_JOB,
    'ANALYST' AS ANALYST_JOB,
    'CLERK' AS CLERK_JOB,
    'SALESMAN' AS SALESMANJOB
    )
    )ORDER BY deptno;
    使用UNPIVOT
    WITH temp AS(
    SELECT * FROM 
    ( SELECT deptno,job,sal FROM emp)
    PIVOT(
    SUM(sal) 
    FOR job IN(
    'PRESIDENT' AS PRESIDENT_JOB,
    'MANAGER' AS MANAGER_JOB,
    'ANALYST' AS ANALYST_JOB,
    'CLERK' AS CLERK_JOB,
    'SALESMAN' AS SALESMANJOB
    )
    )ORDER BY deptno)
    SELECT * FROM temp
    UNPIVOT INCLUDE NULLS(
    sal_sum FOR job IN(
    PRESIDENT_JOB AS 'PRESIDENT',
    MANAGER_JOB AS 'MANAGER' ,
    ANALYST_JOB AS 'ANALYST' ,
    CLERK_JOB AS 'CLERK' ,
    SALESMANJOB AS 'SALESMAN'
    )
    )ORDER BY deptno;

     

    展开全文
  • 开 发dz3.0插件的朋友都知道,在开发插件中,大部分插件都要涉及到数据库的操作,而官方提醒开发插件要尽量用官方提供的数据库操作函数,但是我在网上找 了很多资料,都没有看到数据库操作函数的一些具体说明,不得...

    开 发dz3.0插件的朋友都知道,在开发插件中,大部分插件都要涉及到数据库的操作,而官方提醒开发插件要尽量用官方提供的数据库操作函数,但是我在网上找 了很多资料,都没有看到数据库操作函数的一些具体说明,不得已只能自己看函数,算是对数据库操作函数有了点基本了解,今天我就给大家分享出来。


    首先我要来介绍2个文件

    【1】/source/class/db/db_driver_mysql.php   内部SQL处理库 文件里类为(db_driver_mysql)

    【2】/source/class/discuz/discuz_database.php  全局SQL语句处理类 文件里类为(discuz_database)


    我们用到的数据库操作函数基本是第【2】 个文件中里的函数,而第【2】 个文件中的函数有好些是来自第【1】 个文件函数,只是重新封装了下

    在开发插件中我们常用到的函数是来自第【2】 个文件,废话不多说了,来说明函数吧。


    注意提示:下边发遇到pre_都为dz数据表前缀





    table($table) 

    参数:$table---数据库表名称

    作用:给表加前缀,如数据库表名称为common_cron,调用函数会在表前添加安装dz时所填写的数据表前缀,如pre_common_cron

    本函数是将db_driver_mysql类中的table_name($tablename)函数重新封装





    delete($table, $condition, $limit = 0, $unbuffered = true)

    参数:$table---数据库表名称

    $condition---可以是数字也可以是变量,存放where条件语句,为数组的时候比较复杂,一般都说是为变量,直接 存放where条件

    作用:按where条件删除$table表里的对应数据






      1. 实例讲解:删除pre_com数据表中id为1的记录

      1. $table="com";

      1. $condition="id=1";

      1. DB::delete($table, $condition, $limit = 0, $unbuffered = true)








    insert($table, $data, $return_insert_id = false, $replace = false, $silent = false) 

    参数:$table---数据库表名称

              $data---数组,如要在$table 中的title和content字段中插入“标题”和“内容”,$data的值可以为

              $data=array(“title”=>"标题",“content”=>"内容")

              注意:MySQL 3.22.10或以后版本才可以使用此函数

    作用:插入数据,如果$replace参数设置为true,改函数功能可以验证数据是否存在,不存在插入,存在更新记录,$return_insert_id参数设置为true将返回新插入的记录ID







      1. 实例讲解:在pre_com中的title和content字段中插入“标题”和“内容”

      1. $table="com";

      1. $data=array("title"=>"标题","content"=>"内容")

      1. DB::insert($table, $data, $return_insert_id = false, $replace = false, $silent = false)








    update($table, $data, $condition, $unbuffered = false, $low_priority = false)

    参数:

    $table---数据库表名称

    $data---数组,如要更新$table 中的title和content字段为“标题”和“内容”,$data的值可以为$data=array(“title”="标题",“content”="内容")

    $condition---可以是数字也可以是变量,存放where条件语句,为数组的时候比较复杂,一般都说是为变量,直接 存放where条件

    作用:

    更新数据表中的记录








      1. 实例讲解:在表pre_com中更新id为1的title和content字段值为“标题”和“内容”

      1. $table="com";

      1. $data=array("title"=>"标题","content"=>"内容");

      1. $condition="id=1";

      1. DB::update($table, $data, $condition, $unbuffered = false, $low_priority = false)




    insert_id()作用:




    返回前次插入记录的ID

    是对db_driver_mysql种函数insert_id()重新封装



    函数原型



      1. function insert_id()

      1. {

      1. return ($id = mysql_insert_id($this->curlink)) >= 0 ? $id : $this->result($this->query("SELECT last_insert_id()"), 0);

      1. }






    fetch($resourceid, $type = MYSQL_ASSOC)

    参数:

    $resourceid-是mysql_query($SQL) 的返回值,或者是dz定义函数query()的返回值

    作用:

    结果集中取得一行作为关联数组,或数字数组,或二者兼有,相当于执行php中mysql_fetch_array(data,array_type)函数

    是将db_driver_mysql类中fetch_array函数重新封装








      1. 函数原型为

      1. function fetch_array($query, $result_type = MYSQL_ASSOC) {

      1.                 return mysql_fetch_array($query, $result_type);

      1.         }







      1. fetch函数原型为

      1. public static function fetch($resourceid, $type = MYSQL_ASSOC) {

      1.                 return self::$db->fetch_array($resourceid, $type);

      1.         }




    fetch_first($sql, $arg = array(), $silent = false)

    参数:

    $sql-要执行的sql语句

    作用:获取集合中的第一条记录








      1. $sql=“select * from table ”;

      1. $n=fetch_first($sql)

      1. 以上功能就相当于

      1. $sql=“select * from table ”;

      1. $query=mysql_query($sql)

      1. $n=mysql_fetch_array($sql);








    fetch_all($sql, $arg = array(), $keyfield = '', $silent=false) 

    参数:

    $sql-要执行的sql语句

    作用:获取集合中的全部记录










      1. $sql=“select * from table ”;

      1. $n[]= DB::fetch_all ($sql)

      1. 以上功能相当于

      1. $sql=“select * from table ”;

      1. $query=mysql_query($sql)

      1. while ($row = self::$db->fetch_array($query))

      1. {

      1.         $n[] = $row;

      1. }




    result($resourceid, $row = 0)

    参数:

    $resourceid-是mysql_query($SQL) 的返回值,或者是dz定义函数query()的返回值 

    $row-或者那一行的字段值

    作用:

    获取记录集中某个字段的值








      1. 执行此函数相当于执行php中mysql_result($query, $row)函数




    query($sql, $arg = array(), $silent = false, $unbuffered = false)

    参数:$sql-要执行的sql语句




    作用:

    执行一条sql语句

    如果sql语句为“select .....”函数返回记录集,如果为“UPDATE或者DELETE”函数返回操作所影响的记录条数,如果为“INSERT”函数返回新插入记录的id

    执行query($sql)函数类似执行php中mysql_query($sql)






    num_rows($resourceid)

    参数:

    $resourceid-是mysql_query($SQL) 的返回值,或者是dz定义函数query()的返回值

    作用:

    返回记录条数

    执行num_rows($query)相当于执行php中mysql_num_rows($query);







    affected_rows()

    作用:

    返回上次操作所影响的记录条数

    执行affected_rows()相当于执行php中mysql_affected_rows()


    本贴原文来自:http://www.cms97.com/read.php?tid=37

    转载于:https://www.cnblogs.com/jevil/p/3559406.html

    展开全文
  • 数据库统计函数包括: 1. 计数函数 2. 求和、乘积函数 3. 最值函数 4. 均值、方差、标准差函数 5. 其他数据库函数 详细说明: Category Subtype Function Usage Excel...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 5,255
精华内容 2,102
关键字:

数据库分析函数