【数据库】SQL高级处理:窗口函数，GROUPING

最新推荐文章于 2022-04-24 00:07:06 发布
最新推荐文章于 2022-04-24 00:07:06 发布
阅读量199 收藏 1
点赞数
文章标签： sql mysql 数据库 postgres oracle
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/marcotsui/article/details/115897833
版权

8.1 窗口函数
8.1 根据不同的种类，销售单价由高到低排列RANK

postgres=# SELECT product_name,product_type,sale_price,
postgres-# RANK () OVER (PARTITION BY product_type
postgres(# ORDER BY sale_price) AS ranking
postgres-# FROM Product;
 product_name | product_type | sale_price | ranking
--------------+--------------+------------+---------
 圆珠笔       | 办公用品     |        100 |       1
 打孔器       | 办公用品     |        500 |       2
 叉子         | 厨房用具     |        500 |       1
 擦菜板       | 厨房用具     |        880 |       2
 菜刀         | 厨房用具     |       3000 |       3
 高压锅       | 厨房用具     |       6800 |       4
 T恤          | 衣服         |       1000 |       1
 运动T恤      | 衣服         |       4000 |       2
(8 行记录)

8.3 使用RANK,DENSE_RANK或ROW_NUMBER排序的区别

postgres=# SELECT product_name,product_type,sale_price,
postgres-# RANK () OVER (ORDER BY sale_price) AS ranking,
postgres-# DENSE_RANK () OVER (ORDER BY sale_price) AS dense_ranking,
postgres-# ROW_NUMBER () OVER (ORDER BY sale_price) AS row_ranking
postgres-# FROM Product;
 product_name | product_type | sale_price | ranking | dense_ranking | row_ranking
--------------+--------------+------------+---------+---------------+-------------
 圆珠笔       | 办公用品     |        100 |       1 |             1 |           1
 叉子         | 厨房用具     |        500 |       2 |             2 |           2
 打孔器       | 办公用品     |        500 |       2 |             2 |           3
 擦菜板       | 厨房用具     |        880 |       4 |             3 |           4
 T恤          | 衣服         |       1000 |       5 |             4 |           5
 菜刀         | 厨房用具     |       3000 |       6 |             5 |           6
 运动T恤      | 衣服         |       4000 |       7 |             6 |           7
 高压锅       | 厨房用具     |       6800 |       8 |             7 |           8
(8 行记录)

注：窗口函数的使用位置：SELECT子句当中
8.4 SUM作为窗口函数

postgres=# SELECT product_id,product_name,sale_price,
postgres-# SUM (sale_price) OVER (ORDER BY product_id) AS current_sum
postgres-# FROM Product;
 product_id | product_name | sale_price | current_sum
------------+--------------+------------+-------------
 0001       | T恤          |       1000 |        1000
 0002       | 打孔器       |        500 |        1500
 0003       | 运动T恤      |       4000 |        5500
 0004       | 菜刀         |       3000 |        8500
 0005       | 高压锅       |       6800 |       15300
 0006       | 叉子         |        500 |       15800
 0007       | 擦菜板       |        880 |       16680
 0008       | 圆珠笔       |        100 |       16780
(8 行记录)

8.5 AVG 函数作为窗口函数

postgres=# SELECT product_id,product_name,sale_price,
postgres-# AVG(sale_price) OVER (ORDER BY product_id) AS avg_price
postgres-# FROM Product;
 product_id | product_name | sale_price |       avg_price
------------+--------------+------------+-----------------------
 0001       | T恤          |       1000 | 1000.0000000000000000
 0002       | 打孔器       |        500 |  750.0000000000000000
 0003       | 运动T恤      |       4000 | 1833.3333333333333333
 0004       | 菜刀         |       3000 | 2125.0000000000000000
 0005       | 高压锅       |       6800 | 3060.0000000000000000
 0006       | 叉子         |        500 | 2633.3333333333333333
 0007       | 擦菜板       |        880 | 2382.8571428571428571
 0008       | 圆珠笔       |        100 | 2097.5000000000000000
(8 行记录)

8.6 指定最靠近3行作为汇总对象

postgres=# SELECT product_id,product_name,sale_price,
postgres-# AVG(sale_price) OVER (ORDER BY product_id
postgres(# ROWS 2 PRECEDING) AS avg_p2
postgres-# FROM Product;
 product_id | product_name | sale_price |        avg_p2
------------+--------------+------------+-----------------------
 0001       | T恤          |       1000 | 1000.0000000000000000
 0002       | 打孔器       |        500 |  750.0000000000000000
 0003       | 运动T恤      |       4000 | 1833.3333333333333333
 0004       | 菜刀         |       3000 | 2500.0000000000000000
 0005       | 高压锅       |       6800 | 4600.0000000000000000
 0006       | 叉子         |        500 | 3433.3333333333333333
 0007       | 擦菜板       |        880 | 2726.6666666666666667
 0008       | 圆珠笔       |        100 |  493.3333333333333333
(8 行记录)

8.7 将当前记录的前后作为汇总对象

postgres=# SELECT product_id,product_name,sale_price,
postgres-# AVG(sale_price) OVER (ORDER BY product_id
postgres(# ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING ) AS avg_3
postgres-# FROM Product;
 product_id | product_name | sale_price |         avg_3
------------+--------------+------------+-----------------------
 0001       | T恤          |       1000 |  750.0000000000000000
 0002       | 打孔器       |        500 | 1833.3333333333333333
 0003       | 运动T恤      |       4000 | 2500.0000000000000000
 0004       | 菜刀         |       3000 | 4600.0000000000000000
 0005       | 高压锅       |       6800 | 3433.3333333333333333
 0006       | 叉子         |        500 | 2726.6666666666666667
 0007       | 擦菜板       |        880 |  493.3333333333333333
 0008       | 圆珠笔       |        100 |  490.0000000000000000
(8 行记录)

8.8 想要结果是按照ranking的顺序，还得使用ORDER BY在语句末尾

postgres=# SELECT product_id,product_name,sale_price,
postgres-# RANK () OVER (ORDER BY sale_price) AS ranking
postgres-# FROM Product
postgres-# ORDER BY ranking;
 product_id | product_name | sale_price | ranking
------------+--------------+------------+---------
 0008       | 圆珠笔       |        100 |       1
 0006       | 叉子         |        500 |       2
 0002       | 打孔器       |        500 |       2
 0007       | 擦菜板       |        880 |       4
 0001       | T恤          |       1000 |       5
 0004       | 菜刀         |       3000 |       6
 0003       | 运动T恤      |       4000 |       7
 0005       | 高压锅       |       6800 |       8
(8 行记录)

8.11 UNION ALL连接汇总行和小结

postgres=# SELECT '合计' AS product_type,SUM(sale_price)
postgres-# FROM Product
postgres-# UNION ALL
postgres-# SELECT product_type,SUM(sale_price)
postgres-# FROM Product
postgres-# GROUP BY product_type;
 product_type |  sum
--------------+-------
 合计         | 16780
 衣服         |  5000
 办公用品     |   600
 厨房用具     | 11180
(4 行记录)

GROUPING :ROLLUP,CUBE,GROUPING SETS
8.12 使用ROLLUP同时得出合计和小计

postgres=# SELECT product_type,SUM(sale_price) AS sum_price
postgres-# FROM product
postgres-# GROUP BY ROLLUP (product_type);
 product_type | sum_price
--------------+-----------
 办公用品     |       600
 厨房用具     |     11180
 衣服         |      5000
              |     16780
(4 行记录)

8.14 在GROUP中添加登记日期

postgres=# SELECT product_type, regist_date, SUM(sale_price) AS sum_price
postgres-#   FROM Product
postgres-#  GROUP BY ROLLUP(product_type,regist_date);
 product_type | regist_date | sum_price
--------------+-------------+-----------
 办公用品     | 2009-09-11  |       500
 办公用品     | 2009-11-11  |       100
 办公用品     |             |       600
 厨房用具     | 2008-04-28  |       880
 厨房用具     | 2009-01-15  |      6800
 厨房用具     | 2009-09-20  |      3500
 厨房用具     |             |     11180
 衣服         | 2009-09-20  |      1000
 衣服         |             |      4000
 衣服         |             |      5000
              |             |     16780
(11 行记录)

8.15 用GROUPING 判断是否为NULL

postgres=# SELECT GROUPING(product_type) AS product_type,
postgres-# GROUPING(regist_date) AS regist_date,SUM(sale_price) AS sum_price
postgres-# FROM Product
postgres-# GROUP BY ROLLUP(product_type,regist_date);
 product_type | regist_date | sum_price
--------------+-------------+-----------
            0 |           0 |       500
            0 |           0 |       100
            0 |           1 |       600
            0 |           0 |       880
            0 |           0 |      6800
            0 |           0 |      3500
            0 |           1 |     11180
            0 |           0 |      1000
            0 |           0 |      4000
            0 |           1 |      5000
            1 |           1 |     16780
(11 行记录)

8.16 在超级分组记录中插入需要的字符串

postgres=# SELECT CASE WHEN GROUPING(product_type) = 1
postgres-#             THEN '商品种类 合计'
postgres-#             ELSE product_type END AS product_type,
postgres-#        CASE WHEN GROUPING(regist_date) = 1
postgres-# THEN '登记日期 合计'
postgres-#  ELSE CAST(regist_date AS VARCHAR(16)) END AS regist_date,
postgres-#        SUM(sale_price) AS sum_price
postgres-#   FROM Product
postgres-#  GROUP BY ROLLUP(product_type, regist_date);
 product_type  |  regist_date  | sum_price
---------------+---------------+-----------
 办公用品      | 2009-09-11    |       500
 办公用品      | 2009-11-11    |       100
 办公用品      | 登记日期 合计 |       600
 厨房用具      | 2008-04-28    |       880
 厨房用具      | 2009-01-15    |      6800
 厨房用具      | 2009-09-20    |      3500
 厨房用具      | 登记日期 合计 |     11180
 衣服          | 2009-09-20    |      1000
 衣服          |               |      4000
 衣服          | 登记日期 合计 |      5000
 商品种类 合计 | 登记日期 合计 |     16780

8.17 使用CUBE代替ROLLUP取得全部组合的结果

postgres=# SELECT CASE WHEN GROUPING(product_type) = 1
postgres-#             THEN '商品种类 合计'
postgres-#             ELSE product_type END AS product_type,
postgres-#        CASE WHEN GROUPING(regist_date) = 1
postgres-#             THEN '登记日期 合计'
postgres-#             ELSE CAST(regist_date AS VARCHAR(16)) END AS regist_date,
postgres-#        SUM(sale_price) AS sum_price
postgres-#   FROM Product
postgres-# GROUP BY CUBE(product_type,regist_date);
 product_type  |  regist_date  | sum_price
---------------+---------------+-----------
 办公用品      | 2009-09-11    |       500
 办公用品      | 2009-11-11    |       100
 办公用品      | 登记日期 合计 |       600
 厨房用具      | 2008-04-28    |       880
 厨房用具      | 2009-01-15    |      6800
 厨房用具      | 2009-09-20    |      3500
 厨房用具      | 登记日期 合计 |     11180
 衣服          | 2009-09-20    |      1000
 衣服          |               |      4000
 衣服          | 登记日期 合计 |      5000
 商品种类 合计 | 登记日期 合计 |     16780
 商品种类 合计 | 2008-04-28    |       880
 商品种类 合计 | 2009-01-15    |      6800
 商品种类 合计 | 2009-09-11    |       500
 商品种类 合计 | 2009-09-20    |      4500
 商品种类 合计 | 2009-11-11    |       100
 商品种类 合计 |               |      4000
(17 行记录)

8.18 使用GROUPING SETS取得部分组合的结果

postgres=# SELECT CASE WHEN GROUPING(product_type) = 1
postgres-#             THEN '商品种类 合计'
postgres-#             ELSE product_type END AS product_type,
postgres-#        CASE WHEN GROUPING(regist_date) = 1
postgres-#             THEN '登记日期 合计'
postgres-#             ELSE CAST(regist_date AS VARCHAR(16)) END AS regist_date,
postgres-#        SUM(sale_price) AS sum_tanka
postgres-#   FROM Product
postgres-#  GROUP BY GROUPING SETS (product_type, regist_date);
 product_type  |  regist_date  | sum_tanka
---------------+---------------+-----------
 办公用品      | 登记日期 合计 |       600
 厨房用具      | 登记日期 合计 |     11180
 衣服          | 登记日期 合计 |      5000
 商品种类 合计 | 2008-04-28    |       880
 商品种类 合计 | 2009-01-15    |      6800
 商品种类 合计 | 2009-09-11    |       500
 商品种类 合计 | 2009-09-20    |      4500
 商品种类 合计 | 2009-11-11    |       100
 商品种类 合计 |               |      4000
(9 行记录)

练习:
在这里插入图片描述

postgres=# SELECT regist_date,product_name,sale_price,
postgres-# SUM(sale_price) OVER(ORDER BY regist_date NULLS FIRST) AS sum_sale_price
postgres-# FROM Product;
 regist_date | product_name | sale_price | sum_sale_price
-------------+--------------+------------+----------------
             | 运动T恤      |       4000 |           4000
 2008-04-28  | 擦菜板       |        880 |           4880
 2009-01-15  | 高压锅       |       6800 |          11680
 2009-09-11  | 打孔器       |        500 |          12180
 2009-09-20  | T恤          |       1000 |          16680
 2009-09-20  | 菜刀         |       3000 |          16680
 2009-09-20  | 叉子         |        500 |          16680
 2009-11-11  | 圆珠笔       |        100 |          16780
(8 行记录)
码布里
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
SQL学习笔记——grouping运算符
Hu_Pengxue
07-23 3207
我最近看了MICK的两本SQL教程，分别是《SQL基础教程》与《SQL进阶教程》，教材内容深入浅出，非常值得学习。   仅Oracle、SQL Server、DB2支持grouping   grouping运算符有三类： rollup、cube、grouping sets 使用方式/书写格式为 group by rollup/cube/grouping sets   三者的区别在于...
SQL—解决多维度随机组合查询场景：grouping sets函数
weixin_46010244的博客
08-02 1377
通过grouping sets函数，极大提高我们的开发效率，即使有更多维度组合的增加，只需要在grouping sets函数中新增即可，当然案例中姓名不具有实质性分组聚合意义，数据的展示问题需要做进一步处理，但相较于大量的union，grouping sets函数已经可以极大的对代码进行调优。
Oracle PL/SQL之GROUPING 函数
t0nsha's Blog
06-11 2097
在使用ROLLUP或CUBE进行GROUP操作时会产生内容为空的字段，即值为NULL的字段，那我们如何区分这些显示为空的字段到底是该字段本身值就是NULL还是由ROLLUP和CUBE生成的呢？这时可以用GROUPING(groupingfield) 来解决，如果列中的值来自事实数据，则 GROUPING 函数返回 0；如果列中的值是ROLLUP或CUBE操作所生成的 NULL，则返回 1。 eg：
SQL grouping
前端精读周刊
04-06 2845
SQL grouping 解决 OLAP 场景总计与小计问题，其语法分为几类，但要解决的是同一个问题：ROLLUP 与 CUBE 是封装了规则的 GROUPING SETS，而 GROUPING SETS 则是最原始的规则。为了方便理解，让我们从一个问题入手，层层递进吧。底表以上是示例底表，共有 8 条数据，城市1、城市2 两个城市，下面各有地区1～4，每条数据都有该数据...
sql:grouping运算符
qq_41805514的博客
08-17 1万+
只使用group by子句和聚合函数是无法同时得出小计和合计的，想要同时得到，可以使用grouping运算符。 同时得到合计行 使用group by 只能得到每组的合计值，不能得到总计： select product_type, sum(sale_price) from Product group by product_type; 通常计算合计是分别计算出合计行和按照商品种类进行汇总...
postgreSQL窗口函数总结
01-20
PostgreSQL的窗口函数是SQL查询中的强大工具，它们允许在一组相关的行（即“窗口”）上执行计算，而不仅仅是单行或者整个表。窗口函数在处理分组数据时尤其有用，能提供比普通聚合函数更复杂的分析能力。 1. **窗口...
Oracle 10g中的高级SQL函数
12-08
在Oracle 10g数据库系统中，高级SQL函数是开发者不可或缺的工具，它们极大地扩展了标准SQL的功能，使得数据查询和处理更为灵活高效。本文将深入探讨这些高级SQL函数，帮助你更好地理解和运用它们。 一、窗口函数 ...
SQL Server SQL高级查询语句小结
09-11
SQL Server的高级查询语句是数据库管理中的核心部分，它们帮助我们从大量数据中提取有用信息，进行复杂的分析和操作。以下是对标题和描述中提到的一些关键知识点的详细解释： 1. **基本查询**： - `SELECT`：用于...
oracle sql高级编程
11-27
这个主题包括了性能优化、子查询、联接、窗口函数、递归查询、存储过程以及高级聚合等功能。让我们深入探讨一下这些核心概念。 1. **性能优化**：在处理大量数据时，优化SQL查询至关重要。Oracle提供了多种工具和...
SQLServer中汇总功能的使用GROUPING,ROLLUP和CUBE
09-11
查看SQL Server的帮助才发现,厉害啊,原来还有这么厉害的东西,不由的想起以前做水晶报表的时候,原来在SQL Server中就可以实现这样的功能.
sql 中的grouping函数
weixin_30364147的博客
12-08 179
代码declare@Temptable(Codevarchar(10),IDint,SIntint)insertinto@Temp(Code,ID,SInt)select'01',1,3unionselect'01',2,3unionselect'01',2,3unionselect'02',1,3unionselect'02',3,5unionselect'02...
sql 学习之 group by 及 聚合函数
weixin_30628801的博客
06-17 244
1.在使用 GROUP BY 子句时，Select列表中的所有列必须是聚合列（SUM,MIN/MAX,AVG等）或是GROUP BY 子句中包括的列。同样，如果在SELECT 列表中使用聚合列，SELECT列表必须只包括聚合列，否则必须有一个GROUP BY 子句。例如：　　 select customerid,salespersonid,count(*) from sales....
Grouping函数
laodajiushiniu的专栏
05-12 1005
<br />Grouping函数:<br />可以接受一列，返回0或1。如果列值为空，则返回1，非空则返回0。它只能在rollup和或cube函数中使用，因为在统计中显示“全部”的那一项统计值时，那一项的标签通常是空的，这时grouping就非常有用，还可以在grouping的基础上进行decode, case等进行美化。<br />SQL> select grouping(index_type) g_ind, grouping(status) g_st, index_type, status, count
选择排序
weixin_42981168的博客
02-18 301
算法描述：对于给定的一组记录，经过第一轮比较后得到最小的记录，然后将该记录与第一个记录的位置进行交换；接着对不包括第一个记录以外的其他记录进行第二轮比较，得到最小的记录并与第二个记录进行位置交换；重复该过程，直到进行比较的记录只有一个时为止。 public class SortSelect { public static void main(String[] args){ //定义一个数组 int...
SQL Sever 学习笔记十一——窗口函数、GROUPING运算符
weixin_45666566的博客
05-28 897
SQL 高级处理窗口函数GROUPING运算符 窗口函数 窗口函数可以进行排序，生成序列号等一般的聚合函数无法实现的高级操作。 窗口函数也称为OLAP函数，意思是对数据库数据进行实时分析处理。窗口函数就是为了实现OLAP而添加的标准SQL功能。 1、窗口函数语法：其中[]中的内容可以省略 <窗口函数> over ([partition by <列清单>] order by <排序用列清单>) PARTITION BY子句在
postgreSQL 窗口函数
hyoka的博客
08-23 490
0.明明前不久还在杵前端的… pgsql 12 官方中文文档 1.为什么需要窗口函数 group by的分组查询方式对于复杂结果集的处理来说，粒度还是不够细，要是可以结果集中不同的组进行局部的操作就好了，对吧？ 2.文档中出现的一些概念 说是概念，其实也就是对于窗口函数使用过程中出现的不同类型的情况的一些划分 逻辑层次 一整个的结果集(WHERE、GROUP BY、HAVING过滤后的) 分区 窗口帧 当前行 平级行 2.1 分区(partition by) 窗口函数不会使多行被聚集成一
Hive/Presto中函数grouping sets用法详解（踩坑总结，看到赚到）
热门推荐
LdyLly的博客
04-24 2万+
本文详细记录了函数grouping sets使用时遇到的坑，基于Hive和Presto实现。
grouping sets的用法
qq_41081716的博客
02-14 1万+
HIve-Grouping sets用法 grouping sets不需要把所有的列名进行组合，把需要的进行组合就可以了；(dt),(dt, lead_pro_name),(dt, lead_city_name)，这里加了三种情况，分别表示的是每天全国维度，每天省份维度，每天城市维度，如果再加(dt, lead_pro_name，lead_city_name)，这个就是和(dt, lead_city_name)一样的情况了，会造成重复，所以写的时候先考虑清楚，是需要将哪几种维度的放在一起。 selec
SQLsever窗口函数
最新发布
08-18
SQL Server窗口函数是一种用于进行分组或分区计算的函数。它可以与聚合函数或排序函数结合使用。窗口函数在SQL Server中被用于实现OLAP（在线分析处理）功能，并且可以对数据库中的数据进行实时分析处理。窗口函数的分类包括能够作为窗口函数的聚合函数（如SUM、AVG、COUNT、MAX、MIN）以及专用窗口函数（如RANK、DENSE_RANK、ROW_NUMBER）。这些函数可以根据指定的分组或分区对数据进行计算，并返回结果。因此，SQL Server窗口函数在数据分析和报表制作中起着重要的作用。<span class="em">1</span><span class="em">2</span><span class="em">3</span> #### 引用[.reference_title] - *1* *2* [SQL Sever 学习笔记十一——窗口函数、GROUPING运算符](https://blog.csdn.net/weixin_45666566/article/details/106407762)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v93^chatsearchT3_2"}}] [.reference_item style="max-width: 50%"] - *3* [SQL SERVER中的开窗函数](https://blog.csdn.net/sinat_25172701/article/details/48084511)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v93^chatsearchT3_2"}}] [.reference_item style="max-width: 50%"] [ .reference_list ]

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值