[MySQL] SQL ZOO window functions 답 해설

Window functions - SQLZOO

표가 있어서 이해가 잘 됐다. 전에 NSS문제는 표가 없어서 힘들었따...

정리는 내일 할거다. 게임 한판, 넷플릭스 유튜브 보다 자야징~~~

 

#1 Warming up

WHERE절 AND사용

SELECT lastName, party, votes
FROM ge
WHERE constituency = 'S14000024'AND yr = 2017
ORDER BY votes DESC

 

#2 Who won?

WHERE절 AND사용

SELECT party, votes,
       RANK() OVER (ORDER BY votes DESC) as posn
  FROM ge
 WHERE constituency = 'S14000024' AND yr = 2017
ORDER BY party

 

#3 PARTITION BY

WHERE절 AND사용

SELECT yr,party, votes,
      RANK() OVER (PARTITION BY yr ORDER BY votes DESC) as posn
  FROM ge
 WHERE constituency = 'S14000021'
ORDER BY party,yr

 

#4 Edinburgh Constituency

(Q22 AND '(8) Computer Science') OR (Q22 AND subject='(H) Creative Arts and Design')

이렇게 할 수도 있을 것 같다. 

과목별 응답자를 출력한다.

SELECT constituency,party, votes,
       RANK() OVER (PARTITION BY constituency ORDER BY votes DESC) AS winner
FROM ge
WHERE constituency BETWEEN 'S14000021' AND 'S14000026'
AND yr  = 2017
ORDER BY winner, constituency 

 

#5 Winners Only

HINT를 클릭하면 자세히 볼 수 있는데, A_STRONGLY_AGREE가 백분율이어서 응답수를 곱해야 하고 그것을 100으로 나누어야 한다고 쓰여져있다. 솔직히 이해가 잘 안됐다.

아마 내 이해로는 응답자가 100명이고 강한 긍정 비율이 33(퍼센트)이면 33명을 출력하기 위해선 100 * (33 / 100)을 해주어야 한다. 이게 아닐까..

SELECT constituency,party FROM
(SELECT constituency,party, votes,
RANK() OVER (PARTITION BY constituency ORDER BY votes DESC) as posn
  FROM ge
 WHERE constituency BETWEEN 'S14000021' AND 'S14000026'
   AND yr  = 2017) RK
WHERE RK.posn=1

 

#6 Scottish seats

패쓰.

SELECT party,COUNT(*) FROM (
SELECT constituency,party, votes,
RANK() OVER (PARTITION BY constituency ORDER BY votes DESC) as posn
FROM ge
WHERE constituency LIKE 'S%' AND yr  = 2017) rk
WHERE rk.posn=1
GROUP BY rk.party

 

정리하기