SQL基础之高级处理


本文主要是阅读《SQL 基础教程》这本书总结而来,有需求的话可以阅读原著。

SQL基础之高级处理


1. 窗口函数

  • 窗口函数可以进行排序、生成序列号等一般的集合函数无法实现的高级操作
  • 需要深入理解partitonorder by两个关键字的含义
  • 窗口函数意为数据库数据进行实时分析处理

1.1 窗口函数的基础知识

  • 语法格式
<窗口函数> over ([partiton by <列清单>]) order by <排序用列清单>)
  • 能够作为窗口函数使用的函数
    • 聚合类函数
    • sumavgcountmaxmin
    • 专用窗口函数
    • rankdense_rankrow_number

1.2 rank函数

  • 注意事项

    • 用来计算记录排序的函数
    • 窗口函数兼具分组和排序两种功能
    • partiton by能够设定排序的对象范围
    • order by能够指定按照那一列、何种顺序进行排序
  • 指定partiton by

# Oracle、DB2、PostgreSQL
# 将各种商品按照销售单价从低到高的顺序创建排序表
mysql> select shohin_mei, shohin_bunrui, hanbai_tanka,
    -> rank () over (partiton by shohin_bunrui
    -> order by hanbai_tanka) as ranking
    -> from Shohin;
  • 无需指定partiton by
# Oracle、DB2、PostgreSQL
# 只是对于hanbai_tanka进行从低到高的排序
mysql> select shohin_mei, shohin_bunrui, hanbai_tanka,
    -> rank () over (order by hanbai_tanka) as ranking
    -> from Shohin;

1.3 专用的窗口函数的种类

  • rank
    • 计算排序时,如果存在相同位次的记录,则会跳过之后的位次
  • 如:1,1,1,4 …
    • dense_rank
  • 计算排序时,及时存在相同位次的记录,也不会跳过之后的位次
    • 如:1,1,1,2 …
  • row_number
    • 赋予唯一的连续位次
    • 如:1,2,3,4 …
    • 专用窗口函数无需参数,通常括号中都是空的
    • 原则上窗口函数只能在select子句中使用

1.4 作为窗口函数使用的聚合函数

# Oracle、DB2、PostgreSQL
mysql> select shohin_id, shohin_mei, hanbai_tanka,
    -> avg(hanbai_tanka) over (order by shohin_id) as current_avg
    -> from Shohin;

2. grouping运算符

  • 只使用group by子句和聚合函数无法同时计算小计或者合计值
  • 如果想要一次得到这两个值可以使用grouping运算符

2.1 同时计算出合计值

  • 实例演示
# 使用`group by`无法得到合计行
mysql> select shohin_bunrui, sum(hanbai_tanka)
    -> from Shohin
    -> group by shohin_bunrui;
+---------------+-------------------+
| shohin_bunrui | sum(hanbai_tanka) |
+---------------+-------------------+
| 衣服        |              5000 |
| 办公用品  |               695 |
| 厨房用具  |             11180 |
+---------------+-------------------+
3 rows in set (0.00 sec)
  • 优化优化
# 分别计算出合计行和聚合结果再通过union all进行连接
mysql> select '合计' as shohin_bunrui, sum(hanbai_tanka)
    -> from Shohin
    -> union all
    -> select shohin_bunrui, sum(hanbai_tanka)
    -> from Shohin
    -> group by shohin_bunrui;
+---------------+-------------------+
| shohin_bunrui | sum(hanbai_tanka) |
+---------------+-------------------+
| 合计        |             16875 |
| 衣服        |              5000 |
| 办公用品  |               695 |
| 厨房用具  |             11180 |
+---------------+-------------------+
4 rows in set (0.00 sec)

2.2 同时计算出合计值和小计值

  • rollup
  • cube
  • grouping sets
  • 后面的两个自己看吧,不做介绍了

2.3 rollup 使用方式

# 算出合计行
# MySQL
mysql> select shohin_bunrui, sum(hanbai_tanka) as sum_tanka
    -> from Shohin
    -> group by shohin_bunrui with rollup;
+---------------+-----------+
| shohin_bunrui | sum_tanka |
+---------------+-----------+
| 衣服        |      5000 |
| 办公用品  |       695 |
| 厨房用具  |     11180 |
| NULL          |     16875 |
+---------------+-----------+
4 rows in set (0.00 sec)
# 算出合计行
# Oracle、DB2、PostgreSQL
mysql> select shohin_bunrui, sum(hanbai_tanka) as sum_tannka
    -> from Shohin
    -> group by rollup(shohin_bunrui);
  • 将日期添加到聚合建当中
# 使用group by添加日期
mysql> select shohin_bunrui, torokubi, sum(hanbai_tanka) as sum_tanka
    -> from Shohin
    -> group by shohin_bunrui, torokubi;
+---------------+------------+-----------+
| shohin_bunrui | torokubi   | sum_tanka |
+---------------+------------+-----------+
| 衣服        | NULL       |      4000 |
| 衣服        | 2009-09-20 |      1000 |
| 办公用品  | 2009-09-11 |       500 |
| 办公用品  | 2009-11-11 |       100 |
| 办公用品  | 2009-11-30 |        95 |
| 厨房用具  | 2008-09-20 |       880 |
| 厨房用具  | 2009-09-15 |      6800 |
| 厨房用具  | 2009-09-20 |      3500 |
+---------------+------------+-----------+
8 rows in set (0.00 sec)

# 使用rollup添加日期
# MySQL
mysql> select shohin_bunrui, torokubi, sum(hanbai_tanka) as sum_tanka
    -> from Shohin
    -> group by shohin_bunrui, torokubi with rollup;
+---------------+------------+-----------+
| shohin_bunrui | torokubi   | sum_tanka |
+---------------+------------+-----------+
| 衣服        | NULL       |      4000 |
| 衣服        | 2009-09-20 |      1000 |
| 衣服        | NULL       |      5000 |
| 办公用品  | 2009-09-11 |       500 |
| 办公用品  | 2009-11-11 |       100 |
| 办公用品  | 2009-11-30 |        95 |
| 办公用品  | NULL       |       695 |
| 厨房用具  | 2008-09-20 |       880 |
| 厨房用具  | 2009-09-15 |      6800 |
| 厨房用具  | 2009-09-20 |      3500 |
| 厨房用具  | NULL       |     11180 |
| NULL          | NULL       |     16875 |
+---------------+------------+-----------+

# Oracle、DB2、PostgreSQL
mysql> select shohin_bunrui, torokubi, sum(hanbai_tanka) as sum_tanka
    -> from Shohin
    -> group by rollup(shohin_bunrui, torokubi);

2.4 grouping 函数

  • null更加容易分辨
  • 碰到超级分组记录中的null时返回1
  • 原始数据为null时返回0
mysql> select grouping(shohin_bunrui) as shohin_bunrui,
    -> grouping(torokubi) as torokubi,
    -> sum(hanbai_tanka) as sum_tanka
    -> from Shohin
    -> group by rollup(shohin_bunrui, torokubi);

文章作者: Escape
版权声明: 本博客所有文章除特別声明外,均采用 CC BY 4.0 许可协议。转载请注明来源 Escape !
  目录