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

  

      插入多条例数据

--   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 2 90.74
2010 5 3 66.50
2010 5 5 197.22
2010 5 7 107.77
2010 5 8 4.91
2010 5 9 84.58
2010 5 10 96.62
2010 5 11 120.57
2010 5 12 95.56
2010 5 13 13.62
2010 5 14 71.82
2010 5 15 110.79
2010 5 16 135.36
2010 5 19 117.35
2010 5 20 49.45
2010 5 21 149.49
2010 5 22 97.02
2010 5 23 80.09
2010 5 25 96.98
2010 5 26 47.05
2010 5 27 43.25
2010 5 29 69.73
2010 5 30 89.54
2010 5 2115.93
2010 6 1 122.45
2010 6 3 82.32
2010 6 4 41.16
2010 6 5 64.56
2010 6 7 68.54
2010 6 8 190.04
2010 6 9 9.24
2010 6 12 261.90
2010 6 13 233.62
2010 6 14 131.85
2010 6 15 39.85
2010 6 16 156.80
2010 6 17 27.05
2010 6 18 41.41
2010 6 20 133.44
2010 6 21 42.45
2010 6 22 68.92
2010 6 23 87.95
2010 6 24 91.05
2010 6 25 92
2010 6 26 1.22
2010 6 27 8.86
2010 6 28 31.82
2010 6 29 87.42
2010 6 30 72.93
2010 6 2188.85
2010 7 1 82.02
2010 7 2 101.52
2010 7 4 55.39
2010 7 6 94.87
2010 7 7 17.53
2010 7 8 90.96
2010 7 9 142.60
2010 7 10 4.12
2010 7 11 146.75
2010 7 13 93.23
2010 7 14 60.59
2010 7 15 118.32
2010 7 16 161.91
2010 7 17 108.26
2010 7 18 99.06
2010 7 19 124.82
2010 7 20 275.40
2010 7 21 94.63
2010 7 23 36.37
2010 7 24 277.17
2010 7 25 221.28
2010 7 27 74.43
2010 7 28 189.14
2010 7 29 27.53
2010 7 30 151.82
2010 7 2849.72
2010 8 1 71.65
2010 8 2 35.09
2010 8 3 2.96
2010 8 4 81.85
2010 8 5 53.37
2010 8 6 71.79
2010 8 7 44.19
2010 8 8 64.50
2010 8 9 16.48
2010 8 10 43.81
2010 8 11 170.61
2010 8 12 122.69
2010 8 13 96.63
2010 8 15 52.55
2010 8 16 88.31
2010 8 18 127.46
2010 8 19 111.38
2010 8 21 164.81
2010 8 22 105.49
2010 8 23 121.57
2010 8 24 171.81
2010 8 25 186.60
2010 8 26 207.90
2010 8 28 80.21
2010 8 29 158.19
2010 8 30 90.76
2010 8 2542.66
2010 9 1 144.95
2010 9 2 93.21
2010 9 3 235.92
2010 9 4 76.49
2010 9 5 50.24
2010 9 6 130.71
2010 9 7 123.14
2010 9 8 82.30
2010 9 9 128.02
2010 9 10 126.12
2010 9 12 8.17
2010 9 13 141.58
2010 9 14 43.45
2010 9 15 114.97
2010 9 16 77.58
2010 9 17 111.79
2010 9 18 164.89
2010 9 20 182.62
2010 9 21 17.35
2010 9 22 164.03
2010 9 23 83.52
2010 9 25 133.78
2010 9 26 295.68
2010 9 27 158.05
2010 9 29 93.35
2010 9 30 163.44
2010 9 3145.35
2010 10 2 62.71
2010 10 4 85.61
2010 10 5 21.38
2010 10 6 82.47
2010 10 8 74
2010 10 9 161.64
2010 10 10 85.45
2010 10 12 41.36
2010 10 14 106.54
2010 10 15 118.10
2010 10 16 84.21
2010 10 18 57.54
2010 10 19 121.90
2010 10 20 58.42
2010 10 22 45.39
2010 10 23 30.42
2010 10 24 90.25
2010 10 25 18.33
2010 10 27 76.33
2010 10 29 59.51
2010 10 30 302.43
2010 10 1783.99
2010 11 1 85.96
2010 11 2 334.49
2010 11 4 89.39
2010 11 5 350.13
2010 11 6 94.73
2010 11 7 144.28
2010 11 8 48.87
2010 11 9 63.33
2010 11 11 285.34
2010 11 14 154.50
2010 11 18 5.55
2010 11 20 92.08
2010 11 21 116.79
2010 11 23 207.33
2010 11 24 75.93
2010 11 25 205.99
2010 11 26 38.46
2010 11 27 162.04
2010 11 30 2.82
2010 11 2558.01
2010 25975.03
2011 1 1 9.01
2011 1 2 188.73
2011 1 3 27.26
2011 1 4 134.35
2011 1 5 77.88
2011 1 6 8.97
2011 1 7 96.74
2011 1 8 69.41
2011 1 9 6.75
2011 1 10 141.84
2011 1 11 62.19
2011 1 13 166.90
2011 1 14 77.28
2011 1 15 36.08
2011 1 17 42.49
2011 1 18 74.15
2011 1 19 105.29
2011 1 21 202.57
2011 1 22 168.84
2011 1 23 88.91
2011 1 24 227.09
2011 1 25 215.32
2011 1 26 321.23
2011 1 27 49.70
2011 1 28 14.64
2011 1 29 239.37
2011 1 30 63.57
2011 1 2916.56
2011 2 2 146.95
2011 2 3 85.88
2011 2 5 69.49
2011 2 6 9.53
2011 2 8 71.17
2011 2 9 84.70
2011 2 10 144.82
2011 2 11 134.53
2011 2 13 79.61
2011 2 14 27.12
2011 2 15 93.34
2011 2 16 33.07
2011 2 17 25.14
2011 2 18 47.67
2011 2 19 66.09
2011 2 20 91.41
2011 2 21 162.53
2011 2 22 100.31
2011 2 23 8.83
2011 2 24 86.72
2011 2 25 181.51
2011 2 26 75.17
2011 2 27 3.17
2011 2 29 134.75
2011 2 1963.51
2011 3 1 91.30
2011 3 2 59.31
2011 3 3 25.44
2011 3 5 55.46
2011 3 6 157.43
2011 3 7 184.13
2011 3 8 258.75
2011 3 9 91.53
2011 3 10 161.97
2011 3 11 78.80
2011 3 12 126.22
2011 3 13 27.61
2011 3 15 73.08
2011 3 17 82.14
2011 3 18 121.29
2011 3 19 71.22
2011 3 20 155.64
2011 3 21 109.29
2011 3 22 102.80
2011 3 23 79.94
2011 3 24 70.12
2011 3 25 48.59
2011 3 26 127.14
2011 3 27 78.46
2011 3 28 107
2011 3 29 27.49
2011 3 30 84.42
2011 3 2656.57
2011 4 1 118.69
2011 4 2 56.96
2011 4 3 63.52
2011 4 5 96.79
2011 4 7 137.32
2011 4 8 86.24
2011 4 10 117.12
2011 4 11 50.88
2011 4 12 21.13
2011 4 13 251.24
2011 4 14 84.25
2011 4 15 70.98
2011 4 16 88.12
2011 4 17 103.72
2011 4 19 128.86
2011 4 20 49.12
2011 4 21 217.42
2011 4 22 92.97
2011 4 23 84.74
2011 4 24 62.05
2011 4 26 82.74
2011 4 28 176.14
2011 4 29 88.67
2011 4 30 116.13
2011 4 2445.80
2011 5 1 99.88
2011 5 2 52.14
2011 5 3 22.29
2011 5 4 47.21
2011 5 5 57.89
2011 5 6 45.79
2011 5 8 44.92
2011 5 9 28.19
2011 5 10 99.02
2011 5 11 104.83
2011 5 12 43.95
2011 5 13 97.98
2011 5 14 57.83
2011 5 15 59.43
2011 5 16 167.79
2011 5 17 80.36
2011 5 18 30.25
2011 5 19 180.76
2011 5 20 17.38
2011 5 23 102.63
2011 5 24 290.26
2011 5 25 181.04
2011 5 26 86.50
2011 5 28 106.03
2011 5 29 113.43
2011 5 30 114.11
2011 5 2331.89
2011 6 1 132.13
2011 6 4 48.24
2011 6 5 70.67
2011 6 6 156.36
2011 6 7 40.45
2011 6 8 71.16
2011 6 9 88.50
2011 6 10 10.60
2011 6 11 18.09
2011 6 12 145.65
2011 6 13 176.66
2011 6 14 91.84
2011 6 15 4.23
2011 6 16 57.82
2011 6 17 227.70
2011 6 19 153.34
2011 6 20 166.60
2011 6 21 76.15
2011 6 22 149.78
2011 6 23 133.38
2011 6 24 23.16
2011 6 25 97.68
2011 6 27 223.54
2011 6 28 88.65
2011 6 29 31.72
2011 6 2484.10
2011 7 1 45.59
2011 7 4 171.58
2011 7 5 18.85
2011 7 6 30.83
2011 7 7 171.10
2011 7 8 95.36
2011 7 11 17.47
2011 7 12 303.02
2011 7 13 69.02
2011 7 14 133.99
2011 7 15 69.99
2011 7 16 49.69
2011 7 19 14.27
2011 7 20 97.66
2011 7 21 1.68
2011 7 22 152.53
2011 7 23 54.20
2011 7 25 58.79
2011 7 26 45.81
2011 7 28 29.64
2011 7 29 109.96
2011 7 30 110.57
2011 7 1851.60
2011 8 1 86.82
2011 8 2 48.23
2011 8 3 83.35
2011 8 4 114.49
2011 8 5 129.28
2011 8 8 4.18
2011 8 9 57
2011 8 11 90.25
2011 8 12 71.27
2011 8 13 339.89
2011 8 14 25.73
2011 8 16 80.19
2011 8 17 79.24
2011 8 18 86.43
2011 8 20 80.57
2011 8 21 68.47
2011 8 22 96.37
2011 8 23 272.41
2011 8 25 72.51
2011 8 26 13.61
2011 8 28 97.20
2011 8 30 14.75
2011 8 2012.24
2011 9 1 77.53
2011 9 2 158.86
2011 9 3 44.58
2011 9 4 92.76
2011 9 5 119.75
2011 9 6 145.40
2011 9 7 24.81
2011 9 9 30.42
2011 9 10 172.24
2011 9 11 28.56
2011 9 12 144.10
2011 9 13 156.53
2011 9 14 63.32
2011 9 16 60.08
2011 9 17 248.93
2011 9 18 62.48
2011 9 19 259.09
2011 9 20 55.63
2011 9 21 1.77
2011 9 22 103.93
2011 9 25 17.82
2011 9 26 121.97
2011 9 27 16.84
2011 9 29 105.99
2011 9 30 196.31
2011 9 2509.70
2011 10 1 49.29
2011 10 2 114.94
2011 10 3 142.74
2011 10 4 121.29
2011 10 5 145.99
2011 10 6 90.87
2011 10 7 206.03
2011 10 10 33.88
2011 10 11 70.73
2011 10 13 24.98
2011 10 15 204.29
2011 10 17 97.09
2011 10 18 141.28
2011 10 19 162.26
2011 10 20 75.37
2011 10 21 235.55
2011 10 22 218.79
2011 10 24 11.43
2011 10 27 32.23
2011 10 28 50.90
2011 10 29 13.43
2011 10 30 20.53
2011 10 2263.89
2011 11 1 16.95
2011 11 2 50.30
2011 11 3 184.85
2011 11 4 92.75
2011 11 5 45.22
2011 11 7 84.39
2011 11 8 10.06
2011 11 9 59.24
2011 11 10 83.23
2011 11 11 88.08
2011 11 12 91.31
2011 11 13 146.71
2011 11 15 27.03
2011 11 16 82.23
2011 11 17 64.83
2011 11 18 162.89
2011 11 19 61.29
2011 11 20 29.24
2011 11 21 76.84
2011 11 22 232.17
2011 11 23 78.75
2011 11 25 183.84
2011 11 27 93.48
2011 11 28 89.27
2011 11 29 93.72
2011 11 30 8.46
2011 11 2237.13
2011 25672.99
51648.02