[sql] 과금 구간별 10단위 ntile ,중앙값 구하기

 

 

 

 

use workdb
go

select		a.ntile, 
				count(distinct accountid) 'pu',    
				min(sales) 'min_sales', 
				max(sales) 'max_sales', 
				avg(sales) 'avg_sales',
				sum(sales)'total.sum'
from		
(
select		server, accountid, sum(price) 'sales',  ntile(10) over ( order by sum(price)  desc) as ntile
from		dbo.purchase a with (nolock)
join			dbo.cm_purchase b with (nolock) on a.clientproductid=b.clientproductid
where
				cast(time as date) between '2019-09-01' and ' 2019-09-30'    --- 기간
group by
				server, accountid
) as a
group by
				a.ntile
order by 1

---- 10단위별 pu 및 매출 분포(중앙값)

select		aa.ntile, aa.median_sales 
from		
(
select		a.ntile
				,percentile_disc(0.5) within group (order by a.sales desc )  over (partition by a.ntile)  'median_sales'
from		
(
select		server, accountid, sum(price) 'sales',  ntile(10) over ( order by sum(price)  desc) as ntile
from		dbo.purchase a with (nolock)
join			dbo.cm_purchase b with (nolock) on a.clientproductid=b.clientproductid
where
				cast(time as date) between '2019-09-01' and ' 2019-09-30'  -- 기간
group by
				server, accountid
) as a
) as aa 
group by
				aa.ntile, aa.median_sales