2021-12-05 16:31 작성

My SQL basic 메모

Table of contents

• CROSS JOIN 심화 예시
  • 샘플 tables 세팅하기
  • 예시

---

CROSS JOIN 심화 예시

샘플 tables 세팅하기

먼저, salesdb라는 새로운 데이터베이스를 생성한다.

CREATE DATABASE IF NOT EXISTS salesdb;

둘째, 새로운 데이터베이스 salesdb로 전환한다.

USE salesdb;

셋째, salesdb 데이터베이스에서 새로운 tables를 생성한다.

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    product_name VARCHAR(100),
    price DECIMAL(13,2 )
);

-- DECIMAL(P, D): 총 P개의 자릿수를 가지며, D개의 소수점 자릿수를 가짐(따라서 P - D 개의 자릿수는 정수 자릿수를 의미한다.)

CREATE TABLE stores (
    id INT PRIMARY KEY AUTO_INCREMENT,
    store_name VARCHAR(100)
);

CREATE TABLE sales (
    product_id INT,
    store_id INT,
    quantity DECIMAL(13, 2 ) NOT NULL,
    sales_date DATE NOT NULL,
    PRIMARY KEY (product_id, store_id),
    FOREIGN KEY (product_id)
        REFERENCES products (id)
        ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (store_id)
        REFERENCES stores (id)
        ON DELETE CASCADE ON UPDATE CASCADE 
);

-- ON DELETE CASCADE는 상위 테이블에서 해당 row가 사라지면 하부 테이블의 값이 사라지게 한다.
-- 만약, 특정 product가 사라지거나 특정 store가 사라지게 된다면 관련 sales 품목은 모두 사라져야 하며 업데이트 될 경우 모두 업데이트 되어야 한다. (FOREIGN KEY)

세 개의 tables는 다음과 같다.

• products table은 product id, product name 그리고 sales price를 포함하는 products 마스터 데이터를 포함하고 있다.
• stores table은 products를 파는 상점들을 포함한다.
• sales table은 팔린 양과 날짜와 함께 특정 지점의 팔린 products를 포함한다.

마지막으로, 세 개의 tables에 데이터를 입력한다. North와 South 두 개의 지점에서 팔고 있는 iPhone, iPad 그리고 Macbook Pro 세 개의 products를 가정해보자.

INSERT INTO products(product_name, price)
VALUES('iPhone', 699),
      ('iPad',599),
      ('Macbook Pro',1299);

INSERT INTO stores(store_name)
VALUES('North'),
      ('South');

INSERT INTO sales(store_id,product_id,quantity,sales_date)
VALUES(1,1,20,'2017-01-02'),
      (1,2,15,'2017-01-05'),
      (1,3,25,'2017-01-05'),
      (2,1,30,'2017-01-02'),
      (2,2,35,'2017-01-05');

예시

아래의 구문은 각각의 store와 product의 총 sales를 반환한다. Sales를 계산하고 store와 product로 group을 만들 수 있다.

SELECT
    store_name,
    product_name,
    SUM(quantity * price) AS revenue
FROM
    sales
        INNER JOIN
    products ON products.id = sales.product_id
        INNER JOIN
    stores ON stores.id = sales.store_id
GROUP BY store_name, product_name;

output:

store_name	product_name	revenue
North	iPad	8985.0000
North	iPhone	13980.0000
North	Macbook Pro	32475.0000
South	iPad	20965.0000
South	iPhone	20970.0000

만약 특정 product의 sales가 없는 store를 알고 싶다고 가정해보자. 위의 쿼리로는 질문에 맞는 답을 찾을 수 없다.

이 문제를 해결하기 위해서는 CROSS JOIN을 사용해야 한다.

먼저, 모든 stores와 products의 조합을 얻을 수 있도록 CROSS JOIN을 사용해야 한다:

SELECT
    store_name, product_name
FROM
    stores AS a
        CROSS JOIN
    products AS b;

store_name	product_name
North	iPhone
South	iPhone
North	iPad
South	iPad
North	Macbook Pro
South	Macbook Pro

다음으로, 위의 쿼리로 나타난 결과에 store와 product의 총 sales를 반환하는 다른 쿼리를 join한다. 다음의 쿼리가 아이디어가 될 수 있다:

SELECT
    b.store_name,
    a.product_name,
    IFNULL(c.revenue, 0) AS revenue
FROM
    products AS a
        CROSS JOIN
    stores AS b
        LEFT JOIN
    (SELECT
        stores.id AS store_id,
        products.id AS product_id,
        store_name,
        product_name,
        ROUND(SUM(quantity * price), 0) AS revenue
    FROM
        sales
    INNER JOIN products ON products.id = sales. product_id
    INNER JOIN stores ON stores.id = sales.store_id
    GROUP BY stores.id, products.id, store_name, product_name) AS c ON c.store_id = b.id AND c.product_id = a_id
ORDER BY b.store_name;

store_name	product_name	revenue
North	Macbook Pro	32475
North	iPad	8985
North	iPhone	13980
South	iPhone	20970
South	Macbook Pro	0
South	iPad	20965

1. FROM 절 이하에서 products의 각각의 제품을 stores의 각각의 지점에 모두 대응시킨다.(CROSS JOIN: 제품 수 * 지점 수)
2. 메모리에 저장된 cross joined table에서 SELECT 절 이하를 LEFT JOIN한다.
3. SELECT 절 이하에서는 sales와 products 그리고 stores를 INNER JOIN하여 메모리에 저장한다. 그 후에 GROUP BY를 통해 통합된 데이터를 DISTINCT 형태로 만든다. stores.id, products.id, store_name, product_name으로 GROUP BY하기 때문에 각 지점에서 각 품명 단위로 판매 수량이 종합되게 된다. 그리고 SELECT 이하에서 오는 ROUND(SUM())에서 판매 수량과 각 품목 별 가격을 곱해 revenue로 만들고 stores.id와 products.id와 함께 LEFT JOIN된다.
4. IFNULL을 통해 판매내역이 없는 경우 0으로 만들고 store_name 단위로 정렬한다.