oracle（41）
在 应用系统开发中，进行需要统计数据库中的数据，当执行数据统计时，需要将表中的数据进行分组显示，在统计分组中是通过group by子句、分组函数、having子句共同实现的。其中group by子句用于指定要分组的列，而分组函数用户指定显示统计的结果，而having子句用户限制显示分组结果。&
&&&一、分组函数&
&& 分组函数用于统计表的数据，并作用于多行，但是返回一个结果，一般情况下，分组函数要与group by子句结合使用，Oracle数据库提供了大量的分组函数，常用的五个分组函数：&
Oracle代码&&
Max：该函数用于取得列或表达式的最大值，适用于任何数据类型。&&
Min：该函数用于取得列或表达式的最小值，适用于任何数据类型。&&
Avg：该函数用于取得列或表达式的平均值，适用于数字类型。&&
Sum：该函数用于取得列或表达式的总和，&&适用于数字类型。&&
Count：该函数用于取的行数总和。&&
1、当使用分组函数时,分组函数只能出现在选择列表、order by和having子句中，而不能出现在where、group by子句中。&
2、当使用分组函数时，除了函数count(*)外，其他分组函数都会忽略NULL行。&
3、当执行select语句时,如果选择列表同时包括列、表达式和分组函数,那么这些列、表达式必须出现在group by子句中。&
4、当使用分组函数时，在分组函数中可以指定all和distinct选项，其中all是默认选项，该选项表示统计所有行数据(包括重复行),distinc可以统计不同行数据。&
示例如下：&
1、取得某列最小值、最大值、平均值、总和和总计行数&
Oracle代码&&
select&max(id)&as&max_id,min(id)&as&min_id,avg(id)&as&avg_id,sum(id)&as&sum_id,count(*)&as&count&from&cip_&&
2、去除重复值&
Oracle代码&&
select&count(distinct&id)&from&cip_&&
二、group by和having子句&
&& group by子句是对统计的结果进行分组统计，而having子句用于限制分组显示结果，语法如下：&
select column，group_function from table [where condition][group by group_by_experssion][having group_function];如上所示，column用于指定列表中的列或表达式，group_function用于指定分组函数，condition用于指定条件子句，group_by_experssion用于指定分组表达式，group_function用于指定排除分组结果条件。&
1、使用group by进行单列分组，如下：&
Oracle代码&&
select&id&as&id,min(age)&max_age,max(age)&max_age&from&cip_temps&group&by&&&
2、使用having子句限制分组显示结果，如下：&
Oracle代码&&
select&id&as&id,count(age)&count&from&cip_temps&group&by&id&having&count(age)=2;&&
三、case表达式&
case格式如下：&
Oracle代码&&
case&when&条件&then&返回值1&when&条件2&then&返回值2&else&返回值3&end&&
示例如下：&
select name,age,address,case when id=21 then 'abc' when id=22 then 'def' else 'hij' end alias from cip_&
四、Oracle常用统计函数&
1、数字函数&
& (1)、mod(m,n)该函数用于返回取得两个数字相除后的余数，如果数字为0，则返回结果为m。&
& (2)、round(n,[m]该函数用于取得四舍五入运算，如果省略m，则四舍五入至整数位；如果m是负数，则四舍五入到小数点前m位；如果m是正数，则四舍五入到小数点后m位。&
& (3)、trunc(n,[m])该函数用于截取数字，如果省略m，则将数字n的小数部门截取；如果m为正数，则将数字n截取至小数点后的第m位，如果m为负数，则将数字n截取小数点的前m为。&
示例如下：&
Oracle代码&&
select&mod(10,4)&from&&&
select&round(,-4)&from&&&
select&round(101.234567,4)&from&&&
select&trunc(,2)&from&&&
select&trunc(,-2)&from&&&
2、日期函数&
&& (1)、round(d,[fmt])该函数用于返回日期时间的四舍五入结果，如果fmt指定年度，则7月1日为分割线；如果fmt指定月，则16日为分割线；如果fmt指定为天，则中午12:00为分割线。&
&& (2)、trunc(d,[fmt])该函数用于截取日期时间数据，如果fmt指定年度，则结果为本年度的1月1日，如果fmt指定月，则结果为本月1日。&
示例如下：&
Oracle代码&&
select&round(sysdate,'yyyy')&from&&&
select&round(sysdate,'mm')&from&&&
select&round(sysdate,'dd')&from&&&
select&trunc(sysdate,'yyyy')&from&&&
select&trunc(sysdate,'mm')&from&&&
select&trunc(sysdate,'dd')&from&&&
& 3、转换函数&
&&& (1)、to_char(date,fmt)该函数用于将日期类型转换为字符串类型，其中fmt用于指定日期格式。&
&&& (2)、to_date(char,fmt)该函数用于将符合特定日期格式的字符串转变为date类型的值。&
&&& (3)、to_number(char)该函数用于将符合特定数字格式的字符串转换为数字类型。&
示例如下：&
Oracle代码&&
select&to_date('','yyyy-mm-dd')&from&&&
select&to_char(sysdate,'YYYY-MM-DD&HH24:MI:SS')&from&&&
select&to_number('10.123')&from&&&
4、其他单行函数&
(1)、decode(expr,search1,result1[,search2,result2,...],default)该函数用于返回匹配于
特定表达式结果，如果search1匹配与expr，则返回结果result1，如果search2匹配expr，则返回结果result2，以此类推，
如果没有任何匹配关系，则返回默认default。&
示例如下：&
Oracle代码&&
select&name,decode(age,'bb21',id*10,'bb22',id*20,1000)&as&decodee&from&cip_&&
注意：decode函数和case表达式的用法基本相似，但是case表达式可以多个条件进行判断，从而返回结果。&
示例如下：&
Oracle代码&&
select&name,case&when&(&&
&&&&&&&&&&&&&(age='bb21'&and&address='cc21')&&&
&&&&&&&&&&&&&or&(age='bb22'&and&address='cc22')&&&
&&&&&&&&&&&&&or&(age='bb23'&and&address='cc23')&&&
&&&&&&&&&&&)&then&1&else&0&end&as&cases&from&cip_temps
阅读(...) 评论()Oracle中用GROUPING SETS分组自定义汇总_Oracle教程_动态网站制作指南
Oracle中用GROUPING SETS分组自定义汇总
来源：人气：5611
本文通过文章作者的亲身经历来讲解如何在中使用GROUPING SETS分组自定义。
当你与COUNT和SUM这类总计函数一起使用GROUP BY语句时，你一般得不到多级总数。GROUP BY中每个唯一的列组合生成一个总数，但这些总数不会“累加”到更高一级的总数中。
要实现这一点，你可以用GROUP BY ROLLUP或GROUP BY CUBE替代GROUP BY，不过它们会生成所有可能的总数，而你可能不需要全部总数。对GROUP BY CUBE而言，将会生成2^n组总数，这里的n是GROUP BY中列的数目。
查看下面的查询，它使用了SH样本模式：
SELECT od_id, cust_id, channel_id, SUM(quantity_sold)
FROM sales
WHERE cust_id & 3
GROUP BY CUBE (prod_id, cust_id, channel_id)
这将生成8组总数：
所有行的总和
每个通道，包括所有产品和顾客。
每个顾客，包括所有产品和通道。
每项产品，包括所有顾客和通道。
每个通道/顾客组合，包括所有产品。
每个通道/产品组合，包括所有顾客。
每个产品/顾客组合，包括所有通道。
每个产品、顾客和通道组合。
可能的组合非常多。GROUP BY CUBE中每增加一列，生成的总数就会翻一番。
可以用GROUP BY GROUPING SETS来代替GROUP BY CUBE。你可以应用来指定你感爱好的总数组合。因为它不必计算它不需要集合（也不会产生太多结果），所以对SQL引擎来说更为高效。
其格式为：
GROUP BY GROUPING SETS ((list), (list) ... )
这里（list）是圆括号中的一个列序列，这个组合生成一个总数。要增加一个总和，必须增加一个(NUlL)分组集。
例如，假如只要生成每项产品（包括所有顾客和通道）和每个顾客/通道组合（包括所有产品）的总数，可以输入：
SELECT prod_id, cust_id, channel_id, SUM(quantity_sold)
FROM sales
WHERE cust_id & 3
GROUP BY GROUPING SETS (
(prod_id), (cust_id, channel_id)
这种方法将这个数据集生成的总数数量从180个减少到37个，并帮助你着重回答你希望解答的问题。
优质网站模板北京盛拓优讯信息技术有限公司. 版权所有 京ICP备号 北京市公安局海淀分局网监中心备案编号：10
广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员新手园地& & & 硬件问题Linux系统管理Linux网络问题Linux环境编程Linux桌面系统国产LinuxBSD& & & BSD文档中心AIX& & & 新手入门& & & AIX文档中心& & & 资源下载& & & Power高级应用& & & IBM存储AS400Solaris& & & Solaris文档中心HP-UX& & & HP文档中心SCO UNIX& & & SCO文档中心互操作专区IRIXTru64 UNIXMac OS X门户网站运维集群和高可用服务器应用监控和防护虚拟化技术架构设计行业应用和管理服务器及硬件技术& & & 服务器资源下载云计算& & & 云计算文档中心& & & 云计算业界& & & 云计算资源下载存储备份& & & 存储文档中心& & & 存储业界& & & 存储资源下载& & & Symantec技术交流区安全技术网络技术& & & 网络技术文档中心C/C++& & & GUI编程& & & Functional编程内核源码& & & 内核问题移动开发& & & 移动开发技术资料ShellPerlJava& & & Java文档中心PHP& & & php文档中心Python& & & Python文档中心RubyCPU与编译器嵌入式开发驱动开发Web开发VoIP开发技术MySQL& & & MySQL文档中心SybaseOraclePostgreSQLDB2Informix数据仓库与数据挖掘NoSQL技术IT业界新闻与评论IT职业生涯& & & 猎头招聘IT图书与评论& & & CU技术图书大系& & & Linux书友会二手交易下载共享Linux文档专区IT培训与认证& & & 培训交流& & & 认证培训清茶斋投资理财运动地带快乐数码摄影& & & 摄影器材& & & 摄影比赛专区IT爱车族旅游天下站务交流版主会议室博客SNS站务交流区CU活动专区& & & Power活动专区& & & 拍卖交流区频道交流区
丰衣足食, 积分 964, 距离下一级还需 36 积分
论坛徽章:0
20可用积分
比方说排序后为
我想每样随机取出3条得到如下结果,应该怎么做?
组的话有无数组(如 1 2 3 4 5 6 9 10 21),不要源代码级别穷举的方法,谢谢
使用“分析函数”
以下是DB2的SQL，Oracle 应该是类似的：
巨富豪门, 积分 38232, 距离下一级还需 1768 积分
论坛徽章:23
使用“分析函数”
以下是DB2的SQL，Oracle 应该是类似的：select
& & & && &col1
& & & && &,col2
from (
& & & &&&select
& & & &&&& & & && & col1
& & & & & & & && & ,col2
& & & & & & & && & ,rank() over(partition by col1 order by col2) as rank
& & & &&&from tab
)
where rank &= 3复制代码
家境小康, 积分 1633, 距离下一级还需 367 积分
论坛徽章:1
留座学习。听说sqlserver有top用法。
oracle里是不是得写个方法或者过程什么的。
order by ...
丰衣足食, 积分 964, 距离下一级还需 36 积分
论坛徽章:0
davidbeckham921
& & 单个还好处理,问题是现在客户要求每组随机取样80个,让我欲哭无泪,好像非得写PROC不可
稍有积蓄, 积分 232, 距离下一级还需 268 积分
论坛徽章:0
with test as (
select 1 id ,'a' value from dual union all
select 1 id ,'s' value from dual union all
select 1 id ,'d' value from dual union all
select 1 id ,'f' value from dual union all
select 2 id ,'g' value from dual union all
select 2 id ,'h' value from dual union all
select 2 id ,'j' value from dual union all
select 2 id ,'k' value from dual union all
select 2 id ,'l' value from dual union all
select 2 id ,'q' value from dual union all
select 2 id ,'w' value from dual union all
select 2 id ,'e' value from dual union all
select 2 id ,'r' value from dual union all
select 3 id ,'t' value from dual union all
select 3 id ,'y' value from dual union all
select 3 id ,'u' value from dual union all
select 3 id ,'i' value from dual union all
select 3 id ,'o' value from dual)
select id ,value from (
select id ,value,row_number() over(partition by id order by
dbms_random.value()) rn from test) t
where t.rn&4
这样是否可用？
巨富豪门, 积分 25103, 距离下一级还需 14897 积分
论坛徽章:3
恩& &通过row_number&&结合 partition&&及rownum
丰衣足食, 积分 964, 距离下一级还需 36 积分
论坛徽章:0
哎,好像大家都没看懂偶的题目
还是直接写存储过程不用想了
论坛徽章:53
duolanshizhe
& & 4楼明显是不正确的啊。他把数据写死的。5楼看着还有点像样。
巨富豪门, 积分 25103, 距离下一级还需 14897 积分
论坛徽章:3
晕&&四楼那是自己构造数据
原来就是这样&&五楼就是按照这个思路把表套进去了
白手起家, 积分 45, 距离下一级还需 155 积分
论坛徽章:0
看是不是要这种效果。
CREATE TABLE test(id NUMBER(5),name VARCHAR2(20));
INSERT INTO test VALUES(1 ,'a');
INSERT INTO test VALUES(1 ,'s');
INSERT INTO test VALUES(1 ,'d');
INSERT INTO test VALUES(1 ,'f');
INSERT INTO test VALUES(2 ,'g');
INSERT INTO test VALUES(2 ,'h');
INSERT INTO test VALUES(2 ,'j');
INSERT INTO test VALUES(2 ,'k');
INSERT INTO test VALUES(2 ,'l');
INSERT INTO test VALUES(2 ,'q');
INSERT INTO test VALUES(2 ,'w');
INSERT INTO test VALUES(2 ,'e');
INSERT INTO test VALUES(2 ,'r');
INSERT INTO test VALUES(3 ,'t');
INSERT INTO test VALUES(3 ,'y');
INSERT INTO test VALUES(3 ,'u');
INSERT INTO test VALUES(3 ,'i');
INSERT INTO test VALUES(3 ,'o');
SELECT b.id,b.name FROM
(SELECT a.id nid ,MIN(ROWNUM) nSeq FROM test a GROUP BY a.id) a,(SELECT ROWNUM nSeq1,id,name FROM test ORDER BY id) b
WHERE a.nid=b.id
AND b.nseq1&a.nseq+3
ORDER BY b.id,b.nameoracle学习笔记７: 高级分组 - 简书
oracle学习笔记７: 高级分组
group by 用来在原始数据上创建聚合来将数据转化为有用的信息。
基本的group by
列出个个部门的名称，员工总数
select d.dname, count(empno) empcount
from scott.dept d
left outer join scott.emp e
on d.deptno = e.deptno
group by d.dname
order by d.
select列表中的每一列必须包含在group by子句中。如果没有则会导致错误。如：
SQL& select d.dname, d.loc, count(empno) empcount
from scott.emp e
join scott.dept d
on d.deptno = e.deptno
group by d.
select d.dname, d.loc, count(empno) empcount
第 1 行出现错误:
ORA-00979: 不是 GROUP BY 表达式
尽管包含group by子句的select语句输出看上去是按顺序列出的，你不能期待group by每次都排好序地返回数据，如果输出结果必须按照一定的顺序排列，则必须使用order by子句。
--没有排序的group by
select deptno,count(*)
--复杂的sql
--复杂的sql
select /* lst7-4 */
distinct dname,
decode(d.deptno,
(select count(*) from emp where deptno = 1),
(select count(*) from emp where deptno = 2),
(select count(*) from emp where deptno = 3),
(select count(*) from emp where deptno not in (1, 2, 3))) dept_count
from (select distinct deptno from emp) d
join dept d2
on d.deptno = d.
@E:\bjc2016\study\pln lst7-4
上面的写法，会使SQL语句更加复杂难以理解并且难以维护。group by子句极大的简化必须写的sql语句以外，还消除了数据库不必要的IO。
--复杂的sql
select /* lst7-5 */
distinct dname,
count(empno) empcount
join dept d
on d.deptno = d.deptno
group by d.dname
order by d.
@E:\bjc2016\study\pln lst7-5
group by 优点：
使sql语句更具有可读性
书写起来比使用很多相关子查询更简单
减少了重复访问同一个数据块的次数，从而得到更好的性能。
在分组之后，还在数据集上应用了having子句。另一方面，在获取数据行之后，进行分组之前，应用了where子句。having 子句中可以使用运算，函数及子查询。
SQL& --having子句
SQL& select /* lst7-6 */
d.dname, trunc(e.hiredate, 'yyyy') hiredate, count(empno) empcount
from emp e
join dept d
on e.deptno = e.deptno
group by d.dname, trunc(e.hiredate, 'yyyy')
having count(empno) &= 5 and trunc(e.hiredate, 'yyyy') between (select min(hiredate)
from scott.emp) and (select max(hiredate)
from scott.emp)
order by d.
SQL& @E:\bjc2016\study\pln lst7-6
WHERE UPPER(SQL_TEXT) LIKE '%&1%'
WHERE UPPER(SQL_TEXT) LIKE '%lst7-6%'
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
0wcfknkztdxqt, child number 0
-------------------------------------
select /* lst7-6 */
d.dname, trunc(e.hiredate, 'yyyy') hiredate,
count(empno) empcount
from emp e
join dept d
on e.deptno =
group by d.dname, trunc(e.hiredate, 'yyyy') having
count(empno) &= 5 and trunc(e.hiredate, 'yyyy') between (select
min(hiredate)
from scott.emp) and (select max(hiredate)
scott.emp)
order by d.dname
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value:
---------------------------------------------------------------------------
| Operation
| Name | E-Rows |
1Mem | Used-Mem |
---------------------------------------------------------------------------
0 | SELECT STATEMENT
SORT GROUP BY
2048 | 2048
MERGE JOIN CARTESIAN|
TABLE ACCESS FULL
BUFFER SORT
2048 | 2048
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
TABLE ACCESS FULL | EMP
SORT AGGREGATE
TABLE ACCESS FULL
SORT AGGREGATE
TABLE ACCESS FULL
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter((COUNT(*)&=5 AND TRUNC(INTERNAL_FUNCTION("E"."HIREDATE"),'
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
fmyyyy')&= AND TRUNC(INTERNAL_FUNCTION("E"."HIREDATE"),'fmyyyy')&=))
- Warning: basic plan statistics not available. These are only collected when
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system leve
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
已选择41行。
group by的cube扩展
当与group by子句一起使用时，将会使得对每一行都要考虑包含在cube的参数中的所有可能的元素组合。这个运算将会生成比表中实际存在的行数更多的数据行。
-- hr.emplyees表的cube运算
select last_name, first_name
from hr.employees
group by first_name, last_
with emps as
(select /* lst-7 */
last_name, first_name
from hr.employees
group by cube(last_name, first_name))
select rownum, last_name, first_
对于每一对last_name,first_name，cube将会按顺序为每个元素替换为null值。cube生成的数据行在Oracle文档中称为超级聚合行，可以在运算列中加入null值来识别。
SQL& --预测cube返回行数
SQL& with counts as
(select count(distinct first_name) first_name_count,
count(distinct last_name) last_name_count,
count(distinct(first_name || last_name)) full_name_count
from hr.employees)
select first_name_count,
last_name_count,
full_name_count,
first_name_count + last_name_count + full_name_count + 1 total_count
FIRST_NAME_COUNT LAST_NAME_COUNT FULL_NAME_COUNT TOTAL_COUNT
---------------- --------------- --------------- -----------
下面用ＳＱＬ语句模拟cube，可以看出cube为我们节省了许多力气。
--用union all生成cube数据行
with emps as (
select last_name,first_name from hr.employees
mycube as (
select last_name,first_name from emps
select last_name,null first_name from emps
select null last_name,first_name from emps
select null last_name,null first_name from emps
select /*+ gather_plan_statistics */ *
from mycube group by last_name,first_
cube实际应用
sales_history模式中包含年的销售数据。
下面的ＳＱＬ展示2001年的所有销售数据。并想要查看各个产品种类的销售情况汇总，包含基于10年消费者年龄段，收入水平的聚合；按照收入水平而不考虑年龄的汇总；以及按年龄而不考虑收入水平的聚合。
--销售数据的union all查询
with tsales as
(select /* lst7-10 */
s.quantity_sold,
s.amount_sold,
to_char(mod(c.cust_year_of_birth, 10) * 10) || '_' ||
to_char((mod(c.cust_year_of_birth, 10) * 10) + 10) age_range,
nvl(c.cust_income_level, 'A: below 30,000') cust_income_level,
p.prod_name,
p.prod_desc,
p.prod_category,
(pf.unit_cost * s.quantity_sold) total_cost,
s.amount_sold - (pf.unit_cost * s.quantity_sold) profit
from sh.sales s
join sh.customers c
on c.cust_id = s.cust_id
join sh.products p
on p.prod_id = s.prod_id
join sh.times t
on t.time_id = s.time_id
join sh.costs pf
on pf.channel_id = s.channel_id
and pf.prod_id = s.prod_id
and pf.promo_id = s.promo_id
and pf.time_id = s.time_id
where (t.fiscal_year = 2001)),
(select --Q1 - 所有分类通过收入和年龄范围
'Q1' query_tag,
prod_category,
cust_income_level,
age_range,
sum(profit) profit
from tsales
group by prod_category, cust_income_level, age_range
select --Q2 - 所有分类通过年龄范围
'Q2' query_tag,
prod_category,
'ALL INCOME' cust_income_level,
age_range,
sum(profit) profit
from tsales
group by prod_category, 'ALL INCOME', age_range
select --Q3 - 所有分类通过收入
'Q3' query_tag,
prod_category,
cust_income_level,
'ALL AGE' age_range,
sum(profit) profit
from tsales
group by prod_category, cust_income_level, 'ALL AGE'
select --Q4 - 所有分类
'Q4' query_tag,
prod_category,
'ALL INCOME' cust_income_level,
'ALL AGE' age_range,
sum(profit) profit
from tsales
group by prod_category, 'ALL INCOME', 'ALL AGE'
select * from gb order by prod_category,
【语法】NVL (expr1, expr2)
【功能】若expr1为NULL，返回expr2；expr1不为NULL，返回expr1。
注意两者的类型要一致
nvl(c.cust_income_level, 'A: below 30,000') cust_income_level
若c.cust_income_level为null,则返回'A: below 30,000'
【功能】返回x除以y的余数
【参数】x,y，数字型表达式
【返回】数字
select mod(23,8),mod(24,8)
to_char(mod(c.cust_year_of_birth, 10) * 10) || '_' || to_char((mod(c.cust_year_of_birth, 10) * 10) + 10) age_range
上面是求年龄段，如果56，则求出的范围为50_60
--用cube代替union all
with tsales as
(select /* lst7-11 */
s.quantity_sold,
s.amount_sold,
to_char(mod(c.cust_year_of_birth, 10) * 10) || '_' ||
to_char((mod(c.cust_year_of_birth, 10) * 10) + 10) age_range,
nvl(c.cust_income_level, 'A: below 30,000') cust_income_level,
p.prod_name,
p.prod_desc,
p.prod_category,
(pf.unit_cost * s.quantity_sold) total_cost,
s.amount_sold - (pf.unit_cost * s.quantity_sold) profit
from sh.sales s
join sh.customers c
on c.cust_id = s.cust_id
join sh.products p
on p.prod_id = s.prod_id
join sh.times t
on t.time_id = s.time_id
join sh.costs pf
on pf.channel_id = s.channel_id
and pf.prod_id = s.prod_id
and pf.promo_id = s.promo_id
and pf.time_id = s.time_id
where (t.fiscal_year = 2001))
select 'Q' || decode(cust_income_level,
decode(age_range, null, 4, 3),
decode(age_range, null, 2, 1)) query_tag,
prod_category,
cust_income_level,
age_range,
sum(profit) profit
from tsales
group by prod_category, cube(cust_income_level, age_range)
order by prod_category,
decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)
【功能】根据条件返回相应值
【参数】c1, c2, ...,cn,字符型/数值型/日期型，必须类型相同或null
注：值1……n 不能为条件表达式,这种情况只能用case when then end解决
·含义解释：　　
　　decode(条件,值1,翻译值1,值2,翻译值2,...值n,翻译值n,缺省值)　　
　　该函数的含义如下：　　
　　IF 条件=值1 THEN
　　RETURN(翻译值1)
　　ELSIF 条件=值2 THEN
　　RETURN(翻译值2)
　　......
　　ELSIF 条件=值n THEN
　　RETURN(翻译值n)　　
　　RETURN(缺省值)
　　END IF
　　when case 条件=值1 THEN
　　RETURN(翻译值1)
　　ElseCase 条件=值2 THEN
　　RETURN(翻译值2)
　　......
　　ElseCase 条件=值n THEN
　　RETURN(翻译值n)　　
RETURN(缺省值)
'Q' || decode(cust_income_level, null,decode(age_range, null, 4, 3),decode(age_range, null, 2, 1)) query_tag
是返回查询分类标识cust_income_level为null返返回decode(age_range, null, 4, 3)否则返回decode(age_range, null, 2, 1)
cust_income_level==null and age_range==null,query_tag=4
cust_income_level==null and age_range!=null,query_tag=3
cust_income_level!=null and age_range==null,query_tag=2
cust_income_level!=null and age_range!=null,query_tag=1
用grouping()函数排除空值
上面的ＳＱＬ有个问题，尽管总行数与之前使用union all运算符所得到的相一致，一些数据行中的cust_income_level和age_range具有空值，并且有一行的这两列都为空值。当cube的参数中包含生成列的所有可能组合时，每一列都有会产生n-1个空值，n为列表中的数目。在查询的例子中有两个例，因此对于每个唯一的age_range值都会在cust_income_level列上产生空值。对于age_range列来说也适用同样的规则。如果这两列中的数据在某些行上原本就有空值，这些空值就可能出问题。如何辨别数据中原有的空值和cube扩展所插入的值呢？在oracle 8i中引入了grouping()函数，可以用来识别这些超聚合行。被用来作为grouping()函数参数的表达式必须与出现在group by子句中的表达式相匹配。例如
decode(grouping(age_range),1,'ALL AGE',age_range) age_range
age_range检测age_range是否有一行由cube产生的空值，或者是否其在数据库中本身就是空值。如果当前行是由cube生成的超聚合行则返回值为１，对于其它所有情况返回值都为０。
当与case()表达式或decode()函数组合时，超聚合行中的空值可以用一个报告中有用的值替换。这种情况下，decode()看上去是更好的选择，因为它更简便并且grouping()函数仅有两种可能的返回值。
--grouping()函数
--无grouping
with tsales as
(select /* lst7-11 */
s.quantity_sold,
s.amount_sold,
to_char(mod(c.cust_year_of_birth, 10) * 10) || '_' ||
to_char((mod(c.cust_year_of_birth, 10) * 10) + 10) age_range,
nvl(c.cust_income_level, 'A: below 30,000') cust_income_level,
p.prod_name,
p.prod_desc,
p.prod_category,
(pf.unit_cost * s.quantity_sold) total_cost,
s.amount_sold - (pf.unit_cost * s.quantity_sold) profit
from sh.sales s
join sh.customers c
on c.cust_id = s.cust_id
join sh.products p
on p.prod_id = s.prod_id
join sh.times t
on t.time_id = s.time_id
join sh.costs pf
on pf.channel_id = s.channel_id
and pf.prod_id = s.prod_id
and pf.promo_id = s.promo_id
and pf.time_id = s.time_id
where (t.fiscal_year = 2001))
select 'Q' || decode(cust_income_level,
decode(age_range, null, 4, 3),
decode(age_range, null, 2, 1)) query_tag,
prod_category,
cust_income_level,
age_range,
sum(profit) profit
from tsales
group by prod_category, cube(cust_income_level, age_range)
order by prod_category,
--有grouping
--case和decode都可以工作，我更喜欢用decode
with tsales as
(select /* lst7-12 */
s.quantity_sold,
s.amount_sold,
to_char(mod(c.cust_year_of_birth, 10) * 10) || '_' ||
to_char((mod(c.cust_year_of_birth, 10) * 10) + 10) age_range,
nvl(c.cust_income_level, 'A: below 30,000') cust_income_level,
p.prod_name,
p.prod_desc,
p.prod_category,
(pf.unit_cost * s.quantity_sold) total_cost,
s.amount_sold - (pf.unit_cost * s.quantity_sold) profit
from sh.sales s
join sh.customers c
on c.cust_id = s.cust_id
join sh.products p
on p.prod_id = s.prod_id
join sh.times t
on t.time_id = s.time_id
join sh.costs pf
on pf.channel_id = s.channel_id
and pf.prod_id = s.prod_id
and pf.promo_id = s.promo_id
and pf.time_id = s.time_id
where (t.fiscal_year = 2001))
select 'Q' || decode(cust_income_level,
decode(age_range, null, 4, 3),
decode(age_range, null, 2, 1)) query_tag,
prod_category,
case grouping(cust_income_level)
when 1 then
'ALL INCOME'
cust_income_level
end cust_income_level,
decode(grouping(age_range), 1, 'ALL AGE', age_range) age_range,
sum(profit) profit
from tsales
group by prod_category, cube(cust_income_level, age_range)
order by prod_category,
用grouping()扩展报告
另一种使用grouping()的方法是放在having子句中，用来控制在输出中显示哪个层级的聚合。
使用grouping()函数可以被浓缩为对cube扩展中的各行或所有行进行滚动小计。
--在having子句中进行grouping()
with tsales as
(select /* lst7-13 */
s.quantity_sold,
s.amount_sold,
to_char(mod(c.cust_year_of_birth, 10) * 10) || '_' ||
to_char((mod(c.cust_year_of_birth, 10) * 10) + 10) age_range,
nvl(c.cust_income_level, 'A: below 30,000') cust_income_level,
p.prod_name,
p.prod_desc,
p.prod_category,
(pf.unit_cost * s.quantity_sold) total_cost,
s.amount_sold - (pf.unit_cost * s.quantity_sold) profit
from sh.sales s
join sh.customers c
on c.cust_id = s.cust_id
join sh.products p
on p.prod_id = s.prod_id
join sh.times t
on t.time_id = s.time_id
join sh.costs pf
on pf.channel_id = s.channel_id
and pf.prod_id = s.prod_id
and pf.promo_id = s.promo_id
and pf.time_id = s.time_id
where (t.fiscal_year = 2001))
select 'Q' || decode(cust_income_level,
decode(age_range, null, 4, 3),
decode(age_range, null, 2, 1)) query_tag,
prod_category,
case grouping(cust_income_level)
when 1 then
'ALL INCOME'
cust_income_level
end cust_income_level,
decode(grouping(age_range), 1, 'ALL AGE', age_range) age_range,
sum(profit) profit
from tsales
group by prod_category, cube(cust_income_level, age_range)
--having grouping(cust_income_level)=1
--having grouping(age_range)=1
having grouping(cust_income_level)=1 and grouping(age_range)=1
order by prod_category,
上面sql的数据可以看到将grouping()应用到cust_income_level列对所有age_range值跨各个收入层次创建聚合。对age_range列进行这样的操作会得到类似的效果，对所有cust_income_level值进行聚合而不考虑age_range的值。将cube扩展中的所有列作为grouping()函数的参数将会导致聚合被浓缩为一行类似sum(profit)和group by prod_category所实现的功能。但是，使用cube扩展简单修改having子句就可以创建几份不同的报告。
用grouping_id()扩展报告
grouping_id()函数相对grouping()函数来说是相对较新的，在oracle 9i中引入，与grouping()函数在某种程度上是类似的。不同的是grouping()计算一个表达式并返回０或１，而grouping_id()计算一个表达式，确定其参数中的哪一行（如果有的话）用来生成超聚合行，然后创建一个位矢量，并将该值作为整形值返回。
--group_id()位矢量
with rowgen as (
select 1 bit_1,0 bit_0
), cubed as (
grouping_id(bit_1,bit_0) gid,
to_char(grouping(bit_1)) bv_1,
to_char(grouping(bit_0)) bv_0,
decode(grouping(bit_1),1,'GRP BIT 1') gb_1,
decode(grouping(bit_0),1,'GRP BIT 0') gb_0
from rowgen
group by cube(bit_1,bit_0)
select gid,bv_1 || bv_0 bit_vector,
from cubed
group_id()位矢量运行结果
我们己经知道如何使用grouping()通过having子句来控制输出，但考虑数据库效率时，单独的grouping_id()调用可以用来取代所有不同的having grouping()子句。grouping()函数的功能仅仅用来辨别数据行，因为它仅能返回０或１。由于grouping_id()函数返回一个基于位矢量的数值，它可以轻易被用来进行各种不同的比较而不用修改sql语句。
为什么要关注不改变SQL语句就能改变比较呢？如上面基于销售历史的例子中，用户可能会被给出４个输出选项，任意一个或多个可能会被选中。用户的选择可以用来作为使用having grouping_id()函数的一个单独的sql语句，而不是基于having grouping()的不同组全的多个sql语句的输入，因此需要数据库解析sql语句的次数也比就较少。同时这也会使得需要执行的sql语句更少，使用更小的IO,以及更少的内存。
正如使用cube来避免通过union all将多个sql语句结合起来一样，grouping_id()能够避免在应用中使用多个sql语句。
--显示所有收入层次和年龄段的聚合
variable N_ALL_DATA number
--显示所有年龄段的聚合
variable N_AGE_RANGE number
--显示所有收入层次的聚合
variable N_INCOME_LEVEL number
--只给出汇总
variable N_SUMMAY number
:N_ALL_DATA
:N_AGE_RANGE
:N_INCOME_LEVEL
with tsales as
(select /* lst7-15 */
s.quantity_sold,
s.amount_sold,
to_char(mod(c.cust_year_of_birth, 10) * 10) || '_' ||
to_char((mod(c.cust_year_of_birth, 10) * 10) + 10) age_range,
nvl(c.cust_income_level, 'A: below 30,000') cust_income_level,
p.prod_name,
p.prod_desc,
p.prod_category,
(pf.unit_cost * s.quantity_sold) total_cost,
s.amount_sold - (pf.unit_cost * s.quantity_sold) profit
from sh.sales s
join sh.customers c
on c.cust_id = s.cust_id
join sh.products p
on p.prod_id = s.prod_id
join sh.times t
on t.time_id = s.time_id
join sh.costs pf
on pf.channel_id = s.channel_id
and pf.prod_id = s.prod_id
and pf.promo_id = s.promo_id
and pf.time_id = s.time_id
where (t.fiscal_year = 2001))
select 'Q' || to_char(grouping_id(cust_income_level,age_range)+1) query_tag,
prod_category,
decode(grouping(cust_income_level),1,'ALL INCOME',cust_income_level) cust_income_level,
decode(grouping(age_range),1,'ALL AGE',age_range) age_range,
sum(profit) profit
from tsales
group by prod_category,cube(cust_income_level,age_range)
having grouping_id(cust_income_level,age_range)+1 in(:N_ALL_DATA,:N_AGE_RANGE,:N_INCOME_LEVEL,:N_SUMMAY)
order by prod_category,
使用grouping函数也可以实现同的结果，但需要在having子句中进行一些测试。示例销售历史数据查询在cube参数中只包含两列。在having子句中总共需要进行４次测试，因为grouping子句将会返回１或者０，每一列有两个可能的值。从而需要４次测试。如果３列，则需要８次，所需的测试次数将会是２的n次方，其中n为cube中参数列或表达式的个数。
用grouping()代替grouping_id()的having子句的例子
--显示所有收入层次和年龄段的聚合
variable N_ALL_DATA number
--显示所有年龄段的聚合
variable N_AGE_RANGE number
--显示所有收入层次的聚合
variable N_INCOME_LEVEL number
--只给出汇总
variable N_SUMMAY number
:N_ALL_DATA
:N_AGE_RANGE
:N_INCOME_LEVEL
with tsales as
(select /* lst7-16 */
s.quantity_sold,
s.amount_sold,
to_char(mod(c.cust_year_of_birth, 10) * 10) || '_' ||
to_char((mod(c.cust_year_of_birth, 10) * 10) + 10) age_range,
nvl(c.cust_income_level, 'A: below 30,000') cust_income_level,
p.prod_name,
p.prod_desc,
p.prod_category,
(pf.unit_cost * s.quantity_sold) total_cost,
s.amount_sold - (pf.unit_cost * s.quantity_sold) profit
from sh.sales s
join sh.customers c
on c.cust_id = s.cust_id
join sh.products p
on p.prod_id = s.prod_id
join sh.times t
on t.time_id = s.time_id
join sh.costs pf
on pf.channel_id = s.channel_id
and pf.prod_id = s.prod_id
and pf.promo_id = s.promo_id
and pf.time_id = s.time_id
where (t.fiscal_year = 2001))
select 'Q' || to_char(grouping_id(cust_income_level,age_range)+1) query_tag,
prod_category,
decode(grouping(cust_income_level),1,'ALL INCOME',cust_income_level) cust_income_level,
decode(grouping(age_range),1,'ALL AGE',age_range) age_range,
sum(profit) profit
from tsales
group by prod_category,cube(cust_income_level,age_range)
(bin_to_num(grouping(cust_income_level),grouping(age_range))+1 = :N_ALL_DATA)
or (bin_to_num(grouping(cust_income_level),grouping(age_range))+1 = :N_AGE_RANGE)
or (bin_to_num(grouping(cust_income_level),grouping(age_range))+1 = :N_INCOME_LEVEL)
or (bin_to_num(grouping(cust_income_level),grouping(age_range))+1 = :N_SUMMAY)
order by prod_category,
１.　使用grouping可以判断该行是数据库中本来的行，还是有统计产生的行
grouping值为0时说明这个值是数据库中本来的值，为1说明是统计的结果(也可以说该列为空时是1，不为空时是0)
2.　GROUPING_ID()函数可以接受一列或多列，返回GROUPING位向量的十进制值。GROUPING位向量的计算方法是将按照顺序对每一列调用GROUPING函数的结果组合起来，所以说01和10的值不一样的
3.　group_id的使用 当group by子句中重复使用一个列时,通过group_id来去除重复值
grouping sets与rollup()
group by的grouping sets()扩展在oracle 9i中初次登场，前面的例子中的整个group by...having子句可以用group by grouping sets()替换。
with tsales as
(select /* lst7-17 */
s.quantity_sold,
s.amount_sold,
to_char(mod(c.cust_year_of_birth, 10) * 10) || '_' ||
to_char((mod(c.cust_year_of_birth, 10) * 10) + 10) age_range,
nvl(c.cust_income_level, 'A: below 30,000') cust_income_level,
p.prod_name,
p.prod_desc,
p.prod_category,
(pf.unit_cost * s.quantity_sold) total_cost,
s.amount_sold - (pf.unit_cost * s.quantity_sold) profit
from sh.sales s
join sh.customers c
on c.cust_id = s.cust_id
join sh.products p
on p.prod_id = s.prod_id
join sh.times t
on t.time_id = s.time_id
join sh.costs pf
on pf.channel_id = s.channel_id
and pf.prod_id = s.prod_id
and pf.promo_id = s.promo_id
and pf.time_id = s.time_id
where (t.fiscal_year = 2001))
select 'Q' || to_char(grouping_id(cust_income_level,age_range)+1) query_tag,
prod_category,
decode(grouping(cust_income_level),1,'ALL INCOME',cust_income_level) cust_income_level,
decode(grouping(age_range),1,'ALL AGE',age_range) age_range,
sum(profit) profit
from tsales
group by prod_category,grouping sets(
rollup(prod_category), --产品分类小计
(cust_income_level),--产品分类和收入层次
(age_range),　--产品分类和年龄范围
(cust_income_level,age_range)　--产品分类，年龄范围和收入层次
--having group_id() & 1
order by prod_category,
group by cube having grouping_id()与group by grouping sets一个主要的区别是，前者能将变量设定为正确的值来简便修改输出，而后者的输出不能修改，除非修改或动态生成sql语句。修改sql语句意味着需要维护更多的代码并且占用更多的数据库资源。最好尽量避免使用动态生成sql语句，因为它会消耗的数据库资源更多，并且在出现问题时难以检修。
某些时候grouping_sets()扩展会导致输出中出现重复。重复是由rollup(prod_category)产生的。可以通过去掉rollup()然后重新运行得到验证，重复的行将不复存在。但是，每种产品种类的总计也不存在了。解决的办法就是使用group_id()函数标记重复的行，并将其插入到having子句中。
在上面的sql中将--having group_id() & 1 改成　having group_id() & 1
这样，输出结果就如预期的那样不包含重复的行了。有趣的是如果将rollup(prod_category)改成null,去掉having子句，同时还能得到预期的输出。代码如下：
with tsales as
(select /* lst7-17-1 */
s.quantity_sold,
s.amount_sold,
to_char(mod(c.cust_year_of_birth, 10) * 10) || '_' ||
to_char((mod(c.cust_year_of_birth, 10) * 10) + 10) age_range,
nvl(c.cust_income_level, 'A: below 30,000') cust_income_level,
p.prod_name,
p.prod_desc,
p.prod_category,
(pf.unit_cost * s.quantity_sold) total_cost,
s.amount_sold - (pf.unit_cost * s.quantity_sold) profit
from sh.sales s
join sh.customers c
on c.cust_id = s.cust_id
join sh.products p
on p.prod_id = s.prod_id
join sh.times t
on t.time_id = s.time_id
join sh.costs pf
on pf.channel_id = s.channel_id
and pf.prod_id = s.prod_id
and pf.promo_id = s.promo_id
and pf.time_id = s.time_id
where (t.fiscal_year = 2001))
select 'Q' || to_char(grouping_id(cust_income_level,age_range)+1) query_tag,
prod_category,
decode(grouping(cust_income_level),1,'ALL INCOME',cust_income_level) cust_income_level,
decode(grouping(age_range),1,'ALL AGE',age_range) age_range,
sum(profit) profit
from tsales
group by prod_category,grouping sets(
--rollup(prod_category), --产品分类小计
(cust_income_level),--产品分类和收入层次
(age_range),　--产品分类和年龄范围
(cust_income_level,age_range)　--产品分类，年龄范围和收入层次
--having group_id() & 1
order by prod_category,profit
group by的rollup()扩展也可以单独用来计算否则将会需要由union all结合起来的多个查询完成的小计。
例如：创建显示器所有名字以Sul开头的消费者各自的购买总额报告，并且要求对每个消费者分别按年，产品分类进行小计，还要有所有消费的总计。这种类型的任务可以使用rollup()完成。
--rollup()小计
with mysales as (
select c.cust_last_name || ',' || c.cust_first_name cust_name,
p.prod_category,
to_char(trunc(time_id,'YYYY'),'YYYY') sale_year,
p.prod_name,
s.amount_sold
from sh.sales s
join sh.products p on p.prod_id=s.prod_id
join sh.customers c on c.cust_id=s.cust_id
where c.cust_last_name like 'Sul%'
decode(grouping(m.cust_name),1,'GRAND TOTAL',m.cust_name) cust_name,
decode(grouping(m.sale_year),1,'TOTAL BY YEAR',m.sale_year) sale_year,
decode(grouping(m.prod_category),1,'TOTAL BY CATEGORY',m.prod_category) prod_category,
sum(m.amount_sold) amount_sold
from mysales m
group by rollup(m.cust_name,m.prod_category,m.sale_year)
order by grouping(m.cust_name), 1,2,3;
注意decode()和grouping()函数再一次被用来表示小计行。使用grouping(m.cust_name)将总计显示在报告的最后。由于这个值&0的唯一情况就是当计算所有消费者总计时，这个总计值只会出现在报告的最后。
group by的局限性
LOB列，嵌套表或数组不能用做group by表达式的一部分
SQL& with lobtest as (
select to_clob(d.dname) dname
from scott.emp e
join scott.dept d on d.deptno=e.deptno
select l.dname
from lobtest l
group by l.
group by l.dname
第 8 行出现错误:
ORA-00932: 数据类型不一致: 应为 -, 但却获得 CLOB
不允许使用标量子查询表达式
SQL& select d.dname,count(empno) empcount
from scott.emp e
join scott.dept d on d.deptno=e.deptno
group by (select dname from scott.dept d2 where d2.dname = d.dname )
order by d.
group by (select dname from scott.dept d2 where d2.dname = d.dname )
第 4 行出现错误:
ORA-22818: 这里不允许出现子查询表达式
如果group by子句引用任何对象类型的列则查询不能并行化
create type dept_location_type as object
street_address varchar2(40),
postal_code varchar2(10),
city varchar2(30),
state_province varchar2(10),
country_id char(2),
order member function match (e dept_location_type) return integer
create or replace type body
dept_location_type
as order member function match (e dept_location_type) return integer
if city &e.city then
return -1;
elsif city & e.city then
create table deptobj
select d.deptno,d.dname
from scott.
alter table
deptobj add (dept_location dept_location_type);
update deptobj set dept_location=dept_location_type('1234 fenmenao st','453076','ShenZhen','GuangDong','GD') where deptno=1;
update deptobj set dept_location=dept_location_type('345 Leshan st','123456','LeShan','SiCuan','SC') where deptno=2;
update deptobj set dept_location=dept_location_type('345 ChongQing st','123456','ChongQing','ChongQing','CQ') where deptno=3;
update deptobj set dept_location=dept_location_type('345 ChangChun st','123456','ChangChun','GuiYang','GY') where deptno=4;
--对象列的并行group by
select /*+ gather_plan_statictics parallel(e 2)*/
d.dept_location,count(e.ename) ecount
from scott.emp e,deptobj d
where e.deptno=d.deptno
group by d.dept_location
order by d.dept_
对象列的并行group by
dept_location类型体中的成员函数匹配用来进行城市值的比较，然后使用group by将雇员按城市分组。最后一个列出的局限性在后期的版本是可以工作的。
Ｏracle以group by子句扩展的形式为SQL开发者提供了一些极佳的工具，帮助我们不仅能够减少代码量，并且能提高数据库效率。大多数的特性也要与其它不同的功能进行组合。
group by总结
12年软件开发，一线码农。
Spark SQL, DataFrames and Datasets Guide Overview SQL Datasets and DataFrames 开始入门 起始点: SparkSession 创建 DataFrames 无类型的Dataset操作 (aka Dat...
Spark SQL, DataFrames and Datasets Guide Overview SQL Datasets and DataFrames 开始入门 起始点: SparkSession 创建 DataFrames 无类型的Dataset操作 (aka Dat...
1:使用SHOW语句找出在服务器上当前存在什么数据库： mysql& SHOW DATABASES; 2:2、创建一个数据库MYSQLDATA mysql& CREATE DATABASE MYSQLDATA; 3:选择你所创建的数据库 mysql& USE MYSQLDA...
1.简介 数据存储有哪些方式？电子表格，纸质文件，数据库。 那么究竟什么是关系型数据库？ 目前对数据库的分类主要是关系型和非关系型两种，关系型的主要代表有oracle，db2，mysql，sqlserver等，非关系型数据库也称作nosql， 主要有mongodb,hbas...
入门心法：要练此功，先废其功。（先忘记已学的其他语言，用T-SQL来思考。）所需代码：https://github.com//Microsoft-SQL-Server-2008-T-SQL-Fundamentals 目录 第一章 T-SQL查询和编程基础 ...
文/萧路遥 因字而知不相识， 隔屏常聚论诗篇。 异国回转归乡里， 几度相逢睹笑颜。
下班时候，她突然发来微信，她公司楼下有个台湾美食节，因为没见过，说想去看看。其实我对这个什么台湾美食节无感，毕竟见过好多次，也去过好多次，本想着能过过嘴瘾，但是每次都让人失望。 但是她开口了，我也算是有个理由去见她，所以迅速跑回家洗漱了一把，换了身衣服去找她了。 那个美食节...
要事优先，这个概念，在无数个时间管理课程中，都被提到，并且被提到了很多次。 可见，事情的重要性是我们必须关注的内容。 今天的帖子，我们要从工具和流程的层面，给大家讲一讲时间管理中的“要事优先”。 关注“一周”的时间表而不是“一天” 希望大家从今天开始，首先做到你的视角是一周...