发布日期:2015-11-05 09:57 来源: 标签: 数据库 oracle教程 oracle函数AVG count及其他
本章我们主要学习oracle数据库实际应用中常用函数有哪些?下面我们就做一下具体讲解,希望大家多多支持中国站长网络学院。
1、AVG 
返回一个正在运行的平均值:
AVG(DISTINCT | ALL <expression>) OVER (analytic clause) 
eg:
CREATE TABLE vote_count (
submit_date  DATE NOT NULL,
num_votes    NUMBER NOT NULL);

INSERT INTO vote_count VALUES (TRUNC(SYSDATE)-4, 100);
INSERT INTO vote_count VALUES (TRUNC(SYSDATE)-3, 150);
INSERT INTO vote_count VALUES (TRUNC(SYSDATE)-2, 75);
INSERT INTO vote_count VALUES (TRUNC(SYSDATE)-3, 25);
INSERT INTO vote_count VALUES (TRUNC(SYSDATE)-1, 50);
COMMIT;

SELECT * FROM vote_count;

SELECT submit_date, num_votes, TRUNC(AVG(num_votes)
OVER(ORDER BY submit_date ROWS UNBOUNDED PRECEDING)) AVG_VOTE_PER_DAY
FROM vote_count
ORDER BY submit_date;

SELECT submit_date, num_votes, TRUNC(AVG(num_votes)
OVER(PARTITION BY submit_date ORDER BY submit_date ROWS UNBOUNDED PRECEDING)) AVG_VOTE_PER_DAY
FROM vote_count
ORDER BY submit_date;

2、CORR
返回一组数对的相关性系数:
CORR(<expression1>, <expression2>) OVER (<analytic clause>)
eg:
conn sh/sh

SELECT t.calendar_month_number,
CORR (SUM(s.amount_sold), SUM(s.quantity_sold))
OVER (ORDER BY t.calendar_month_number) AS CUM_CORR
FROM sales s, times t
WHERE s.time_id = t.time_id AND calendar_year = 1998
GROUP BY t.calendar_month_number;

3、COUNT
1)返回所有记录或分区的运行计数:
COUNT(<*, [DISTINCT | ALL] <expression>>) OVER (<analytic clause>)
eg:
SELECT submit_date, num_votes, TRUNC(COUNT(num_votes)
OVER(ORDER BY submit_date ROWS UNBOUNDED PRECEDING)) AS DAY_COUNT
FROM vote_count
ORDER BY submit_date;

SELECT submit_date, COUNT(*)
OVER(PARTITION BY submit_date ORDER BY submit_date
ROWS UNBOUNDED PRECEDING) NUM_RECS
FROM vote_count;
2)返回的频率分布
CREATE TABLE myprods (
prod1 NUMBER(3),
prod2 NUMBER(3),
prod3 NUMBER(3));

INSERT INTO myprods VALUES (34,23,45);
INSERT INTO myprods VALUES (34,22,34);
INSERT INTO myprods VALUES (54,44,45);
INSERT INTO myprods VALUES (23,22,45);
INSERT INTO myprods VALUES (45,22,34);

SELECT prod1, COUNT(prod1) OVER (PARTITION BY prod1) freq1,
       prod2, COUNT(prod2) OVER (PARTITION BY prod2) freq2,
       prod3, COUNT(prod3) OVER (PARTITION BY prod3) freq3
FROM myprods;

4、COVAR_POP
返回一组数对的人口协方差:
COVAR_POP(<expression1>, <expression2>) OVER (<analytic clause>)
eg:
conn oe/oe

SELECT job_id,
COVAR_POP(SYSDATE-hire_date, salary) AS covar_pop,
COVAR_SAMP(SYSDATE-hire_date, salary) AS covar_samp
FROM employees
WHERE department_id in (50, 80)
GROUP BY job_id;

5、COVAR_SAMP
返回一组数对的样本协方差:
COVAR_SAMP(<expression1>, <expression2>) OVER (<analytic clause>)
eg:
See COVAR_POP Demo above

6、CUME_DIST
返回值在一组值中的累积分布:
CUME_DIST(<value>) OVER (<partition_clause> <order by clause>)
eg:
conn oe/oe

SELECT job_id, last_name, salary, CUME_DIST()
OVER (PARTITION BY job_id ORDER BY salary) AS cume_dist
FROM employees
WHERE job_id LIKE 'PU%';

7、DENSE_RANK
在一组中,在有联系的时候,不留空白的排名项目:
DENSE_RANK() OVER (<query_partition_clause> <order_by_clause>) 
eg:
conn oe/oe

SELECT d.department_name, e.last_name, e.salary, DENSE_RANK()
OVER (PARTITION BY e.department_id ORDER BY e.salary) AS DENSE_RANK
FROM employees e, departments d
WHERE e.department_id = d.department_id
AND d.department_id IN (30, 60);

8、FIRST
返回的行用dense_rank排名第一:
SELECT <aggregate_function(column_name)> KEEP
(DENSE_RANK FIRST ORDER BY <column_name> [<ASC|DESC> NULLS <FIRST|LAST>)
OVER (PARTITION BY <column_name>)
FROM <table_name>
GROUP BY <column_name>;
eg:
conn oe/oe

SELECT last_name, department_id, salary,
MIN(salary) KEEP (DENSE_RANK FIRST ORDER BY commission_pct)
OVER (PARTITION BY department_id) "Worst",
MAX(salary) KEEP (DENSE_RANK LAST ORDER BY commission_pct)
OVER (PARTITION BY department_id) "Best"
FROM employees
WHERE department_id IN (30, 60)
ORDER BY department_id, salary;

9、FIRST_VALUE
返回一个有序集合中的第一个值。如果第一个值为空,则返回null除非你指定忽略空值:
FIRST_VALUE(<expression> [IGNORE NULLS]) OVER (<analytic clause>)
eg:
conn oe/oe

SELECT last_name, salary, hire_date, FIRST_VALUE(hire_date)
OVER (ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS lv
FROM (SELECT * FROM employees WHERE department_id = 90
ORDER BY hire_date);

10、IGNORE NULLS
以下是由汤姆Kyte在Oracle杂志十一月/十二月2006期发表的一个演示的一个小的修改:
(<column_name> IGNORE NULLS)
eg:
CREATE TABLE t1 (
row_num NUMBER(3),
col1 VARCHAR2(15),
col2 VARCHAR2(15));

INSERT INTO t1 VALUES (6, NULL, NULL);
INSERT INTO t1 VALUES (1, 'Category 1', 'Mango');
INSERT INTO t1 VALUES (2, NULL, NULL);
INSERT INTO t1 VALUES (3, NULL, NULL);
INSERT INTO t1 VALUES (4, NULL, 'Banana');
INSERT INTO t1 VALUES (5, NULL, NULL);
INSERT INTO t1 VALUES (6, NULL, NULL);
INSERT INTO t1 VALUES (7, 'Category 2', 'Vanilla');
INSERT INTO t1 VALUES (8, NULL, NULL);
INSERT INTO t1 VALUES (9, 'Category 3', 'Strawberry');
COMMIT;

SELECT * FROM t1;

SELECT row_num,
LAST_VALUE(col1 IGNORE NULLS) OVER (ORDER BY row_num) col1,
LAST_VALUE(col2 IGNORE NULLS) OVER (ORDER BY row_num) col2
FROM t1
ORDER BY row_num;

11、LAG
滞后提供了在同一个表中的一行的访问,而不具有自连接。给定一系列从查询返回的行和游标的位置,在该位置之前,该行在给定的物理偏移量上提供访问。:
LAG(<value expression>, <offset>, <default>)
OVER ([<query partition clause>] <order_by_clause>)
eg:
conn oe/oe

SELECT last_name, hire_date, salary,
LAG(salary, 1, 0) OVER (ORDER BY hire_date) AS PREV_SAL
FROM employees
WHERE job_id = 'PU_CLERK';

12、LAST
返回行排名最后使用dense_rank:
<aggregate function> KEEP (DENSE_RANK LAST ORDER BY
(<expression> <ASC | DESC> NULLS <FIRST | LAST>)
eg:
See FIRST Demo above

13、LAST_VALUE
返回一个有序集合中的最后一个值。如果在设置最后值为空,则返回null除非你指定忽略空值。此设置是有用的数据致密化。如果你指定了忽略空值,然后last_value返回第一个非空值的设置,如果所有的值都是null或空。:
LAST_VALUE (<expression> IGNORE NULLS) OVER (<analytic clause>)
eg:
conn oe/oe

SELECT last_name, salary, hire_date, FIRST_VALUE(hire_date)
OVER (ORDER BY salary ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS lv
FROM (SELECT * FROM employees WHERE department_id = 90
ORDER BY hire_date);

14、LEAD
铅在一个给定的物理偏移量以外的位置提供访问:
LEAD(<expression, offset, default>)
[(<query_partition_clause>)]
OVER (<order_by_clause>)
eg:
SELECT submit_date, num_votes,
LEAD(num_votes, 1, 0) OVER (ORDER BY submit_date) AS NEXT_VAL
FROM vote_count;

15、MAX
按分区返回最大值:
MAX (<DISTINCT | ALL> expression) OVER (<analytic clause>)
eg:conn oe/oe

SELECT manager_id, last_name, salary
FROM (
  SELECT manager_id, last_name, salary,
  MAX(salary) OVER (PARTITION BY manager_id) AS rmax_sal
  FROM employees)
WHERE salary = rmax_sal;

16、MIN
按分区返回最小值:
MIN (<DISTINCT | ALL> expression) OVER (<analytic clause>) 
eg:
conn oe/oe

SELECT manager_id, last_name, salary
FROM (
  SELECT manager_id, last_name, salary,
  MAX(salary) OVER (PARTITION BY manager_id) AS rmax_sal
  FROM employees)
WHERE salary = rmax_sal; 

17、NTILE
将一个有序的数据集成桶的数量表示的表达式并分配适当的桶数每一行。水桶编号1通过expr。该表达式的值必须解决每个分区的一个正的常数。
NTILE (<expression>) OVER ([query_partition_clause] <order by clause>) 
eg:
conn oe/oe

SELECT last_name, salary,
NTILE(4) OVER (ORDER BY salary DESC) AS quartile
FROM employees
WHERE department_id = 100; 

18、OVER PARTITION BY 
这个演示返回员工都是在各自部门的平均工资之上:
NTILE (<expression>) OVER ([query_partition_clause] <order by clause>) 
eg:
conn hr/hr

col ename format a30
col department_name format a20

SELECT * FROM (
  SELECT e.ffirst_name || ' ' || e.last_name ENAME, d.department_name,
  e.salary, TRUNC(e.salary - avg(e.salary) OVER (PARTITION BY 
  e.department_id)) sal_dif
FROM employees e, departments d
WHERE e.department_id=d.department_id)
WHERE sal_dif > 0
ORDER BY 2,4 DESC; 

19、PERCENT_RANK
一排percent_rank R,R减去计算排名1,除以1,低于行正在评估数(整个查询结果集或一个分区)。
PERCENT_RANK(<value>) OVER (<partition_clause> <order_by_clause>) 
eg:
conn oe/oe

SELECT department_id, last_name, salary, PERCENT_RANK()
OVER (PARTITION BY department_id ORDER BY salary DESC) AS pr
FROM employees
ORDER BY pr, salary; 

20、PERCENTILE_CONT
负分布函数,假设连续分布模型。它需要一个百分值和一个排序规范,并返回一个内插的值,该值将下降到该排序规范的百分值。在计算中忽略空值。
PERCENTILE_CONT(<value>) WITHIN GROUP (ORDER BY <expression> [ASC | DESC]) OVER (<partition_clause>) 
eg:
conn oe/oe

SELECT last_name, salary, department_id,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary DESC)
OVER (PARTITION BY department_id) PCT_CONT, PERCENT_RANK()
OVER (PARTITION BY department_id ORDER BY salary DESC) PCT_RANK
FROM employees
WHERE department_id IN (30, 60); 

21、PERCENTILE_DISC
具有离散分布模型的逆分布函数。它需要一个百分值和一个排序规范,并返回一个元素的集合。在计算中忽略空值。
PERCENTILE_DISC(<expression>) WITHIN GROUP (ORDER BY <order_by_clause>) 
eg:
conn oe/oe

col cume_dist format 9.999

SELECT last_name, salary, department_id,
PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY salary DESC)
OVER (PARTITION BY department_id) PCT_DISC,
CUME_DIST() OVER (PARTITION BY department_id
ORDER BY salary DESC) CUME_DIST
FROM employees
WHERE department_id IN (30, 60); 

22、RANK
计算值在一组值中的秩:
RANK(<value>) OVER (<partition_clause> ORDER BY <order_by_clause>) 
conn oe/oe

SELECT department_id, last_name, salary, commission_pct,
RANK() OVER (PARTITION BY department_id
ORDER BY salary DESC, commission_pct) "Rank"
FROM employees
WHERE department_id = 80;
/* The following query finds the 5 top-selling products for each product subcategory where that product contributes more than 20% of the sales within its product category. */
eg:
conn sh/sh

col categ format a15
col prod_subcategory format a20

SELECT SUBSTR(prod_category,1,8) AS CATEG, prod_subcategory, prod_id, sales
FROM (
  SELECT p.prod_category, p.prod_subcategory, p.prod_id,
  SUM(amount_sold) as SALES, SUM(SUM(amount_sold))
  OVER (PARTITION BY p.prod_category) AS CAT_SALES,
  SUM(SUM(amount_sold))
  OVER (PARTITION BY p.prod_subcategory) AS SUBCAT_SALES,
  RANK() OVER (PARTITION BY p.prod_subcategory
  ORDER BY SUM(amount_sold) ) AS RANK_IN_LINE
  FROM sales s, customers c, countries co, products p
  WHERE s.cust_id = c.cust_id
  AND c.country_id = co.country_id
  AND s.prod_id = p.prod_id
  AND s.time_id = TO_DATE('11-OCT-2000')
  GROUP BY p.prod_category, p.prod_subcategory, p.prod_id
  ORDER BY prod_category, prod_subcategory)
WHERE SUBCAT_SALES > 0.2 * CAT_SALES
AND RANK_IN_LINE<=5; 

23、RATIO_TO_REPORT
计算值与一组值之和的比值。如果expr值为null,然后报告值的比率也为零。
RATIO_TO_REPORT(<value>) OVER (<partition_clause>) 
eg:
conn oe/oe

SELECT last_name, salary, RATIO_TO_REPORT(salary) OVER () AS RR
FROM employees
WHERE job_id = 'PU_CLERK'; 

24、 REGR_ (Linear Regression) Functions 
1)泛型语法
FUNCTION_NAME (<expression1>,<expression2>) OVER (<analytic_clause>)
2)_ avgx回归
-- see REGR_AVGX Demo
conn oe/oe

SELECT job_id, employee_id ID, salary,
REGR_SLOPE(SYSDATE-hire_date, salary)
OVER (PARTITION BY job_id) slope,
REGR_INTERCEPT(SYSDATE-hire_date, salary)
OVER (PARTITION BY job_id) intcpt,
REGR_R2(SYSDATE-hire_date, salary)
OVER (PARTITION BY job_id) rsqr,
REGR_COUNT(SYSDATE-hire_date, salary)
OVER (PARTITION BY job_id) count,
REGR_AVGX(SYSDATE-hire_date, salary)
OVER (PARTITION BY job_id) avgx,
REGR_AVGY(SYSDATE-hire_date, salary)
OVER (PARTITION BY job_id) avgy
FROM employees
WHERE department_id in (50, 80)
ORDER BY job_id, employee_id;
3)_ avgy回归
-- see REGR_AVGX Demo
conn oe/oe

SELECT job_id,
REGR_AVGY(SYSDATE - hire_date, salary) avgy,
REGR_AVGX(SYSDATE - hire_date, salary) avgx
FROM employees
WHERE department_id in (30, 50)
GROUP BY job_id;
4)_回归。
-- see REGR_AVGX Demo
conn oe/oe

SELECT job_id,
REGR_COUNT(SYSDATE-hire_date, salary) count
FROM employees
WHERE department_id in (30, 50)
GROUP BY job_id;
5)_回归截距
-- see REGR_AVGX Demo
conn oe/oe

SELECT job_id,
REGR_SLOPE(SYSDATE - hire_date, salary) slope,
REGR_INTERCEPT(SYSDATE - hire_date, salary) intercept
FROM employees
WHERE department_id in (50,80)
GROUP BY job_id
ORDER BY job_id;
6)_回归R2
-- see REGR_AVGX Demo
conn oe/oe

SELECT job_id, REGR_R2(SYSDATE-hire_date, salary) Regr_R2
FROM employees
WHERE department_id IN (50, 80)
GROUP by job_id;
7)_回归斜率
See REGR_AVGX Demo
See REGR_INTERCEPT Demo
8)_ SXX回归
conn oe/oe

SELECT job_id,
REGR_SXY(SYSDATE - hire_date, salary) regr_sxy,
REGR_SXX(SYSDATE - hire_date, salary) regr_sxx,
REGR_SYY(SYSDATE - hire_date, salary) regr_syy
FROM employees
WHERE department_id in (50, 80)
GROUP BY job_id
ORDER BY job_id;
9)回归分析_ SXY
See REGR_SXX Demo
10)回归分析_ SYY
See REGR_SXX Demo

25、ROW_NUMBER
指定的行数与每个独特的应用(它是指每行或每行的分区由查询返回的有序序列),在该行中指定ORDER BY子句的开始与1。
ROW_NUMBER(<value>) OVER (<partition_clause> ORDER BY <order_by_clause>) 
eg“
CREATE TABLE test (
id      NUMBER(1),
degrees NUMBER(3));

INSERT INTO test VALUES (0,235);
INSERT INTO test VALUES (0,276);
INSERT INTO test VALUES (1,211);
INSERT INTO test VALUES (1,250);
INSERT INTO test VALUES (1,255);
INSERT INTO test VALUES (2,55);
INSERT INTO test VALUES (2,277);
INSERT INTO test VALUES (2,69);
INSERT INTO test VALUES (3,25);
INSERT INTO test VALUES (3,166);
INSERT INTO test VALUES (3,262);
INSERT INTO test VALUES (4,47);
INSERT INTO test VALUES (4,238);
INSERT INTO test VALUES (4,40);
COMMIT;

SELECT * FROM test;

-- choose the starting cell
SELECT id, degrees s
FROM (
  SELECT id, degrees, (360 - degrees) d360,
  ROW_NUMBER() OVER(PARTITION BY id
  ORDER BY CASE
    WHEN (degrees < 360 - degrees) THEN degrees
    ELSE 360 - degrees
    END) rn
  FROM test) t
WHERE rn = 1;

-- order the rest clockwise
SELECT *
FROM (
  SELECT t.id, t.degrees,
  ROW_NUMBER() OVER(PARTITION BY t.id
  ORDER BY CASE
    WHEN (t.degrees < starting_cell.degrees) THEN t.degrees + 360
    ELSE t.degrees
    END) rn
  FROM test t
JOIN (
  SELECT id, degrees, (360 - degrees) d360,
  ROW_NUMBER() OVER(PARTITION BY id
  ORDER BY CASE
    WHEN (degrees < 360 - degrees) THEN degrees
    ELSE 360 - degrees
    END) rn
  FROM test) starting_cell
  ON t.id = starting_cell.id
  WHERE starting_cell.rn=1)t
ORDER BY id, rn; 

26、STDDEV
返回表达式的样本标准偏差:
STDDEV([DISTINCT | ALL] <expression>) OVER (<analytic_clause>) 
eg、
conn oe/oe

col stddev format 99999.999

SELECT last_name, salary,
STDDEV(salary) OVER (ORDER BY hire_date) "StdDev"
FROM employees
WHERE department_id = 30;  

27、STDDEV_POP
计算人口标准偏差,并返回人口方差的平方根:
STDDEV_POP(<expression>) OVER (<analytic_clause>) 
eg:
conn oe/oe

SELECT department_id, last_name, salary,
STDDEV_POP(salary) OVER (PARTITION BY department_id) AS pop_std
FROM employees; 


28、STDDEV_SAMP
计算累积样本标准差,并返回样本方差的平方根。
STDDEV_SAMP(<expression>) OVER (<analytic_clause>) 
eg:
conn oe/oe

SELECT department_id, last_name, hire_date, salary,
STDDEV_SAMP(salary) OVER (PARTITION BY department_id
ORDER BY hire_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_sdev
FROM employees; 

29、SUM
计算累积样本总数,修正由于注意从梅特史戴芬森丹麦。
eg:
CREATE TABLE vote_count (
submit_date  DATE NOT NULL,
num_votes    NUMBER NOT NULL);

INSERT INTO vote_count VALUES (TRUNC(SYSDATE)-4, 100);
INSERT INTO vote_count VALUES (TRUNC(SYSDATE)-3, 150);
INSERT INTO vote_count VALUES (TRUNC(SYSDATE)-2, 75);
INSERT INTO vote_count VALUES (TRUNC(SYSDATE)-3, 25);
INSERT INTO vote_count VALUES (TRUNC(SYSDATE)-1, 50);
COMMIT;

SELECT * FROM vote_count;

SELECT submit_date, num_votes, SUM(num_votes)
OVER(ORDER BY submit_date ROWS UNBOUNDED PRECEDING) TOT_VOTE
FROM vote_count
ORDER BY submit_date; 

30、VAR_POP
返回一组数字的人口方差:
VAR_POP(<value>) OVER (<analytic_clause>) 
eg:
conn sh/sh

SELECT t.calendar_month_desc, VAR_POP(SUM(s.amount_sold))
OVER (ORDER BY t.calendar_month_desc) "Var_Pop",
VAR_SAMP(SUM(s.amount_sold))
OVER (ORDER BY t.calendar_month_desc) "Var_Samp"
FROM sales s, times t
WHERE s.time_id = t.time_id AND t.calendar_year = 2001
GROUP BY t.calendar_month_desc; 

31、VAR_SAMP
返回一组数字的样本方差:
VAR_SAMP(<value>) OVER (<analytic_clause>) 
eg:
See VAR_POP Demo above 

32、VARIANCE
返回表达式的方差:
VARIANCE([DISTINCT | ALL] <value>) OVER (<analytic_clause>) 
eg:
conn oe/oe

SELECT last_name, salary,
VARIANCE(salary) OVER (ORDER BY hire_date) AS VARIANCE
FROM employees
WHERE department_id = 30; 

33、Additional Demos
另一个例子:
这一格言写在demenko
eg:
CREATE TABLE test (id, quality, weight) AS
SELECT 1,'A',10 FROM dual UNION ALL
SELECT 2,'A',11 FROM dual UNION ALL
SELECT 3,'A',12 FROM dual UNION ALL
SELECT 4,'B',11 FROM dual UNION ALL
SELECT 5,'B',19 FROM dual UNION ALL
SELECT 6,'A',9 FROM dual UNION ALL
SELECT 7,'A',14 FROM dual UNION ALL
SELECT 8,'C',4 FROM dual UNION ALL
SELECT 9,'C',7 FROM dual; 

SELECT *
FROM test;

SELECT MAX(id) ID, MAX(quality) QUALITY, SUM(weight) WEIGHT
FROM (
  SELECT id, quality, weight, SUM(new_seq) OVER (ORDER BY id) new_grp
  FROM (
    SELECT id, quality, weight, DECODE(LAG(quality) OVER (ORDER BY id),
    quality, 0, id) new_seq
FROM test))
GROUP BY new_grp
ORDER BY 1;





相关评论

专题信息
    ORACLE数据库系统是美国ORACLE公司(甲骨文)提供的以分布式数据库为核心的一组软件产品,是目前最流行的客户/服务器(CLIENT/SERVER)或B/S体系结构的数据库之一。比如SilverStream就是基于数据库的一种中间件。ORACLE数据库是目前世界上使用最为广泛的数据库管理系统,作为一个通用的数据库系统,它具有完整的数据管理功能;本教程主要是针对oracle应用过程中我们常用的函数进行深入讲解,让大家能够通过代码实例更快、更有效的掌握oracle函数,希望对大家有所帮助。