选读SQL经典实例笔记13_case与聚合

恒达丰鑫

1. 识别非小计行

1.1. 结果集

1.2. DB2

1.3. Oracle

1.4. 超级聚合（supera ggregate）值

1.4.1. sql

1. select deptno, job, sum(sal) sal,
2.        grouping(deptno) deptno_subtotals,
3.        grouping(job) job_subtotals
4.   from emp
5. group by cube(deptno,job)

复制代码

1.5. SQL Server

1.5.1.  sql

1. select deptno, job, sum(sal) sal,
2.         grouping(deptno) deptno_subtotals,
3.         grouping(job) job_subtotals
4.    from emp
5.   group by deptno,job with cube

复制代码

2. 使用CASE表达式标记行数据

2.1. 结果集

2.2. sql

1. select ename,
2.        case when job = 'CLERK'
3.             then 1 else 0
4.        end as is_clerk,
5.        case when job = 'SALESMAN'
6.             then 1 else 0
7.        end as is_sales,
8.        case when job = 'MANAGER'
9.             then 1 else 0
10.        end as is_mgr,
11.        case when job = 'ANALYST'
12.             then 1 else 0
13.        end as is_analyst,
14.        case when job = 'PRESIDENT'
15.             then 1 else 0
16.        end as is_prez
17.   from emp
18. order by 2,3,4,5,6

复制代码

3. 创建稀疏矩阵

3.1. 结果集

3.2. sql

1. select case deptno when 10 then ename end as d10,
2.        case deptno when 20 then ename end as d20,
3.        case deptno when 30 then ename end as d30,
4.        case job when 'CLERK'     then ename end as clerks,
5.        case job when 'MANAGER'   then ename end as mgrs,
6.        case job when 'PRESIDENT' then ename end as prez,
7.        case job when 'ANALYST'   then ename end as anals,
8.        case job when 'SALESMAN'  then ename end as sales
9.   from emp

复制代码

3.3. sql

1. select max(case deptno when 10 then ename end) d10,
2.        max(case deptno when 20 then ename end) d20,
3.        max(case deptno when 30 then ename end) d30,
4.        max(case job when 'CLERK'     then ename end) clerks,
5.        max(case job when 'MANAGER'   then ename end) mgrs,
6.        max(case job when 'PRESIDENT' then ename end) prez,
7.        max(case job when 'ANALYST'   then ename end) anals,
8.        max(case job when 'SALESMAN' then ename end) sales
9.   from (
10. select deptno, job, ename,
11.        row_number()over(partition by deptno order by empno) rn
12.   from emp
13.        ) x
14. group by rn

复制代码

3.3.1. 删除一些Null行，以便让整个报表显得“紧密”一些

4. 按照时间单位分组

4.1. 结果集

4.1.1. sql

1. select trx_id,
2.        trx_date,
3.        trx_cnt
4.   from trx_log
5. TRX_ID TRX_DATE                TRX_CNT
6. ------ -------------------- ----------
7.      1 28-JUL-2005 19:03:07         44
8.      2 28-JUL-2005 19:03:08         18
9.      3 28-JUL-2005 19:03:09         23
10.      4 28-JUL-2005 19:03:10         29
11.      5 28-JUL-2005 19:03:11         27
12.      6 28-JUL-2005 19:03:12         45
13.      7 28-JUL-2005 19:03:13         45
14.      8 28-JUL-2005 19:03:14         32
15.      9 28-JUL-2005 19:03:15         41
16.     10 28-JUL-2005 19:03:16         15
17.     11 28-JUL-2005 19:03:17         24
18.     12 28-JUL-2005 19:03:18         47
19.     13 28-JUL-2005 19:03:19         37
20.     14 28-JUL-2005 19:03:20         48
21.     15 28-JUL-2005 19:03:21         46
22.     16 28-JUL-2005 19:03:22         44
23.     17 28-JUL-2005 19:03:23         36
24.     18 28-JUL-2005 19:03:24         41
25.     19 28-JUL-2005 19:03:25         33
26.     20 28-JUL-2005 19:03:26         19

复制代码

4.1.2. 结果集

1. GRP TRX_START            TRX_END                   TOTAL
2. --- -------------------- -------------------- ----------
3.   1 28-JUL-2005 19:03:07 28-JUL-2005 19:03:11        141
4.   2 28-JUL-2005 19:03:12 28-JUL-2005 19:03:16        178
5.   3 28-JUL-2005 19:03:17 28-JUL-2005 19:03:21        202
6.   4 28-JUL-2005 19:03:22 28-JUL-2005 19:03:26        173

复制代码

4.2. sql

1. select ceil(trx_id/5.0) as grp,
2.        min(trx_date)    as trx_start,
3.        max(trx_date)    as trx_end,
4.        sum(trx_cnt)     as total
5.   from trx_log
6. group by ceil(trx_id/5.0)

复制代码

5. 多维度聚合运算

5.1. 结果集

5.2. DB2

5.3. Oracle

5.4. SQL Server

5.5. 窗口函数COUNT OVER

5.5.1. sql

1. select ename,
2.        deptno,
3.        count(*)over(partition by deptno) deptno_cnt,
4.        job,
5.        count(*)over(partition by job) job_cnt,
6.        count(*)over() total
7.   from emp

复制代码

5.6. PostgreSQL

5.7. MySQL

5.8. 使用标量子查询

5.8.1. sql

1. select e.ename,
2.        e.deptno,
3.        (select count(*) from emp d
4.          where d.deptno = e.deptno) as deptno_cnt,
5.        job,
6.        (select count(*) from emp d
7.          where d.job = e.job) as job_cnt,
8.        (select count(*) from emp) as total
9.   from emp e

复制代码

6. 动态区间聚合运算

6.1. 入职最早的员工的HIREDATE作为起始点，每隔90天计算一次工资合计值

6.1.1. 结果集

1. HIREDATE        SAL SPENDING_PATTERN
2. ----------- ------- ----------------
3. 17-DEC-1980     800              800
4. 20-FEB-1981    1600             2400
5. 22-FEB-1981    1250             3650
6. 02-APR-1981    2975             5825
7. 01-MAY-1981    2850             8675
8. 09-JUN-1981    2450             8275
9. 08-SEP-1981    1500             1500
10. 28-SEP-1981    1250             2750
11. 17-NOV-1981    5000             7750
12. 03-DEC-1981     950            11700
13. 03-DEC-1981    3000            11700
14. 23-JAN-1982    1300            10250
15. 09-DEC-1982    3000             3000
16. 12-JAN-1983    1100             4100

复制代码

6.2. DB2

6.3. Oracle

6.4. 窗口函数SUM OVER

6.4.1. sql

1. select hiredat,
2.        sal,
3.        sum(sal)over(order by days(hiredate)
4.                        range between 90 preceding
5.                          and current row) spending_pattern
6.   from emp e

复制代码

6.4.2. sql

1. select hiredate,
2.        sal,
3.        sum(sal)over(order by hiredate
4.                        range between 90 preceding
5.                          and current row) spending_pattern
6.   from emp e

复制代码

6.4.2.1. Oracle的窗口函数支持DATE类型排序

6.5. PostgreSQL

6.6. MySQL

6.7. SQL Server

6.8. 使用标量子查询

6.8.1. sql

1. select e.hiredate,
2.        e.sal,
3.        (select sum(sal) from emp d
4.          where d.hiredate between e.hiredate-90
5.                               and e.hiredate) as spending_pattern
6.   from emp e
7. order by 1

复制代码

7. 变换带有小计的结果集

7.1. 结果集

7.2. DB2

7.3. Oracle

7.4. 使用GROUP BY的ROLLUP扩展

7.4.1.  sql

1. select mgr,
2.         sum(case deptno when 10 then sal else 0 end) dept10,
3.         sum(case deptno when 20 then sal else 0 end) dept20,
4.         sum(case deptno when 30 then sal else 0 end) dept30,
5.         sum(case flag when '11' then sal else null end) total
6.    from (
7. select deptno,mgr,sum(sal) sal,
8.         cast(grouping(deptno) as char(1))||
9.         cast(grouping(mgr) as char(1)) flag
10.    from emp
11.   where mgr is not null
12.   group by rollup(deptno,mgr)
13.         ) x
14.   group by mgr

复制代码

7.5. SQL Server

7.5.1. sql

1. select mgr,
2.        sum(case deptno when 10 then sal else 0 end) dept10,
3.        sum(case deptno when 20 then sal else 0 end) dept20,
4.        sum(case deptno when 30 then sal else 0 end) dept30,
5.        sum(case flag   when '11' then sal else null end) total
6.   from (
7. select deptno,mgr,sum(sal) sal,
8.        cast(grouping(deptno) as char(1))+
9.        cast(grouping(mgr)    as char(1)) flag
10.   from emp
11. where mgr is not null
12. group by deptno,mgr with rollup
13.        ) x
14. group by mgr

复制代码

来源:https://www.cnblogs.com/lying7/p/17578482.html
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！