select sale_person,sum(sum(sale_amount)) over (partition by sale_person) as total_per_person,
avg(sum(sale_amount)) over (partition by sale_person) as avg_per_person,
sale_region,
sum(sum(sale_amount)) over (partition by sale_region) as total_per_region,
min(sum(sale_amount)) over (partition by sale_region) as min_per_region
from sales_fact_2006
group by sale_person,sale_region
order by sale_person,sale_region;
sales_fact_2006 的表结构如下:
SALE_YEAR NOT NULL VARCHAR2(4)
SALE_QUARTER NOT NULL NUMBER(1)
SALE_MONTH NOT NULL NUMBER(2)
SALE_BOOK_ID NOT NULL VARCHAR2(20)
SALE_REGION NOT NULL VARCHAR2(10)
SALE_PERSON NOT NULL VARCHAR2(10)
SALE_AMOUNT NOT NULL NUMBER(10,2) 能不能帮忙 解释一下这个 语句 sum(sum(....))是什么 , partition by sale_region 这个分组 是基于 group by sale_person, sale_region 上的 还是对原表的分组计算!
avg(sum(sale_amount)) over (partition by sale_person) as avg_per_person,
sale_region,
sum(sum(sale_amount)) over (partition by sale_region) as total_per_region,
min(sum(sale_amount)) over (partition by sale_region) as min_per_region
from sales_fact_2006
group by sale_person,sale_region
order by sale_person,sale_region;
sales_fact_2006 的表结构如下:
SALE_YEAR NOT NULL VARCHAR2(4)
SALE_QUARTER NOT NULL NUMBER(1)
SALE_MONTH NOT NULL NUMBER(2)
SALE_BOOK_ID NOT NULL VARCHAR2(20)
SALE_REGION NOT NULL VARCHAR2(10)
SALE_PERSON NOT NULL VARCHAR2(10)
SALE_AMOUNT NOT NULL NUMBER(10,2) 能不能帮忙 解释一下这个 语句 sum(sum(....))是什么 , partition by sale_region 这个分组 是基于 group by sale_person, sale_region 上的 还是对原表的分组计算!
sum(sale_amount)理解为一列,partition by sale_region是对sum(sale_amount)分组
多次分组,为什么不用ROLLUP或者CUBE有什么特殊需求?
sum(total_per_person) total_per_person,
avg(avg_per_person) avg_per_person,
sum(total_per_region) total_per_region,
min(min_per_region) min_per_region
from (
select sale_person,
sale_region,
sum(sale_amount) over (partition by sale_person) as total_per_person,
sum(sale_amount) over (partition by sale_person) as avg_per_person,
sum(sale_amount) over (partition by sale_region) as total_per_region,
sum(sale_amount) over (partition by sale_region) as min_per_region
from sales_fact_2006
)
group by sale_person,sale_region
order by sale_person,sale_region;
网上很多资料,参考:
http://blog.csdn.net/youjianbo_han_87/archive/2009/06/25/4297867.aspx