数据库学习—高级分组（聚合）函数

  

      插入多条例数据

--   INSERT INTO sales_tab 
--   SELECT TRUNC(DBMS_RANDOM.value(2010,2012)) AS year_id,
--          TRUNC(DBMS_RANDOM.value(1,12)) AS month_id,
--          TRUNC(DBMS_RANDOM.value(1,31)) AS day_id,
--          ROUND(DBMS_RANDOM.value(1,100),2) AS sales_value
--   FROM dual
--  CONNECT BY level <= 1000;

   集合操作： 对结果集的操作,注意，只有字段数相同才能做集合操作

   并集（UNION/UNION ALL），交集(INTERSECT)、差集(MINUS)

  SELECT statement1

 [UNION | UNION ALL | INTERSECT | MINUS]

 SELECT statement2;

SELECT * FROM emp 
WHERE ENAME='SMITH'
UNION 
SELECT * FROM emp
WHERE ename='WARD'

         高级聚合（分组）函数： 

         作用在GROUP BY 中，每个高级分组函数都有一套分组策略

   1、ROLLUP()

    不用聚合函数：

   --查看每天的营业额
  SELECT year_id,month_id,day_id,SUM(sales_value) FROM sales_tab
  GROUP BY year_id,month_id,day_id
  ORDER BY year_id,month_id,day_id

   

--查看每月营业额
  SELECT year_id,month_id,SUM(sales_value) FROM sales_tab
  GROUP BY year_id,month_id
  ORDER BY year_id,month_id

--查看每年的营业额
  SELECT year_id,SUM(sales_value) FROM sales_tab
  GROUP BY year_id
  ORDER BY year_id

 ROLLUP() ,分组原则，参数逐次递减，一直到所有的参数都不要，每一种分组都要统计一次结果，并且并在

一个结果集显示。(参数一定是逐级递减，不然不能用)

   GROUP BY ROLLUP(a,b,c)

  等价于：

  GROUP BY a,b,c

  UNION ALL

  GROUP BY a,b

  UNION ALL

  GROUP BY a

  UNION ALL

   全表

 

      -- 
    --查看上面的三种情况在一个表中
   SELECT year_id,month_id,day_id,SUM(sales_value) 
   FROM sales_tab
   GROUP BY ROLLUP(year_id,month_id,day_id)

   参数逐级递减，每天，每月，每年，总共的营业额

   往下参数递减时，都是补的NULL

2010 1 1 175.38
2010 1 2 37.51
2010 1 4 56.28
2010 1 5 64.44
2010 1 6 158.19
2010 1 7 112.64
2010 1 9 80.51
2010 1 10 204.83
2010 1 13 73.26
2010 1 14 42.49
2010 1 15 67.38
2010 1 17 66.30
2010 1 19 20.52
2010 1 20 22.42
2010 1 23 160.03
2010 1 25 74.56
2010 1 26 110.13
2010 1 27 14.74
2010 1 28 29.10
2010 1 29 45.24
2010 1 30 52.67
2010 1 1668.62
2010 2 1 252.80
2010 2 2 30.55
2010 2 3 115.40
2010 2 4 105.04
2010 2 5 92.20
2010 2 6 33.66
2010 2 7 48
2010 2 8 53.27
2010 2 9 5.05
2010 2 10 81.71
2010 2 11 100.64
2010 2 12 86.46
2010 2 14 95.31
2010 2 15 8.89
2010 2 16 69.83
2010 2 17 69.67
2010 2 18 63.08
2010 2 19 214.37
2010 2 20 37.67
2010 2 21 89.26
2010 2 22 81.35
2010 2 23 172.15
2010 2 24 180.51
2010 2 25 61.68
2010 2 27 152.49
2010 2 28 193.21
2010 2 29 45.64
2010 2 30 112.66
2010 2 2652.55
2010 3 2 93.43
2010 3 4 226.02
2010 3 5 29.92
2010 3 6 51.59
2010 3 7 1.04
2010 3 8 79.30
2010 3 9 186.17
2010 3 10 88.94
2010 3 13 154.35
2010 3 15 96.78
2010 3 17 115.33
2010 3 18 213.68
2010 3 19 93.57
2010 3 20 68.13
2010 3 22 145.34
2010 3 23 126.59
2010 3 24 6.72
2010 3 25 98.25
2010 3 26 96.08
2010 3 27 257.66
2010 3 28 147.94
2010 3 30 12.97
2010 3 2389.80
2010 4 1 260.56
2010 4 2 179.29
2010 4 3 65.51
2010 4 4 77.78
2010 4 5 179.42
2010 4 6 18.52
2010 4 7 58.29
2010 4 9 43.44
2010 4 10 139.54
2010 4 14 92.79
2010 4 15 154.14
2010 4 16 80.25
2010 4 17 100.18
2010 4 19 52.41
2010 4 20 33.76
2010 4 23 175.51
2010 4 24 64.88
2010 4 25 78.67
2010 4 28 115.39
2010 4 29 44.89
2010 4 30 64.33
2010 4 2079.55
2010 5 1 79.92
2010 5