BOAZ SQL 스터디(3)-SubQuery

Writing Subqueries in SQL

Subquery basics

여러 단계를 수행하는 작업의 도구다.

서브쿼리는 쿼리 안 여러 곳에 쓰일 수 있으나, FROM문에서 쓰는 게 가장 쉬우므로 FROM문에서 시작한다.

SELECT sub.*
  FROM (
        SELECT *
          FROM tutorial.sf_crime_incidents_2014_01
         WHERE day_of_week = 'Friday'
       ) sub
 WHERE sub.resolution = 'NONE'

위 쿼리를 실행했을 때, inner query가 먼저 실행된다. db는 inner query 또한 독립적인 쿼리로 취급한다.

서브쿼리는 alias를 지정해줘야 한다. 서브쿼리를 쓸 때는 꼭 들여쓰기 등을 사용해서 가독성을 고려해서 작성하는게 좋다. 또한 많은 서브쿼리를 포함하는 것은 실용적이지 못하다.

Using subqueries to aggregate in multiple stages

1. 각 날짜의 사고의 수를 센다
2. 월 평균을 결정한다

SELECT LEFT(sub.date, 2) AS cleaned_month,
       sub.day_of_week,
       AVG(sub.incidents) AS average_incidents
  FROM (
        SELECT day_of_week,
               date,
               COUNT(incidnt_num) AS incidents
          FROM tutorial.sf_crime_incidents_2014_01
         GROUP BY 1,2
       ) sub
 GROUP BY 1,2
 ORDER BY 1,2

Subqueries in conditional logic

WHERE, JOIN/ON, CASE 등의 조건 로직에서도 서브쿼리를 사용할 수 있다.

SELECT *
  FROM tutorial.sf_crime_incidents_2014_01
 WHERE Date = (SELECT MIN(date)
                 FROM tutorial.sf_crime_incidents_2014_01
              )

위 쿼리는 서브쿼리의 결과가 1개 셀이기 때문에 작동한다. 대부분의 조건 로직은 서브쿼리가 1개의 결과를 가질 때 작동한다.

IN을 사용하면 서브쿼리가 여러 결과를 가질 때도 작동한다.

SELECT *
  FROM tutorial.sf_crime_incidents_2014_01
 WHERE Date IN (SELECT date
                 FROM tutorial.sf_crime_incidents_2014_01
                ORDER BY date
                LIMIT 5
              )

조건문에서는 서브쿼리에 대해 alias를 사용하면 안된다. 서브쿼리를 테이블이 아닌 각각의 값(또는 IN의 경우 값의 집합)으로 보기 때문이다.

Joining subqueries

서브쿼리와 외부 쿼리문과 같은 테이블을 조인하는 방식의 필터링 방법도 많이 쓴다.

SELECT *
  FROM tutorial.sf_crime_incidents_2014_01 incidents
  JOIN ( SELECT date
           FROM tutorial.sf_crime_incidents_2014_01
          ORDER BY date
          LIMIT 5
       ) sub
    ON incidents.date = sub.date

위 조건문에 서브쿼리를 쓴 결과와 동일하다.

aggregation과 섞어서 쓸 때 부분적으로 유용하다.

WHERE절에서 서브쿼리 쓸때보다 엄격하지 않다. 예를 들어 아래 쿼리는 내부 쿼리가 여러 결과를 출력해도 그 결과값을 외부 쿼리 결과값과 함께 출력할 수 있으며 정렬에도 사용한다.

SELECT incidents.*,
       sub.incidents AS incidents_that_day
  FROM tutorial.sf_crime_incidents_2014_01 incidents
  JOIN ( SELECT date,
          COUNT(incidnt_num) AS incidents
           FROM tutorial.sf_crime_incidents_2014_01
          GROUP BY 1
       ) sub
    ON incidents.date = sub.date
 ORDER BY sub.incidents DESC, time

서브쿼리는 쿼리 효율을 높이는 데 매우 좋습니다.

SELECT COALESCE(acquisitions.acquired_month, investments.funded_month) AS month,
       COUNT(DISTINCT acquisitions.company_permalink) AS companies_acquired,
       COUNT(DISTINCT investments.company_permalink) AS investments
  FROM tutorial.crunchbase_acquisitions acquisitions
  FULL JOIN tutorial.crunchbase_investments investments
    ON acquisitions.acquired_month = investments.funded_month
 GROUP BY 1

위 쿼리는 join에서 데이터 폭발이 일어나게 됩니다. join 연산은 카디전 프로덕트 연산 이후에 실행되기 떄문입니다.

테이블을 두 개로 분리하여 aggregate하면 보다 효율적으로 해결할 수 있다. 분리한 뒤 더 작은 데이터셋에서 join하면 된다.

SELECT COALESCE(acquisitions.month, investments.month) AS month,
-- month가 한 쪽에 없을 경우 다른 쪽 month로 출력함
-- FULL JOIN 했기 때문에 없을 수도 있다.
       acquisitions.companies_acquired,
       investments.companies_rec_investment
  FROM (
        SELECT acquired_month AS month,
               COUNT(DISTINCT company_permalink) AS companies_acquired
          FROM tutorial.crunchbase_acquisitions
         GROUP BY 1
       ) acquisitions

  FULL JOIN (
        SELECT funded_month AS month,
               COUNT(DISTINCT company_permalink) AS companies_rec_investment
          FROM tutorial.crunchbase_investments
         GROUP BY 1
       )investments

    ON acquisitions.month = investments.month
 ORDER BY 1 DESC

Subqueries and UNIONs

데이터셋이 분리되어 있을 때, 서브쿼리를 이용해 전체 합쳐진 데이터셋에 같은 연산을 실행할 수 있다.

SELECT COUNT(*) AS total_rows
  FROM (
        SELECT *
          FROM tutorial.crunchbase_investments_part1

         UNION ALL

        SELECT *
          FROM tutorial.crunchbase_investments_part2
       ) sub