商品带来其它商品的销售统计 UPC带货能力 （二）

select * from #temp 

 

 

count() over()、count() over(order by)、count() over(partition by)

select 
 order_id
 ,upc
 ,sum(sales) over() as 销售额
 ,count(order_id) over() as 总订单数
 ,count(order_id) over(order by order_id) as 递增订单数
 ,count(order_id) over(partition by order_id) as 分区订单数
from #temp

 

select 
 order_id, upc
 ,count(order_id) over(partition by order_id) as par_orders
 ,sum(sales) over(partition by order_id) as par_order_sales
 ,sum(units) over(partition by order_id) as par_order_units
from #temp

 

select 
 upc
 ,sum(par_orders) as sum_upc
 ,sum(par_order_sales) as sum_sales
 ,sum(par_order_units) as sum_units
from(
--1
select 
 order_id, upc
 ,count(order_id) over(partition by order_id) as par_orders
 ,sum(sales) over(partition by order_id) as par_order_sales
 ,sum(units) over(partition by order_id) as par_order_units
from #temp
--
) as a
group by upc