[leetcode / MySQL] 585. Investments in 2016

문제

💡 Write a solution to report the sum of all total investment values in 2016 tiv_2016, for all policyholders who:

• have the same tiv_2015 value as one or more other policyholders, and
• are not located in the same city as any other policyholder (i.e., the (lat, lon) attribute pairs must be unique).

Round tiv_2016 to two decimal places.

 

테이블 형태

 

풀이

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(*) >= 2)
    AND (lat, lon) IN (SELECT lat, lon FROM Insurance
                    GROUP BY CONCAT(lat, lon)
                    HAVING COUNT(*) = 1)

 

해설

• 한 명 이상의 다른 보험 계약자와 동일한 tiv_2015 값을 가지며
• 다른 정책 계약자와 같은 도시에 위치하지 않는(=lat, lon 쌍 값이 겹치지 않는)
• 보험 계약자의 tiv_2016를 합해 소수점 2자리까지 반올림해 나타내야 한다

FROM Insurance
WHERE tiv_2015 IN (SELECT tiv_2015 FROM Insurance
                    GROUP BY tiv_2015
                    HAVING COUNT(*) >= 2)

• WHERE절 서브쿼리와 IN을 활용해 tiv_2015, lat, lon 조건을 만족하는 데이터를 필터링해주었다. 먼저 tiv_2015 값은 1명 이상의 다른 보험 계약자와 중복되어야 하므로 WHERE문 서브쿼리에서 tiv_2015값으로 GROUP BY한 뒤 그룹의 행 개수가 2개 이상인 tiv_2015 를 가지는 데이터만 필터링해주었다

    AND (lat, lon) IN (SELECT lat, lon FROM Insurance
                    GROUP BY CONCAT(lat, lon)
                    HAVING COUNT(*) = 1)

• 그리고 lat, lon 조건 역시 동일한 포맷을 취해주었다. (lat, lon)값 쌍이 고유해야 하므로 CONCNAT으로 두 값을 연결한 뒤, 이를 기준으로 GROUP BY하여 그룹의 행 개수가 1개인 lat, lon을 가지는 데이터만 필터링해주었다. 나중에 보니까 lat, lon을 굳이 CONCAT하지 않고 lat, lon의 2가지 기준으로 GROUP BY 할 수 있었다

SELECT ROUND(SUM(tiv_2016),2) AS tiv_2016

• 조건을 충족하는 데이터만 필터링했기 때문에 메인쿼리 SELECT문에서 SUM을 활용해 tiv_2016값을 합해주기만 하면 된다. ROUND로 소수 2자리까지 반올림해 tiv_2016 값을 나타냈다
• 런타임 1098ms로 속도가 빠른 쿼리는 아니다

 

다른 사람의 풀이

• GROUP BY를 사용하지 않은 Atrilos의 풀이

SELECT ROUND(SUM(tiv_2016), 2) tiv_2016
FROM Insurance a
WHERE a.tiv_2015 IN (SELECT tiv_2015
                     FROM Insurance
                     WHERE pid <> a.pid)
  AND (a.lat, a.lon) NOT IN
      (SELECT lat, lon
       FROM Insurance
       WHERE pid <> a.pid)

• ‘한 명 이상의 다른 보험 계약자와 동일한’, ‘다른 보험 계약자와 lat, lon 쌍 값이 겹치지 않는’의 조건을 쿼리로 어떻게 나타내야 하는지가 문제였는데, 이 사람의 풀이를 보고 알 수 있었다. 간단하게 서브쿼리의 WHERE문에 메인쿼리 테이블의 pid값이 서브쿼리 테이블의 pid값과 동일하지 않으면 된다(=pid <> a.pid). pid 컬럼은 중복값을 가지지 않는 P.K(primary key) 컬럼이므로 pid값이 다르면 서로 다른 보험 계약자의 데이터기 때문이다. 속도도 꽤 빠르다