데이터베이스/0 + MySQL

[MySQL] Lateral Derived Table 활용하는 방법

힘들면힘을내는쿼카 2024. 12. 24. 00:12

728x90

Lateral Derived Table 이란?!

Derived Table(파생 테이블)은 쿼리의 from 절에서 서브쿼리를 통해 생성되는 임시 테이블을 의미합니다.
일반적으로 Derived Table은 선행 테이블의 컬럼을 참조할 수 없으나, Lateral Derived Table은 참조 가능합니다.
정의된 Dervied Table 앞부분에 LATERAL 키워드를 추가해서 사용 합니다.
참조한 값을 바탕으로 동적으로 결과를 생성할 수 있습니다.

동작 방법

select e.emp_no,
        s.sales_count, 
        s.total_sales
from employees e
left join lateral(
            select count(*) as sales_count,
            ifnull(sum(total_price), 0), as total_sales
            from sales
            where emp_no = e.emp_no
) s on true;

lateral derived 테이블의 쿼리를 살펴보면, sales 테이블의 선행 테이블인 employees 테이블의 emp_no 를 where 절에서 사용하는 것을 확인할 수 있습니다.

또한, lateral 로 join 이 실행된 경우 일반적으로 서브쿼리 where 절에 join 조건이 사용 됩니다.

즉, where emp_no = e.emp_no 가 join 조건이고 문법을 준수하기 위해서 on 절에 true를 사용(on true)합니다.

참고

MariaDB에서는 lateral 키워드 사용이 불가 합니다.

내부적으로 Lateral Derived 최적화를 지원하는 것으로 보입니다…!

활용 예시

종속 서브 쿼리의 다중 값 반환

부서별 가장 먼저 입사한 직원의 입사일과 직원 이름을 조회

Query 1)

부서별 가장 먼저 입사한 직원의 입사일과 직원 이름을 조회

select d.dept_name,
       (select e.hire_date as earliest_hire_date, -- 입사일
               concat(e.first_name, ' ', e.last_name) as full_name -- 직원 이름
        from dept_emp de
        inner join employees e on e.emp_no = de.emp_no
        where de.dept_no = d.dept_no
        order by e.hire_date limit 1)
from departments d;

ERROR 1241 (21000): Operand should contain 1 column(s)

위 쿼리를 실행하면 에러가 발생하는데, select 절의 서브쿼리가 2개의 컬럼을 조회하고 있기 때문 입니다.

참고

select 절에서 서브쿼리를 사용하는 경우에는 하나의 컬럼 값만 반환이 가능 합니다.

Query 2)

select 절의 서브쿼리를 사용할 때는 1개의 컬럼만 반환 가능하니까, 서브쿼리를 2개 만들어 볼까?!

select 
        d.dept_name, 
        (select e.hire_date -- 입사일
         from dept_emp de
         inner join employees e on e.emp_no = de.emp_no
         where de.dept_no = d.dept_no
         order by e.hire_date limit 1) as earliest_hire_date,
        (select concat(e.first_name, ' ', e.last_name) -- 직원 이름
         from dept_emp de
         inner join employees e on e.emp_no = de.emp_no
         where de.dept_no = d.dept_no
         order by e.hire_date limit 1) as full_name
from departments d;

동일한 데이터를 가져오는 서브쿼리가 중복해서 실행되기 때문에 매우 비효율적 입니다…. 🥲

🤔 한번에 조회할 수 있는 방법이 없을까….?

Query 3)

select 절의 서브쿼리를 from 절에서 lateral derived 테이블로 전환해보자!

select d.dept_name,
       x.earliest_hire_date, -- 입사일
       x.full_name -- 직원 이름
from departments d
inner join lateral (
        select e.hire_date as earliest_hire_date,
               concat(e.first_name, ' ', e.last_name) as full_name
        from dept_emp de
        inner join employees e on e.emp_no = d.emp_no
        where de.dept_no = d.dept_no
        order by e.hire_date limit 1) x;

lateral 을 사용하면 1개의 서브쿼리로 원하는 값들을 모두 조회할 수 있기 때문에 가독성도 좋아지고, 쿼리 처리 효율도 향상 됩니다~!

select 절 내 연산 결과 반복 참조

일별 매출 데이터를 조회하는 쿼리

Query 1)

일별 매출 데이터를 조회하는 쿼리

select (total_sales * margin_rate) as profit,
       ((total_sales * margin_rate) / total_sales_number) as avg_profit,
       (expected_sales * margin_rate) as expected_profit,
       ((total_sales * margin_rate) / (expected_sales * margin_rate) * 100) as sales_achievement_rate
from daily_revenue
where sales_date = '2023-12-01'

select 문 내에서 연산 결과를 참조하기 위해서 total_sales * margin_rate, expected_sales * margin_rate 같은 동일한 연산을 중복으로 사용하고 있습니다..

참고

select 절에서 계산된 각각의 열 값은 동일한 select 절 내에 다른 열에서 참조할 수 없습니다.

Query 2)

중복 연산하는 부분을 제거하고, 먼저 계산된 값을 참조해서 연산해보자!

select profit,
       avg_profit,
       expected_profit,
       sales_achievement_rate
from daily_revenue,
    lateral (select (total_sales * margin_rate) as profit) p,
    lateral (select (profit / total_sales_number) as avg_profit) ap,
    lateral (select (expected_sales * margin_rate) as expected_profit) ep,
    lateral (select (profit / expected_profit) as sales_achievement_rate) sar
where sales_date = '2023-12-01';

from절을 보면 lateral 쿼리에서 연산한 값을 참조해서 사용하는 것을 확인할 수 있습니다!

선행 데이터를 기반으로 한 데이터 분석

처음 서비스에 가입하고나서 일주일내로 결제 완료한 사용자의 비율
- 2024년 1월에 가입한 유저들을 대상으로 분석
- 사용자 관련 이벤트 데이터를 저장하는 user_events 테이블 활용(약 50만건)

create table user_events (
    id int not null auto_increment,
    user_id int not null,
    event_type varchar(50) not null,
    ... ,
    created_at datetime not null,
    primary key (id),
    key ix_eventtype_userid_createdat (event_type, user_id, created_at)
);

요구사항: 2024년 1월에 가입한 유저들 중 일주일내로 결제까지 완료한 사용자의 비율

Query 1)

2024년 1월에 가입한 유저들 중 일주일 내로 결제까지 완료한 사용자 비율

select sum(sign_up) as signed_up,
       sum(complete_purchase) as completed_purchase,
      (sum(complete_purchase) / sum(sign_up) * 100) as conversion_rate
from (
            -- 1월에 새로 가입한 유저 목록
            select user_id,
                   1 as sign_up,
                   min(created_at) as sign_up_time
            from user_events
            where event_type = 'SIGN_UP'
            and created_at >= '2024-01-01' and created_at < '2024-02-01'
            group by user_id
) e1 left join (
            -- 처음 결제한 시점 정보 목록
            select user_id, 
                   1 as complete_purchase,
                   min(created_at) as complete_purchase_time
            from user_events
            where event_type = 'COMPLETE_PURCHASE'
            group by user_id
) e2 on e2.user_id = e1.user_id
        and e2.complete_purchase_time >= e1.sign_up_time -- 처음 결제 시점 >= 가입일시
        and e2.complete_purchase_time < date_add(e1.sign_up_time, interval 7 day); -- 첫 결제 시점 < 가입일시 + 7일

e2 부분의 쿼리를 보면 결제에 해당하는 모든 회원들에 대해서 그루핑을 수행하고 있습니다.

우리가 원하는 대상은 2024년 1월에 가입한 유저들만 필요한데도 말이죠!

Query 2)

2024년 1월에 가입한 유저들 중 일주일 내로 결제까지 완료한 사용자 비율

select sum(sign_up) as signed_up,
       sum(complete_purchase) as completed_purchase,
       (sum(complete_purchase) / sum(sign_up) * 100) as conversion_rate
from (
            -- 1월에 새로 가입한 유저 목록
            select user_id,
                   1 as sign_up,
                   min(created_at) as sign_up_time
            from user_events
            where event_type = 'SIGN_UP'
            and created_at >= '2024-01-01'
            and created_at < '2024-02-01'
            group by user_id
) e1 left join lateral (
            -- 가입 후 일주일 내에 결제한 이력
            select 1 as complete_purchase
            from user_events
            where user_id = e1.user_id
            and event_type = 'COMPLETE_PURCHASE'
            and created_at >= e1.sign_up_time -- 결제 시점 >= 가입일시
            and created_at < date_add(e1.sign_up_time, interval 7 day) -- 결제 시점 < 가입일시 + 7일
            order by event_type, user_id, created_at
            limit 1
) e2 on true;

e2 부분은 분석 대상 유저들에 대해서만 추가적인 조건을 확인합니다.

결과

5배 정도의 성능차이가 있는 것을 확인할 수 있습니다!

Top N 데이터 조회

카테고리별 조회수가 가장 높은 3개 기사 추출

create table categories (
    id int not null auto_increment,
    name varchar(50) not null,
    ... ,
    primary key(id)
);

create table articles (
    id int not null auto_increment,
    category_id int not null,
    title varchar(255) not null,
    views int not null,
    ... ,
    primary key (id),
    key ix_categoryid_views (category_id, views)
)

Query 1)

카테고리별 조회수가 가장 높은 3개의 기사 추출

select x.name,
       x.title,
       x.views
from (
    select c.name,
           a.title,
           a.views,
           row_number() over (partition by a.category_id order by a.views desc) 
                as article_rank
    from categories c
    inner join articles a on a.category_id = c.id
) x
where x.article_rank <= 3;

윈도우 함수를 사용해서 category_id 별로 데이터를 그룹화 하고 views를 기준 내림차순으로 순위를 부여하고 있습니다.(row_number() over (partition by a.category_id order by a.views desc))

Query 2)

카테고리별 조회수가 가장 높은 3개의 기사 추출

select c.name,
       a.title,
       a.views
from categories c
inner join lateral (
                select category_id,
                       title,
                       views
                from articles
                where category_id = c.id
                order by category_id desc, views desc
                limit 3
) a;

articles 테이블에서 전체 데이터가 아닌 카테고리 별로 원하는 데이터 3건에서만 조회하여 처리하기 때문에 query1에서 사용했던 query 보다 처리효율이 훨씬 향상된 것을 확인할 수 있습니다!

참고

https://www.inflearn.com/course/real-mysql-part-1/dashboard

Real MySQL 시즌 1 - Part 1 강의 | 이성욱 - 인프런

이성욱 | MySQL의 핵심적인 기능들을 살펴보고, 실무에 효과적으로 활용하는 방법을 배울 수 있습니다. 또한, 오랫동안 관성적으로 사용하며 무심코 지나쳤던 중요한 부분들을 새롭게 이해하고,

www.inflearn.com

728x90

저작자표시 비영리 변경금지

'데이터베이스 > 0 + MySQL' 카테고리의 다른 글

[MySQL] Error Handling (1)	2024.12.28
[MySQL] 쿼리 실행 구조 (0)	2024.12.28
[MySQL] Stored Function 에서 많이 하는 실수 방지하기 (0)	2024.08.21
[MySQL] left join 아무생각 없이 사용하고 있는 것은 아니지? (0)	2024.08.15
[MySQL] 페이징 쿼리 작성하기 (0)	2024.07.21

현재글[MySQL] Lateral Derived Table 활용하는 방법

250x250

어제 보다 나은 내일은 토요일이다.

jpa, 디자인 패턴, 스프링, ElasticSearch, 기계학습, 컴퓨터 구조, 헤드퍼스트 디자인 패턴, java, 운영체제, 패스트캠퍼스, 머신러닝, 스프링 MVC, 정보처리기사, dfs, 스프링 시큐리티, 정보처리기사 실기, udp, MySQL, spring boot, JUnit5,

Today :
Yesterday :

힘들면 힘을내자!

[MySQL] Lateral Derived Table 활용하는 방법

Lateral Derived Table 이란?!

동작 방법

활용 예시

종속 서브 쿼리의 다중 값 반환

Query 1)

Query 2)

Query 3)

select 절 내 연산 결과 반복 참조

Query 1)

Query 2)

선행 데이터를 기반으로 한 데이터 분석

Query 1)

Query 2)

결과

Top N 데이터 조회

Query 1)

Query 2)

참고

'데이터베이스 > 0 + MySQL' 카테고리의 다른 글

'데이터베이스/0 + MySQL'의 다른글

티스토리툴바

« 2025/01 »
일	월	화	수	목	금	토
			1	2	3	4
5	6	7	8	9	10	11
12	13	14	15	16	17	18
19	20	21	22	23	24	25
26	27	28	29	30	31

[MySQL] Lateral Derived Table 활용하는 방법

Lateral Derived Table 이란?!

동작 방법

활용 예시

종속 서브 쿼리의 다중 값 반환

Query 1)

Query 2)

Query 3)

select 절 내 연산 결과 반복 참조

Query 1)

Query 2)

선행 데이터를 기반으로 한 데이터 분석

Query 1)

Query 2)

결과

Top N 데이터 조회

Query 1)

Query 2)

참고

'데이터베이스 > 0 + MySQL' 카테고리의 다른 글

'데이터베이스/0 + MySQL'의 다른글

관련글

티스토리툴바