30 Khoo 3100 3069.6091
30 Baida 2900 3069.6091
30 Colmenares 2500 3069.6091
30 Himuro 2600 3069.6091
30 Tobias 2800 3069.6091
60 Hunold 9000 1722.32401
60 Ernst 6000 1722.32401
60 Austin 4800 1722.32401
60 Pataballa 4800 1722.32401
60 Lorentz 4200 1722.32401
STDDEV_SAMP
功能描述： 该函数计算累积样本标准偏离，并返回总体变量的平方根，其返回值与VAR_POP函数的平方根相同。（Standard Deviation－Sample）
SAMPLE：下例返回部门20、30、60的薪水值的样本标准偏差
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
WHERE department_id in (20,30,60);
DEPARTMENT_ID LAST_NAME HIRE_DATE SALARY CUM_SDEV
------------- ------------------------- ---------- ---------- ----------
20 Hartstein 17-2月 -96 13000
20 Fay 17-8月 -97 6000 4949.74747
30 Raphaely 07-12月-94 11000
30 Khoo 18-5月 -95 3100 5586.14357
30 Tobias 24-7月 -97 2800 4650.0896
30 Baida 24-12月-97 2900 4035.26125
30 Himuro 15-11月-98 2600 3649.2465
30 Colmenares 10-8月 -99 2500 3362.58829
60 Hunold 03-1月 -90 9000
60 Ernst 21-5月 -91 6000 2121.32034
60 Austin 25-6月 -97 4800 2163.33077
60 Pataballa 05-2月 -98 4800 1982.42276
60 Lorentz 07-2月 -99 4200 1925.61678
SUM
功能描述：该函数计算组中表达式的累积和。
SAMPLE：下例计算同一经理下员工的薪水累积值
SELECT manager_id, last_name, salary,
SUM (salary) OVER (PARTITION BY manager_id ORDER BY salary
RANGE UNBOUNDED PRECEDING) l_csum
FROM employees
WHERE manager_id in (101,103,108);
MANAGER_ID LAST_NAME SALARY L_CSUM
---------- ------------------------- ---------- ----------
101 Whalen 4400 4400
101 Mavris 6500 10900
101 Baer 10000 20900
101 Greenberg 12000 44900
101 Higgins 12000 44900
103 Lorentz 4200 4200
103 Austin 4800 13800
103 Pataballa 4800 13800
103 Ernst 6000 19800
108 Popp 6900 6900
108 Sciarra 7700 14600
108 Urman 7800 22400
108 Chen 8200 30600
108 Faviet 9000 39600
VAR_POP
功能描述：（Variance Population）该函数返回非空集合的总体变量（忽略null），VAR_POP进行如下计算：
(SUM(expr2) - SUM(expr)2 / COUNT(expr)) / COUNT(expr)
SAMPLE：下例计算1998年每月销售的累积总体和样本变量（本例在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 = 1998
GROUP BY t.calendar_month_desc;
CALENDAR Var_Pop Var_Samp
-------- ---------- ----------
1998-01 0
1998-02 6.1321E+11 1.2264E+12
1998-03 4.7058E+11 7.0587E+11
1998-04 4.6929E+11 6.2572E+11
1998-05 1.5524E+12 1.9405E+12
1998-06 2.3711E+12 2.8453E+12
1998-07 3.7464E+12 4.3708E+12
1998-08 3.7852E+12 4.3260E+12
1998-09 3.5753E+12 4.0222E+12
1998-10 3.4343E+12 3.8159E+12
1998-11 3.4245E+12 3.7669E+12
1998-12 4.893上一页 [1] [2] [3] [4] [5] [6] [7] [8] [9] 下一页 |
