oracle 多维度查询

来源：互联网发布：淘宝3大组装电脑卖家编辑：程序博客网时间：2024/05/17 11:07

select * from rpt_shipping_sum t
/*----------------------------------rollup cause ---------------------------------------------------------------*/
根据纬度计算出每一级纬度的subtotal ,一级一级的最后计算出总的total
select out_date, grade ,sum(panel_qty) , count(*) from rpt_shipping_sum t group by rollup(out_date,grade)
/*----------------------------------cube cauese-----------------------------------------------------------------*/
首先算出总的计算值，然后二级纬度的值，然后，每一级的纬度值出来，就是各个子集
select out_date,grade,sum(panel_qty) from rpt_shipping_sum t group by cube(out_date,grade)
/*----------------------------------Grouping Function-----------------------------------------------------------*/
用来判断该行资料中的纬度是否是summary的subtotal的纬度
select out_date ,grade,sum(panel_qty),count(*),grouping(out_date),grouping(grade) from rpt_shipping_sum t group by rollup(out_date,grade)
/*----------------------------------Grouping Sets Function------------------------------------------------------*/
可以同时在一个sql 中按照两种纬度summary data结果会顺序排下来
select out_date, grade , sum(panel_qty) from rpt_shpping_sum t group by grouping sets(out_date,grade)
/*----------------------------------as of timestamp ------------------------------------------------------------*/
查询历史时间点的table 中的数据
select * from wip_panel_det as of timestamp to_timestamp('2005/09/22 15:00:00','yyyy/mm/dd hh24:mi:ss')
/*----------------------------------with 子句重用子查询---------------------------------------------------------*/
查询的结果作为with子语句，可以改变纬度的查询各个纬度的summary data
with summary as(
select out_date ,grade,sum(panel_qty) total_qty from rpt_shipping_sum t group by out_date,grade
)
select out_date ,total_qty from summary where total_qty < (select sum(total_qty) * 1/3 from summary)
/*----------------------------------nvl2 function -------------------------------------------------------------*/
如果第一个表达式为空，则结果为第三个表达式的值，否则为第二个表达式的值
select nvl2(null, 1,2) from dual
/*----------------------------------nullif function--------------------------------------------------*/
如果两个表达式的值相等，则返回null ,否则返回第一个表达式的值
select nullif(100,101) from dual