oracle 按旬统计并且每月小计 行转列 PIVOT函数 与分组小计 ROLLUP 函数

article/2025/9/13 14:42:33

数据库版本:oralce  11g 

表名WM_TD_WATER_DAILY 结构如下,我把没用到的字段隐藏了,全放出来不太好哈.

MONITOR_ID为BI_TB_MONITOR_BI的ID ,用于关联站点名称(SHORT_NAME).

业务需求 :现有日水量表,每条数据记录着一天的用水量,bi_tb_monitor_bi中有一下九个站点

 全部SQL如下,下面有SQL分解,看起来能清晰很多

SELECT MON, CASEWHEN MON IS NULL AND DATETIME IS NULL THEN'累计水量'WHEN MON IS NOT NULL AND DATETIME IS NULL THEN'小计'ELSECASEWHEN INSTR(DATETIME, '1旬') > 0 THENREPLACE(DATETIME, '1旬', '上旬')WHEN INSTR(DATETIME, '2旬') > 0 THENREPLACE(DATETIME, '2旬', '中旬')WHEN INSTR(DATETIME, '3旬') > 0 THENREPLACE(DATETIME, '3旬', '下旬')ENDEND DATETIME,SUM(NVL(车逻洞, 0)) 车逻洞,SUM(NVL(车逻闸, 0)) 车逻闸,SUM(NVL(车逻, 0)) 车逻,SUM(NVL(南关, 0)) 南关,SUM(NVL(头闸, 0)) 头闸,SUM(NVL(周山洞, 0)) 周山洞,SUM(NVL(界首小闸, 0)) 界首小闸,SUM(NVL(子英闸, 0)) 子英闸,SUM(NVL(周山, 0)) 周山,SUM(NVL(车逻洞, 0)) + SUM(NVL(车逻闸, 0)) + SUM(NVL(南关, 0)) +SUM(NVL(头闸, 0)) + SUM(NVL(周山洞, 0)) + SUM(NVL(界首小闸, 0)) +SUM(NVL(子英闸, 0)) + SUM(NVL(周山, 0)) 合计FROM (SELECT TO_CHAR(MONITOR_DATE, 'yyyy') YEAR,TO_CHAR(MONITOR_DATE, 'yyyy') || TO_CHAR(MONITOR_DATE, 'mm') MON,TO_CHAR(MONITOR_DATE, 'mm') || '月' ||DECODE(TRUNC((TO_CHAR(MONITOR_DATE, 'dd') - 1) / 10), 0, '1旬' ，1, '2旬', '3旬') DATETIME,SUM(车逻洞) / 10000 车逻洞,SUM(车逻闸) / 10000 车逻闸,SUM(车逻) / 10000 车逻,SUM(南关) / 10000 南关,SUM(头闸) / 10000 头闸,SUM(周山洞) / 10000 周山洞,SUM(界首小闸) / 10000 界首小闸,SUM(子英闸) / 10000 子英闸,SUM(周山) / 10000 周山FROM (SELECT B.SHORT_NAME, T.MONITOR_DATE, T.WATER_DAILYFROM WM_TD_WATER_DAILY TLEFT JOIN BI_TB_MONITOR_BI BON T.MONITOR_ID = B.IDWHERE T.REGION_ID = '6-100-018'AND T.MONITOR_DATE BETWEEN TO_DATE('2018-10', 'yyyy-MM') ANDTO_DATE('2018-12', 'yyyy-MM'))PIVOT(SUM(WATER_DAILY)FOR SHORT_NAME IN('车逻洞' 车逻洞,'车逻闸' 车逻闸,'车逻' 车逻,'南关' 南关,'头闸' 头闸,'周山洞' 周山洞,'界首小闸' 界首小闸,'子英闸' 子英闸,'周山' 周山))GROUP BY TO_CHAR(MONITOR_DATE, 'yyyy'),TO_CHAR(MONITOR_DATE, 'mm'),DECODE(TRUNC((TO_CHAR(MONITOR_DATE, 'dd') - 1) / 10),0,'1旬' ，1,'2旬','3旬')ORDER BY MON)GROUP BY ROLLUP(MON, DATETIME)

SQL分解 

第一层SQL:

只做最基本的查询,并把条件都写好

 SELECT B.SHORT_NAME, T.MONITOR_DATE, T.WATER_DAILYFROM WM_TD_WATER_DAILY TLEFT JOIN BI_TB_MONITOR_BI BON T.MONITOR_ID = B.IDWHERE T.REGION_ID = '6-100-018'AND T.MONITOR_DATE BETWEEN TO_DATE('2018-10', 'yyyy-MM') ANDTO_DATE('2018-12', 'yyyy-MM')

查询结果如下(只插入了两个站点的数据<车逻洞>,<车逻闸>): 

 

第二层SQL:

用decode(trunc((to_char(monitor_date,'dd')-1)/10),0,'1旬'，1,'2旬','3旬') 把旬分组,

行转列用了 PIVOT (sum(water_daily) for short_name in ('车逻洞' 车逻洞, '车逻闸' 车逻闸,'车逻' 车逻,
 '南关' 南关,'头闸' 头闸,'周山洞' 周山洞,'界首小闸' 界首小闸,'子英闸' 子英闸,'周山' 周山))

每个sum后除以10000是业务需求

SELECT TO_CHAR(MONITOR_DATE, 'yyyy') YEAR,TO_CHAR(MONITOR_DATE, 'yyyy') || TO_CHAR(MONITOR_DATE, 'mm') MON,TO_CHAR(MONITOR_DATE, 'mm') || '月' ||DECODE(TRUNC((TO_CHAR(MONITOR_DATE,'dd') - 1) / 10), 0, '1旬' ，1, '2旬', '3旬')DATETIME,SUM(车逻洞) / 10000 车逻洞,SUM(车逻闸) / 10000 车逻闸,SUM(车逻) / 10000 车逻,SUM(南关) / 10000 南关,SUM(头闸) / 10000 头闸,SUM(周山洞) / 10000 周山洞,SUM(界首小闸) / 10000 界首小闸,SUM(子英闸) / 10000 子英闸,SUM(周山) / 10000 周山FROM (SELECT B.SHORT_NAME, T.MONITOR_DATE, T.WATER_DAILYFROM WM_TD_WATER_DAILY TLEFT JOIN BI_TB_MONITOR_BI BON T.MONITOR_ID = B.IDWHERE T.REGION_ID = '6-100-018'AND T.MONITOR_DATE BETWEEN TO_DATE('2018-10', 'yyyy-MM') ANDTO_DATE('2018-12', 'yyyy-MM'))PIVOT(SUM(WATER_DAILY)FOR SHORT_NAME IN('车逻洞' 车逻洞,'车逻闸' 车逻闸,'车逻' 车逻,'南关' 南关,'头闸' 头闸,'周山洞' 周山洞,'界首小闸' 界首小闸,'子英闸' 子英闸,'周山' 周山))GROUP BY TO_CHAR(MONITOR_DATE, 'yyyy'),TO_CHAR(MONITOR_DATE, 'mm'),DECODE(TRUNC((TO_CHAR(MONITOR_DATE, 'dd') - 1) / 10),0,'1旬' ，1,'2旬','3旬')ORDER BY MON

查询结果如下:

最后一层SQL:

用了 GROUP BY ROLLUP (MON,DATETIME)  按MON,DATETIME分组合计

SELECT CASEWHEN MON IS NULL AND DATETIME IS NULL THEN'累计水量'WHEN MON IS NOT NULL AND DATETIME IS NULL THEN'小计'ELSECASEWHEN INSTR(DATETIME, '1旬') > 0 THENREPLACE(DATETIME, '1旬', '上旬')WHEN INSTR(DATETIME, '2旬') > 0 THENREPLACE(DATETIME, '2旬', '中旬')WHEN INSTR(DATETIME, '3旬') > 0 THENREPLACE(DATETIME, '3旬', '下旬')ENDEND DATETIME,SUM(NVL(车逻洞, 0)) 车逻洞,SUM(NVL(车逻闸, 0)) 车逻闸,SUM(NVL(车逻, 0)) 车逻,SUM(NVL(南关, 0)) 南关,SUM(NVL(头闸, 0)) 头闸,SUM(NVL(周山洞, 0)) 周山洞,SUM(NVL(界首小闸, 0)) 界首小闸,SUM(NVL(子英闸, 0)) 子英闸,SUM(NVL(周山, 0)) 周山,SUM(NVL(车逻洞, 0)) + SUM(NVL(车逻闸, 0)) + SUM(NVL(南关, 0)) +SUM(NVL(头闸, 0)) + SUM(NVL(周山洞, 0)) + SUM(NVL(界首小闸, 0)) +SUM(NVL(子英闸, 0)) + SUM(NVL(周山, 0)) 合计FROM (SELECT TO_CHAR(MONITOR_DATE, 'yyyy') YEAR,TO_CHAR(MONITOR_DATE, 'yyyy') || TO_CHAR(MONITOR_DATE, 'mm') MON,TO_CHAR(MONITOR_DATE, 'mm') || '月' ||DECODE(TRUNC((TO_CHAR(MONITOR_DATE,'dd') - 1) / 10), 0, '1旬' ，1, '2旬', '3旬')DATETIME,SUM(车逻洞) / 10000 车逻洞,SUM(车逻闸) / 10000 车逻闸,SUM(车逻) / 10000 车逻,SUM(南关) / 10000 南关,SUM(头闸) / 10000 头闸,SUM(周山洞) / 10000 周山洞,SUM(界首小闸) / 10000 界首小闸,SUM(子英闸) / 10000 子英闸,SUM(周山) / 10000 周山FROM (SELECT B.SHORT_NAME, T.MONITOR_DATE, T.WATER_DAILYFROM WM_TD_WATER_DAILY TLEFT JOIN BI_TB_MONITOR_BI BON T.MONITOR_ID = B.IDWHERE T.REGION_ID = '6-100-018'AND T.MONITOR_DATE BETWEEN TO_DATE('2018-10', 'yyyy-MM') ANDTO_DATE('2018-12', 'yyyy-MM'))PIVOT(SUM(WATER_DAILY)FOR SHORT_NAME IN('车逻洞' 车逻洞,'车逻闸' 车逻闸,'车逻' 车逻,'南关' 南关,'头闸' 头闸,'周山洞' 周山洞,'界首小闸' 界首小闸,'子英闸' 子英闸,'周山' 周山))GROUP BY TO_CHAR(MONITOR_DATE, 'yyyy'),TO_CHAR(MONITOR_DATE, 'mm'),DECODE(TRUNC((TO_CHAR(MONITOR_DATE, 'dd') - 1) / 10),0,'1旬' ，1,'2旬','3旬')ORDER BY MON)GROUP BY ROLLUP(MON, DATETIME)

查询结果如下:

select后的不加case when的效果如下

mon用于第二层的排序 省略掉了

总结

这段SQL中用到了以下函数:

TO_DATE(),TO_CHAR(),BETWEEN,TRUNC(),DECODE(),NVL(),SUM(),PIVOT(  FOR  ),CASE  WHEN  THEN  ELSE  END,ROLLUP()

欢迎指正并优化SQL!


http://chatgpt.dhexx.cn/article/Pnq4pes0.shtml

相关文章

rollup分析函数

rollup分析函数

表的初始数据&#xff1a; 使用rollup进行汇总之后的数据&#xff1a; select t.first_name,sum(t.salary) from t_test1 tgroup by rollup(t.first_name) 可以改写为&#xff1a; select nvl(t.first_name,total),sum(t.salary) from t_test1 tgroup by rollup(t.first_name…
阅读更多...
达梦cube和rollup函数使用

达梦cube和rollup函数使用

在使用前需要安装达梦数据库&#xff0c;并创建示例库DMHR ● ROLLUP ● 比如获取公司各城市每个部门员工人数分布和薪资分布情况&#xff0c;使用group by rollup的sql样例参考如下&#xff1a; 从结果集中可以看出group by rollup聚合了城市和部门组合&#xff0c;城市组合…
阅读更多...
with rollup函数做合计以及行转列

with rollup函数做合计以及行转列

MySQL-with rollup函数运用 _20160930 在博客里http://www.cnblogs.com/Mr-Cxy/p/5898839.html提到了行转列&#xff0c;(http://blog.csdn.net/ybygjy/article/details/41557425)行转列 列转行都有 如果想在下面这个表下面添加一行 总计 数据行SQL代码怎么实现 并且根据9月金…
阅读更多...
mysql有rollup函数_MySQL 聚合函数（二）Group By的修饰符——ROLLUP

mysql有rollup函数_MySQL 聚合函数（二）Group By的修饰符——ROLLUP

一、ROLLUP 修饰符的意义 GROUP BY子句允许添加WITH ROLLUP修饰符，该修饰符可以对分组后各组的某个列的结果值进行汇总，并在结果中输出，即提供更高一级的聚合操作。 因此，ROLLUP使您能够使用单个查询在多个分析级别回答问题。例如，ROLLUP可用于为OLAP(在线分析处理)操作提…
阅读更多...
Mysql表数据如何增加汇总统计行（GROUP BY WITH ROLLUP函数用法）

Mysql表数据如何增加汇总统计行（GROUP BY WITH ROLLUP函数用法）

举例一&#xff1a; 下面是一张【商品的售出情况汇总表 tb_goods_sale】 &#xff0c;表字段解释如下&#xff1a; 目前表中有3条数据&#xff0c;如下图&#xff0c;我们利用GROUP BY WITH ROLLUP 进行统计汇总商品的【售出总数】和【营销额】 sql如下&#xff1a; SELECTa.…
阅读更多...
mysql中的cube和rollup_【hive】cube和rollup函数

mysql中的cube和rollup_【hive】cube和rollup函数

cube 数据立方体(Data Cube)，是多维模型的一个形象的说法.(关于多维模型这里不讲述,在数据仓库设计过程中还挺重要的,有兴趣自行查阅) 立方体其本身只有三维，但多维模型不仅限于三维模型，可以组合更多的维度 为什么叫数据立方体? 一方面是出于更方便地解释和描述，同时也是…
阅读更多...
ORACLE rollup函数

ORACLE rollup函数

rollup函数应用场景&#xff1a; 主要使用在 分组中&#xff0c;将每个分组求汇总值&#xff08;就是小计&#xff09;&#xff0c;最后再讲所有值&#xff08;除去小计&#xff09;求和&#xff08;就是合计&#xff09; 当然&#xff0c;使用union 也可以达到同样的效果。先将…
阅读更多...
mysql有rollup函数吗_MySQL-with rollup函数运用 _20160930

mysql有rollup函数吗_MySQL-with rollup函数运用 _20160930

标签&#xff1a; 如果想在下面这个表下面添加一行 总计 数据行SQL代码怎么实现 并且根据9月金额进行城市降序 总计置于底部呢 MySQL提供了 group by with rollup 函数进行group by 字段的汇总 但是order by 互斥的不能同时用 第一步还是是先计算各城市每个月的金额 SELECT b.城…
阅读更多...
mysql有rollup函数吗_MySQL-with rollup函数运用

mysql有rollup函数吗_MySQL-with rollup函数运用

如果想在下面这个表下面添加一行 总计 数据行SQL代码怎么实现 并且根据9月金额进行城市降序 总计置于底部呢 MySQL提供了 group by with rollup 函数进行group by 字段的汇总 但是order by 互斥的不能同时用 第一步还是是先计算各城市每个月的金额 SELECT b.城市,SUM(IF(b.年月…
阅读更多...
mysql有rollup函数,Mysql,Oracle使用rollup函数完成行列统计

mysql有rollup函数,Mysql,Oracle使用rollup函数完成行列统计

时间 2014-02-25 00:05:38 ITeye-博客 昨天突然在 一篇博客中看到了Mysql也有rollup函数&#xff0c;原博文使用了rollup进行行列统计&#xff0c;原博文链接如下&#xff1a; 本博文主要是记录下mysql和oracle使用rollup函数进行行列统计&#xff0c;内容比较简单。 首先是my…
阅读更多...
Oracle-rollup()函数[转载]

Oracle-rollup()函数[转载]

参考学习了&#xff1a; http://blog.itpub.net/519536/viewspace-610995 http://blog.csdn.net/huang_xw/article/details/6402396 rollup()是group by的一个扩展函数&#xff0c;初步的感觉是&#xff0c;是gropu的升级版&#xff0c;可以多个列进行group by&#xff0c;然后…
阅读更多...
数据库小计和总计之 Rollup函数 简单介绍

数据库小计和总计之 Rollup函数 简单介绍

以下内容仅是站长或网友个人学习笔记、总结和研究收藏。不保证正确性 来源于 易百教程 SQL ROLLUP简介ROLLUP是GROUP BY子句的扩展。 ROLLUP选项允许包含表示小计的额外行&#xff0c;通常称为超级聚合行&#xff0c;以及总计行。 通过使用ROLLUP选项&#xff0c;可以使用单个…
阅读更多...
Oracle中rollup函数详解

Oracle中rollup函数详解

【基本介绍】 【格式】&#xff1a;group by rollup(字段1,字段2,字段3,...,字段n) 【说明】&#xff1a;rollup主要用于分组汇总&#xff0c;如果rollup中有n个字段&#xff0c;则会分别按【字段1】、【字段1,字段2】&#xff0c;【字段1,字段2,字段3】&#xff0c;...&…
阅读更多...
超硬核，SQL注入之时间盲注，原理+步骤+实战思路

超硬核，SQL注入之时间盲注，原理+步骤+实战思路

「作者主页」&#xff1a;士别三日wyx 「作者简介」&#xff1a;CSDN top100、阿里云博客专家、华为云享专家、网络安全领域优质创作者 「专栏简介」&#xff1a;此文章已录入专栏《网络安全快速入门》 时间盲注 一、什么是时间盲注&#xff1f;二、使用场景三、使用步骤第一步…
阅读更多...
SQL盲注注入——布尔型

SQL盲注注入——布尔型

盲注是注入的一种&#xff0c;指的是在不知道数据库返回值的情况下对数据中的内容进行猜测&#xff0c;实施SQL注入。盲注一般分为布尔盲注和基于时间的盲注和报错的盲注。本次主要讲解的是基于布尔的盲注。 Length&#xff08;&#xff09;函数 返回字符串的长度 Substr&…
阅读更多...
【SQL注入漏洞-04】布尔盲注靶场实战

【SQL注入漏洞-04】布尔盲注靶场实战

布尔盲注 当我们改变前端页面传输给后台sql参数时&#xff0c;页面没有显示相应内容也没有显示报错信息时&#xff0c;不能使用联合查询注入和报错注入&#xff0c;这时我们可以考虑是否为基于布尔的盲注。 布尔盲注原理&#xff1a; 利用页面返回的布尔类型状态&#xff0c;正…
阅读更多...
SQL注入之盲注简单总结

SQL注入之盲注简单总结

Mysql盲注总结 什么是盲注&#xff1f; 盲注就是在sql注入过程中&#xff0c;sql语句执行的选择后&#xff0c;选择的数据不能回显到前端页面。此时&#xff0c;我们需要利用一些方法进行判断或者尝试&#xff0c;这个过程称之为盲注。SQL盲注与SQL普通注入的区别&#xff1f;…
阅读更多...
sql盲注

sql盲注

提示&#xff1a;文章写完后&#xff0c;目录可以自动生成&#xff0c;如何生成可参考右边的帮助文档 sql注入--盲注 前言一、什么是盲注二、步骤示例1.测试注入点2.测所在数据库长度3. 利用ASCII码猜当前数据库名 4.利用ASCII码查询表名5、利用ASCII码查询字段名sql手注所用到…
阅读更多...
Sql盲注与普通注入的区别

Sql盲注与普通注入的区别

Sql盲注与普通注入的区别 在学习sql注入的时候&#xff0c;好多同学都是不弄清楚原理&#xff0c;去浏览器上狂搜一下注入语句&#xff0c;就开始对老师给的靶机注入&#xff0c;虽然能注入成功&#xff0c;但是不清楚原理&#xff0c;对以后的学习和工作没有多大的好处。现在我…
阅读更多...
SQL 注入-盲注

SQL 注入-盲注

目录 一、什么时候用到盲注&#xff1f; 二、盲注的优缺点 三、盲注的流程 四、盲注的分类 五、布尔盲注 5.1、原理以及什么时候使用布尔盲注&#xff1f; 5.2、例如upload-labs靶场中的Less-8 5.3、布尔盲注常用函数及定义 5.4、length()函数的使用方法 5.5、substr…
阅读更多...
推荐文章

Copyright @ 2022~2023