1. 对于复杂的数值计算而言，SQL 并非首选工具

2. 求和

2.1. SUM函数会忽略Null，但是我们可能会遇到Null分组

2.2. sql

select deptno, comm
  from emp
 where deptno in (10,30)
 order by 1
   DEPTNO       COMM
---------- ----------
        10
        10
        10
        30        300
        30        500
        30
        30          0
        30       1300
        30
select sum(comm)
  from emp
SUM(COMM)
----------
      2100
select deptno, sum(comm)
  from emp
 where deptno in (10,30)
 group by deptno
   DEPTNO  SUM(COMM)
---------- ----------
        10
        30       2100

3. 行数

3.1. COUNT函数会忽略Null

3.2. 使用符号*或者常量参数的时候，就会包含Null

4. 累计求和

4.1. DB2

4.2. Oracle

4.3. 使用SUM函数的窗口函数版本进行累计求和

select ename, sal,
        sum(sal) over (order by sal,empno) as running_total
   from emp
   order by 2
ENAME             SAL RUNNING_TOTAL
---------- ---------- -------------
SMITH             800           800
JAMES             950          1750
ADAMS            1100          2850
WARD             1250          4100
MARTIN           1250          5350
MILLER           1300          6650
TURNER           1500          8150
ALLEN            1600          9750
CLARK            2450         12200
BLAKE            2850         15050
JONES            2975         18025
SCOTT            3000         21025
FORD             3000         24025
KING             5000         29025

4.4. PostgreSQL

4.5. MySQL

4.6. SQL Server

4.7. 使用标量子查询来进行累计求和

select e.ename, e.sal,
        (select sum(d.sal) from emp d
          where d.empno ＜= e.empno) as running_total
   from emp e
  order by 3
ENAME             SAL RUNNING_TOTAL
---------- ---------- -------------
SMITH             800           800
ALLEN            1600          2400
WARD             1250          3650
JONES            2975          6625
MARTIN           1250          7875
BLAKE            2850         10725
CLARK            2450         13175
SCOTT            3000         16175
KING             5000         21175
TURNER           1500         22675
ADAMS            1100         23775
JAMES             950         24725
FORD             3000         27725
MILLER           1300         29025

5. 累计乘积

5.1. DB2

5.2. Oracle

5.3. 使用窗口函数SUM OVER，并利用对数来模拟乘法

select empno,ename,sal,
        exp(sum(ln(sal))over(order by sal,empno)) as running_prod
   from emp
  where deptno = 10
EMPNO ENAME       SAL         RUNNING_PROD
----- ---------- ---- --------------------
 7934 MILLER     1300                 1300
 7782 CLARK      2450              3185000
 7839 KING       5000          15925000000

5.4. PostgreSQL

5.5. MySQL

5.6. SQL Server

5.7. 标量子查询

select e.empno,e.ename,e.sal,
         (select exp(sum(ln(d.sal)))
            from emp d
           where d.empno ＜= e.empno
             and e.deptno=d.deptno) as running_prod
   from emp e
   where e.deptno=10
EMPNO ENAME       SAL         RUNNING_PROD
----- ---------- ---- --------------------
 7782 CLARK      2450                 2450
 7839 KING       5000             12250000
 7934 MILLER     1300          15925000000

5.7.2. 对于SQL Server而言，还需要用LOG函数来替代LN函数

6. 累计差

6.1. DB2

6.2. Oracle

6.3. 使用窗口函数SUM OVER

select ename,sal,
         sum(case when rn = 1 then sal else -sal end)
         over(order by sal,empno) as running_diff
    from (
  select empno,ename,sal,
         row_number() over(order by sal,empno) as rn
    from emp
   where deptno = 10
          ) x

6.4. PostgreSQL

6.5. MySQL

6.6. SQL Server

6.7. 使用标量子查询

select a.empno, a.ename, a.sal,
        (select case when a.empno = min(b.empno) then sum(b.sal)
                     else sum(-b.sal)
                end
           from emp b
          where b.empno ＜= a.empno
            and b.deptno = a.deptno ) as rnk
   from emp a
  where a.deptno = 10

7. 众数

7.1. 在一组数据里出现次数最多的那个数

7.2. DB2

7.3. SQL Server

7.4. 使用窗口函数DENSE_RANK

select sal
    from (
  select sal,
         dense_rank() over(order by cnt desc) as rnk
    from (
  select sal, count(*) as cnt
    from emp
   where deptno = 20
   group by sal
         ) x
         ) y
   where rnk = 1

7.5. Oracle

select max(sal)
          keep(dense_rank first order by cnt desc) sal
    from (
  select sal, count(*) cnt
    from emp
   where deptno=20
   group by sal
         )

7.6. PostgreSQL

7.7. MySQL

7.8. 使用子查询

select sal
    from emp
   where deptno = 20
   group by sal
  having count(*) ＞= all ( select count(*)
                             from emp
                            where deptno = 20
                            group by sal )

8. 中位数

8.1. 按顺序排列的一组数据中居于中间位置的数

8.2. DB2

'select avg(sal)
    from (
  select sal,
         count(*) over() total,
         cast(count(*) over() as decimal)/2 mid,
         ceil(cast(count(*) over() as decimal)/2) next,
         row_number() over (order by sal) rn
    from emp
   where deptno = 20
         ) x
   where ( mod(total,2) = 0
           and rn in ( mid, mid+1 )
         )
      or ( mod(total,2) = 1
           and rn = next
         )

8.2.2. DB2则使用MOD函数

8.3. SQL Server

select avg(sal)
    from (
  select sal,
         count(*) over() total,
         cast(count(*) over() as decimal)/2 mid,
         ceiling(cast(count(*)over() as decimal)/2) next,
         row_number() over(order by sal) rn
    from emp
   where deptno = 20
         ) x
   where ( total%2 = 0
           and rn in ( mid, mid+1 )
         )
      or ( total%2 = 1
           and rn = next
         )

8.3.2. SQL Server的取模运算符是%

8.4. Oracle

select median(sal)
  from emp
 where deptno=20

8.4.1.1. Oracle Database 10g

select percentile_cont(0.5)
        within group(order by sal)
  from emp
 where deptno=20

8.4.2.1. Oracle 9i

8.5. PostgreSQL

8.6. MySQL

8.7. 使用自连接查询

select avg(sal)
    from (
  select e.sal
    from emp e, emp d
   where e.deptno = d.deptno
     and e.deptno = 20
   group by e.sal
  having sum(case when e.sal = d.sal then 1 else 0 end)
                            ＞= abs(sum(sign(e.sal - d.sal)))
         )

9. 百分比

9.1. 某一列的值占总和的百分比

9.2. DB2

9.3. Oracle

9.4. SQL Server

9.5. sql

select distinct (d10/total)*100 as pct
   from (
 select deptno,
        sum(sal)over() total,
        sum(sal)over(partition by deptno) d10
   from emp
        ) x
  where deptno=10

9.6. MySQL

9.7. PostgreSQL

9.8. sql

select (sum(
          case when deptno = 10 then sal end)/sum(sal)
         )*100 as pct
   from emp

10. 聚合Null列

10.1. 使用聚合函数时一定要记住，Null值会被忽略

10.2. 一旦涉及聚合运算，就要相应地考虑如何处理Null值

select avg(coalesce(comm,0)) as avg_comm
    from emp
   where deptno=30

11. 计算平均值时去掉最大值和最小值

11.1. DB2

11.2. Oracle

11.3. SQL Server

11.4. 窗口函数MAX OVER和MIN OVER

select avg(sal)
    from (
  select sal, min(sal) over()min_sal, max(sal)over() max_sal
    from emp
         ) x
   where sal not in (min_sal,max_sal)

11.5. PostgreSQL

11.6. MySQL

11.7. 使用子查询去掉最大值和最小值

select avg(sal)
    from emp
   where sal not in (
      (select min(sal) from emp),
      (select max(sal) from emp)
   )

11.7.2. 如果希望只去掉一个最大值和一个最小值，只需要把它们从合计值里先减掉，再做除法即可

select (sum(sal)-min(sal)-max(sal))/(count(*)-2)
  from emp

12. 修改累计值

12.1. 示例

create view V (id,amt,trx)
as
select 1, 100, 'PR' from t1 union all
select 2, 100, 'PR' from t1 union all
select 3, 50,  'PY' from t1 union all
select 4, 100, 'PR' from t1 union all
select 5, 200, 'PY' from t1 union all
select 6, 50,  'PY' from t1
select * from V
ID        AMT TRX
-- ---------- ---
 1        100  PR
 2        100  PR
 3         50  PY
 4        100  PR
 5        200  PY
 6         50  PY

12.2. DB2

12.3. Oracle

12.4. 使用窗口函数SUM OVER进行累计求和

select case when trx = 'PY'
              then 'PAYMENT'
              else 'PURCHASE'
          end trx_type,
          amt,
          sum(
           case when trx = 'PY'
              then -amt else amt
           end
         ) over (order by id,amt) as balance
    from V

12.4.2. 使用CASE表达式来决定交易的类型

12.5. PostgreSQL

12.6. MySQL

12.7. SQL Server

12.8. 使用标量子查询进行累计求和

select case when v1.trx = 'PY'
              then 'PAYMENT'
              else 'PURCHASE'
          end as trx_type,
          v1.amt,
          (select sum(
                   case when v2.trx = 'PY'
                        then -v2.amt else v2.amt
                   end
                 )
            from V v2
           where v2.id ＜= v1.id) as balance
   from V v1

12.8.2. 使用CASE表达式来决定交易的类型