- 浏览: 808968 次
- 性别:
- 来自: 北京
-
文章分类
- 全部博客 (413)
- 项目 (12)
- 统计分析 (3)
- java (7)
- jquery (15)
- oracle (33)
- 面试题 (8)
- 排序算法 (3)
- 蛇形矩阵算法 (3)
- 字符串 (1)
- ICU分析插件 (1)
- html5 (1)
- Ubuntu (4)
- Linux (4)
- memcache (2)
- myeclipse (3)
- hadoop (18)
- hbase (14)
- hive (7)
- zookeeper (2)
- pig (0)
- mysql (11)
- Redis (8)
- MongoDB (7)
- Cassandra (0)
- Neo4j (0)
- springMVC (3)
- ibatis (1)
- mahout (0)
- Highcharts (1)
- maven (7)
- 生活随笔 (6)
- 存储过程 (4)
- mybatis (3)
- bootstrap (19)
- 工作中遇到的问题 (23)
- node.js (3)
- web前段 (6)
- AngularJS (5)
- GIT (2)
- nginx (5)
- hive，sqoop (1)
- 高并发 (2)
- 算法 (3)
- 工具类 (2)
- ckeditor (1)
- java代码自动生成 (3)
- ueditor (6)
- svn (4)
- easyui (3)
- 数据挖掘 (4)
- ligerUi (1)
- fullcalendar (1)
- 微信公众号开发 (1)
- tomcat (2)
- 面试 (7)
- 数据库性能优化 (2)
- R语言 (3)
- R (6)
- ylcf (39)
- CentOS (13)
- docker (28)
- Spring Boot (10)
- Nexus (1)
- thymeleaf (2)
- nodejs (2)
- swagger (1)
- Jenkins (1)
- SpringCloud (9)
- vue (1)
- springClould问题汇总 (3)
- layui (1)
最新评论
-
greatwqs：
在前置请求页面返回token, 在提交接口中验证token, ...
Spring MVC 用拦截器+token防止重复提交 -
zhukewen_java：
这种方法的缺陷在于如果请求了两个save=true的方法，后面 ...
Spring MVC 用拦截器+token防止重复提交 -
byrgl5：
请问如果重复提交了三次呢？能避免吗？
Spring MVC 用拦截器+token防止重复提交 -
381895649：
是不是有病？
服务器被如下ip攻击，如何根据ip超找攻击来源 -
greatwqs：
最棒的10款MySQL管理工具
分析函数是oracle816引入的一个全新的概念,为我们分析数据提供了一种简单高效的处理方式.在分析函数出现以前,我们必须使用自联查询,子查询或者内联视图,甚至复杂的存储过程实现的语句,现在只要一条简单的sql语句就可以实现了,而且在执行效率方面也有相当大的提高.
下面主要介绍一下以下几个函数的使用方法
1. Over() 开窗函数
2. Nvl()函数
3. Rollup,Cube自动汇总函数
4. Rank,Dense_rank,Row_number函数
5. Lag , Lead函数
6. Sum,Avg, Count, Max函数
7. Ratio_to_report报表处理函数
8. First,Last，First_value，Last_value取基数的分析函数
9. Greatest, Least 函数
10. Trunc, Round,Decode, Substr函数
一． Over() 开窗函数
Over() 开窗函数是Oracle的分析函数，其语法如下：
函数名([ 参数 ]) over( [ 分区子句 ] [ 排序子句 [ 滑动窗口子句 ] ])
分区子句类似于聚组函数所需要的group by，排序子句可看成是SQL语句中的order by，只不过在此语句中还可指定null值排前（nulls first）还是排后（nulls last）。
开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化，举例如下：
over（order by salary） 按照salary排序进行累计，order by是个默认的开窗函数
over（partition by deptno）按照部门分区
over（order by salary range between 50 preceding and 150 following）
每行对应的数据窗口是之前行幅度值不超过50，之后行幅度值不超过150
over（order by salary rows between 50 preceding and 150 following）
每行对应的数据窗口是之前50行，之后150行
over（order by salary rows between unbounded preceding and unbounded following）
每行对应的数据窗口是从第一行到最后一行，等效：
over（order by salary range between unbounded preceding and unbounded following）
二．Nvl() 函数
NVL（EXP1,EXP2），函数返回exp1和exp2 中第一个不为空的值。
如果exp1为空则返回exp2，否则返回exp1。
注意：如果exp1不是字符串，那么返回的数据类型和exp1的数据类型相同，否则返回的数据类型为varchar2型。
SQL> select nvl('This is not null', 7) Frist, nvl(null, 'My Oracle') Second from dual;
FRIST SECOND
------------------ -------------
This is not null My Oracle
三． 自动汇总函数rollup,cube
Rollup:表示的意思是：除了分组的功能外，还进行累加的的，多了一个汇总。
如果是 GROUP BY ROLLUP(A, B, C) 的话， GROUP BY 顺序
(A 、 B 、 C)
(A 、 B)
(A)
最后对全表进行 GROUP BY 操作。
Cube提供了按照多字段汇总的功能。
如果是 GROUP BY CUBE(A, B, C) ， GROUP BY 顺序
(A 、 B 、 C)
(A 、 B)
(A 、 C)
(A) ，
(B 、 C)
(B)
(C) ，
最后对全表进行 GROUP BY 操作。
示例：
CREATE TABLE studentscore
(
student_name varchar2(20),
subjects varchar2(20),
score number
)
INSERT INTO studentscore VALUES('WBQ','ENGLISH',90);
INSERT INTO studentscore VALUES('WBQ','MATHS',95);
INSERT INTO studentscore VALUES('WBQ','CHINESE',88);
INSERT INTO studentscore VALUES('CZH','ENGLISH',80);
INSERT INTO studentscore VALUES('CZH','MATHS',90);
INSERT INTO studentscore VALUES('CZH','HISTORY',92);
INSERT INTO studentscore VALUES('CB','POLITICS',70);
INSERT INTO studentscore VALUES('CB','HISTORY',75);
INSERT INTO studentscore VALUES('LDH','POLITICS',80);
INSERT INTO studentscore VALUES('LDH','CHINESE',90);
INSERT INTO studentscore VALUES('LDH','HISTORY',95);
select * from studentscore;
/* Formatted on 2009/11/08 20:35 (Formatter Plus v4.8.8) */
SELECT student_name, subjects, SUM (score)
FROM studentscore
GROUP BY CUBE (student_name, subjects)
ORDER BY 1;
等同于以下标准 SQL
/* Formatted on 2009/11/08 20:35 (Formatter Plus v4.8.8) */
SELECT NULL, subjects, SUM (score)
FROM studentscore
GROUP BY subjects
UNION
SELECT student_name, NULL, SUM (score)
FROM studentscore
GROUP BY student_name
UNION
SELECT NULL, NULL, SUM (score)
FROM studentscore
UNION
SELECT student_name, subjects, SUM (score)
FROM studentscore
GROUP BY student_name, subjects
/* Formatted on 2009/11/08 20:35 (Formatter Plus v4.8.8) */
SELECT student_name, subjects, SUM (score)
FROM studentscore
GROUP BY ROLLUP (student_name, subjects);
/* Formatted on 2009/11/08 20:35 (Formatter Plus v4.8.8) */
SELECT student_name, NULL, SUM (score)
FROM studentscore
GROUP BY student_name
UNION
SELECT NULL, NULL, SUM (score)
FROM studentscore
UNION
SELECT student_name, subjects, SUM (score)
FROM studentscore
GROUP BY student_name, subjects
/* Formatted on 2009/11/08 20:35 (Formatter Plus v4.8.8) */
SELECT GROUPING (student_name), GROUPING (subjects), student_name, subjects,
SUM (score)
FROM studentscore
GROUP BY CUBE (student_name, subjects)
ORDER BY 1, 2;
/* Formatted on 2009/11/08 20:36 (Formatter Plus v4.8.8) */
SELECT GROUPING (student_name), GROUPING (subjects), student_name, subjects,
SUM (score)
FROM studentscore
GROUP BY ROLLUP (student_name, subjects)
ORDER BY 1, 2;
/* Formatted on 2009/11/08 20:36 (Formatter Plus v4.8.8) */
SELECT GROUPING_ID (student_name, subjects), student_name, subjects,
SUM (score)
FROM studentscore
GROUP BY CUBE (student_name, subjects)
ORDER BY 1;
/* Formatted on 2009/11/08 20:36 (Formatter Plus v4.8.8) */
SELECT GROUPING_ID (student_name, subjects), student_name, subjects,
SUM (score)
FROM studentscore
GROUP BY ROLLUP (student_name, subjects)
ORDER BY 1;
/* Formatted on 2009/11/08 20:36 (Formatter Plus v4.8.8) */
SELECT GROUPING (student_name), GROUPING (subjects),
CASE
WHEN GROUPING (student_name) = 0
AND GROUPING (subjects) = 1
THEN ' 学生成绩合计 '
WHEN GROUPING (student_name) = 1
AND GROUPING (subjects) = 0
THEN ' 课目成绩合计 '
WHEN GROUPING (student_name) = 1
AND GROUPING (subjects) = 1
THEN ' 总 计 '
ELSE ''
END summary,
student_name, subjects, SUM (score)
FROM studentscore
GROUP BY CUBE (student_name, subjects)
ORDER BY 1, 2;
四． rank, dense_rank,row_number函数
Rank，Dense_rank，Row_number函数为每条记录产生一个从1开始至N的自然数，N的值可能小于等于记录的总数。这3个函数的唯一区别在于当碰到相同数据时的排名策略。
① ROW_NUMBER：
Row_number函数返回一个唯一的值，当碰到相同数据时，排名按照记录集中记录的顺序依次递增。
② DENSE_RANK：
Dense_rank函数返回一个唯一的值，除非当碰到相同数据时，此时所有相同数据的排名都是一样的。
③ RANK：
Rank函数返回一个唯一的值，除非遇到相同的数据时，此时所有相同数据的排名是一样的，同时会在最后一条相同记录和下一条不同记录的排名之间空出排名。
示例：
/* Formatted on 2009/11/08 20:48 (Formatter Plus v4.8.8) */
SELECT ename, deptno, sal,
RANK () OVER (PARTITION BY deptno ORDER BY sal DESC) "RANK",
DENSE_RANK () OVER (PARTITION BY deptno ORDER BY sal DESC) "DENSE_RANK",
ROW_NUMBER () OVER (PARTITION BY deptno ORDER BY sal DESC) "ROW_NUMBER"
FROM scott.emp
|
ENAME |
DEPTNO |
SAL |
RANK |
DENSE_RANK |
ROW_NUMBER |
|
KING |
10 |
5,000 |
1 |
1 |
1 |
|
CLARK |
10 |
2,450 |
2 |
2 |
2 |
|
MILLER |
10 |
1,300 |
3 |
3 |
3 |
|
SCOTT |
20 |
3,000 |
1 |
1 |
1 |
|
FORD |
20 |
3,000 |
1 |
1 |
2 |
|
JONES |
20 |
2,975 |
3 |
2 |
3 |
|
ADAMS |
20 |
1,100 |
4 |
3 |
4 |
|
SMITH |
20 |
800 |
5 |
4 |
5 |
|
BLAKE |
30 |
2,850 |
1 |
1 |
1 |
|
ALLEN |
30 |
1,600 |
2 |
2 |
2 |
|
TURNER |
30 |
1,500 |
3 |
3 |
3 |
|
MARTIN |
30 |
1,250 |
4 |
4 |
4 |
|
WARD |
30 |
1,250 |
4 |
4 |
5 |
|
JAMES |
30 |
950 |
6 |
5 |
6 |
五. lag,lead函数
lag(expression,<offset>,<default>) 函数可以访问组内当前行之前的行，
lead(expression,<offset>,<default>) 函数则正相反，可以反问组内当前行之后的行．
其中，offset是正整数，默认为1．因组内第一个条记录没有之前的行，最后一行没有之后的行，它表示要取列第N行之前或者之后的值，default就是用于处理这样的信息，默认为空．它用于当之前或者之后第N行不存在时的值。
注意：这2个函数必须指定 order By 字句．
/* Formatted on 2009/11/08 21:09 (Formatter Plus v4.8.8) */
SELECT ename, deptno, sal, LAG (sal) OVER (ORDER BY sal) LAG,
LEAD (sal) OVER (ORDER BY sal) LEAD
FROM scott.emp;
|
ENAME |
DEPTNO |
SAL |
LAG |
LEAD |
|
SMITH |
20 |
800 |
|
950 |
|
JAMES |
30 |
950 |
800 |
1,100 |
|
ADAMS |
20 |
1,100 |
950 |
1,250 |
|
WARD |
30 |
1,250 |
1,100 |
1,250 |
|
MARTIN |
30 |
1,250 |
1,250 |
1,300 |
|
MILLER |
10 |
1,300 |
1,250 |
1,500 |
|
TURNER |
30 |
1,500 |
1,300 |
1,600 |
|
ALLEN |
30 |
1,600 |
1,500 |
2,450 |
|
CLARK |
10 |
2,450 |
1,600 |
2,850 |
|
BLAKE |
30 |
2,850 |
2,450 |
2,975 |
|
JONES |
20 |
2,975 |
2,850 |
3,000 |
|
SCOTT |
20 |
3,000 |
2,975 |
3,000 |
|
FORD |
20 |
3,000 |
3,000 |
5,000 |
|
KING |
10 |
5,000 |
3,000 |
|
/* Formatted on 2009/11/08 21:11 (Formatter Plus v4.8.8) */
SELECT ename, deptno, sal, LAG (sal, 2, 0) OVER (ORDER BY sal) LAG,
LEAD (sal, 2, sal) OVER (ORDER BY sal) LEAD
FROM scott.emp;
|
ENAME |
DEPTNO |
SAL |
LAG |
LEAD |
|
SMITH |
20 |
800 |
0 |
1,100 |
|
JAMES |
30 |
950 |
0 |
1,250 |
|
ADAMS |
20 |
1,100 |
800 |
1,250 |
|
WARD |
30 |
1,250 |
950 |
1,300 |
|
MARTIN |
30 |
1,250 |
1,100 |
1,500 |
|
MILLER |
10 |
1,300 |
1,250 |
1,600 |
|
TURNER |
30 |
1,500 |
1,250 |
2,450 |
|
ALLEN |
30 |
1,600 |
1,300 |
2,850 |
|
CLARK |
10 |
2,450 |
1,500 |
2,975 |
|
BLAKE |
30 |
2,850 |
1,600 |
3,000 |
|
JONES |
20 |
2,975 |
2,450 |
3,000 |
|
SCOTT |
20 |
3,000 |
2,850 |
5,000 |
|
FORD |
20 |
3,000 |
2,975 |
3,000 |
|
KING |
10 |
5,000 |
3,000 |
5,000 |
六. sum,avg, count，max 函数
6.1 SUM 函数
/* Formatted on 2009/11/08 21:25 (Formatter Plus v4.8.8) */
SELECT SUM (sal) "sum"
FROM scott.emp;
/* Formatted on 2009/11/08 21:27 (Formatter Plus v4.8.8) */
SELECT ename, deptno, sal,
SUM (sal) OVER (PARTITION BY deptno ORDER BY sal RANGE UNBOUNDED PRECEDING) "SUM"
FROM scott.emp;
|
ENAME |
DEPTNO |
SAL |
SUM |
|
MILLER |
10 |
1,300 |
1,300 |
|
CLARK |
10 |
2,450 |
3,750 |
|
KING |
10 |
5,000 |
8,750 |
|
SMITH |
20 |
800 |
800 |
|
ADAMS |
20 |
1,100 |
1,900 |
|
JONES |
20 |
2,975 |
4,875 |
|
SCOTT |
20 |
3,000 |
10,875 |
|
FORD |
20 |
3,000 |
10,875 |
|
JAMES |
30 |
950 |
950 |
|
MARTIN |
30 |
1,250 |
3,450 |
|
WARD |
30 |
1,250 |
3,450 |
|
TURNER |
30 |
1,500 |
4,950 |
|
ALLEN |
30 |
1,600 |
6,550 |
|
BLAKE |
30 |
2,850 |
9,400 |
6.2 AVG 函数
/* Formatted on 2009/11/08 21:29 (Formatter Plus v4.8.8) */
SELECT AVG (sal) "avg"
FROM scott.emp;
/* Formatted on 2009/11/08 21:31 (Formatter Plus v4.8.8) */
SELECT ename, deptno, sal,hiredate,
round((AVG (sal) OVER (PARTITION BY deptno ORDER BY hiredate
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)),2) "c_mavg"
FROM scott.emp;
Round() 取小数点后面2位
ENAME DEPTNO SAL HIREDATE c_mavg
CLARK 10 2450 1981-6-9 3725
KING 10 5000 1981-11-17 2916.67
MILLER 10 1300 1982-1-23 3150
SMITH 20 800 1980-12-17 1887.5
JONES 20 2975 1981-4-2 2258.33
FORD 20 3000 1981-12-3 2991.67
SCOTT 20 3000 1987-4-19 2366.67
ADAMS 20 1100 1987-5-23 2050
ALLEN 30 1600 1981-2-20 1425
WARD 30 1250 1981-2-22 1900
BLAKE 30 2850 1981-5-1 1866.67
TURNER 30 1500 1981-9-8 1866.67
MARTIN 30 1250 1981-9-28 1233.33
JAMES 30 950 1981-12-3 1100
6.3 Count 函数
/* Formatted on 2009/11/09 16:50 (Formatter Plus v4.8.8) */
SELECT COUNT (*) "Total"
FROM scott.emp;
/* Formatted on 2009/11/10 10:42 (Formatter Plus v4.8.8) */
SELECT ename, empno, deptno, sal,
COUNT (sal) OVER (PARTITION BY deptno ORDER BY sal
RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING)
AS mov_count FROM scott.emp;
|
ENAME |
EMPNO |
DEPTNO |
SAL |
MOV_COUNT |
|
MILLER |
7,934 |
10 |
1,300 |
1 |
|
CLARK |
7,782 |
10 |
2,450 |
1 |
|
KING |
7,839 |
10 |
5,000 |
1 |
|
SMITH |
7,369 |
20 |
800 |
1 |
|
ADAMS |
7,876 |
20 |
1,100 |
1 |
|
JONES |
7,566 |
20 |
2,975 |
3 |
|
SCOTT |
7,788 |
20 |
3,000 |
3 |
|
FORD |
7,902 |
20 |
3,000 |
3 |
|
JAMES |
7,900 |
30 |
950 |
1 |
|
MARTIN |
7,654 |
30 |
1,250 |
2 |
|
WARD |
7,521 |
30 |
1,250 |
2 |
|
TURNER |
7,844 |
30 |
1,500 |
2 |
|
ALLEN |
7,499 |
30 |
1,600 |
1 |
|
BLAKE |
7,698 |
30 |
2,850 |
1 |
6.4 Max 函数
/* Formatted on 2009/11/10 14:45 (Formatter Plus v4.8.8) */
SELECT MAX (sal) MAX
FROM scott.emp;
/* Formatted on 2009/11/10 14:46 (Formatter Plus v4.8.8) */
SELECT ename, sal, deptno, MAX (sal) OVER (PARTITION BY deptno) AS MAX
FROM scott.emp;
/* Formatted on 2009/11/10 14:47 (Formatter Plus v4.8.8) */
SELECT ename, sal, deptno
FROM (SELECT ename, sal, deptno, MAX (sal) OVER (PARTITION BY deptno) sal_max
FROM scott.emp)
WHERE sal = sal_max;
|
ENAME |
SAL |
DEPTNO |
|
KING |
5,000 |
10 |
|
FORD |
3,000 |
20 |
|
SCOTT |
3,000 |
20 |
|
BLAKE |
2,850 |
30 |
七． ratio_to_report报表处理函数
除报告详细数据外，许多报告中还包括每行总数的百分比。例如，每名客户的订单相对于总订单的百分比，或每位销售代表的销售额相对于总销售额的百分比。
传统上，Oracle计算百分比的方法是在总计报告的子查询中使用SUM函数总计报告，然后把那个结果放到细节表中相除来计算百分比。
分析函数RATIO_TO_REPORT 用来计算当前记录的指标expr占开窗函数over中包含记录的所有同一指标的百分比. 这里如果开窗函数的统计结果为null或者为0,就是说占用比率的被除数为0或者为null, 则得到的结果也为0.
开窗条件query_partition_clause决定被除数的值, 如果用户忽略了这个条件, 则计算查询结果中所有记录的汇总值.
用户不能使用其他分析函数或者ratio_to_report作为分析函数ratio_to_report的参数expr, 也就是说这个函数不能循环使用. 但我们可以使用其他普通函数作为这个分析函数的查询结果.
RATIO_TO_REPORT解析函数使得这种类型的查询更容易编码。它的格式如下：
RATIO_TO_REPORT (expr) OVER (query_partition_clause)
/* FORMATTED ON 2009/11/10 20:24 (FORMATTER PLUS V4.8.8) */
SELECT ENAME, SAL, DEPTNO, RATIO_TO_REPORT (SAL) OVER () AS RR
FROM SCOTT.EMP
WHERE DEPTNO = 10;
|
ENAME |
SAL |
DEPTNO |
RR |
|
CLARK |
2,450 |
10 |
0.28 |
|
KING |
5,000 |
10 |
0.571428571428571 |
|
MILLER |
1,300 |
10 |
0.148571428571429 |
/* FORMATTED ON 2009/11/10 20:36 (FORMATTER PLUS V4.8.8) */
SELECT ENAME, SAL, DEPTNO,
RATIO_TO_REPORT (SAL) OVER (PARTITION BY DEPTNO) AREA_PCT
FROM SCOTT.EMP;
|
ENAME |
SAL |
DEPTNO |
AREA_PCT |
|
CLARK |
2,450.0000000000 |
10 |
0.2800000000 |
|
KING |
5,000.0000000000 |
10 |
0.5714285714 |
|
MILLER |
1,300.0000000000 |
10 |
0.1485714286 |
|
JONES |
2,975.0000000000 |
20 |
0.2735632184 |
|
FORD |
3,000.0000000000 |
20 |
0.2758620690 |
|
ADAMS |
1,100.0000000000 |
20 |
0.1011494253 |
|
SMITH |
800.0000000000 |
20 |
0.0735632184 |
|
SCOTT |
3,000.0000000000 |
20 |
0.2758620690 |
|
WARD |
1,250.0000000000 |
30 |
0.1329787234 |
|
TURNER |
1,500.0000000000 |
30 |
0.1595744681 |
|
ALLEN |
1,600.0000000000 |
30 |
0.1702127660 |
|
JAMES |
950.0000000000 |
30 |
0.1010638298 |
|
BLAKE |
2,850.0000000000 |
30 |
0.3031914894 |
|
MARTIN |
1,250.0000000000 |
30 |
0.1329787234 |
八． First,Last，First_value，Last_value取基数的分析函数
First功能描述：从DENSE_RANK返回的集合中取出排在最前面的一个值的行（可能多行，因为值可能相等），因此完整的语法需要在开始处加上一个集合函数以从中取出记录。
Last功能描述：从DENSE_RANK返回的集合中取出排在最后面的一个值的行（可能多行，因为值可能相等），因此完整的语法需要在开始处加上一个集合函数以从中取出记录。
FIRST_VALUE、LAST_VALUE：返回结果集中排在第一位和最后一位的值。
语法是：FIRST_VALUE (expr) OVER ( analytic_clause)
/* FORMATTED ON 2009/11/10 20:51 (FORMATTER PLUS V4.8.8) */
SELECT ENAME, DEPTNO, SAL,
MIN (SAL)KEEP (DENSE_RANK FIRST ORDER BY DEPTNO) OVER (PARTITION BY DEPTNO) "Worst",
MAX (SAL)KEEP (DENSE_RANK LAST ORDER BY DEPTNO) OVER (PARTITION BY DEPTNO) "Best"
FROM SCOTT.EMP;
|
ENAME |
DEPTNO |
SAL |
Worst |
Best |
|
CLARK |
10 |
2,450 |
1,300 |
5,000 |
|
KING |
10 |
5,000 |
1,300 |
5,000 |
|
MILLER |
10 |
1,300 |
1,300 |
5,000 |
|
JONES |
20 |
2,975 |
800 |
3,000 |
|
FORD |
20 |
3,000 |
800 |
3,000 |
|
ADAMS |
20 |
1,100 |
800 |
3,000 |
|
SMITH |
20 |
800 |
800 |
3,000 |
|
SCOTT |
20 |
3,000 |
800 |
3,000 |
|
WARD |
30 |
1,250 |
950 |
2,850 |
|
TURNER |
30 |
1,500 |
950 |
2,850 |
|
ALLEN |
30 |
1,600 |
950 |
2,850 |
|
JAMES |
30 |
950 |
950 |
2,850 |
|
BLAKE |
30 |
2,850 |
950 |
2,850 |
|
MARTIN |
30 |
1,250 |
950 |
2,850 |
/* Formatted on 2009/11/10 20:55 (Formatter Plus v4.8.8) */
SELECT ENAME, DEPTNO, SAL,
FIRST_VALUE (ENAME) OVER (PARTITION BY DEPTNO ORDER BY SAL ASC) AS LOWEST_SAL
FROM SCOTT.EMP;
|
ENAME |
DEPTNO |
SAL |
LOWEST_SAL |
|
MILLER |
10 |
1,300 |
MILLER |
|
CLARK |
10 |
2,450 |
MILLER |
|
KING |
10 |
5,000 |
MILLER |
|
SMITH |
20 |
800 |
SMITH |
|
ADAMS |
20 |
1,100 |
SMITH |
|
JONES |
20 |
2,975 |
SMITH |
|
FORD |
20 |
3,000 |
SMITH |
|
SCOTT |
20 |
3,000 |
SMITH |
|
JAMES |
30 |
950 |
JAMES |
|
MARTIN |
30 |
1,250 |
JAMES |
|
WARD |
30 |
1,250 |
JAMES |
|
TURNER |
30 |
1,500 |
JAMES |
|
ALLEN |
30 |
1,600 |
JAMES |
|
BLAKE |
30 |
2,850 |
JAMES |
九． Greatest， Least 函数
Greatest函数：取一个一维数组中的最大值。
Least函数：取一个一维数组中的最小值。
/* Formatted on 2009/11/10 20:59 (Formatter Plus v4.8.8) */
SELECT GREATEST (1, 2, 3, 4, 5, 6) MAX
FROM DUAL;
/* Formatted on 2009/11/10 20:59 (Formatter Plus v4.8.8) */
SELECT LEAST (1, 2, 3, 4, 5, 6) MIN
FROM DUAL;
十． Trunc, round, decode，substr函数
全角的数字/字母/标点符号转半角to_single_byte
10.1 Trunc 函数
Trunc实际上是truncate函数，字面意思是截断，截尾。函数的功能是将数字进行截断。tranc()并不四舍五入。
/* Formatted on 2009/11/10 21:19 (Formatter Plus v4.8.8) */
SELECT TRUNC (1234.5678, 2) rs
FROM DUAL;
RS
----------
1234.56
/* Formatted on 2009/11/10 21:19 (Formatter Plus v4.8.8) */
SELECT TRUNC (1234.5678, 0) rs
FROM DUAL;
RS
----------
1234
/* Formatted on 2009/11/10 21:19 (Formatter Plus v4.8.8) */
SELECT TRUNC (1234.5678, -2) rs
FROM DUAL;
RS
------------
1200
10.2 Round 函数
Round 函数: 返回按指定位数进行四舍五入的数值。
语法: Round(expression[, numdecimalplaces])
expression 必选。 数值表达式 被四舍五入。
numdecimalplaces 可选。数字表明小数点右边有多少位进行四舍五入。如果省略，则 Round 函数返回整数。
/* Formatted on 2009/11/10 21:23 (Formatter Plus v4.8.8) */
SELECT ROUND (12.45, 1) rs
FROM DUAL;
RS
---------------
12.5
10.3 Decode 函数
decode(条件,值1,返回值1,值2,返回值2,...值n,返回值n,缺省值)
decode(字段或字段的运算，值1，值2，值3）
SELECT DECODE (value, <if this value>, <return this value>) FROM table
这个函数运行的结果是，当字段或字段的运算的值等于值1时，该函数返回值2，否则返回值3
当然值1，值2，值3也可以是表达式，这个函数使得某些sql语句简单了许多
/* Formatted on 2009/11/10 21:28 (Formatter Plus v4.8.8) */
SELECT DECODE (NAME, 'Dave', 'I Love BL', 'NoLove') rs
FROM (SELECT 'Dave' NAME
FROM DUAL);
RS
---------
I Love BL
/* Formatted on 2009/11/10 21:29 (Formatter Plus v4.8.8) */
SELECT DECODE (NAME, 'BL', 'I love Dave', 'NoLove') rs
FROM (SELECT 'Dave' NAME
FROM DUAL);
RS
------
NoLove
10.4 SubStr 函数
SUBSTR(string,start,count) ： 取子字符串,从start开始,取count个。
/* Formatted on 2009/11/10 21:37 (Formatter Plus v4.8.8) */
SELECT SUBSTR ('I Love You', 3, 4) rs
FROM DUAL;
RS
----
Love
讲解详细发表评论
-
oracle jdbc链接SID和Service Name的区别
2018-12-24 09:27 1707application-dev.yml配置文件如下 eur ... -
Oracle批量操作
2018-04-24 15:52 491racle批量插入： Xml代码 & ... -
mybatis 需要注意的点 MyBatis 插入空值时，需要指定JdbcType (201
2018-03-07 10:19 1633Oracle数据库mybatis 插入空值时报错（with ... -
Oracle JDBC driver Maven配置
2018-03-05 13:41 1327问题： maven项目，spri ... -
ibatis结合Oracle的iterate insert批量插入
2018-02-27 13:50 1593ibatis批量插入oracle数据库 <inser ... -
数据库分页大全（oracle利用解析函数row_number高效分页）
2018-02-24 17:26 720最近在转换mysql项目到oracle项目，遇到mysql分 ... -
Mybatis结合Oracle的foreach insert批量插入
2018-02-24 14:17 3463最近在做将mysql数据库项目迁移到oracle数据库项目 ... -
oracle聚合函数汇总
2018-02-24 13:02 1664今天在做项目，讲mysql项目转换为oracle项目的时候， ... -
spring集中druid进行数据库监控
2018-02-11 17:52 1895Druid内置提供了一个StatViewServlet用于 ... -
mysql和oracle数据互相迁移
2018-02-11 15:53 847工作中，项目原本是mysql数据库 后台需要换成oracl ... -
[Navicat] 连接Oracle报错_Cannot load OCI DLL 87
2018-02-11 15:43 1942Navicat可以用来连接各 ... -
oracle批处理开启或者关闭服务
2013-12-16 22:53 1018机子上装着Oracle，但并不是很常要用到，所以把原先 ... -
oracle 正则表达式用法
2011-08-05 09:30 1731l 方括号表达示 方括号表达式 ... -
oracle case when 用法总结
2011-08-04 14:27 2974Oracle dbms_job package 用法小结 O ... -
Oracle语句优化30个规则详解
2011-08-04 11:07 2875select tab_name from tables ... -
oracle decode()函数用法总结
2011-08-04 10:06 2997·含义解释： decode(条件 ... -
Oracle中的Union,Union All,Intersect,Minus
2011-08-04 09:09 1165众所周知的几个结果 ... -
oracle partition by与group by 的区别
2011-08-03 10:49 10607SELECT b, c, d,SUM(d) O ... -
oracle executeimmediate用法小解
2011-08-03 09:16 1306EXECUTE IMMEDIATE 代替了以 ... -
oracle merge的用法
2011-08-02 17:53 1116MERGE语句是Oracle9i新增的语法，用来合并UPDAT ...
相关推荐
### Oracle分析函数详解 #### 一、概述 Oracle分析函数是一种强大的工具，它允许用户对分组数据执行复杂的计算，并且结果可以根据特定条件进行动态调整。这种灵活性使得Oracle分析函数在处理复杂的数据集时非常...
Oracle分析函数是数据库管理系统Oracle中的一个强大特性，它允许用户在SQL查询中执行复杂的分析操作。分析函数在处理报表和数据迁移任务时尤其有用，因为它们可以基于分组计算聚合值，并为每个分组返回多行，而不...
Oracle 分析函数（用法+实例） Oracle 分析函数是 Oracle 8.1.6 版本中引入的高级应用，属于 Oracle 的一大亮点。分析函数可以分为四大类：排名函数、聚合函数、行比较函数和统计函数。下面将对分析函数的原理、...
Oracle 分析函数是一种强大的SQL工具，它允许你在处理数据时执行复杂的分析操作，而不像聚合...参考书籍如Tom Kyte的《Expert One-on-One》和Oracle 9i SQL Reference等，都是深入学习和理解Oracle分析函数的宝贵资源。
Oracle 分析函数详解 Oracle 分析函数是 Oracle 数据库中的一种强大功能，能够帮助用户快速进行数据分析和处理。在本文中，我们将对 Oracle 分析函数进行详细的介绍，并对其各个函数进行解释。 一、总体介绍 ...
Oracle 分析函数是一种高级SQL功能，它允许在单个查询中对数据集进行复杂的分析，无需额外的编程或多次数据库交互。分析函数处理的结果通常基于数据的分组、排序或特定窗口，为统计汇总和复杂的数据分析提供了便利。...
Oracle分析函数是数据库管理系统Oracle中的一种高级SQL特性，它允许用户在单个查询中对一组行进行计算，而无需使用子查询或自连接。这些函数极大地增强了数据分析和报告的能力，提高了查询性能。以下是对Oracle分析...
### Oracle分析函数详解 #### 一、概述 Oracle分析函数是一种强大的工具，它自Oracle 8.1.6版本开始引入，并在后续版本中不断完善和发展。这类函数的主要用途在于能够针对一组数据执行复杂的聚合计算，并且不同于...
Oracle 分析函数是数据库查询中的强大工具，它们允许在单个SQL语句中对结果集进行复杂的计算和分组操作，极大地简化了数据分析的过程。在Oracle 8i版本之后引入，分析函数为处理大量数据提供了高效的方法，避免了...
Oracle分析函数是数据库管理系统Oracle中的一种高级SQL特性，自8.1.6版本引入，极大地扩展了SQL在数据处理和分析上的能力。分析函数的主要作用是进行聚合操作，但与传统的聚合函数（如SUM, COUNT, AVG等）不同，它们...
ORACLE分析函数 ORACLE分析函数是数据库管理系统中的一种功能强大且灵活的分析工具，能够对数据进行复杂的分析和处理。通过使用分析函数，开发者可以更加方便地实现业务逻辑，提高查询效率和数据处理速度。 在本...
### Oracle分析函数详解 #### 一、概述 Oracle分析函数是一种强大的工具，它允许用户对分组数据执行复杂的计算，并且能够返回多个结果行。这与传统的聚合函数（如`SUM`、`COUNT`等）形成鲜明对比，后者通常只针对...
Oracle分析函数是数据库管理系统Oracle中一组强大的工具，用于处理集合数据，特别是在复杂的报表和数据分析场景中。它们允许用户在单个SQL查询中执行聚合操作，同时保持行的原始顺序，这是传统的GROUP BY函数无法...
Oracle分析函数是数据库管理系统Oracle中的一个重要特性，自8.1.6版本开始引入，它们用于执行基于组的聚合计算，并且为每个组返回多行结果，而不仅仅是单行。这使得分析函数在数据分析和报表生成方面非常有用，能够...