分组求和SQL示例sql语言应用



1、ROLLUP和CUBE函数，自动汇总数据
select * from test_tbl的数据这样的 col_a col_b col_c —- —– —– 1 b1 12 1 b1 2 1 b2 31 2 b2 7 2 b3 42 2 b3 1 2 b3 3 如果按A、B列进行汇总C列，用一般的方法是这样：
select col_a,col_b,sum(col_c) from test_tbl group by col_a,col_b 结果如下 col_a col_b sum(col_c) —- —– ——– 1 b1 14 1 b2 31 2 b2 7 2 b3 46 但是如果这时候还想按A列汇总且要C列的合计数，那就要再用两个SQL来嵌套，很麻烦，不过用
rollup就简单多了： select nvl(col_a,’合计’) col_a,nvl(col_b,decode(col_a,null,”,’小计’||col_a))
col_b,sum(col_c) from test_tbl group by rollup(col_a,col_b)，结果如下 col_a col_b sum(col_c) —- —– ——– 1 b1 14 1 b2 31 1 小计1 45 2 b2 7 2 b3 46 2 小计2 53 合计 98 结果集刚好是先按A和B汇总，然后是按A汇总，最后是全部汇总这时候如果再要按B列汇总，怎么办
呢？又要用SQL嵌套吗？不是的，如果有这要求的话，改用cube函数就OK啦
select nvl(col_a,decode(col_b,null,’合计’,'小计’||col_b)) col_a,nvl(col_b,decode
(col_a,null,”,’小计’||col_a)) col_b,sum(col_c) from test_tbl group by cube(col_a,col_b) 结果如下 col_a col_b sum(col_c) —- —– ——– 1 b1 14 1 b2 31 1 小计1 45 2 b2 7 2 b3 46 2 小计2 53 小计b1 b1 14 小计b2 b2 38 小计b3 b3 46 合计 98 跟刚才rollup函数得到的结果集有点不一样，那就是多了些按B列的汇总行。
2、LAG和LEAD函数，自动链接上/下行记录值
SQL> desc test_tbl Name Type —– —— COL_K NUMBER 现在按顺序的往这个test_tbl表中插入一系列数据，下面是SQL： insert into test_tbl values(1) insert into test_tbl values(2) insert into test_tbl values(4) insert into test_tbl values(5) insert into test_tbl values(8) insert into test_tbl values(9) insert into test_tbl values(11) insert into test_tbl values(12) insert into test_tbl values(13) …….. 数据插完后，要检查插入的数据中，从最小数到最大数之间有那些数是没被插入表，找出这些数的
前一个和后一个数？如这个例里从1到13当中有目字3、6、7、10没被插入表中，这些数的前一个和后一
个分别是2和4、5和8、9和11，即 PREV_VAL NEXT_VAL ———- ———- 2 4 5 8 9 11 如果不用分析函数要得到这后结果集那真不敢想象是怎么样的一段SQL，但用LAG分析函数那就简单
了，这样写就OK select prev_val,next_val from( select col_k next_val,lag(col_k,1,0) over (order by col_k) prev_val from test_tbl ) where next_val-prev_val>1 对于LEAD函数是一样的，只不过它是往后链接而已。
3、RANK和DENSE_RANK函数，对数据进行排名
测试表是这样的select *from test_tbl结集如下 COL_A COL_B ———- ———- A 242 A 233 B 154 C 287 C 76 D 66 E 154 F 154 G 212 G 43 按A列来统计B列的值，用一般的SQL是这样select col_a, sum(col_b) from test_tbl group by
col_a order by 2 desc 结果是这样 COL_A SUM(COL_B) ———- ———- A 475 C 363 G 255 B 154 F 154 E 154 D 66 从这个数据集可以看出A是最大的，C是第二大的，当数据多时就不知道谁是排第几了，这时用
DENSE_RANK可以达到这目的 select col_a,sum(col_b),dense_rank() over (order by sum(col_b) desc) ranks from
test_tbl group by col_a 结果如下 COL_A SUM(COL_B) RANKS ———- ———- ———- A 475 1 C 363 2 G 255 3 B 154 4 F 154 4 E 154 4 D 66 5 这个数据集把每个值都排了名次，可以直接看得出，相同值的名次是相同的。 用RANK跟DENSE_RANK差不多，不过就是当出现在名次相同时，下一个名次会跳跃 select col_a,sum(col_b),rank() over (order by sum(col_b) desc ) ranks from test_tbl
group by col_a 结果如下 COL_A SUM(COL_B) RANKS ———- ———- ———- A 475 1 C 363 2 G 255 3 B 154 4 F 154 4 E 154 4 D 66 7 可以看到名次从4跳跃到7，就是因为名次4重复出现了两次
实际应用中可能会比这些例子要复杂多点，可能会先对表的数据分组，然后再用分析，如 select *from test_tbl的结果是这样的 COL_G COL_A COL_B ———- ———- ———- G1 A 242 G1 A 233 G2 C 287 G2 C 76 G2 D 66 G2 E 154 G3 F 154 G3 G 212 G3 G 43 G2 B 154 对这个数据集按G和A列汇总B列进行排名，就要先对表按G列进行分组，然后再按A列汇总B列值进行
排名 select col_g,col_a,sum(col_b),dense_rank() over (partition by col_g order by sum
(col_b) desc ) ranks from test_tbl group by col_g,col_a这个SQL加了partition by先按G列分组，结果如下 COL_G COL_A SUM(COL_B) RANKS ———- ———- ———- ———- G1 A 475 1 G2 C 363 1 G2 B 154 2 G2 E 154 2 G2 D 66 3 G3 G 255 1 G3 F 154 2 可以看到名次都是在G列的组别发生变化时，就会重新开始新排列