반응형
Q: 각 도의 시군구를 방문한 내국인 방문객 합계를 구하고 년도별 방문객 최대도시, 최저도시를 조회하라.
hint:
- 각년도별 도시 방문객 합계 구한뒤 순위 : table Q
- 각 년도별 도시 방문객 합계 구한뒤 순위 구하고 꼴지등수만 골라내기!!!!!!!! : table W.
- 두 테이블 묵시적 조인.
1)각 도(province) 의 시군구 (district) 방문한 내국방문객 합계 sum(native_cnt)
SELECT p.prvn_name,d.distc_name,SUBSTR(f.basis_date,1,4) AS year, SUM(f.native_cnt) as sum
FROM province p
JOIN district d ON p.prvn_cd = d.prvn_cd
JOIN attraction att ON d.prvn_cd = att.prvn_cd AND d.distc_cd = att.distc_cd
JOIN figure f ON att.prvn_cd = f.prvn_cd AND att.distc_cd = f.distc_cd AND att.attrc_cd = f.attrc_cd
GROUP BY p.prvn_cd, d.distc_cd, year
ORDER BY p.prvn_name, year,sum desc;
2) 순위매기기.(table Q)
SELECT a.prvn_name,a.distc_name,
(CASE @name WHEN year THEN @rownum:=@rownum+1 ELSE @rownum:=1 END) as rank, @name:= year as year , sum
FROM
(SELECT p.prvn_name,d.distc_name,SUBSTR(f.basis_date,1,4) AS year, SUM(f.native_cnt) as sum
FROM province p
JOIN district d ON p.prvn_cd = d.prvn_cd
JOIN attraction att ON d.prvn_cd = att.prvn_cd AND d.distc_cd = att.distc_cd
JOIN figure f ON att.prvn_cd = f.prvn_cd AND att.distc_cd = f.distc_cd AND att.attrc_cd = f.attrc_cd
GROUP BY p.prvn_cd, d.distc_cd, year
ORDER BY p.prvn_name, year,sum desc)a,
(SELECT @RWONUM :=0 AS ROWNUM, @NAME='abc' as name from dual)b;
3) rank 최대값 (즉 꼴등) 구하기.
SELECT c.prvn_name, year, max(rank)
FROM (SELECT a.prvn_name,a.distc_name,
(CASE @name WHEN year THEN @rownum:=@rownum+1 ELSE @rownum:=1 END) as rank, @name:= year as year , sum
FROM
(SELECT p.prvn_name,d.distc_name,SUBSTR(f.basis_date,1,4) AS year, SUM(f.native_cnt) as sum
FROM province p
JOIN district d ON p.prvn_cd = d.prvn_cd
JOIN attraction att ON d.prvn_cd = att.prvn_cd AND d.distc_cd = att.distc_cd
JOIN figure f ON att.prvn_cd = f.prvn_cd AND att.distc_cd = f.distc_cd AND att.attrc_cd = f.attrc_cd
GROUP BY p.prvn_cd, d.distc_cd, year
ORDER BY p.prvn_name, year,sum desc)a,
(SELECT @RWONUM :=0 AS ROWNUM, @NAME='abc' as name from dual)b)c
group by c.prvn_name,year;
.
.
.
★ 최종 쿼리.
SELECT q.prvn_name, q.year,
MAX(IF(q.rank=1 ,q.distc_name,0)) as max_city, // 최대 도시.
MAX(IF(q.prvn_name=w.prvn_name
AND q.year=w.year
AND q.rank=w.max_rank,
q.distc_name,0)) as min_city
// 최저도시: 조건이 3개이다. table q와 w의 도가 같고(각 도의 ) 년도가(년도별 ) 같은 경우
rank값이 max_rank와 일치할때 각 도의 년도별 최저 도시 추출가능..
FROM
(SELECT a.prvn_name,a.distc_name,
(CASE @name WHEN year THEN @rownum:=@rownum+1 ELSE @rownum:=1 END) as rank, @name:= year as year , sum
FROM
(SELECT p.prvn_name,d.distc_name,SUBSTR(f.basis_date,1,4) AS year, SUM(f.native_cnt) as sum
FROM province p
JOIN district d ON p.prvn_cd = d.prvn_cd
JOIN attraction att ON d.prvn_cd = att.prvn_cd AND d.distc_cd = att.distc_cd
JOIN figure f ON att.prvn_cd = f.prvn_cd AND att.distc_cd = f.distc_cd AND att.attrc_cd = f.attrc_cd
GROUP BY p.prvn_cd, d.distc_cd, year
ORDER BY p.prvn_name, year,sum desc)a,
(SELECT @RWONUM :=0 AS ROWNUM, @NAME='abc' as name from dual)b
)q, // table Q: 각 년도별 도시 방문객 합계 구한 뒤 순위 매긴 테이블.
(SELECT c.prvn_name, year, max(rank) as max_rank
FROM (SELECT a.prvn_name,a.distc_name,
(CASE @name WHEN year THEN @rownum:=@rownum+1 ELSE @rownum:=1 END) as rank, @name:= year as year , sum
FROM
(SELECT p.prvn_name,d.distc_name,SUBSTR(f.basis_date,1,4) AS year, SUM(f.native_cnt) as sum
FROM province p
JOIN district d ON p.prvn_cd = d.prvn_cd
JOIN attraction att ON d.prvn_cd = att.prvn_cd AND d.distc_cd = att.distc_cd
JOIN figure f ON att.prvn_cd = f.prvn_cd AND att.distc_cd = f.distc_cd AND att.attrc_cd = f.attrc_cd
GROUP BY p.prvn_cd, d.distc_cd, year
ORDER BY p.prvn_name, year,sum desc)a,
(SELECT @RWONUM :=0 AS ROWNUM, @NAME='abc' as name from dual)b)c
group by c.prvn_name,year)w // table W: 최저도시 구하기위해 꼴지 등수 값 골라내기.
GROUP BY q.prvn_name, q.year
반응형
'Back-end > DataBase' 카테고리의 다른 글
| SQL - Function 함수란? (0) | 2018.06.21 |
|---|---|
| SQL -JOIN 조인이란? (INNER / OUTER) (0) | 2018.06.21 |
| SQL 통계_ 행열 뒤집기. ( 시계열 표현 ) (0) | 2018.06.20 |
| SQL 통계_ 서브쿼리 Sub Query_ Rank 순위매기기 (0) | 2018.06.20 |
| SQL 통계_ 서브쿼리 Sub Query (0) | 2018.06.20 |