2021-12-10 18:38 작성

My SQL basic 메모

Table of contents

Derived tables 도입

Derived table은 SELECT 구문에서 반환된 가상 table을 말한다. Derived table은 temporary table과 유사하지만 SELECT 구문에서 derived table을 사용하는 것은 temporary table을 사용하는 것보다 훨씬 단순하다. 왜냐하면 temporary table을 생성할 필요가 없기 때문이다.

Derived table과 subquery라는 용어는 종종 교차사용되고는 한다. 단독으로 사용되는 subquery는 FROM과 SELECT 구문에서 사용되며 derived table이라고 불리기도 한다.

다음은 derivecd table을 사용하는 쿼리를 직접적으로 보여준다:


단독 subquery는 outer query와 별개로 실행되어도 에러가 발생하지 않는 독립적인 subquery를 말한다.

subquery와 달리, 하나의 derived table은 query 안에서 참조될 수 있는 alias를 가져야만 한다. 만약 derived table이 alias를 가지고 있지 않으면 다음과 같은 에러가 발생한다:

Every derived table must have its own alias.

다음 쿼리는 derived table을 사용하는 문법적 예시다:

SELECT
    select_list
FROM
    (SELECT
        select_list
    FROM
        table_1) derived_table_name
WHERE
    derived_table_name.c1 > 0;



MySQL derived table 간단한 예시

다음의 쿼리는 orders와 orderdetails tables의 2003년 sales 수익에 따른 top 5 products를 추출한다.


SELECT
    productCode,
    ROUND(SUM(quantityOrdered * priceEach)) sales
FROM
    orderdetails
        INNER JOIN
    orders USING (orderNumber)
WHERE
    YEAR(shippedDate) = 2003
GROUP BY productCode
ORDER BY sales DESC
LIMIT 5;

output:

productCode sales
S18_3232 103480
S10_1949 67985
S12_1108 59852
S12_3891 57403
S12_1099 56462

위 쿼리의 결과물을 derived table로 사용하여 products table과 join 할 수 있다.

SELECT 
    productName, sales
FROM
    (SELECT 
        productCode, 
        ROUND(SUM(quantityOrdered * priceEach)) sales
    FROM
        orderdetails
    INNER JOIN orders USING (orderNumber)
    WHERE
        YEAR(shippedDate) = 2003
    GROUP BY productCode
    ORDER BY sales DESC
    LIMIT 5) top5products2003
INNER JOIN
    products USING (productCode);

output:

productName sales
1992 Ferrari 360 Spider red 103480
1952 Alpine Renault 1300 67985
2001 Ferrari Enzo 59852
1969 Ford Falcon 57403
1968 Ford Mustang 56462

이 예시에서:

  1. 먼저, subquery를 실행해 result set을 생성하거나 derived table을 생성한다.
  2. 그리고 outer query가 실행되며 이 때, subquery의 결과물인 top5product2003을 products table의 productCode column과 join한다.

더 복잡한 MySQL derived table 예시

2003년도에 products를 구매한 customers를 3 그룹(platinum, gold 그리고 silver)으로 나눈다고 가정하자. 그리고 각 그룹에서 다음의 조건들을 만족하는 customers의 수를 알아야 한다고 하자:

  • Platinum customers는 100K를 초과한 양의 주문건수가 있는 고객층이다.
  • Gold customers는 10K ~ 100K 사이의 주문건수가 있는 고객층이다.
  • Silver customers는 10K 미만의 주문건수가 있는 고객층이다.

이 쿼리를 작성하기 위해 먼저 CASE expression과 GROUP BY 절을 이용해 각각의 customer를 각각의 그룹에 넣어야 한다:

SELECT
    customerNumber,
    ROUND(SUM(quantityOrdered * priceEach)) sales,
    (CASE
        WHEN SUM(quantityOrdered * priceEach) < 10000 THEN 'Silver'
        WHEN SUM(quantityOrdered * priceEach) BETWEEN 10000 AND 100000 THEN 'Gold'
        WHEN SUM(quantityOrdered * priceEach) > 100000 THEN 'Platinum'
    END) customerGroup
FROM
    orderdetails
        INNER JOIN
    orders USING (orderNumber)
WHERE
    YEAR(shippedDate) = 2003
GROUP BY customerNumber;

output:

customerNumber sales customerGroup
363 65650 Gold
128 34651 Gold
181 5495 Silver
121 51710 Gold
141 189840 Platinum
145 53959 Gold
278 89876 Gold
131 22293 Gold
385 71645 Gold

그 다음 이 쿼리를 derived table로 사용해 다음과 같이 grouping 할 수 있다:

SELECT 
    customerGroup, 
    COUNT(cg.customerGroup) AS groupCount
FROM
    (SELECT 
        customerNumber,
            ROUND(SUM(quantityOrdered * priceEach)) sales,
            (CASE
                WHEN SUM(quantityOrdered * priceEach) < 10000 THEN 'Silver'
                WHEN SUM(quantityOrdered * priceEach) BETWEEN 10000 AND 100000 THEN 'Gold'
                WHEN SUM(quantityOrdered * priceEach) > 100000 THEN 'Platinum'
            END) customerGroup
    FROM
        orderdetails
    INNER JOIN orders USING (orderNumber)
    WHERE
        YEAR(shippedDate) = 2003
    GROUP BY customerNumber) cg
GROUP BY cg.customerGroup;

output:

customerGroup groupCount
Gold 61
Silver 8
Platinum 4