选读SQL经典实例笔记09_数值处理

这篇具有很好参考价值的文章主要介绍了选读SQL经典实例笔记09_数值处理。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

选读SQL经典实例笔记09_数值处理文章来源地址https://www.toymoban.com/news/detail-580297.html

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表达式来决定交易的类型

到了这里,关于选读SQL经典实例笔记09_数值处理的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • 选读SQL经典实例笔记08_区间查询

    1.6.3.1. 即使同一天入职的员工不止一个人,也只会返回一个值 1.6.4.1. 使用MIN函数来确保只返回一个值 2.2.2.1. PROJ_START和PROJ_END的值决定哪些行属于同一个区间 2.2.2.2. 如果某一行的PROJ_START值等于上一行的PROJ_END值,那么该行就是“连续”的,或者说它属于某个组 3.4.1.1. ora

    2024年02月16日
    浏览(98)
  • 选读SQL经典实例笔记16_逻辑否定

    2024年02月14日
    浏览(183)
  • 选读SQL经典实例笔记18_Exactly

    2024年02月14日
    浏览(73)
  • 选读SQL经典实例笔记15_窗口函数

    2.2.1.1. 分组不为空 2.2.1.1.1. 一个分组至少要拥有一个成员(行 2.2.1.1.2. 无法从一个空表中生成任何分组 2.2.1.2. 分组具有唯一性 2.2.1.2.1. 如果查询语句使用了GROUP BY子句,那么通常而言SELECT列表里就不再需要使用DISTINCT了 2.2.2.1. COUNT永远大于0 2.2.2.1.1. 无法从一个空表

    2024年02月14日
    浏览(39)
  • 选读SQL经典实例笔记10_高级查询

    2024年02月17日
    浏览(96)
  • 选读SQL经典实例笔记02_多表查询

    3.1.2.1. 排除重复项

    2024年02月12日
    浏览(144)
  • 选读SQL经典实例笔记11_结果集变换

    3.6.1.1. 为了剔除掉Null,需要调用聚合函数MAX,并基于RN执行GROUP BY 3.9.1.1. 使用标量子查询基于EMPNO为每个员工排序 3.9.1.2. 针对标量子查询的返回值执行GROUP BY 3.9.1.3. 使用CASE表达式和聚合函数MAX实现结果集变换

    2024年02月16日
    浏览(82)
  • 选读SQL经典实例笔记01_检索和排序

    SMITH    800                           0 ALLEN   1600          300          1 WARD    1250         500          1 JONES   2975                          0 MARTIN  1250       1400         1 BLAKE   2850                         

    2024年02月11日
    浏览(117)
  • 选读SQL经典实例笔记22_2版增补

    4.1.3.1. 查找拼写不同但发音相同的字符串 6.1.1.1. 记录值的仪表存在误差 6.1.3.1. 意味着数据点是正确的,但应谨慎根据数据得出任何结论 9.1.2.1. 不使用CASE表达式或额外的连接操作

    2024年02月13日
    浏览(220)
  • 选读SQL经典实例笔记05_日期运算(下)

    3.6.2.1. 对于MySQL 版本的DATEDIFF函数,需要省略第一个参数day,并把剩下的两个参数的顺序颠倒过来 4.3.2.1. Oracle早期版本

    2024年02月13日
    浏览(83)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包