STDDEV_SAMP
语法：
STDDEV_SAMP(expr)
功能：分组求标准差，作为聚集函数或分析函数。
说明：当只有一行数据时，STDDEV返回0， 而STDDEV_SAMP返回null值。
示例：
返回staffS表中工资的样本标准差。
CREATE TABLE staffS
(
staff_ID NUMBER(6) not null,
FIRST_NAME VARCHAR2(20),
LAST_NAME VARCHAR2(25),
EMAIL VARCHAR2(25),
PHONE_NUMBER VARCHAR2(20),
HIRE_DATE DATE,
employment_ID VARCHAR2(10),
SALARY NUMBER(8,2),
COMMISSION_PCT NUMBER(2,2),
MANAGER_ID NUMBER(6),
section_ID NUMBER(4)
);
INSERT INTO staffs (staff_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, employment_ID, SALARY, COMMISSION_PCT, MANAGER_ID, section_ID)
VALUES (198, 'Donald', 'OConnell', 'DOCONNEL', '650.507.9833', to_date('21-06-1999', 'dd-mm-yyyy'), 'SH_CLERK', 2600.00, null, 124, 50);
INSERT INTO staffs (staff_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, employment_ID, SALARY, COMMISSION_PCT, MANAGER_ID, section_ID)
VALUES (199, 'Douglas', 'Grant', 'DGRANT', '650.507.9844', to_date('13-01-2000', 'dd-mm-yyyy'), 'SH_CLERK', 2600.00, null, 124, 50);
INSERT INTO staffs (staff_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, employment_ID, SALARY, COMMISSION_PCT, MANAGER_ID, section_ID)
VALUES (200, 'Jennifer', 'Whalen', 'JWHALEN', '515.123.4444', to_date('17-09-1987', 'dd-mm-yyyy'), 'AD_ASST', 4400.00, null, 101, 10);
INSERT INTO staffs (staff_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, employment_ID, SALARY, COMMISSION_PCT, MANAGER_ID, section_ID)
VALUES (198, 'Donald', 'OConnell', 'DOCONNEL', '650.507.9833', to_date('21-06-1999', 'dd-mm-yyyy'), 'SH_CLERK', 2600.00, null, 124, 50);
SELECT STAFF_ID, FIRST_NAME, SALARY, STDDEV_SAMP(SALARY) OVER (PARTITION BY staff_ID) AS "samp" FROM staffs;
STAFF_ID FIRST_NAME SALARY samp
---------------------------------------- -------------------- ---------------------------------------- ----------------------------------------
198 Donald 2600 0
198 Donald 2600 0
199 Douglas 2600
200 Jennifer 4400
STDDEV_POP
语法：
STDDEV_POP(expr)
功能：分组求数据集的标准差，作为聚集函数或分析函数。
说明：STDDEV_POP是计算一组确定数值的标准差，而不是抽样中样本标准差，返回方差的算术平方根，计算公式和STDDEV稍有不同。
示例：
返回staffS表中工资的总体标准差和样本标准差。
CREATE TABLE staffS
(
staff_ID NUMBER(6) not null,
FIRST_NAME VARCHAR2(20),
LAST_NAME VARCHAR2(25),
EMAIL VARCHAR2(25),
PHONE_NUMBER VARCHAR2(20),
HIRE_DATE DATE,
employment_ID VARCHAR2(10),
SALARY NUMBER(8,2),
COMMISSION_PCT NUMBER(2,2),
MANAGER_ID NUMBER(6),
section_ID NUMBER(4)
);
INSERT INTO staffs (staff_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, employment_ID, SALARY, COMMISSION_PCT, MANAGER_ID, section_ID)
VALUES (198, 'Donald', 'OConnell', 'DOCONNEL', '650.507.9833', to_date('21-06-1999', 'dd-mm-yyyy'), 'SH_CLERK', 2600.00, null, 124, 50);
INSERT INTO staffs (staff_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, employment_ID, SALARY, COMMISSION_PCT, MANAGER_ID, section_ID)
VALUES (199, 'Douglas', 'Grant', 'DGRANT', '650.507.9844', to_date('13-01-2000', 'dd-mm-yyyy'), 'SH_CLERK', 2600.00, null, 124, 50);
INSERT INTO staffs (staff_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, employment_ID, SALARY, COMMISSION_PCT, MANAGER_ID, section_ID)
VALUES (200, 'Jennifer', 'Whalen', 'JWHALEN', '515.123.4444', to_date('17-09-1987', 'dd-mm-yyyy'), 'AD_ASST', 4400.00, null, 101, 10);
INSERT INTO staffs (staff_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, employment_ID, SALARY, COMMISSION_PCT, MANAGER_ID, section_ID)
VALUES (198, 'Donald', 'OConnell', 'DOCONNEL', '650.507.9833', to_date('21-06-1999', 'dd-mm-yyyy'), 'SH_CLERK', 2600.00, null, 124, 50);
SELECT STDDEV_POP(SALARY) "Pop", STDDEV_SAMP(SALARY) "Samp" FROM staffs;
Pop Samp
---------------------------------------- ----------------------------------------
779.422863405994782087350853677642565124 900
1 rows fetched.
