精华内容
参与话题
问答
  • 聚合函数

    千次阅读 2017-02-22 12:54:14
    聚合函数
            聚合函数,又叫做分组函数,多行处理函数。
    count	取得记录数
    sum	求和
    avg	取平均
    max	取最大的数
    min	取最小的数

    一count

    查询所有的员工数

    mysql> select count(*) from emp;
    +----------+
    | count(*) |
    +----------+
    |       14 |
    +----------+
    取得津贴不为NULL的员工

    mysql> select count(comm) from emp;
    +-------------+
    | count(comm) |
    +-------------+
    |           4 |
    +-------------+
    注:count(字段名)会自动去掉NULL,不需要手动添加过滤条件。

    二sum

    Sum可以取得某一个列的和,null会被忽略。

    取得津贴的合计

    mysql> select sum(comm) from emp;
    +-----------+
    | sum(comm) |
    +-----------+
    |   2200.00 |
    +-----------+

    三avg

    取得某一列的平均值

    取得平均薪水

    mysql> select avg(sal) from emp;
    +-------------+
    | avg(sal)    |
    +-------------+
    | 2073.214286 |
    +-------------+

    四max

    取得某个一列的最大值

    取得最高薪水

    mysql> select max(sal) from emp;
    +----------+
    | max(sal) |
    +----------+
    |  5000.00 |
    +----------+

    五min

    取得某个一列的最小值

    取得最低薪水

    mysql> select min(sal) from emp;
    +----------+
    | min(sal) |
    +----------+
    |   800.00 |
    +----------+

    六组合聚合函数

    可以将这些聚合函数都放到select中一起使用

    mysql> select count(*), sum(sal), avg(sal), max(sal), min(sal) from emp;
    +----------+----------+-------------+----------+----------+
    | count(*) | sum(sal) | avg(sal)    | max(sal) | min(sal) |
    +----------+----------+-------------+----------+----------+
    |       14 | 29025.00 | 2073.214286 |  5000.00 |   800.00 |
    +----------+----------+-------------+----------+----------+
    展开全文
  • 因为知道Sql中聚合函数用在子查询中是允许的所以我这样写了一段代码 ``` select Sno,Cno from SC where SC.Grade>(select AVG(SC.Grade) from SC X,SC y where x.Sno=y.Sno) ``` 结果显示...
  • PostgreSql 聚合函数string_agg与array_agg

    万次阅读 2017-12-26 15:23:11
    string_agg,array_agg 这两个函数的功能大同小异,只不过合并数据的类型不同。 https://www.postgresql.org/docs/9.6/static/functions-aggregate.htmlarray_agg(expression) 把表达式变成一个数组 一般配合 array...

    string_agg,array_agg 这两个函数的功能大同小异,只不过合并数据的类型不同。
    https://www.postgresql.org/docs/9.6/static/functions-aggregate.html

    array_agg(expression)
    把表达式变成一个数组 一般配合 array_to_string() 函数使用
    
    string_agg(expression, delimiter)
    直接把一个表达式变成字符串
    

    案例:

    create table jinbo.employee(empno smallint, ename varchar(20), job varchar(20), mgr smallint, hiredate date, sal bigint, comm bigint, deptno smallint);
    
    insert into jinbo.employee(empno,ename,job, mgr, hiredate, sal, comm, deptno) values (7499, 'ALLEN', 'SALEMAN', 7698, '2014-11-12', 16000, 300, 30);
    
    insert into jinbo.employee(empno,ename,job, mgr, hiredate, sal, comm, deptno) values (7566, 'JONES', 'MANAGER', 7839, '2015-12-12', 32000, 0, 20);
    
    insert into jinbo.employee(empno,ename,job, mgr, hiredate, sal, comm, deptno) values (7654, 'MARTIN', 'SALEMAN', 7698, '2016-09-12', 12000, 1400, 30);
    
    select * from jinbo.employee;
     empno | ename  |   job   | mgr  |  hiredate  |  sal  | comm | deptno 
    -------+--------+---------+------+------------+-------+------+--------
      7499 | ALLEN  | SALEMAN | 7698 | 2014-11-12 | 16000 |  300 |     30
      7566 | JONES  | MANAGER | 7839 | 2015-12-12 | 32000 |    0 |     20
      7654 | MARTIN | SALEMAN | 7698 | 2016-09-12 | 12000 | 1400 |     30
    (3 rows)
    

    1.查询同一个部门下的员工且合并起来

    方法1:
    select deptno, string_agg(ename, ',') from jinbo.employee group by deptno;
    
     deptno |  string_agg  
    --------+--------------
         20 | JONES
         30 | ALLEN,MARTIN
    
    方法2:
    select deptno, array_to_string(array_agg(ename),',') from jinbo.employee group by deptno;
     deptno | array_to_string 
    --------+-----------------
         20 | JONES
         30 | ALLEN,MARTIN
    

    2、在1条件的基础上,按ename 倒叙合并

    select deptno, string_agg(ename, ',' order by ename desc) from jinbo.employee group by deptno;
     deptno |  string_agg  
    --------+--------------
         20 | JONES
         30 | MARTIN,ALLEN
    
    

    3、按数组格式输出使用 array_agg

    select deptno, array_agg(ename) from jinbo.employee group by deptno;
     deptno |   array_agg    
    --------+----------------
         20 | {JONES}
         30 | {ALLEN,MARTIN}
    
    

    4、array_agg 去重元素,例如查询所有的部门

    select array_agg(distinct deptno) from jinbo.employee;
    array_agg 
    -----------
     {20,30}
    (1 row)
    
    #不仅可以去重,还可以排序
    
    select array_agg(distinct deptno order by deptno desc) from jinbo.employee;
     array_agg 
    -----------
     {30,20}
    (1 row)
    

    5、array_agg 排序再array取值,例如查询每个部门第一个入职的人

    select deptno, (array_agg(ename order by hiredate asc))[1] from jinbo.employee group by deptno;
     deptno | array_agg 
    --------+-----------
         20 | JONES
         30 | ALLEN
    
    展开全文
  • 聚合函数使用

    2014-09-11 08:59:53
    db.Log.aggregate([ {$match: {"ft":1,"ut":{$lt:ISODate("2014-08-19 00:00:00")}}}, {$group: { _id: {un:"$un"}, count: { $sum: { $cond: [ { $gt: [ "$pid", 0 ] } , 1, 0] }} ...{$project:{un:1,count

    数据格式如下:

    {
      "_id" : 1,
      "ft" : "云功能",
      "pro" : "江苏省",
      "pid" : 3,
      "prid" : 320000,
      "guid" : "20140904abcdefabcd10000000",
      "fn" : "云检查",
      "ct" : "无锡市",
      "hds" : "76332f9bafe7aff580e47e42df07e5c7",
      "cid" : 320200,
      "ut" : new Date("1/9/2014 20:01:00"),
      "un" : "shao1"
    }


    需求:根据用户进行聚合操作,聚合出某个时间之内的用户的使用ft的次数,最后根据使用次数进行过滤。


    db.Log.aggregate([

    {$match: {"ft":1,"ut":{$lt:ISODate("2014-08-19 00:00:00")}}},
    {$group: {
    _id: {un:"$un"},
    count: { $sum: { $cond: [ { $gt: [ "$pid", 0 ] } , 1, 0] }}
    }},
    {$project:{un:1,count: 1}},
    {$match: {"count":{$gt: 1}}}

    ]);


    展开全文
  • PostgreSql的窗口函数使用 文档中涉及的表的结构和数据: 1. 表emp_detail: create table emp_detail(  empno integer,  ename varchar(10),  sal numeric,  dept_no integer,  time_stamp date  );...

    PostgreSql的窗口函数使用
    文档中涉及的表的结构和数据:
    1. 表emp_detail:
    create table emp_detail(
     empno integer,
     ename varchar(10),
     sal numeric,
     dept_no integer,
     time_stamp date
     );
     
     insert into emp_detail values(7369,'SMITH',100);
     
     insert into emp_detail values(7369,'SMITH',100,20,'2015-04-01');
     insert into emp_detail values(7369,'SMITH',105,20,'2015-04-02');
     insert into emp_detail values(7369,'SMITH',120,20,'2015-04-03');
     insert into emp_detail values(7369,'SMITH',150,20,'2015-04-04');
     insert into emp_detail values(7369,'SMITH',200,20,'2015-04-05');
     insert into emp_detail values(7369,'SMITH',400,20,'2015-04-06');
     insert into emp_detail values(7369,'SMITH',180,20,'2015-04-07');


    2. 表Student:
    create table student(
      id int,
      stu_name varchar(50),
      chinese numeric,
      english numeric,
      math    numeric
      );
      
      insert into student values(1001,'小明',80,75,90);
      insert into student values(1002,'小红',70,75,85);
      insert into student values(1003,'小强',80,90,100);


    3. 表emp:
    CREATE TABLE public.emp (
      empno INTEGER,
      ename VARCHAR(10),
      job VARCHAR(9),
      mgr INTEGER,
      hiredate TIMESTAMP(6) WITHOUT TIME ZONE,
      sal DOUBLE PRECISION,
      comm DOUBLE PRECISION,
      dept_no INTEGER
    );




    INSERT INTO public.emp ("empno", "ename", "job", "mgr", "hiredate", "sal", "comm", "dept_no")
    VALUES (7369, E'SMITH', E'CLERK', 7902, E'1980-12-17 00:00:00', 800, NULL, 20);


    INSERT INTO public.emp ("empno", "ename", "job", "mgr", "hiredate", "sal", "comm", "dept_no")
    VALUES (7499, E'ALLEN', E'SALESMAN', 7698, E'1981-02-20 00:00:00', 1600, 306, 30);


    INSERT INTO public.emp ("empno", "ename", "job", "mgr", "hiredate", "sal", "comm", "dept_no")
    VALUES (7521, E'WARD', E'SALESMAN', 7698, E'1981-02-22 00:00:00', 1250, 506, 30);


    INSERT INTO public.emp ("empno", "ename", "job", "mgr", "hiredate", "sal", "comm", "dept_no")
    VALUES (7566, E'JONES', E'MANAGER', 7839, E'1981-04-02 00:00:00', 2975, NULL, 20);


    INSERT INTO public.emp ("empno", "ename", "job", "mgr", "hiredate", "sal", "comm", "dept_no")
    VALUES (7654, E'MARTIN', E'SALESMAN', 7698, E'1981-09-28 00:00:00', 1250, 1406, 30);


    INSERT INTO public.emp ("empno", "ename", "job", "mgr", "hiredate", "sal", "comm", "dept_no")
    VALUES (7698, E'BLAKE', E'MANAGER', 7839, E'1981-05-01 00:00:00', 2850, NULL, 30);


    INSERT INTO public.emp ("empno", "ename", "job", "mgr", "hiredate", "sal", "comm", "dept_no")
    VALUES (7782, E'CLARK', E'MANAGER', 7839, E'1981-06-09 00:00:00', 2450, NULL, 10);


    INSERT INTO public.emp ("empno", "ename", "job", "mgr", "hiredate", "sal", "comm", "dept_no")
    VALUES (7788, E'SCOTT', E'ANALYST', 7566, E'1987-04-19 00:00:00', 3000, NULL, 20);


    INSERT INTO public.emp ("empno", "ename", "job", "mgr", "hiredate", "sal", "comm", "dept_no")
    VALUES (7839, E'KING', E'PRESIDENT', NULL, E'1981-11-17 00:00:00', 5000, NULL, 10);


    INSERT INTO public.emp ("empno", "ename", "job", "mgr", "hiredate", "sal", "comm", "dept_no")
    VALUES (7844, E'TURNER', E'SALESMAN', 7698, E'1981-09-08 00:00:00', 1500, 6, 30);


    INSERT INTO public.emp ("empno", "ename", "job", "mgr", "hiredate", "sal", "comm", "dept_no")
    VALUES (7876, E'ADAMS', E'CLERK', 7788, E'1987-05-23 00:00:00', 1100, NULL, 20);


    INSERT INTO public.emp ("empno", "ename", "job", "mgr", "hiredate", "sal", "comm", "dept_no")
    VALUES (7900, E'JAMES', E'CLERK', 7698, E'1981-12-03 00:00:00', 950, NULL, 30);


    INSERT INTO public.emp ("empno", "ename", "job", "mgr", "hiredate", "sal", "comm", "dept_no")
    VALUES (7902, E'FORD', E'ANALYST', 7566, E'1981-12-03 00:00:00', 3000, NULL, 20);


    INSERT INTO public.emp ("empno", "ename", "job", "mgr", "hiredate", "sal", "comm", "dept_no")
    VALUES (7934, E'MILLER', E'CLERK', 7782, E'1982-01-23 00:00:00', 1300, NULL, 10);
    一、 窗口函数的语法
    窗口函数的基本结构:
    function_name ([expression [, expression ... ]]) OVER window_name
    function_name ([expression [, expression ... ]]) OVER ( window_definition )
    function_name ( * ) OVER window_name
    function_name ( * ) OVER ( window_definition )
    window_definition 的定义: 
    [ existing_window_name ]
    [ PARTITION BY expression [, ...] ]
    [ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ]
    [ frame_clause ]
    配置项frame_clause的选择: 
    [ RANGE | ROWS ] frame_start
    [ RANGE | ROWS ] BETWEEN frame_start AND frame_end
    窗口起始项 frame_start 和结束项 frame_end的选择:
    UNBOUNDED PRECEDING  
    value PRECEDING
    CURRENT ROW
    value FOLLOWING
    UNBOUNDED FOLLOWING


    这里,
    expression代表着任何的值表达式,但是不包含自身的窗口函数调用。
    Window_name代表一个窗口名称,一个完整的窗口是用WINDOW关键字命名并且定义的,也可以使匿名的。
    PARTITION BY 与group by是类似的都是用来分组,但是与group by不同的是PARTITION BY中就只是一个表达式,不像groupby其除了分组字段以外不能再select中显示,除非是聚合函数。如果没有PARTITION BY,那么每一行就是一个分组一个窗口。
    ORDER BY作用是使PARTITION BY分组中的排序方式,支持与通常的排序是一致的可以有ASC,DESC,nulls first or last等等。如果没有指定order by那么就是没有指定排序方式。
    frame_clause就是指窗口的大小,窗口如何移动等。
    frame_start和frame_end就是来确定窗口大小的两个参数或者是窗口的边界。
        frame_start不能使用unbounded following,同样frame_end也不能使用unbounded preceding
    UNBOUNDED PRECEDING 的意思是从第一行开始,但是只能用在frame_start的位置。
    UNBOUNDED FOLLOWING 的意思更好与UNBOUNDED PRECEDING相反指的是到最后一行,其也只能用在frame_end的位置。
    value PRECEDING和 value FOLLOWING都只能使用咋rows模式中不能使用在range模式中,value的值是一个整型的数值也可以使整型表达式,不能为变量,聚合函数,窗口函数等,value不能为空或者是不明确的,但是可以为0,为0的时候表示的就是当前行。
    value PRECEDING是指从哪一行开始,value FOLLOWING指的是从哪一行结束。
    Value = 1 时 value PRECEDING 指的是当前行的前一行开始,value FOLLOWING则为当前行的前一行为止。随着表中数据的扫描窗口会以这个尺寸一直走下去,执行相关的分析函数。


    二、窗口函数示例:
    1. 从员工表(emp)中查询每个员工的信息,并且查询整个公司的工资总额。


    select ename,sal,
    sum(sal)over(order by empno range between unbounded preceding and unbounded following)
     from emp;




    2. 从员工明细表中查询一个员工在前后三天所得的工资总和。


    select empno,
            ename,
            sal,
            dept_no,
            sum(sal) over(
     order by empno, time_stamp rows between 1 preceding and 1 following)
     from emp_detail;










    3. 从员工表(emp)中查询每个员工的信息,并且查询每个部门的工资总和。


    select ename,
            sal,
            sum(sal) over(partition by dept_no
     order by empno range between unbounded preceding and unbounded following)
     from emp;


    三、分析函数的介绍
    row_number():在一个结果集中,返回当前的行的号码。
    rank()、dense_rank():在一个结果集中,用来排名,前者是完全差异后者是不完全差异,简言之前者是按阿拉伯数字顺序来,后者则会跳跃。
    lag(value any)、lead(value any):用来对当前行对于指定的字段与下一行或者前一行的值进行比较。
    first_value(value any)、last_value(value any):在一个窗口中,返回指定排序的第一个值和最后一个值。
    其他类似与sum(),agv(),max(),min()也都是能与窗口函数配合使用,当做分析函数。


    四、分析函数与窗口函数的混用示例
    1. 从员工表(emp)中按照员工被雇佣的时间大小,查询入职时间的先后顺序。
    Select row_number() over(
     order by hiredate asc),
              ename,
              empno,
              hiredate
     from emp;
    2. 从员工表中查询每个部门的工资排名,并且给工资最高的人加10%的奖金。
        update emp
     set comm = comm + sal * 0.01
     where empno in (
                      select *
                      from (
                             select ename,
                                    empno,
                                    sal,
                                    dept_no,
                                    dense_rank() over(partition by dept_no
                             order by sal desc) as level_
                             from emp
                           ) t
                      where level_ = 1
           );
    3. 从员工表中查询每个部门的工资排名,并且给每个部门中工资排名在第三名的员工加20%的奖金。
    update emp
     set comm = comm + sal * 0.02
     where empno in (
                      select *
                      from (
                             select ename,
                                    empno,
                                    sal,
                                    dept_no,
                                    rank() over(partition by dept_no
                             order by sal desc) as level_
                             from emp
                           ) t
                      where level_ = 1
           );
    4. 从员工表中查询每个部门的员工的工资从大到小排序,并且计算前后两名的工资差值。
    select ename,
     empno,
     sal,
     dept_no,
     lag(sal)over(partition by dept_no order by sal) as lag_end,
     sal - lag(sal)over(partition by dept_no order by sal)
      from emp order by dept_no,sal asc;
      查询结果中存在null中,原因是每个分组的第一行没有前一行一次为空值。
      那么假定第一行的值需要与最后一行来比较,那么应该怎么做:
       select ename,
     empno,
     sal,
     dept_no,
     CASE when lag(sal)over(partition by dept_no order by sal) is null then max(sal)OVER(partition by dept_no order by sal desc)
     else lag(sal)over(partition by dept_no order by sal)
     end lag_end
      from emp order by dept_no,sal asc;
    也可以是这样:
    select ename,
     empno,
     sal,
     dept_no,
     CASE when lag(sal)over(partition by dept_no order by sal) is null then first_value(sal)OVER(partition by dept_no order by sal desc)
     else lag(sal)over(partition by dept_no order by sal)
     end lag_end
      from emp order by dept_no,sal asc;
    5. 查询学生表中每一个学生按照科目的分数排序。
      select id,stu_name,course,point_,first_value(point_)over(partition by id order by point_ desc ) from (
      with temp as (
      select id,stu_name,chinese,english,math from student
      ),t1 as (select id,stu_name,chinese as point_, '语文'::text as course from temp
      ),t2 as (select id,stu_name,english as point_, '英语'::text as course from temp
      ),t3 as (select id,stu_name,math as point_, '数学'::text as course from temp)
      select * from t1 
      union all (select * from t2)
      union all (select * from t3)

    展开全文
  • 窗口聚合函数与分组聚合函数的功能是相同的;唯一不同的是,分组聚合函数通过分组查询来进行,而窗口聚合函数通过OVER子句定义的窗口来进行。 --《T-SQL性能调优秘笈---基于SQL Server2012窗口函数》2.1.1窗口聚合...
  • 摘要:PostgreSQL支持较多的聚合函数, 以PostgreSQL 9.4为例, 支持例如一般性的聚合, 统计学科的聚合, 排序集聚合, 假象集聚合等. 本文将对一般性聚合函数举例说明其功能和用法. 聚合函数有哪些,见 ...
  • [图片说明](https://img-ask.csdn.net/upload/201804/23/1524463871_996得66963.png) 我想要计算消费周期的平均值,所以就改写了上门那段代码,但是得出不能对包含聚合或子查询的表达式执行聚合函数的错误提示, !...
  • Android数据库高手秘籍(八)——使用LitePal的聚合函数

    万次阅读 多人点赞 2014-12-04 09:09:01
    但是呢,在SQL语句当中,有一种查询是比较特殊的,就是聚合函数查询,它不像传统查询一样是将表中的某些列的数据查询出来,而是将查询结果进行聚合和统计,最终将统计后的结果进行返回。因此,任何一个关系型数据库...
  • sql中聚合函数和分组函数 SQL has many cool features and aggregate functions are definitely one of these features, actually functions. While they are not specific to SQL, they are used often. They are...
  • 下面是一张 salary 工资表 ![图片说明]... 执行代码 ...SELECT department , SUM(gain) AS sgn FROM salary GROUP BY department HAVING COUNT(sgn) = 0;...

空空如也

1 2 3 4 5 ... 20
收藏数 17,848
精华内容 7,139
关键字:

聚合函数