Books/MySQL 8.0
LATERAL JOIN을 사용하여 각 그룹의 “TOP N” 레코드 가져오기
우니wooni
2024. 7. 10. 14:48
MySQL에서 어려웠던 SQL 쿼리 유형 중 하나는 각 그룹에 대해 "Top N" 행을 얻는 것이었다. LATERAL 조인은 여러 복잡한 쿼리들을 효율적으로 작성할 수 있는 기능을 제공한다.
LATERAL JOIN을 사용하여 각 회원별 가장 마지막 주문을 가져오는 방법을 알아보자.(Top N)
✔️ 예제 테이블
MEMBERS 테이블
select * from members;
+----+--------------------+---------------------------+
| id | name | email |
+----+--------------------+---------------------------+
| 1 | PcbiQuxvPqj | [lboag0@nymag.com](<mailto:lboag0@nymag.com>) |
| 2 | BmrkbsGmZupvwPshit | [mregglar0@webnode.com](<mailto:mregglar0@webnode.com>) |
| 3 | dLjbhuPxbFXTMDHak | [hfound1@lycos.com](<mailto:hfound1@lycos.com>) |
| 4 | vRF | [whymus2@weibo.com](<mailto:whymus2@weibo.com>) |
| 5 | YICxcKwxeSWOCXh | [wblindmann3@wordpress.com](<mailto:wblindmann3@wordpress.com>) |
| 6 | DCuVfkyncJKVcMkUWs | [dbrydone4@adobe.com](<mailto:dbrydone4@adobe.com>) |
| 7 | A | [lhiskey5@disqus.com](<mailto:lhiskey5@disqus.com>) |
| 8 | Uz | [biacomo6@reuters.com](<mailto:biacomo6@reuters.com>) |
| 9 | caNOO | [skilford7@oaic.gov.au](<mailto:skilford7@oaic.gov.au>) |
| 10 | BSaQahVoWPyKz | [nsidlow8@godaddy.com](<mailto:nsidlow8@godaddy.com>) |
+----+--------------------+---------------------------+
10 rows in set (0.00 sec)
ORDERS 테이블
select * from orders;
+--------------+--------------------------------+---------------------+-----------+
| order_number | product_name | payment_datetime | member_id |
+--------------+--------------------------------+---------------------+-----------+
| 5Q8937N88B0N | Food Colouring - Green | 2023-09-05 12:22:16 | 1 |
| 4N25JW0966Y3 | Sauce - Caesar Dressing | 2023-10-15 22:02:50 | 1 |
| U821M7X2824Q | General Purpose Trigger | 2023-12-15 22:41:41 | 1 |
| 7MX07823M822 | Bread - Pita | 2024-04-15 21:41:58 | 1 |
| W5V5VUWHO4OA | Wine - Red, Wolf Blass, Yellow | 2023-07-14 10:17:25 | 2 |
| EJQE7212L165 | Compound - Raspberry | 2024-02-19 22:36:49 | 2 |
| LP209M2OJ3E4 | Lettuce - Lambs Mash | 2023-10-30 12:59:48 | 3 |
| F628XX6K4086 | Beer - True North Strong Ale | 2024-02-09 22:09:22 | 3 |
| OLU1C30GO83I | Compound - Passion Fruit | 2024-06-07 00:46:13 | 3 |
| DS9Z119HAJZ1 | Pork Ham Prager | 2023-09-12 18:21:23 | 4 |
| EB65IMT5J09C | Veal Inside - Provimi | 2023-07-16 08:33:39 | 5 |
| GL3URQT8J7O1 | Kolrabi | 2023-08-10 06:04:59 | 5 |
| QJ2MT6Z71V35 | Soup - Campbells, Minestrone | 2024-01-12 15:02:18 | 5 |
| J4K0B5Y52D85 | Lid - 0090 Clear | 2024-03-30 05:32:29 | 5 |
| K587DTQ3C39Y | Crush - Grape, 355 Ml | 2024-05-28 11:38:13 | 5 |
| 2P8M248CD6L0 | Doilies - 12, Paper | 2024-06-03 06:29:14 | 5 |
| 60K26FJ2D4HS | Pork - Liver | 2024-02-19 09:55:30 | 6 |
| 8I1909883FSO | Appetizer - Escargot Puff | 2024-05-20 06:07:44 | 6 |
| 465E290AHS8W | Juice - V8 Splash | 2023-07-05 23:27:32 | 7 |
| 8667O12H781I | Wine - Casillero Deldiablo | 2023-09-06 18:20:52 | 7 |
| 626XL5872UQ5 | Turkey - Breast, Bone - In | 2024-05-01 13:30:24 | 7 |
| 40F7ATQLZJ14 | Ice Cream - Strawberry | 2024-06-29 04:19:17 | 7 |
| ZH309ETZWMH5 | Flour - Semolina | 2023-08-04 19:49:06 | 8 |
| 0ENSQ213KB95 | Vinegar - Rice | 2024-05-12 08:26:04 | 8 |
| I4SK91Z8ZNY9 | Crackers - Graham | 2024-03-03 02:01:53 | 9 |
| PM8J9X0GXKA5 | Flower - Commercial Spider | 2023-08-23 05:07:27 | 10 |
| C5GU80FXWZ5C | Lettuce - Spring Mix | 2024-02-03 17:18:39 | 10 |
| TM59W31FHJXW | Tomatoes - Roma | 2024-02-10 23:54:32 | 10 |
| 75ZZRGS5GO76 | Lettuce - Romaine, Heart | 2024-05-16 04:07:55 | 10 |
+--------------+--------------------------------+---------------------+-----------+
29 rows in set (0.01 sec)
✔️ LATERAL JOIN의 기능
LATERAL JOIN은 서브쿼리가 메인 쿼리의 칼럼을 사용할 수 있는 형태의 조인이다. 이를 통해 이전에는 복잡하게 작성해야 했던 쿼리를 간단하고 직관적으로 작성할 수 있다.
✔️ LATERAL JOIN의 기본 문법
SELECT ...
FROM table1
JOIN LATERAL (
SELECT ...
FROM table2
WHERE table2.column = table1.column
) AS alias ON true;
여기서 table1의 각 행에 대해 table2를 참조하여 서브쿼리를 실행할 수 있다. 이 방식은 특히 각 그룹의 TOP N 레코드를 가져올 때 유용하다.
✔️ LATERAL JOIN 없이 쿼리 작성하기
LATERAL JOIN이 도입되기 전에는 각 회원별로 가장 마지막 주문을 가져오기 위해 서브쿼리나 GROUP BY와 함께 MAX 함수를 사용해야 했다.
SELECT
m.id AS member_id,
m.name AS member_name,
o.order_number,
o.product_name,
o.payment_datetime
FROM
members m
JOIN (
SELECT
o1.member_id,
o1.order_number,
o1.product_name,
o1.payment_datetime
FROM
orders o1
JOIN (
SELECT
member_id,
MAX(payment_datetime) AS latest_payment_datetime
FROM
orders
GROUP BY
member_id
) o2 ON o1.member_id = o2.member_id
AND o1.payment_datetime = o2.latest_payment_datetime
) o ON m.id = o.member_id;
쿼리가 복잡해지고 읽기 어려워진다. 대량의 데이터가 있을 경우, 서브쿼리와 조인 연산으로 인해 성능이 저하될 수도 있다.
✔️ LATERAL JOIN을 사용한 쿼리
SELECT *
FROM MEMBERS m
LEFT JOIN LATERAL (
SELECT *
FROM ORDERS o
WHERE o.member_id = m.id
ORDER BY o.payment_datetime DESC
LIMIT 1
) o2 ON o2.member_id = m.id
쿼리가 훨씬 간결해진것을 볼 수 있다.
✔️ 실행 계획
+----+-------------------+------------+------------+------+-----------------+-----------------+---------+-----------+------+----------+----------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------------+------------+------------+------+-----------------+-----------------+---------+-----------+------+----------+----------------------------+
| 1 | PRIMARY | m | NULL | ALL | NULL | NULL | NULL | NULL | 3966 | 100.00 | Rematerialize (<derived2>) |
| 1 | PRIMARY | <derived2> | NULL | ref | <auto_key0> | <auto_key0> | 4 | idus.m.id | 2 | 100.00 | NULL |
| 2 | DEPENDENT DERIVED | o | NULL | ref | idx_id_datetime | idx_id_datetime | 4 | idus.m.id | 3 | 100.00 | Using filesort |
+----+-------------------+------------+------------+------+-----------------+-----------------+---------+-----------+------+----------+----------------------------+
3 rows in set, 2 warnings (0.00 sec)