One to Many 조인에서 Many 쪽 최종 레코드만 조회하기 share

today 2018-11-20 face Posted by appkr turned_in Work & Play forum 0

고객 목록에 고객별 최근 주문 1건에 대한 요약 정보를 보여주세요.

라는 요구사항이 있습니다.

“고객” 객체와 “주문” 객체간의 관계는 다음과 같습니다.

+------------+           0..* +------------+
|  Customer  | <>-----------> |   Order    |
+------------+                +------------+

애플리케이션 레이어에서 구현한다면, ORM을 통해 구한 Collection<Customer>을 순회하면서, Customer객체의 멤버 필드인 Collection<Order>를 대상으로 최근 Order 객체만 필터링했거나, 적절한 순서로 정렬하여 뽑아 쓰기 쉽도록 했을겁니다. 요런 느낌으로요(검증 안된 Pseudo Code 입니다).

// Service Layer
public function listCustomers()
{
    return $customers->map(function (Customer $customer) {
        $sorted = $customer->orders->sortByDesc('id')->values();
        $customer->setRelation('orders', $sorted);
        return $customer;
    });
}

// Controller/View 등
foreach ($customers as $customer) {
    $lastOrder = $customer->orders->first();
    $lastOrder->order_number; // 최근 주문 번호   
}

문제점들이 눈에 띕니다.

  • 시간복잡도는 O(m x n)입니다.
  • CPU와 메모리를 혹사시킵니다.
  • Customer.orders: Collection<Order>를 미리 로드하지 않았다면, N + 1 문제가 발생합니다.
  • 이 외에도 제가 보지 못한 문제점들이 더 있을 겁니다…

읽기 전용 쿼리이므로 ORM을 쓰지 않아도 됩니다. 싸고, 빠르고, 안전하게 SQL만으로 뽑아내는 방법을 찾아봤습니다.

• • •

1. 테이블 스키마

간결함을 위해 주변 테이블과 정규화등은 모듀 생략합니다(e.g. order_lines, products, …).

+------------+                +------------+
|  customers | -|----------o< |   orders   |
+------------+                +------------+
CREATE TABLE customers (
  customer_id int(11) unsigned NOT NULL AUTO_INCREMENT,
  name varchar(40),
  zipcode varchar(5),
  phonenumber varchar(13),
  PRIMARY KEY (customer_id)
);
INSERT INTO customers (name, zipcode, phonenumber)
VALUES ('이덕승', '34603', '01012345678'),
       ('임희일', '06035', '01011112222'),
       ('신초아', '06611', '01043218765')
;
CREATE TABLE orders (
  order_number int(11) unsigned NOT NULL AUTO_INCREMENT,
  customer_id int(11) unsigned,
  order_total int(11),
  created_at datetime,
  PRIMARY KEY (order_number),
  CONSTRAINT orders_customer_id_foreign FOREIGN KEY (customer_id) REFERENCES customers (customer_id)
);
INSERT INTO orders (customer_id, order_total, created_at)
VALUES (1, 150, "2018-01-01 12:00:00"),
       (1, 120, "2018-08-25 13:00:00"),
       (1, 200, "2018-11-20 14:00:00"),
       (3, 100, "2018-05-05 09:00:00"),
       (3, 100, "2018-10-01 10:00:00"),
;

2. 고객과 주문 테이블 LEFT 조인

고객번호 1번 “이덕승” 고객의 주문 3건과, 고객번호 3번 “신초아” 고객의 주문 2건, 한번도 주문한 적이 없는 고객번호 2번 “임희일” 고객의 NULL을 포함해 총 6건의 결과가 출력됩니다.

SELECT 
    c.customer_id AS cid,
    o1.order_number AS o1n
FROM customers AS c
LEFT JOIN orders AS o1 ON c.customer_id = o1.customer_id
; -- No errors; 6 rows affected
cid o1n
1   1
1   2
1   3
2   NULL
3   4
3   5

참고로, 한번이라도 주문한 고객의 고객별…과 같은 요구사항이었다면, LEFT JOIN 대신 INNER JOIN을 이용했을테고, 5건의 결과가 출력됐을 겁니다.

3. 고객과 주문 테이블 LEFT 조인

조인은 두 테이블을 조인해 중간 결과 집합을 만든 후, 이 결과 집합과 다음 테이블을 조인하는 식으로 동작한다고 알고 있습니다. 2절의 선행 조인에서 만들어진 결과 집합의 각 로우와 주문 테이블 o2를 한번 더 LEFT 조인합니다.

SELECT 
    c.customer_id AS cid,
    o1.order_number AS o1n,
    o2.order_number AS o2n
FROM customers AS c
LEFT JOIN orders AS o1 ON c.customer_id = o1.customer_id
LEFT JOIN orders AS o2 ON c.customer_id = o2.customer_id
; -- No errors; 14 rows affected
cid o1n o2n
1   1   1
1   1   2
1   1   3
1   2   1
1   2   2
1   2   3
1   3   1
1   3   2
1   3   3
2   NULLNULL
3   4   4
3   4   5
3   5   4
3   5   5
  • 선행 결과 집합의 {cid:1, o1n:1} 로우와 조인되는 주문 테이블 o2의 로우는 customer_id가 1인 로우 3개입니다.
  • 선행 결과 집합의 {cid:1, o1n:2} 로우와 조인되는 주문 테이블 o2의 로우는 customer_id가 1인 로우 3개입니다.
  • 선행 결과 집합의 {cid:1, o1n:3} 로우와 조인되는 주문 테이블 o2의 로우는 customer_id가 1인 로우 3개입니다.
  • 선행 결과 집합의 {cid:2, o1n:NULL} 로우와 조인되는 주문 테이블 o2의 로우는 없습니다.
  • 선행 결과 집합의 {cid:3, o1n:4} 로우와 조인되는 주문 테이블 o2의 로우는 customer_id가 3인 로우 2개입니다.
  • 선행 결과 집합의 {cid:3, o1n:5} 로우와 조인되는 주문 테이블 o2의 로우는 customer_id가 3인 로우 2개입니다.

왜 결과가 14행이 나왔는지 이해되셨죠?

4. LEFT 조인 조건 추가

왼쪽 또는 오른쪽 어느 쪽이든 무관하지만, 여기서는 왼쪽(선행 결과 집합) 테이블의 레코드를 버리도록 하겠습니다. 버리는 조건은 “나한테 가위바위보 진 사람 전부 앉아!”와 비슷합니다. 왼쪽이 오른쪽보다 작은 주문번호를 가졌다면 버리는 겁니다.

주문번호가 더 크다면 더 최근에 주문한 건이며, 같은 고객을 대상으로 주문번호가 가장 큰 녀석을 골라내는 과정이라 이해할 수 있습니다. 버린다고 표현했지만, 엄밀히 말하면 LEFT JOIN의 조건식을 충족하지 못하는 것입니다.

그런데 LEFT JOIN이므로 2절에서 도출한 선행 결과 집합은 그대로 유지해야 합니다. {cid:1, o1n:3} 및 {cid:3, o1n:5} 로우는 충족하는 오른쪽 값이 없네요. 그럼에도 선행 결과 집합 유지를 위해 NULL이라고 표현하고 있습니다.

cid o1n o2n
1   1   1   (1 < 1) 버림
1   1   2   (1 < 2) 남김
1   1   3   (1 < 3) 남김
1   2   1   (2 < 1) 버림
1   2   2   (2 < 2) 버림
1   2   3   (2 < 3) 남김
1   3   1   (3 < 1) 버림
1   3   2   (3 < 2) 버림
1   3   3   (3 < 3) 버림
3   4   4   (4 < 4) 버림
3   4   5   (4 < 5) 남김
3   5   4   (5 < 4) 버림
3   5   5   (5 < 5) 버림
SELECT 
    c.customer_id AS cid,
    o1.order_number AS o1n,
    o2.order_number AS o2n
FROM customers AS c
LEFT JOIN orders AS o1 ON c.customer_id = o1.customer_id
LEFT JOIN orders AS o2 ON c.customer_id = o2.customer_id
    AND o1.order_number < o2.order_number
; -- No errors; 7 rows affected
cid o1n o2n
1   1   2
1   1   3
1   2   3
1   3   NULL
2   NULLNULL
3   4   5
3   5   NULL

이해를 돕기 위해 (사실은 제가 이해하기 위해) 3절과 4절을 구분 동작으로 표현했지만, 실제로는 연속 동작으로 진행됩니다.

5. WHERE 조건절 추가

부등 조인 조건의 결과가 없다는 것은 가장 큰 값이란 의미입니다. 따라서 o2 테이블 쪽에 NULL을 품고 있는 로우가 고객별 최근 주문입니다.

SELECT 
    c.customer_id AS cid,
    o1.order_number AS o1n,
    o2.order_number AS o2n
FROM customers AS c
LEFT JOIN orders AS o1 ON c.customer_id = o1.customer_id
LEFT JOIN orders AS o2 ON c.customer_id = o2.customer_id
    AND o1.order_number < o2.order_number
WHERE o2.order_number IS NULL   
; -- No errors; 2 rows affected
cid o1n o2n
1   3   NULL
2   NULLNULL
3   5   NULL

6. 최종 쿼리

고객 목록에 최근 주문 요약을 표현하기 위한 최종 쿼리입니다.

SELECT 
    c.customer_id AS "고객번호", 
    c.name AS "고객명",
    o1.order_number AS "주문번호",
    o1.order_total AS "주문금액",
    o1.created_at AS "주문일시"
FROM customers AS c
LEFT JOIN orders AS o1 ON c.customer_id = o1.customer_id
LEFT JOIN orders AS o2 ON c.customer_id = o2.customer_id
    AND o1.order_number < o2.order_number
WHERE o2.order_number IS NULL   
; -- No errors; 2 rows affected
고객번호  고객명  주문번호  주문금액  주문일시
1       이덕승  3       200    2018-11-20 14:00:00
3       신초아  5       100    2018-10-01 10:00:00
2       임희일  NULL    NULL   NULL

7. 애플리케이션에서 어떻게 사용해요?

대략 이런 모습일겁니다(검증 안된 Pseudo Code 입니다).

// Service Layer
public function queryCustomersWithRecentOrderSummary()
{
    $builder = Customer::query();
    $builder->leftJoin('orders AS o1', 'customers.customer_id', '=', 'o1.customer_id');
    $builder->leftJoin('orders AS o2', function ($join) {
        $join->on('customers.customer_id', '=', 'o2.customer_id');
        $join->on('o1.order_number', '<', 'o2.order_number');
    });
    $builder->whereNull('o2.order_number');
    $builder->select(['customers.*', 'o1.*']);
    return $builder->get();
}

이상 쿼리는 MySQL에서만 검증했습니다. 제가 DB 전문가가 아니므로, 본문에 사용한 용어들이 정확하지 않을 수 있습니다.

comments powered by Disqus
keyboard_arrow_up