D-63-데이터분석

1. MySQL 순위 함수
   1,1,2,3 A라는 값으로 순위를 매길때
   

• row_number : 1,2,3,4
• rank : 1,1,3,4
• dense_rank : 1,1,2,3

2. row_number() over()

select City,count(StoreNumber) store_cnt
from (select City
,StoreNumber
,round(sum(saleDollars)) sum_sale
,row_number() over(order by sum(SaleDollars) desc) store_rank
from city_data
group by 1,2
) t1
where t1.store_rank <=20
group by t1.City
order by store_cnt desc
;

3. with절 vs 서브쿼리

 with store_rank_table as(
select City
,StoreNumber
,round(sum(saleDollars)) sum_sale
,row_number() over(order by sum(SaleDollars) desc) store_rank
from city_data
group by 1,2
)
select City, count(StoreNumber) store_cnt
from store_rank_table
where store_rank <=20
group by City
order by store_cnt desc;

select City,count(StoreNumber) store_cnt
from (select City
,StoreNumber
,round(sum(saleDollars)) sum_sale
,row_number() over(order by sum(SaleDollars) desc) store_rank
from city_data
group by 1,2
) t1
where t1.store_rank <=20
group by t1.City
order by store_cnt desc
;