数据库学习—高级分组(聚合)函数
来源:互联网 发布:重庆龙美术馆 知乎 编辑:程序博客网 时间:2024/05/18 02:04
插入多条例数据
-- 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
- 数据库学习—高级分组(聚合)函数
- 使用case when高级用法对数据库进行行列转化 分组、聚合函数使用
- mysql 数据库笔记-基本查询(分组、聚合函数)
- nosql mongodb 学习-----第三天 高级操作之聚合函数、游标、去重复及分组
- 数据库之聚合函数与分组
- 组函数(聚合函数),分组函数
- 数据库学习——聚合函数
- 数据库学习(四)聚合函数
- Unity3D学习之(高级查询和聚合函数)
- 数据库(5)聚合、分组、排序、分页
- 聚合函数与分组
- 分组和聚合函数
- 聚合函数与分组
- 聚合函数与分组
- 聚合函数与分组
- 聚合函数与分组
- 聚合函数与分组
- 关系型数据库基础之:聚合函数与分组
- TensorFlow深度学习框架学习(一):深度学习概念以及应用
- 大疆创新发布禅思Zenmuse X7云台相机 全球首款专为专业航空摄影而生的S35数字电影相机
- 麦肯锡:潜力巨大,障碍多多,人工智能医疗是蓝海还是虚火?
- 献给普通游戏玩家:vivo X20王者荣耀实测
- 带着最完整的语音方案,Rokid CEO祝铭明三年后重返云栖大会
- 数据库学习—高级分组(聚合)函数
- 关于timer
- Java学习--提高代码水平与设计
- java算法之Search for a Range
- LFR基准网络及其生成程序
- js数组的比较
- 全球最牛白帽黑客汇聚全民黑客盛会GeekPwn2017
- qnx学习笔记-QNX以太网phy驱动调试AR8035 PHY驱动调试
- 盯盯拍CEO 罗勇现身云栖大会 畅谈车联网生态并发布全新产品mini3