240911_TIL

아무리 사소하더라도 배움이 없는 날은 없다.

SQL 문제풀이 복습

문제 링크

SELECT d.dept_name,
       Count(DISTINCT student_id) AS "student_number"
FROM   department d
       LEFT JOIN student s
              ON d.dept_id = s.dept_id
GROUP  BY 1
ORDER  BY 2 DESC,
          1 ASC;

문제 링크

SELECT name
FROM   customer
WHERE  Ifnull(referee_id, 0) <> 2;

문제 링크
문제에서 요구한 2개의 조건을
join과 where절의 조건에 잘 녹이면 된다.
오늘은 인라인뷰와 서브쿼리를 사용해서 아래와 같이 해결.

SELECT Round(Sum(i.tiv_2016), 2) AS "tiv_2016"
FROM   insurance i
       LEFT JOIN (SELECT lat,
                         lon,
                         Count(*) AS "cnt"
                  FROM   insurance
                  GROUP  BY 1,
                            2
                  HAVING cnt >= 2) a
              ON i.lat = a.lat
                 AND i.lon = a.lon
WHERE  a.lat IS NULL
       AND i.tiv_2015 IN (SELECT tiv_2015
                          FROM   insurance
                          GROUP  BY 1
                          HAVING Count(*) > 1);

5/16에 1차로 시도했을 때는 아래와 같이 풀었다.
아예 lat과 lon을 concat으로 묶어서
tiv_2015 조건과 함께 where절 내에서 처리했었네.

SELECT Round(Sum(tiv_2016), 2) AS tiv_2016
FROM   insurance
WHERE  Concat(lat, ',', lon) NOT IN (SELECT Concat(lat, ',', lon) AS location
                                     FROM   insurance
                                     GROUP  BY 1
                                     HAVING Count(*) >= 2)
       AND tiv_2015 IN (SELECT tiv_2015
                        FROM   insurance
                        GROUP  BY 1
                        HAVING Count(*) > 1);

성능상으로는 5/16에 풀었던 방식이
조금 더 유리할 수 있다는 게 GPT의 설명이었지만,
concat을 써서 굳이 lat과 lon을 묶어줄 필요 없이
아래와 같이 처리해도 될 것 같다.

join을 사용하지 않고 where절 내에서만
조건을 줘서 처리하는 것은 5/16과 동일한데,
concat을 쓴 게 아니라 (lat, lon)을 묶어서 where절 내에서 한 번에 처리했다.

join할 필요까지도 없이
이 방법이 가장 효율적일 것 같긴 하다!

SELECT Round(Sum(tiv_2016), 2) AS tiv_2016
FROM   insurance
WHERE  tiv_2015 IN (SELECT tiv_2015
                    FROM   insurance
                    GROUP  BY tiv_2015
                    HAVING Count(*) > 1)
       AND ( lat, lon ) IN (SELECT lat,
                                   lon
                            FROM   insurance
                            GROUP  BY lat,
                                      lon
                            HAVING Count(*) = 1);