oracle sql 分组统计

sql分组统计语句(转载)
1.报表合计专用的Rollup函数
          销售报表
   广州      1月       2000元
   广州      2月       2500元
   广州                  4500元
   深圳      1月       1000元
   深圳      2月       2000元
   深圳                  3000元
   所有地区          7500元

以往的查询SQL:
Select   area,month,sum(money) from SaleOrder group by area,month
然后广州,深圳的合计和所有地区合计都需要在程序里自行累计

1.其实可以使用如下SQL:    Select area,month,sum(total_sale) from SaleOrder group by rollup(area,month)就能产生和报表一模一样的纪录
2.如果year不想累加,可以写成    Select year,month,area,sum(total_sale) from SaleOrder group by year, rollup(month,area)    另外Oracle 9i还支持如下语法:    Select year,month,area,sum(total_sale) from SaleOrder group by rollup( (year,month),area)
3.如果使用Cube(area,month)而不是RollUp(area,month),除了获得每个地区的合计之外,还将获得每个月份的合计,在报表最后显示。
4.Grouping让合计列更好读
   RollUp在显示广州合计时,月份列为NULL,但更好的做法应该是显示为"所有月份"
   Grouping就是用来判断当前Column是否是一个合计列,1为yes,然后用Decode把它转为"所有月份"   Select   Decode(Grouping(area),1,'所有地区',area) area,
           Decode(Grouping(month),1,'所有月份',month),
           sum(money)
   From SaleOrder
   Group by RollUp(area,month);
2.对多级层次查询的start with.....connect by
    比如人员组织,产品类别,Oracle提供了很经典的方法
SELECT LEVEL, name, emp_id,manager_emp_id
FROM employee
START WITH manager_emp_id is null
CONNECT BY PRIOR emp_id = manager_emp_id;
上面的语句demo了全部的应用,start with指明从哪里开始遍历树,如果从根开始,那么它的manager应该是Null,如果从某个职员开始,可以写成emp_id='11'
CONNECT BY 就是指明父子关系,注意PRIOR位置
另外还有一个LEVEL列,显示节点的层次
3.更多报表/分析决策功能
3.1 分析功能的基本结构
      分析功能() over( partion子句,order by子句,窗口子句)
      概念上很难讲清楚,还是用例子说话比较好.       
3.2 Row_Number 和 Rank, DENSE_Rank
     用于选出Top 3 sales这样的报表
     当两个业务员可能有相同业绩时,就要使用Rank和Dense_Rank
     比如
               金额     RowNum   Rank   Dense_Rank
     张三 4000元     1              1         1
     李四 3000元     2              2         2
     钱五 2000元     3              3         3
     孙六 2000元     4              3         3
     丁七 1000元     5              5         4
     这时,应该把并列第三的钱五和孙六都选进去,所以用Ranking功能比RowNumber保险.至于Desnse还是Ranking就看具体情况了。
     SELECT salesperson_id, SUM(tot_sales) sp_sales,
     RANK( ) OVER (ORDER BY SUM(tot_sales) DESC) sales_rank
     FROM orders
     GROUP BY salesperson_id
3.3 NTILE 把纪录平分成甲乙丙丁四等
         比如我想取得前25%的纪录,或者把25%的纪录当作同一个level平等对待,把另25%当作另一个Level平等对待
     SELECT cust_nbr, SUM(tot_sales) cust_sales,
     NTILE(4) OVER (ORDER BY SUM(tot_sales) DESC) sales_quartile
     FROM orders
     GROUP BY cust_nbr
     ORDER BY 3,2 DESC;NTITLE(4)把纪录以 SUM(tot_sales)排序分成4份.
3.4 辅助分析列和Windows Function
      报表除了基本事实数据外,总希望旁边多些全年总销量,到目前为止的累计销量,前后三个月的平均销量这样的列来参考.
     这种前后三个月的平均和到目前为止的累计销量就叫windows function, 见下例     SELECT month, SUM(tot_sales) monthly_sales,
            SUM(SUM(tot_sales)) OVER (ORDER BY month
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) max_preceeding
     FROM orders
     GROUP BY month
     ORDER BY month;



     SELECT month, SUM(tot_sales) monthly_sales,
            AVG(SUM(tot_sales)) OVER (ORDER BY month
            ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) rolling_avg  
     FROM orders
     GROUP BY month
     ORDER BY month;
     Windows Function的关键就是Windows子句的几个取值
     1 PRECEDING 之前的一条记录
     1 FOLLOWING 之后的一条记录
     UNBOUNDED PRECEDING 之前的所有记录
     CURRENT ROW 当前纪录
4.SubQuery总结
   SubQuery天天用了,理论上总结一下.SubQuery 分三种
   1.Noncorrelated 子查询    最普通的样式.
   2.Correlated Subqueries   把父查询的列拉到子查询里面去,头一回cyt教我的时候理解了半天.
   3.Inline View                            也被当成最普通的样式用了.
   然后Noncorrelated 子查询又有三种情况
   1.返回一行一列     where price < (select max(price) from goods )
   2.返回多行一列     where price>= ALL (select price from goods where type=2)
                           or where NOT price< ANY(select price from goods where type=2)
                               最常用的IN其实就是=ANY()
   3.返回多行多列     一次返回多列当然就节省了查询时间            UPDATE monthly_orders
           SET (tot_orders, max_order_amt) =
              (SELECT COUNT(*), MAX(sale_price)
           FROM cust_order)

         
DELETE FROM line_item
           WHERE (order_nbr, part_nbr) IN
            (SELECT order_nbr, part_nbr FROM cust_order c)
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值