sum() over(...)...
1:
WITH t AS(
SELECT DATE'2010-01-03' 时间, 1 新增数量 FROM dual UNION ALL
SELECT DATE'2010-01-04' 时间, 3 新增数量 FROM dual UNION ALL
SELECT DATE'2010-01-05' 时间, 5 新增数量 FROM dual UNION ALL
SELECT DATE'2010-01-06' 时间, 7 新增数量 FROM dual UNION ALL
SELECT DATE'2010-01-07' 时间, 8 新增数量 FROM dual
)
SELECT * FROM t;
WITH t AS(
SELECT DATE'2010-01-03' 时间, 1 新增数量 FROM dual UNION ALL
SELECT DATE'2010-01-04' 时间, 3 新增数量 FROM dual UNION ALL
SELECT DATE'2010-01-05' 时间, 5 新增数量 FROM dual UNION ALL
SELECT DATE'2010-01-06' 时间, 7 新增数量 FROM dual UNION ALL
SELECT DATE'2010-01-07' 时间, 8 新增数量 FROM dual
)
SELECT t.时间,SUM(新增数量) over(ORDER BY 时间) 累计和 FROM t
WHERE t.时间 BETWEEN DATE'2010-01-03' AND DATE'2010-02-03';
2:
WITH t as(
SELECT 200405 BILL_MONTH, '5761' AREA_CODE, 'G' NET_TYPE, 7393344.04 LOCAL_FARE FROM DUAL UNION ALL
SELECT 200405 BILL_MONTH, '5761' AREA_CODE, 'J' NET_TYPE, 5667089.85 LOCAL_FARE FROM DUAL UNION ALL
SELECT 200405 BILL_MONTH, '5762' AREA_CODE, 'G' NET_TYPE, 6315075.96 LOCAL_FARE FROM DUAL UNION ALL
SELECT 200405 BILL_MONTH, '5762' AREA_CODE, 'J' NET_TYPE, 6328716.15 LOCAL_FARE FROM DUAL UNION ALL
SELECT 200405 BILL_MONTH, '5763' AREA_CODE, 'G' NET_TYPE, 8861742.59 LOCAL_FARE FROM DUAL UNION ALL
SELECT 200405 BILL_MONTH, '5763' AREA_CODE, 'J' NET_TYPE, 7788036.32 LOCAL_FARE FROM DUAL UNION ALL
SELECT 200405 BILL_MONTH, '5764' AREA_CODE, 'G' NET_TYPE, 6028670.45 LOCAL_FARE FROM DUAL UNION ALL
SELECT 200405 BILL_MONTH, '5764' AREA_CODE, 'J' NET_TYPE, 6459121.49 LOCAL_FARE FROM DUAL UNION ALL
SELECT 200405 BILL_MONTH, '5765' AREA_CODE, 'G' NET_TYPE, 13156065.77 LOCAL_FARE FROM DUAL UNION ALL
SELECT 200405 BILL_MONTH, '5765' AREA_CODE, 'J' NET_TYPE, 11901671.70 LOCAL_FARE FROM DUAL UNION ALL
SELECT 200406 BILL_MONTH, '5761' AREA_CODE, 'G' NET_TYPE, 7614587.96 LOCAL_FARE FROM DUAL UNION ALL
SELECT 200406 BILL_MONTH, '5761' AREA_CODE, 'J' NET_TYPE, 5704343.05 LOCAL_FARE FROM DUAL UNION ALL
SELECT 200406 BILL_MONTH, '5762' AREA_CODE, 'G' NET_TYPE, 6556992.60 LOCAL_FARE FROM DUAL UNION ALL
SELECT 200406 BILL_MONTH, '5762' AREA_CODE, 'J' NET_TYPE, 6238068.05 LOCAL_FARE FROM DUAL UNION ALL
SELECT 200406 BILL_MONTH, '5763' AREA_CODE, 'G' NET_TYPE, 9130055.46 LOCAL_FARE FROM DUAL UNION ALL
SELECT 200406 BILL_MONTH, '5763' AREA_CODE, 'J' NET_TYPE, 7990460.25 LOCAL_FARE FROM DUAL UNION ALL
SELECT 200406 BILL_MONTH, '5764' AREA_CODE, 'G' NET_TYPE, 6387706.01 LOCAL_FARE FROM DUAL UNION ALL
SELECT 200406 BILL_MONTH, '5764' AREA_CODE, 'J' NET_TYPE, 6907481.66 LOCAL_FARE FROM DUAL UNION ALL
SELECT 200406 BILL_MONTH, '5765' AREA_CODE, 'G' NET_TYPE, 13562968.81 LOCAL_FARE FROM DUAL UNION ALL
SELECT 200406 BILL_MONTH, '5765' AREA_CODE, 'J' NET_TYPE, 12495492.50 LOCAL_FARE FROM DUAL UNION ALL
SELECT 200407 BILL_MONTH, '5761' AREA_CODE, 'G' NET_TYPE, 7987050.65 LOCAL_FARE FROM DUAL UNION ALL
SELECT 200407 BILL_MONTH, '5761' AREA_CODE, 'J' NET_TYPE, 5723215.28 LOCAL_FARE FROM DUAL UNION ALL
SELECT 200407 BILL_MONTH, '5762' AREA_CODE, 'G' NET_TYPE, 6833096.68 LOCAL_FARE FROM DUAL UNION ALL
SELECT 200407 BILL_MONTH, '5762' AREA_CODE, 'J' NET_TYPE, 6391201.44 LOCAL_FARE FROM DUAL UNION ALL
SELECT 200407 BILL_MONTH, '5763' AREA_CODE, 'G' NET_TYPE, 9410815.91 LOCAL_FARE FROM DUAL UNION ALL
SELECT 200407 BILL_MONTH, '5763' AREA_CODE, 'J' NET_TYPE, 8076677.41 LOCAL_FARE FROM DUAL UNION ALL
SELECT 200407 BILL_MONTH, '5764' AREA_CODE, 'G' NET_TYPE, 6456433.23 LOCAL_FARE FROM DUAL UNION ALL
SELECT 200407 BILL_MONTH, '5764' AREA_CODE, 'J' NET_TYPE, 6987660.53 LOCAL_FARE FROM DUAL UNION ALL
SELECT 200407 BILL_MONTH, '5765' AREA_CODE, 'G' NET_TYPE, 14000101.20 LOCAL_FARE FROM DUAL UNION ALL
SELECT 200407 BILL_MONTH, '5765' AREA_CODE, 'J' NET_TYPE, 12301780.20 LOCAL_FARE FROM DUAL UNION ALL
SELECT 200408 BILL_MONTH, '5761' AREA_CODE, 'G' NET_TYPE, 8085170.84 LOCAL_FARE FROM DUAL UNION ALL
SELECT 200408 BILL_MONTH, '5761' AREA_CODE, 'J' NET_TYPE, 6050611.37 LOCAL_FARE FROM DUAL UNION ALL
SELECT 200408 BILL_MONTH, '5762' AREA_CODE, 'G' NET_TYPE, 6854584.22 LOCAL_FARE FROM DUAL UNION ALL
SELECT 200408 BILL_MONTH, '5762' AREA_CODE, 'J' NET_TYPE, 6521884.50 LOCAL_FARE FROM DUAL UNION ALL
SELECT 200408 BILL_MONTH, '5763' AREA_CODE, 'G' NET_TYPE, 9468707.65 LOCAL_FARE FROM DUAL UNION ALL
SELECT 200408 BILL_MONTH, '5763' AREA_CODE, 'J' NET_TYPE, 8460049.43 LOCAL_FARE FROM DUAL UNION ALL
SELECT 200408 BILL_MONTH, '5764' AREA_CODE, 'G' NET_TYPE, 6587559.23 LOCAL_FARE FROM DUAL UNION ALL
SELECT 200408 BILL_MONTH, '5764' AREA_CODE, 'J' NET_TYPE, 7342135.86 LOCAL_FARE FROM DUAL UNION ALL
SELECT 200408 BILL_MONTH, '5765' AREA_CODE, 'G' NET_TYPE, 14450586.63 LOCAL_FARE FROM DUAL UNION ALL
SELECT 200408 BILL_MONTH, '5765' AREA_CODE, 'J' NET_TYPE, 12680052.38 LOCAL_FARE FROM DUAL
)
SELECT nvl(area_code,'合计') area_code ,SUM(local_fare) local_fare FROM t
GROUP BY rollup(nvl(area_code,'合计'));
SELECT area_code,SUM(local_fare) local_fare FROM t
GROUP BY area_code
UNION ALL
SELECT '合计' area_code,SUM(local_fare) local_fare FROM t;
WITH t AS (
SELECT 1 aa FROM dual UNION ALL
SELECT 2 aa FROM dual UNION ALL
SELECT 2 aa FROM dual UNION ALL
SELECT 2 aa FROM dual UNION ALL
SELECT 3 aa FROM dual UNION ALL
SELECT 4 aa FROM dual UNION ALL
SELECT 5 aa FROM dual UNION ALL
SELECT 6 aa FROM dual UNION ALL
SELECT 7 aa FROM dual UNION ALL
SELECT 9 aa FROM dual
)
SELECT aa,SUM(aa) over(ORDER BY aa) FROM t;
--SELECT aa,SUM(aa) over(ORDER BY aa RANGE BETWEEN 2 preceding AND 2 following) FROM t
分享到:
相关推荐
oracle分析函数(用法+实例),这属于oracle的高级应用。
详解Oracle分析函数,主用于OLAP,以实例讲解分析函数. 如: 排序用Rank, Dense_rank, row_number 1.带空值的排列 2.Top/Bottom N查询 3.First/Last排名查询 4.按层次查询 1.窗口函数简介 2.窗口函数示例-全统计 3....
常用oracle分析函数介绍 Oracle分析函数简介 Oracle分析函数简单实例 ...
oracle函数 安静哦爱偶的阿搜到吉安奥地利风景啊楼主擦
oracle 分析函数的教学实例
本文通过实例代码给大家介绍了oracle百分比分析函数RATIO_TO_REPORT() OVER(),代码简单易懂,非常不错,具有一定的参考借鉴价值,需要的朋友可以参考下
主要介绍了PHP实现的oracle分页函数,结合实例形式分析了PHP针对oracle数据库使用rownum代替MySQL中limit实现的分页操作相关技巧,需要的朋友可以参考下
5.结合autotrace创建并验证函数索引 6.sql trace分析工具--TKPROF详细讲解 7.V$SQL视图详解加几个实例 8.autotrace验证压缩表性能 9.autotrace验证消除子查询后的性能 10.基于基本的优化CBO 11.如何统计数据库数据 ...
Oracle 正则表达式实例详解 FORM开发中的按行拆分需求:拆分后的行要有规律,并按前后层次排序 需求分析如下: 现有行: 2 , 2.1 , 2.2 , 2.3 3 2.1.1, 2.1.2 , 2.1.3 , 2.2.1 , 2.1.1.1, ...
通过实例,全面而深入的分析oralce的基本数据类型及它们的存储方式。以ORACLE 10G为基础,介绍oralce 10g引入的新的数据类型。让你对oracle数据类型有一个全新的认识。揭示一些不为人知的秘密和被忽略的盲点。从实用...
第14章 Oracle中的函数与表达式(教学视频:111分钟) 240 第15章 Oracle中的控制语句(教学视频:16分钟) 282 第16章 SQL查询(教学视频:55分钟) 290 第17章 SQL更新数据(教学视频:34分钟) 319 第四篇 ...
ORACLE数据库的优化方式和MYSQL等很大的区别,今天通过一个ORACLE数据库实例从表格、数据等各个方便分析了如何进行ORACLE数据库的优化。 tsfree.sql视图 这个sql语句迅速的对每一个表空间中的空间总量与每一个表...
介绍了PL/SQL中常用的函数、异常处理等,还有对随机数生成、分析函数、多表合并、多表插入等问题的解决方法。第7章 子程序和触发器,包括函数、存储过程、包以及触发器等。对子程序的调用者权限、管道表函数、传递...
实例235 文件属性分析 304 实例236 文件类型检测 306 实例237 判断文件的权限 308 实例238 从文本文件中读取注册服务条款 309 实例239 可以屏蔽刷新功能的文本计数器 310 实例240 判断文件是否被修改 312 实例241 ...
主要介绍了oracle 存储过程、函数和触发器用法,结合实例形式详细分析了oralce 存储过程、函数和触发器具体功能、原理、定义、使用方法及相关操作注意事项,需要的朋友可以参考下
4.6 函数:字符串函数、数字函数和聚集函数(不是分组) 91 4.6.1 字符串函数 91 4.6.2 数字函数 92 4.6.3 聚集函数 92 4.7 日期函数(格式化的和按时间排列的) 93 4.7.1 日期函数 93 4.7.2 特殊格式的日期数据...
本文实例讲述了Oracle存储过程循环语法。分享给大家供大家参考,具体如下: 1、简单循环 语法 loop statements; end loop; 例子: counter := 0; loop counter := counter + 1; exit when counter = 5; end ...
介绍了PL/SQL中常用的函数、异常处理等,还有对随机数生成、分析函数、多表合并、多表插入等问题的解决方法。第7章 子程序和触发器,包括函数、存储过程、包以及触发器等。对子程序的调用者权限、管道表函数、传递...