当前位置：网站首页 > 优雅编程 > 正文

选读SQL经典实例笔记12_桶、图和小计

sinye56 2024-09-22 08:27 4 浏览 0 评论

1. 创建固定大小的数据桶

1.1. 数据放入若干个大小固定的桶（bucket）里，每个桶的元素个数是事先定好的

1.1.1. 针对商值向上取整

1.2. DB2

1.3. Oracle

1.4. SQL Server

1.5. 使用窗口函数ROW_NUMBER OVER

1.5.1. sql

select ceil(row_number()over(order by empno)/5.0) grp,
       empno,
       ename
  from emp

1.6. PostgreSQL

1.7. MySQL

1.8. 使用标量子查询为每个EMPNO生成一个序号

1.8.1. sql

select ceil(rnk/5.0) as grp,
        empno, ename
   from (
 select e.empno, e.ename,
        (select count(*) from emp d
          where e.empno ＜ d.empno)+1 as rnk
   from emp e
        ) x
  order by grp

2. 创建预定数目的桶

2.1. 不在乎每个桶里有多少个元素，但需要创建固定数目（数目已知）的桶

2.2. DB2

2.2.1. sql

select mod(row_number()over(order by empno),4)+1 grp,
       empno,
       ename
  from emp
 order by 1

2.3. Oracle

2.4. SQL Server

2.5. 使用窗口函数NTILE

2.5.1. sql

select ntile(4)over(order by empno) grp,
       empno,
       ename
  from emp

2.6. PostgreSQL

2.7. MySQL

2.8. 使用自连接基于EMPNO为每一行生成一个序号

2.8.1. sql

select mod(count(*),4)+1 as grp,
        e.empno,
        e.ename
  from emp e, emp d
 where e.empno ＞= d.empno
 group by e.empno,e.ename
 order by 1

3. 创建水平直方图

3.1. 结果集

3.1.1. sql

DEPTNO CNT
------ ----------
    10  ****
    20  ********
    30  ****

3.2. DB2

3.2.1. sql

select deptno,
       repeat('*',count(*)) cnt
  from emp
 group by deptno

3.3. Oracle

3.4. PostgreSQL

3.5. MySQL

3.6. 使用LPAD函数生成所需的字符串“*”

3.6.1. sql

select deptno,
       lpad('*',count(*),'*') as cnt
  from emp
 group by deptno

3.6.2. sql

select deptno,
       lpad('*',count(*)::integer,'*') as cnt
  from emp
 group by deptno

3.6.2.1. CAST函数调用是必须的，因为PostgreSQL 要求LPAD的参数为整数

3.7. SQL Server

3.7.1. sql

select deptno,
       replicate('*',count(*)) cnt
  from emp
 group by deptno

4. 创建垂直直方图

4.1. 结果集

4.1.1. sql

D10 D20 D30

--- --- ---

        *
    *   *
    *   *

*   *   *
*   *   *
*   *

4.2. DB2

4.3. Oracle

4.4. SQL Server

4.5. 使用窗口函数ROW_NUMBER OVER

4.5.1. sql

select max(deptno_10) d10,
       max(deptno_20) d20,
       max(deptno_30) d30
  from (
select row_number()over(partition by deptno order by empno) rn,
       case when deptno=10 then '*' else null end deptno_10,
       case when deptno=20 then '*' else null end deptno_20,
       case when deptno=30 then '*' else null end deptno_30
  from emp
       ) x
 group by rn
 order by 1 desc, 2 desc, 3 desc

4.6. PostgreSQL

4.7. MySQL

4.8. 使用标量子查询

4.8.1. sql

select max(deptno_10) as d10,
       max(deptno_20) as d20,
       max(deptno_30) as d30
  from (
select case when e.deptno=10 then '*' else null end deptno_10,
       case when e.deptno=20 then '*' else null end deptno_20,
       case when e.deptno=30 then '*' else null end deptno_30,
       (select count(*) from emp d
         where e.deptno=d.deptno and e.empno ＜ d.empno ) as rnk
  from emp e
       ) x
 group by rnk
 order by 1 desc, 2 desc, 3 desc

5. 返回非分组列

5.1. 希望找出每个部门工资最高和最低的员工，同时也希望找出每个职位对应的工资最高和最低的员工

5.2. DB2

5.3. Oracle

5.4. SQL Server

5.5. 窗口函数MAX OVER和MIN OVER

5.5.1. sql

select deptno,ename,job,sal,
       case when sal = max_by_dept
            then 'TOP SAL IN DEPT'
            when sal = min_by_dept
            then 'LOW SAL IN DEPT'
       end dept_status,
       case when sal = max_by_job
            then 'TOP SAL IN JOB'
            when sal = min_by_job
            then 'LOW SAL IN JOB'
       end job_status
   from (
 select deptno,ename,job,sal,
        max(sal)over(partition by deptno) max_by_dept,
        max(sal)over(partition by job)    max_by_job,
        min(sal)over(partition by deptno) min_by_dept,
        min(sal)over(partition by job)    min_by_job
   from emp
        ) emp_sals
  where sal in (max_by_dept,max_by_job,
                min_by_dept,min_by_job)

5.6. PostgreSQL

5.7. MySQL

5.8. 使用标量子查询

5.8.1. sql

select deptno,ename,job,sal,
        case when sal = max_by_dept
             then 'TOP SAL IN DEPT'
             when sal = min_by_dept
             then 'LOW SAL IN DEPT'
        end as dept_status,
        case when sal = max_by_job
             then 'TOP SAL IN JOB'
             when sal = min_by_job
             then 'LOW SAL IN JOB'
        end as job_status
   from (
 select e.deptno,e.ename,e.job,e.sal,
        (select max(sal) from  emp d
          where d.deptno = e.deptno) as max_by_dept,
        (select max(sal) from  emp d
          where d.job = e.job) as max_by_job,
        (select min(sal) from  emp d
          where d.deptno = e.deptno) as min_by_dept,
        (select min(sal) from  emp d
          where d.job = e.job) as min_by_job
   from emp e
        ) x
  where sal in (max_by_dept,max_by_job,
                min_by_dept,min_by_job)

6. 简单的小计

6.1. 结果集

6.1.1. sql

 JOB              SAL
--------- ----------
ANALYST         6000
CLERK           4150
MANAGER         8275
PRESIDENT       5000
SALESMAN        5600
TOTAL          29025

6.2. DB2

6.3. Oracle

6.4. 使用GROUP BY的ROLLUP

6.4.1. sql

select case grouping(job)
            when 0 then job
            else 'TOTAL'
       end job,
       sum(sal) sal
  from emp
 group by rollup(job)

6.5. PostgreSQL

6.5.1. sql

select job, sum(sal) as sal
  from emp
 group by job
 union all
select 'TOTAL', sum(sal)
  from emp

6.6. MySQL

6.7. SQL Server

6.8. 使用WITH ROLLUP构造

6.8.1. sql

select coalesce(job,'TOTAL') job,
       sum(sal) sal
   from emp
  group by job with rollup

7. 所有可能的表达式组合的小计

7.1. 结果集

7.2. DB2

7.2.1. sql

select deptno,
       job,
       case cast(grouping(deptno) as char(1))||
            cast(grouping(job) as char(1))
            when '00' then 'TOTAL BY DEPT AND JOB'
            when '10' then 'TOTAL BY JOB'
            when '01' then 'TOTAL BY DEPT'
            when '11' then 'TOTAL FOR TABLE'
       end category,
       sum(sal)
  from emp
 group by cube(deptno,job)
 order by grouping(job),grouping(deptno)

7.3. Oracle

7.3.1. sql

select deptno,
        job,
        case grouping(deptno)||grouping(job)
             when '00' then 'TOTAL BY DEPT AND JOB'
             when '10' then 'TOTAL BY JOB'
             when '01' then 'TOTAL BY DEPT'
             when '11' then 'GRAND TOTAL FOR TABLE'
        end category,
        sum(sal) sal
   from emp
  group by cube(deptno,job)
  order by grouping(job),grouping(deptno)

7.4. PostgreSQL

7.5. MySQL

7.6. UNION ALL

7.6.1. sql

select deptno, job,
       'TOTAL BY DEPT AND JOB' as category,
       sum(sal) as sal
  from emp
 group by deptno, job
 union all
select null, job, 'TOTAL BY JOB', sum(sal)
  from emp
 group by job
union all
elect deptno, null, 'TOTAL BY DEPT', sum(sal)
 from emp
group by deptno
union all
elect null,null,'GRAND TOTAL FOR TABLE', sum(sal)
 from emp

7.7. SQL Server

7.7.1. sql

select deptno,
       job,
       case cast(grouping(deptno)as char(1))+
            cast(grouping(job)as char(1))
            when '00' then 'TOTAL BY DEPT AND JOB'
            when '10' then 'TOTAL BY JOB'
            when '01' then 'TOTAL BY DEPT'
            when '11' then 'GRAND TOTAL FOR TABLE'
       end category,
       sum(sal) sal
  from emp
 group by deptno,job with cube
 order by grouping(job),grouping(deptno)

开窗函数 oracle

上一篇：选读SQL经典实例笔记11_结果集变换
下一篇：如何查看Oracle数据块和操作系统块大小