OVER(PARTITION BY )与group by 的区别

–问题：查询出部门的最低工资的userid号
–创建测试数据
CREATE TABLE SS(USERid NUMBER,salary NUMBER,dept NUMBER);
INSERT INTO SS
SELECT 1,2000,1 FROM dual UNION ALL
SELECT 2,1000,1 FROM dual UNION ALL
SELECT 3,500,2 FROM dual UNION ALL
SELECT 4,1000,2 FROM dual;
COMMIT;
–利用OVER(PARTITION BY )给出的结果
SELECT t.userid,t.salary,t.dept,MIN(t.salary)OVER(PARTITION BY dept) AS min_sa FROM SS t;
–result
USERID SALARY DEPT MIN_SA
1 1 2000 1 1000
2 2 1000 1 1000
3 3 500 2 500
4 4 1000 2 500
–执行计划
EXPLAIN PLAN FOR SELECT MIN(t.salary)OVER(PARTITION BY dept) AS min_sa,t.salary,t.dept FROM SS t;
SELECT * FROM TABLE(dbms_xplan.display);
Plan hash value: 1126393237

---

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

| 0 | SELECT STATEMENT | | 4 | 104 | 4 (25)| 00:00:01 |
| 1 | WINDOW SORT | | 4 | 104 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL| SS | 4 | 104 | 3 (0)| 00:00:01 |

Note

• dynamic sampling used for this statement (level=2)
  –利用group by给出的结果
  SELECT *
  FROM SS
  INNER JOIN (SELECT MIN(SALARY) AS SALARY, DEPT FROM SS GROUP BY DEPT) SS2
  USING (SALARY, DEPT);
  –result
  SALARY DEPT USERID
  1 1000 1 2
  2 500 2 3
  –执行计划
  EXPLAIN PLAN FOR (SELECT * FROM SS INNER JOIN (SELECT MIN(SALARY) AS SALARY, DEPT FROM SS GROUP BY DEPT) SS2 USING (SALARY, DEPT));
  SELECT * FROM TABLE(dbms_xplan.display);

Plan hash value: 3057794446

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

| 0 | SELECT STATEMENT | | 4 | 260 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN | | 4 | 260 | 7 (15)| 00:00:01 |
| 2 | TABLE ACCESS FULL | SS | 4 | 156 | 3 (0)| 00:00:01 |
| 3 | VIEW | | 4 | 104 | 4 (25)| 00:00:01 |
| 4 | HASH GROUP BY | | 4 | 104 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL| SS | 4 | 104 | 3 (0)| 00:00:01 |

Predicate Information (identified by operation id):

1 - access(“SS”.“DEPT”=“SS2”.“DEPT” AND “SS”.“SALARY”=“SS2”.“SALARY”)

Note

• dynamic sampling used for this statement (level=2)
  /总结：group by和partition by的用法：group by是对检索结果的保留行进行单纯的分组，一般总爱和聚合函数一起使用;partition by虽然也具有分组功能，但同时也具有oracle的分析函数的功能
  借用如下案例来分析：。/
  –创建测试表A
  CREATE TABLE a (b VARCHAR2(3),c VARCHAR2(3),d VARCHAR2(3));
  INSERT INTO a
  SELECT ‘02’,‘02’,‘1’ FROM dual UNION ALL
  SELECT ‘02’,‘03’,‘2’ FROM dual UNION ALL
  SELECT ‘02’,‘04’,‘3’ FROM dual UNION ALL
  SELECT ‘02’,‘05’,‘4’ FROM dual UNION ALL
  SELECT ‘02’,‘01’,‘5’ FROM dual UNION ALL
  SELECT ‘02’,‘06’,‘6’ FROM dual UNION ALL
  SELECT ‘02’,‘07’,‘7’ FROM dual UNION ALL
  SELECT ‘02’,‘03’,‘5’ FROM dual UNION ALL
  SELECT ‘02’,‘02’,‘12’ FROM dual UNION ALL
  SELECT ‘02’,‘01’,‘2’ FROM dual UNION ALL
  SELECT ‘02’,‘01’,‘23’ FROM dual;
  COMMIT;
  SELECT * FROM a;
  –result
  B C D
  1 02 02 1
  2 02 03 2
  3 02 04 3
  4 02 05 4
  5 02 01 5
  6 02 06 6
  7 02 07 7
  8 02 03 5
  9 02 02 12
  10 02 01 2
  11 02 01 23
  –利用group by 求和
  SELECT b,c,SUM(d) e FROM a GROUP BY b,c ORDER BY 2;
  –result
  B C E
  1 02 01 30
  2 02 02 13
  3 02 03 7
  4 02 04 3
  5 02 05 4
  6 02 06 6
  7 02 07 7
  –利用partition by分析函数求和
  SELECT b,c,d,SUM(d) OVER(PARTITION BY b,c ORDER BY d)e FROM a; --可以通过结果看出是累计添加求和的
  –result
  B C D E
  1 02 01 2 2
  2 02 01 23 25
  3 02 01 5 30
  4 02 02 1 1
  5 02 02 12 13
  6 02 03 2 2
  7 02 03 5 7
  8 02 04 3 3
  9 02 05 4 4
  10 02 06 6 6
  11 02 07 7 7