p; 882 24.5
14 882 24.5
15 801 22.25
15 801 22.25
16 777.6 21.6
18 642.857143 17.8571429
18 642.857143 17.8571429
20 589.5 16.375
21 544 15.1111111
22 592.363636 16.4545455
22 592.363636 16.4545455
24 553.846154 15.3846154
24 553.846154 15.3846154
26 522 14.5
27 578.4 16.0666667
SAMPLE 5：下例计算产品260和270在1998年2月周末销售量中已开发票数量和总数量的累积REGR_SXY, REGR_SXX, and REGR_SYY统计值
SELECT t.day_number_in_month,
REGR_SXY(s.amount_sold, s.quantity_sold)
OVER (ORDER BY t.fiscal_year, t.fiscal_month_desc) "Regr_sxy",
REGR_SYY(s.amount_sold, s.quantity_sold)
OVER (ORDER BY t.fiscal_year, t.fiscal_month_desc) "Regr_syy",
REGR_SXX(s.amount_sold, s.quantity_sold)
OVER (ORDER BY t.fiscal_year, t.fiscal_month_desc) "Regr_sxx"
FROM sales s, times t
WHERE s.time_id = t.time_id
AND prod_id IN (270, 260)
AND t.fiscal_month_desc = ''''1998-02''''
AND t.day_number_in_week IN (6,7)
ORDER BY t.day_number_in_month;
DAY_NUMBER_IN_MONTH Regr_sxy Regr_syy Regr_sxx
------------------- ---------- ---------- ----------
1 18870.4 2116198.4 258.4
1 18870.4 2116198.4 258.4
1 18870.4 2116198.4 258.4
1 18870.4 2116198.4 258.4
7 18870.4 2116198.4 258.4
8 18870.4 2116198.4 258.4
14 18870.4 2116198.4 258.4
15 18870.4 2116198.4 258.4
21 18870.4 2116198.4 258.4
22 18870.4 2116198.4 258.4
ROW_NUMBER
功能描述：返回有序组中一行的偏移量，从而可用于按特定标准排序的行号。
SAMPLE：下例返回每个员工再在每个部门中按员工号排序后的顺序号
SELECT department_id, last_name, employee_id, ROW_NUMBER()
OVER (PARTITION BY department_id ORDER BY employee_id) AS emp_id
FROM employees
WHERE department_id < 50;
DEPARTMENT_ID LAST_NAME EMPLOYEE_ID EMP_ID
------------- ------------------------- ----------- ----------
10 Whalen 200 1
20 Hartstein 201 1
20 Fay 202 2
30 Raphaely 114 1
30 Khoo 115 2
30 Baida 116 3
30 Tobias 117 4
30 Himuro 118 5
30 Colmenares 119 6
40 Mavris 203 1
STDDEV
功能描述：计算当前行关于组的标准偏离。（Standard Deviation）
SAMPLE：下例返回部门30按雇佣日期排序的薪水值的累积标准偏离
SELECT last_name, hire_date,salary,
STDDEV(salary) OVER (ORDER BY hire_date) "StdDev"
FROM employees
WHERE department_id = 30;
LAST_NAME HIRE_DATE SALARY StdDev
------------------------- ---------- ---------- ----------
Raphaely 07-12月-94 11000 0
Khoo 18-5月 -95 3100 5586.14357
Tobias 24-7月 -97 2800 4650.0896
Baida 24-12月-97 2900 4035.26125
Himuro 15-11月-98 2600 3649.2465
Colmenares 10-8月 -99 2500 3362.58829
STDDEV_POP
功能描述：该函数计算总体标准偏离，并返回总体变量的平方根，其返回值与VAR_POP函数的平方根相同。（Standard Deviation－Population）
SAMPLE：下例返回部门20、30、60的薪水值的总体标准偏差
SELECT department_id, last_name, salary,
STDDEV_POP(salary) OVER (PARTITION BY department_id) AS pop_std
FROM employees
WHERE department_id in (20,30,60);
DEPARTMENT_ID LAST_NAME SALARY POP_STD
------------- ------------------------- ---------- ----------
20 Hartstein 13000 3500
20 Fay 6000 3500
30 Raphaely 11000 3069.6091
上一页 [1] [2] [3] [4] [5] [6] [7] [8] [9] 下一页 |
