GBase8s 索引测试方法

-- '------------------ 索引管理-普通索引--------------------------------'

-- 建表，建一个表空间TS1，在表的一列上建普通索引，并指定索引的物理存储（初始簇大小为 50，下次分配簇数目为50，所在表空间为TS1）。伪SQL代码如下：

create table t(c1 int, c2 char(10), c3 date);

create index common_idx on T(C1) ;

-- 通过系统表查询索引的ID

select name, id from sysobjects where name='COMMON_IDX' and subtype$='INDEX';

-- 删除索引

drop index COMMON_IDX;

-- 清除测试数据和环境

drop table t;

-- '------------------ 索引管理-聚集索引--------------------------------'

-- 创建表，在第一列上建聚集索引

create table test_c(id int,name varchar(10));

create cluster index idx_c_id on test_c(id);

-- 通过系统表查询索引的ID

select name, id from sysobjects where name='IDX_C_ID' and subtype$='INDEX';

-- 删除聚集索引

Drop index idx_c_id;

-- 清除测试数据和环境

Drop table test_c;

-- '------------------ 索引管理-唯一索引--------------------------------'

-- 创建表，在第一列上建唯一索引

create table test_c(id int,name varchar(10));

create unique index idx_test_c on TEST_C(ID);

-- 通过系统表查询索引的ID

select name, id from sysobjects where name='IDX_TEST_C' and subtype$='INDEX';

-- 删除唯一索引

drop index idx_test_c ;

-- 清除测试数据和环境

drop table test_c ;

-- '------------------ 索引管理-函数索引--------------------------------'

-- 建表，并创建函数索引，执行查询计划，

create table t_1 (c1 int, c2 int, c3 char(20), c4 date, c6 varchar(30));

create index idx on t_1 (c1+c2);

explain select * from t_1 where c1+c2<20;

-- 建表，不创建函数索引，执行查询计划，

create table t_2(c1 int, c2 int, c3 char(20), c4 date, c6 varchar(30));

explain select * from t_2 where c1+c2<20;

-- 删除函数索引

drop index idx ;

-- 清除测试数据和环境：

drop table t_1;

drop table t_2;

-- '------------------ 索引管理-位图索引--------------------------------'

-- 建表

create table t_31(c1 int, c2 char(10), c3 date);

-- 在表的第一列上创建位图索引，

create or replace bitmap index bm_idx on t_31(c1);

-- 查询索引id

select name, id from sysobjects where name='BM_IDX' and subtype$='INDEX';

-- 删除位图索引

drop index bm_idx;

-- 清除测试数据和环境：

drop table t_31;

-- '------------------ 索引管理-位图连接索引--------------------------------'

-- 创建两张基表并插入数据：

create table t_32(c1 int not CLUSTER primary key, c2 char(10), c3 date);

insert into t_32 values(1, 'a','2012-01-02');

insert into t_32 values(2, 'b','2012-02-02');

insert into t_32 values(3, 'c','2012-03-02');

create table t_33(t1 int unique, t2 char(10), t3 date);

insert into t_33 values(1, 'aa','2012-01-02');

insert into t_33 values(11, 'a','2012-01-02');

commit;

-- 创建位图连接索引：

create or replace bitmap index b_idx on t_32(c1) from t_32, t_33 where t_32.c1=t_33.t1;

-- 进行两表的连接查询：

select * from t_32, t_33 where t_32.c1=t_33.t1 and t_33.t3='2012-01-02';

-- 删除索引：

drop index b_idx;

-- 清除测试数据和环境：

drop table t_32;

drop table t_33;

-- '------------------ 索引管理-分区索引--------------------------------'

-- 创建堆表的list全局分区索引

CREATE TABLE LIST_TABLE(C1 INT) STORAGE(BRANCH (2,4));

-- 创建全局分区索引；

create index IND_LIST_TABLE on LIST_TABLE(c1) global ;

-- 删除索引

drop index IND_LIST_TABLE ;

-- 创建堆表的范围全局分区索引

CREATE TABLE callinfo(

caller CHAR(15),

callee CHAR(15),

time DATETIME,

duration INT

)

PARTITION BY RANGE(time)(

PARTITION p1 VALUES LESS THAN ('2010-04-01'),

PARTITION p2 VALUES LESS THAN ('2010-07-01'),

PARTITION p3 VALUES LESS THAN ('2010-10-01'),

PARTITION p4 VALUES EQU OR LESS THAN ('2010-12-31') --'2010-12-31' 也可替换为MAXVALUE

)STORAGE(BRANCH (2,4));

create index IND_CALLINFO on CALLINFO(time) global ;

-- 删除索引

drop index IND_CALLINFO ;

-- 创建list表的hash全局分区索引；

CREATE TABLE sales01(

sales_id INT,

saleman CHAR(20),

saledate DATETIME,

city CHAR(10)

)

PARTITION BY HASH(city)(

PARTITION p1,

PARTITION p2,

PARTITION p3,

PARTITION p4

)STORAGE(BRANCH (2,4));

create index IND_SALES01 on SALES01(city) global ;

-- 删除索引

drop index IND_SALES01 ;

-- 清除测试数据和环境：删除表

drop table LIST_TABLE;

drop table callinfo;

drop table sales01 ;

-- '------------------ 索引管理-全文索引--------------------------------'

-- 创建表并插入数据：

create table t(c1 int, c2 char(50));

insert into t values(1,' 湖北省武汉市洪山区');

insert into t values(2,' 湖北省武汉市青山区');

insert into t values(3,' 湖北省武汉市汉阳区');

insert into t values(4,' 湖北省武汉市武昌区');

insert into t values(5,' 湖北省武汉市硚口区');

commit;

-- 在表的第二列上创建全文索引

CREATE CONTEXT INDEX cti_c2 ON t(c2) LEXER DEFAULT_LEXER;

-- 更新全文索引；

ALTER CONTEXT INDEX cti_c2 ON t REBUILD;

-- 增量更新：

ALTER CONTEXT INDEX cti_c2 ON t INCREMENT;

-- 执行全文索引查询；

SELECT * FROM t WHERE CONTAINS(c2,' 洪山区' or '青山区');

-- 删除全文索引

DROP CONTEXT INDEX cti_c2 ON t;

-- 清除测试数据和环境：删除表

drop table t;

exit

GBase8s 视图测试方法

-- '------------------ 视图管理-普通视图--------------------------------'

SET FEED OFF

--SET TIMING OFF

create table t_1(c1 int, c2 char(10), c3 varchar(20), c4 date);

insert into t_1 values(1,'a','test1','2016-09-01');

insert into t_1 values(2,'a','test2','2016-10-01');

insert into t_1 values(3,'a','test3','2016-11-01');

insert into t_1 values(4,'a','test4','2016-12-01');

insert into t_1 values(5,'a','test5','2017-01-01');

commit;

create view test_view as select c1+2, c2 from t_1 where c4 < '2016-12-01';

select * from test_view;

ALTER VIEW test_view COMPILE;

update test_view set c2='bb';

-- 清理环境

drop view test_view;

drop table t_1;

-- '------------------ 视图管理-物化视图--------------------------------'

create table t_1(c1 int, c2 char(10), c3 varchar(20), c4 date);

create table t_2(t1 int, t2 char(10), t3 varchar(20), t4 date);

insert into t_1 values(1,'a1','dm1','2016-12-25');

insert into t_1 values(2,'a2','dm2','2016-12-30');

insert into t_1 values(3,'a3','dm3','2016-12-31');

insert into t_2 values(1,'a1','dm1','2016-12-25');

insert into t_2 values(2,'a2','dm2','2016-12-23');

insert into t_2 values(3,'a3','dm3','2016-12-31');

commit ;

SP_INIT_JOB_SYS(1);

create materialized view m_view(v1, v2, v3, v4)REFRESH WITH ROWID START WITH SYSDATE NEXT SYSDATE + 1 AS SELECT t_1.c1, t_1.c3, t_2.t2, t_1.c4 FROM t_1,t_2 where t_1.c1 = t_2.t1 and t_1.c4<'2016_12-31';

select * from m_view;

alter materialized view m_view disable query rewrite;

refresh materialized view m_view complete;

-- 清理环境

drop materialized view m_view;

drop table t_1;

drop table t_2;

exit

GBase8s 标准SQL测试方法

-- '------------------SQL 功能-查询扩展语法--------------------------------'

-- '----------------------- with as ------------------------------------'

CREATE TABLE TEST1(I INT);

INSERT INTO TEST1 VALUES(1);

INSERT INTO TEST1 VALUES(2);

CREATE TABLE TEST2(J INT);

INSERT INTO TEST2 VALUES(5);

INSERT INTO TEST2 VALUES(6);

INSERT INTO TEST2 VALUES(7);

commit;

WITH CTE1(K) AS(SELECT I FROM TEST1 WHERE I > 1),

CTE2(G) AS(SELECT J FROM TEST2 WHERE J > 5)

SELECT K, G FROM CTE1, CTE2;

DROP TABLE TEST1;

DROP TABLE TEST2;

-- '----------------------- TOP ---------------------------------------'

CREATE TABLE TEST1(A INT,B INT);

INSERT INTO TEST1 VALUES(1,1);

INSERT INTO TEST1 VALUES(2,2);

INSERT INTO TEST1 VALUES(3,3);

INSERT INTO TEST1 VALUES(4,4);

INSERT INTO TEST1 VALUES(5,5);

commit;

SELECT TOP 2 A FROM TEST1;

SELECT TOP 1,2 B FROM TEST1;

DROP TABLE TEST1;

-- '----------------------- limit ---------------------------------------'

CREATE TABLE TEST1(A INT,B INT);

INSERT INTO TEST1 VALUES(1,1);

INSERT INTO TEST1 VALUES(2,2);

INSERT INTO TEST1 VALUES(3,3);

INSERT INTO TEST1 VALUES(4,4);

INSERT INTO TEST1 VALUES(5,5);

commit;

select * from test1 limit 2;

select * from test1 limit 2,1;

select * from test1 limit 3 offset 1;

DROP TABLE TEST1;

-- '----------------------- rownum ---------------------------------------'

CREATE TABLE TEST1(A INT,B INT);

INSERT INTO TEST1 VALUES(1,1);

INSERT INTO TEST1 VALUES(2,2);

INSERT INTO TEST1 VALUES(3,3);

INSERT INTO TEST1 VALUES(4,4);

INSERT INTO TEST1 VALUES(5,5);

commit;

SELECT * FROM test1 WHERE rownum < 3;

DROP TABLE TEST1;

-- '----------------------- 情况表达式 ---------------------------------------'

CREATE TABLE TEST1(A INT,B INT);

INSERT INTO TEST1 VALUES(1,1);

INSERT INTO TEST1 VALUES(2,2);

INSERT INTO TEST1 VALUES(3,3);

INSERT INTO TEST1 VALUES(4,4);

INSERT INTO TEST1 VALUES(5,5);

INSERT INTO TEST1 VALUES(15,5);

commit;

SELECT a,

CASE

WHEN b> 3 THEN ' 大于3'

WHEN b <3 THEN ' 小于3'

ELSE ' 等于3'

END AS 结果

FROM test1;

DROP TABLE TEST1;

-- '----------------------- 层次查询 ---------------------------------------'

create table t1(id int ,high_id int);

insert into t1 values(1,null);

insert into t1 values(2,1);

insert into t1 values(3,1);

insert into t1 values(4,2);

insert into t1 values(5,3);

commit;

SELECT * FROM t1 CONNECT BY PRIOR id=high_id START WITH id=1;

DROP TABLE T1;

-- '----------------------- ROLLUP ---------------------------------------'

drop table test;

create table test(id int ,name char(10),num int);

insert into test values(1,' 洪山',45);

insert into test values(1,' 汉口',78);

insert into test values(2,' 武昌',56);

insert into test values(3,' 蔡甸',89);

insert into test values(4,' 江夏',34);

insert into test values(5,' 武汉',23);

insert into test values(5,' 青山',67);

insert into test values(6,' 海淀',78);

commit;

select id,name,num,count(*) as A from test group by rollup(id,name,NUM);

DROP TABLE TEST;

-- '----------------------- CUBE ---------------------------------------'

create table test(id int ,name char(10),num int);

insert into test values(1,' 洪山',45);

insert into test values(1,' 汉口',78);

insert into test values(2,' 武昌',56);

insert into test values(3,' 蔡甸',89);

insert into test values(4,' 江夏',34);

insert into test values(5,' 武汉',23);

insert into test values(5,' 青山',67);

insert into test values(6,' 海淀',78);

commit;

select id,name,count(*) as A from test group by CUBE(id,name);

DROP TABLE TEST;

-- '-----------------------GROUPING SETS ---------------------------------------'

create table test(id int ,name char(10),num int);

insert into test values(1,' 洪山',45);

insert into test values(1,' 汉口',78);

insert into test values(2,' 武昌',56);

insert into test values(3,' 蔡甸',89);

insert into test values(4,' 江夏',34);

insert into test values(5,' 武汉',23);

insert into test values(5,' 青山',67);

insert into test values(6,' 海淀',78);

commit;

select id,name,num,count(*) as A from test group by GROUPING SETS((id,name),num);

DROP TABLE TEST;

-- '----------------------- NULLS FIRST/LAST ---------------------------------------'

create table test(id int ,name char(10),num int);

insert into test values(1,' 洪山',45);

insert into test values(1,' 汉口',null);

insert into test values(2,' 武昌',56);

insert into test values(3,' 蔡甸',89);

insert into test values(4,' 江夏',34);

insert into test values(5,' 武汉',23);

insert into test values(5,' 青山',null);

insert into test values(6,' 海淀',78);

commit;

select id,name,num from test order by num DESC NULLS FIRST;

select id,name,num from test order by num DESC NULLS last;

DROP TABLE TEST;

-- '----------------------- MERGE 合并行语句 ---------------------------------------'

CREATE TABLE MERGE_T1 (C1 INT,C2 varchar2(10));

INSERT INTO MERGE_T1 VALUES(10,'a');

INSERT INTO MERGE_T1 VALUES(20,'b');

INSERT INTO MERGE_T1 VALUES(30,'c');

COMMIT;

CREATE TABLE MERGE_T2 (C1 INT,C2 varchar2(10));

INSERT INTO MERGE_T2 VALUES(30,'d');

INSERT INTO MERGE_T2 VALUES(40,'e');

INSERT INTO MERGE_T2 VALUES(50,'f');

commit;

merge into MERGE_T1 A using MERGE_T2 B on (A.c1=B.c1) when matched then update set a.c2=b.c2 when not matched then insert values(b.c1,b.c2);

Select * from merge_t1;

DROP TABLE MERGE_T1;

DROP TABLE MERGE_T2;

-- '----------------------- (+) 操作符 ---------------------------------------'

CREATE TABLE PLUS_T1 (C1 INT,C2 VARCHAR2(10));

INSERT INTO PLUS_T1 VALUES(10,'A');

INSERT INTO PLUS_T1 VALUES(20,'B');

INSERT INTO PLUS_T1 VALUES(30,'C');

COMMIT;

CREATE TABLE PLUS_T2 (C3 INT,C4 VARCHAR2(10));

INSERT INTO PLUS_T2 VALUES(30,'D');

INSERT INTO PLUS_T2 VALUES(40,'E');

INSERT INTO PLUS_T2 VALUES(50,'F');

COMMIT;

CREATE TABLE PLUS_T3 (C1 INT,C2 VARCHAR2(10));

INSERT INTO PLUS_T3 VALUES(10,'A');

INSERT INTO PLUS_T3 VALUES(20,'B');

INSERT INTO PLUS_T3 VALUES(30,'C');

COMMIT;

CREATE TABLE PLUS_T4 (C3 INT,C4 VARCHAR2(10));

INSERT INTO PLUS_T4 VALUES(30,'D');

INSERT INTO PLUS_T4 VALUES(40,'E');

INSERT INTO PLUS_T4 VALUES(50,'F');

commit;

SELECT * FROM PLUS_T1 A, PLUS_T2 B WHERE A.C1=B.C3(+) ORDER BY A.C1;

SELECT * FROM PLUS_T3 A, PLUS_T4 B WHERE A.C1(+)=B.C3 ORDER BY B.C3;

DROP TABLE PLUS_T1;

DROP TABLE PLUS_T2;

DROP TABLE PLUS_T3;

DROP TABLE PLUS_T4;

-- '----------------------- 数组查询---------------------------------------'

SELECT * FROM ARRAY NEW INT[2]{1};

-- '----------------------- PIVOT 行列转换---------------------------------------'

create table tbl_test

(catalog VARCHAR(1),product VARCHAR(2),amount number);

INSERT INTO tbl_test VALUES('A','A1',1);

INSERT INTO tbl_test VALUES('A','A1',2);

INSERT INTO tbl_test VALUES('B','B1',3);

INSERT INTO tbl_test VALUES('B',B2'',4);

INSERT INTO tbl_test VALUES('B','B2',5);

INSERT INTO tbl_test VALUES('C','C1',6);

INSERT INTO tbl_test VALUES('C','C1',7);

INSERT INTO tbl_test VALUES('C','C2',8);

INSERT INTO tbl_test VALUES('C','C2',9);

COMMIT;

SELECT * FROM tbl_test;

select

listagg(producrt,',') within group( order by amount) as lst

from tbl_test;

select

catalog,listagg(product,',') within group( order by amount) as lst

from tbl_test

group by catalog;

select listagg(product||'-'||amount,',') within group( order by amount) over (partition by catalog)

as lst

from tbl_test;

select * from tbl_test

pivot(

sum(amount)

for(catalog)

in ('A','B','C')

)

order by 1;

drop table tbl_test;

-- '------------------ 数据插入/删除/修改扩展语法--------------------------------'

-- '----------------------- Insert all ----------------------------------------'

CREATE TABLE T1 (C1 INT, C2 char(10));

CREATE TABLE T2 (C3 INT, C4 char(10));

create table t(id int, name char(10));

INSERT INTO T VALUES(1,'AA');

INSERT INTO T VALUES(2,'BB');

INSERT INTO T VALUES(3,'CC');

commit;

insert all

into t1(c1,c2)

into t2(c3,c4)

select * from t;

select * from t1;

select * from t2;

DROP TABLE T1;

DROP TABLE T2;

DROP TABLE T;

-- '----------------------- Merge into ----------------------------------------'

CREATE TABLE T1 (C1 INT, C2 VARCHAR(20));

CREATE TABLE T2 (C3 INT, C4 VARCHAR(20));

INSERT INTO T1 VALUES(1,'T1_1');

INSERT INTO T1 VALUES(2,'T1_2');

INSERT INTO T1 VALUES(3,'T1_3');

INSERT INTO T2 VALUES(2,'T2_2');

INSERT INTO T2 VALUES(4,'T2_4');

commit;

MERGE INTO T1 USING T2 ON (T1.C1=T2.C3)

WHEN MATCHED THEN UPDATE SET T1.C2=T2.C4

WHEN NOT MATCHED THEN INSERT (C1,C2) VALUES(T2.C3, T2.C4);

select * from T1;

DROP TABLE T1;

DROP TABLE T2;

-- '----------------------- Update from ----------------------------------------'

create table test (id int,name varchar);

insert into test values(1,'aaa');

insert into test values(2,'aaa1');

insert into test values(3,'aaa2');

insert into test values(4,'aaa3');

commit;

update test set name = '222' from test where id = 1;

select * from test;

DROP TABLE test;

-- '----------------------- Returning into ----------------------------------------'

CREATE TABLE TEST(C1 INT,C2 VARCHAR(20));

DECLARE

V1 VARCHAR(20);

BEGIN

INSERT INTO TEST(C1,C2) VALUES(1,'ABCDEFGHIJKLMN') RETURNING C2 INTO V1;

PRINT ('C2_DATA:'||V1);

END;

/

commit;

DROP TABLE TEST;

-- '----------------------- 标量函数---------------------------------------------'

-- '----------------------- 数值函数-------------------------------------------'

SELECT COSH(0)"Hyperbolic cosine of 0";

SELECT DEGREES(1.0);

SELECT EXP(4) "e to the 4th power";

SELECT GREATEST(1.2,3.4,2.1);

SELECT LEAST(1.2,3.4,2.1);

create table test(id int,name char(10),age int);

insert into test values(1,'aa',23);

insert into test values(2,'bb',34);

insert into test values(3,'cc',12);

insert into test values(4,'dd',65);

commit;

Select id, mod(age,10) from test;

SELECT POWER(3,2) "Raised";

SELECT RAND();

SELECT ROUND(15.20);

SELECT SINH(1);

SELECT TANH(0);

SELECT TO_CHAR('01110' + 1);

DROP TABLE TEST;

-- '----------------------- 字符串函数-------------------------------------------'

SELECT BIT_LENGTH('ab');

SELECT CHAR(66),CHAR(67),CHAR(68) , CHAR(54992);

SELECT CHAR_LENGTH(' 我们');

SELECT DIFFERENCE('she', 'he');

SELECT INSERT('That is a cake',2,3, 'his') ;

SELECT LCASE('ABC');

SELECT LEFT ('computer science',10);

SELECT LEN ('hi, 你好 ');

SELECT LOCATE('man', 'The manager is a man', 10);

SELECT POSITION(' 数' IN '通用数据库');

SELECT REPEAT ('Hello ',3);

SELECT REPLICATE('aaa',3);

SELECT REVERSE('abcd');

select right('asdqwezxc',3);

SELECT CONCAT(CONCAT('Hello',SPACE(3)), 'world');

SELECT STRPOSDEC('hello');

SELECT STRPOSDEC('hello',3);

SELECT STRPOSINC ('hello');

SELECT STRPOSINC ('hello',3);

SELECT STUFF('ABCDEFG',1,3, 'OOO');

select regexp_count('a 你好ac','a');

select 1 from dual where regexp_like('a 超越aa 自我aac','(a+)');

select regexp_instr('a 你好aaac','aa');

select regexp_substr('a 超越aa 自我aac','(a+)',2);

select regexp_replace('a 你好aaac','aa','bb');

SELECT OVERLAY('txxxxas' PLACING 'hom' FROM 2 FOR 4);

select text_equal( 'a ', 'b ');

select blob_equal(0xFFFEE, 0xFFFEE);

select nlssort('abc');

create table TFun06(id number,val varchar2(20),num number);

insert into TFun06 (ID, VAL, NUM) values (10, 'abc', 1);

insert into TFun06 (ID, VAL, NUM) values (10, 'abc', 2);

insert into TFun06 (ID, VAL, NUM) values (10, 'def', 3);

insert into TFun06 (ID, VAL, NUM) values (10, 'def', 4);

commit;

SELECT ID, WM_CONCAT(VAL) AS ENAMES FROM TFun06 GROUP BY ID ORDER BY ID;

drop table TFun06;

-- '----------------------- 日期时间函数-------------------------------------------'

SELECT ADD_DAYS( DATE '2000-01-12',1);

SELECT ADD_MONTHS(DATE '2000-01-31',1);

SELECT ADD_WEEKS( DATE '2000-01-12',1);

SELECT CURDATE();

SELECT CURTIME();

SELECT DATEDIFF(QQ, '2003-06-01', DATE '2002-01-01');

SELECT DAYNAME(DATE '2012-01-01');

SELECT DAYOFMONTH('2003-01-03');

SELECT DAYOFWEEK('2003-01-01');

SELECT DAYOFYEAR('2003-03-03');

SELECT GETDATE();

SELECT MONTHS_BETWEEN(DATE '1995-02-28', DATE '1995-01-31') "Months";

SELECT NEXT_DAY(DATE '2001-08-02', 'MONDAY');

SELECT TO_DATE('2003-06-19 08:40:36','YYYY-MM-DD HH:MI:SS');

SELECT WEEK(DATE '2003-02-10');

SELECT YEAR(DATE '2001-05-12');

-- '----------------------- 空值判断函数-------------------------------------------'

SELECT COALESCE(NULL,TIME '12:00:00',TIME '11:00:00');

SELECT IFNULL(NULL,3);

Select ISNULL(1,3);

Select nvl(1,4);

select null_equ(1,1);

-- '----------------------- 其它函数-------------------------------------------'

SELECT DECODE(1, 1, 'A', 2, 'B');

SELECT ISDATE('2012-10-9');

SELECT TO_CHAR(SYSDATE,'YYYY/MM/DD');

SELECT TO_NUMBER('-123.4');

-- '----------------------- 分析型函数-------------------------------------------'

-- '----------------------- 集函数-------------------------------------------'

create table STUDENT(id int primary key,name varchar(100),age int);

insert into STUDENT values(1,'wanglei',15);

insert into STUDENT values(2,'liping',16);

insert into STUDENT values(3,'zhangli',17);

commit;

select max(age) from STUDENT;

select min (age) from STUDENT;

select avg(age) from STUDENT;

select sum (age) from STUDENT;

select count(*) from STUDENT;

create table TFun02(a number,b varchar2(10),c number);

insert into TFun02 values(1,'a',3);

insert into TFun02 values(2,'a',2);

insert into TFun02 values(1,'b',2);

insert into TFun02 values(1,'b',3);

insert into TFun02 values(2,'b',7);

insert into TFun02 values(3,'b',1);

insert into TFun02 values(8,'c',4);

commit;

-- 查询a列的总体方差：

select var_pop(a) from TFUN02;

-- 查询a列的样本方差：该函数返回非空集合的样本变量（忽略null）

select var_samp (a) from TFUN02;

-- 查询a列的方差：该函数返回表达式的变量

select variance(a) from TFUN02;

-- 查询a列的标准差：总体标准差。计算一组已经定下来数值的标准差

select stddev_pop(a) from TFUN02;

-- 查询a列的标准差：函数计算样本标准差，返回样本方差的算术平方根.

select stddev_samp(a) from TFUN02;

-- 查询a列的方差：返回样本标准差表达式

select stddev(a) from TFUN02;

-- 查询在区间（1，10）内a列的最大值：

select AREA_MAX(a,1,10) from TFUN02;

drop table STUDENT;

drop table TFun02;

-- '----------------------- 分析函数-------------------------------------------'

drop table test;

create table test(id int,name char(10),age int);

insert into test values(1,'aa',23);

insert into test values(2,'bb',34);

insert into test values(4,'cc',12);

insert into test values(5,'dd',65);

insert into test values(8,'ee',55);

insert into test values(15,'ff',34);

insert into test values(25,'gg',77);

insert into test values(35,'hh',23);

commit;

-- 其参数：over（partition by columnname1 order by columnname2）按columname1指定的字段进行分组排序，或者说按字段columnname1的值进行分组排序

SELECT age,name, AVG(id) OVER (PARTITION BY age) AS AVGID FROM test;

SELECT age,name, count(id) OVER (PARTITION BY age) AS COUNTID FROM test;

SELECT age,name, MAX(id) OVER (PARTITION BY age) AS MAXID FROM test;

SELECT age,name, MIN(id) OVER (PARTITION BY age) AS MINID FROM test;

SELECT age, name, SUM(id) OVER (PARTITION BY age) AS SUMID FROM test;

select id ,age,rank() over(order by age) as rank from test;

select id ,age,DENSE_RANK() over(order by age) as rank from test;

select id ,age,ROW_NUMBER() over(order by age) as rank from test;

-- 测试first、last函数，创建测试表并插入数据：

CREATE TABLE TFun01

(

EMPNO NUMBER(4,0) primary key,

ENAME VARCHAR2(10),

JOB VARCHAR2(9),

MGR NUMBER(4,0),

SAL NUMBER(7,2),

COMM NUMBER(7,2),

DEPTNO NUMBER(2,0)

);