测试目的：
第一道：显示出 业绩 大于同一地区平均值的 合同id 姓名 地区 业绩
第二道：把同一地区的 平均业绩 地区 插入到新表中 （新表只包含两个字段即：平均业绩 地区）
不仅限于这两个比较简单的SQL语句，本文还包括几个比较简单的分析函数的使用，属于入门级。
测试数据：
id sname smoney sprovince
1 zhangsan 2098 A
2 lisi 3000 B
3 wangwu 6789 C
4 liumazi 4587 C
5 dongjiu 3298 B
6 shiga 4567 A
注：id:合同id sname:姓名 smoney :业绩 sprovince:地区
测试环境数据：
DROP TABLE TT PURGE;
CREATE TABLE TT
( ID NUMBER(5),
SNAME VARCHAR2(20),
SMONEY NUMBER(8,2),
SPROVINCE VARCHAR2(2)
);
INSERT INTO TT(ID,SNAME,SMONEY,SPROVINCE)
VALUES(1,'ZHANGSAN',2098, 'A');
INSERT INTO TT(ID,SNAME,SMONEY,SPROVINCE)
VALUES(2,'LISI',3000, 'B');
INSERT INTO TT(ID,SNAME,SMONEY,SPROVINCE)
VALUES(3,'WANGWU', 6789, 'C');
INSERT INTO TT(ID,SNAME,SMONEY,SPROVINCE)
VALUES(4,'LIUMAZI', 4587, 'C');
INSERT INTO TT(ID,SNAME,SMONEY,SPROVINCE)
VALUES(5,'DONGJIU', 3298, 'B');
INSERT INTO TT(ID,SNAME,SMONEY,SPROVINCE)
VALUES(6,'SHIGA', 4567, 'A');
COMMIT;
解决：
第一道：显示出 业绩 大于同一地区平均值的 合同id 姓名 地区 业绩
SELECT A.ID, A.SNAME, A.SPROVINCE, A.SMONEY
FROM TT A
WHERE SMONEY > (SELECT SUM(B.SMONEY) / COUNT(1)
FROM TT B
WHERE B.SPROVINCE = A.SPROVINCE);
ID SNAME SP SMONEY
---------- -------------------- -- ----------
3 WANGWU C 6789
5 DONGJIU B 3298
6 SHIGA A 4567
|
第二道：把同一地区的 平均业绩 地区 插入到新表中 （新表只包含两个字段即：平均业绩 地区）
CREATE TABLE TT_SUM
AS
SELECT SPROVINCE, SUM(SMONEY)/COUNT(1) AVG_PRO
FROM TT
GROUP BY SPROVINCE;
SQL> select * from tt_sum;
SP AVG_PRO
-- ----------
A 3332.5
B 3149
C 5688
|
补充：
该部分将展示RANK、ROW_NUMBER()和DENSE_RANK()以及在分区功能中使用汇总函数的SQL特性。
SELECT * FROM TT;
第三道：查询每个人在地区内的排名（按照业绩顺序--由高到低）
SELECT TT.*,
ROW_NUMBER() OVER(PARTITION BY TT.SPROVINCE ORDER BY SMONEY DESC)
FROM TT;
|
第四道：查询各个地区排名第一的销售员以及业绩
SELECT *
FROM (SELECT TT.*,
ROW_NUMBER() OVER(PARTITION BY TT.SPROVINCE ORDER BY SMONEY DESC) ORD
FROM TT)
WHERE ORD < 2;
--补充数据
INSERT INTO TT VALUES (7, 'thinkaw', 4567, 'A');
SELECT TT.*,
ROW_NUMBER() OVER(PARTITION BY TT.SPROVINCE ORDER BY SMONEY DESC) row_number
FROM TT;
ID SNAME SMONEY SP ROW_NUMBER
---------- -------------------- ---------- -- ----------
7 thinkaw 4567 A 1
6 SHIGA 4567 A 2
1 ZHANGSAN 2098 A 3
5 DONGJIU 3298 B 1
2 LISI 3000 B 2
3 WANGWU 6789 C 1
4 LIUMAZI 4587 C 2
已选择7行。
|
注：此时对于A Porvince 'shiga'和'thinkaw'的销售业绩是一样的，那么如果还是要查询第三道中的问题，并且明确指出，我们要查询出并列第一的销售员信息。
SELECT TT.*,
DENSE_RANK() OVER(PARTITION BY TT.SPROVINCE ORDER BY SMONEY DESC) dense_rank
FROM TT;
ID SNAME SMONEY SP DENSE_RANK
---------- -------------------- ---------- -- ----------
7 thinkaw 4567 A 1
6 SHIGA 4567 A 1
1 ZHANGSAN 2098 A 2
5 DONGJIU 3298 B 1
2 LISI 3000 B 2
3 WANGWU 6789 C 1
4 LIUMAZI 4587 C 2
已选择7行。
SELECT *
FROM (SELECT TT.*,
DENSE_RANK() OVER(PARTITION BY TT.SPROVINCE ORDER BY SMONEY DESC) ORD
FROM TT)
WHERE ORD < 2;
ID SNAME SMONEY SP ORD
---------- -------------------- ---------- -- ----------
6 SHIGA 4567 A 1
7 thinkaw 4567 A 1
5 DONGJIU 3298 B 1
3 WANGWU 6789 C 1
|
另外对于这组分析函数还有一个rank函数，我们对比一下
SELECT TT.*,
DENSE_RANK() OVER(PARTITION BY TT.SPROVINCE ORDER BY SMONEY DESC) DENSE_RANK,
ROW_NUMBER() OVER(PARTITION BY TT.SPROVINCE ORDER BY SMONEY DESC) ROW_NUMBER,
RANK() OVER(PARTITION BY TT.SPROVINCE ORDER BY SMONEY DESC) RANK
FROM TT;
ID SNAME SMONEY SP DENSE_RANK ROW_NUMBER RANK
---------- -------------------- ---------- -- ---------- ---------- ----------
7 thinkaw 4567 A 1 1 1
6 SHIGA 4567 A 1 2 1
1 ZHANGSAN 2098 A 2 3 3
5 DONGJIU 3298 B 1 1 1
2 LISI 3000 B 2 2 2
3 WANGWU 6789 C 1 1 1
4 LIUMAZI 4587 C 2 2 2
已选择7行。
|
注：对比一下显示，然后根据具体情况选择使用
同其他汇总函数结合使用的分析函数
第五道：查询每个地区的业绩总额
SELECT TT.SPROVINCE, SUM(TT.SMONEY) SUM_MONEY
FROM TT
GROUP BY TT.SPROVINCE; 第六道：查询每个销售员销售业绩占本地区销售总额的百分比
SELECT TT.*,
round( TT.SMONEY / SUM(TT.SMONEY) OVER(PARTITION BY TT.SPROVINCE) , 4) * 100 PERCENT_INFO,
SUM(TT.SMONEY) OVER(PARTITION BY TT.SPROVINCE) SUM_MONEY
FROM TT;
ID SNAME SMONEY SP PERCENT_INFO SUM_MONEY
---------- -------------------- ---------- -- ------------ ----------
6 SHIGA 4567 A 40.66 11232
1 ZHANGSAN 2098 A 18.68 11232
7 thinkaw 4567 A 40.66 11232
2 LISI 3000 B 47.63 6298
5 DONGJIU 3298 B 52.37 6298
3 WANGWU 6789 C 59.68 11376
4 LIUMAZI 4587 C 40.32 11376
已选择7行。
|
结论：
1 Over ( Partition by xxxx ) 此部分的功能就是按照xxx把数据分成各个子区间，然后允许在子区间上进行求和、求平均等汇总功能的使用；
2 ROW_NUMBER、DENSE_RANK、RANK对分区上数据的不同排序方式
参考：
阅读(1320) | 评论(1) | 转发(0) |
