DB2 OLAP 函数测试

 

一、建立测试环境
DROP TABLE OLAP_TEST;
CREATE TABLE OLAP_TEST
 (ID   BIGINT          NOT NULL,
  OBJ  VARCHAR(10)     NOT NULL,
  NAME VARCHAR(10),
  BGSX VARCHAR(10)     NOT NULL,
  BGPC VARCHAR(10)
 );

 

ALTER TABLE OLAP_TEST
  ADD CONSTRAINT OLAP_TEST PRIMARY KEY(ID);

 

COMMENT ON TABLE OLAP_TEST IS
'DB2 OLAP函数测试表';

 

COMMENT ON OLAP_TEST(
 ID IS 'ID',
 OBJ IS '对象',
 NAME IS '名称',
 BGSX IS '变更事项',
 BGPC IS '变更批次');

 

INSERT INTO OLAP_TEST (ID, OBJ, NAME, BGSX, BGPC) VALUES(100,'01','SDFSS','A','1');
INSERT INTO OLAP_TEST (ID, OBJ, NAME, BGSX, BGPC) VALUES(101,'01','SDFSS','B','1');
INSERT INTO OLAP_TEST (ID, OBJ, NAME, BGSX, BGPC) VALUES(102,'01','SDFSS','C','1');
INSERT INTO OLAP_TEST (ID, OBJ, NAME, BGSX, BGPC) VALUES(103,'01','SDFSS','D','1');
INSERT INTO OLAP_TEST (ID, OBJ, NAME, BGSX, BGPC) VALUES(104,'02','SDFSS','A','1');
INSERT INTO OLAP_TEST (ID, OBJ, NAME, BGSX, BGPC) VALUES(105,'01','SDFSS','A','2');
INSERT INTO OLAP_TEST (ID, OBJ, NAME, BGSX, BGPC) VALUES(106,'01','SDFSS','D','2');
INSERT INTO OLAP_TEST (ID, OBJ, NAME, BGSX, BGPC) VALUES(107,'01','SDFSS','B','2');
INSERT INTO OLAP_TEST (ID, OBJ, NAME, BGSX, BGPC) VALUES(108,'02','SDXSS','C','1');
INSERT INTO OLAP_TEST (ID, OBJ, NAME, BGSX, BGPC) VALUES(109,'02','SDXSSS','A','2');
INSERT INTO OLAP_TEST (ID, OBJ, NAME, BGSX, BGPC) VALUES(110,'02','SDXSSS','C','2');
INSERT INTO OLAP_TEST (ID, OBJ, NAME, BGSX, BGPC) VALUES(111,'02','SASS','D','2');
INSERT INTO OLAP_TEST (ID, OBJ, NAME, BGSX, BGPC) VALUES(112,'02','SASS','D','3');
INSERT INTO OLAP_TEST (ID, OBJ, NAME, BGSX, BGPC) VALUES(113,'02','SASS','B','3');
INSERT INTO OLAP_TEST (ID, OBJ, NAME, BGSX, BGPC) VALUES(114,'03','STSS','B','1');
COMMIT;

二、测试代码

 

-- 查看所有的记录
select T.*
  from OLAP_TEST T ORDER BY OBJ,ID;

 

-- 只对查询结果编号
select ROW_NUMBER() OVER() AS PC,T.*
  from OLAP_TEST T;
 
-- 对排序后的查询结果编号 
select ROW_NUMBER() OVER(ORDER BY ID) AS PC,T.*
  from OLAP_TEST T;

 

-- 按ZT分组，按PC排序查询，对每组记录分别编号，不跳号。
select ROW_NUMBER() OVER(PARTITION BY OBJ ORDER BY BGPC) AS PC,T.*
  from OLAP_TEST T; 
 
-- 按ZT分组，按PC排序查询，并对每组记录进行编号，不跳号。
select DENSE_RANK() OVER(PARTITION BY OBJ ORDER BY BGPC) AS PC,T.*
  from OLAP_TEST T;
 
-- 按ZT分组，按PC排序查询，并对每组记录进行编号，跳号。
select RANK() OVER(PARTITION BY OBJ ORDER BY BGPC) AS PC,T.*
  from OLAP_TEST T;