real mysql - lateral derived table

Lateral Derived Table

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

활용 예제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

SELECT절에서 서브쿼리를 사용하는 경우 하나의 컬럼 값만 반환 가능 - 에러 발생

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 earliest_hire_date,
FROM departmets d 

동일한 데이터를 가져오는 서브쿼리가 중복해서 실행되므로 비효율적

SELECT d.dept_name,
		x.ealriest_hire_date,
        x.full_name
FROM departments d
INNER JOIN LATERAL (
		SELECT e.hire_date as ealiest_hire_Date,
        		CONCAT(e.first_name, ' ', e.last_naem) AS full_name
        FROM dept_emp dp
        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 절에서 LATERAL 키워드를 사용해 하나의 서브쿼리로 원하는 값들을 모두 조회

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

SELECT (total_sales * margin_rate) as profit,
..
..
..
FROM daily_revenue
WHERE sales_date ='2023-12-01'

SELECT 문 내에서 연산결과를 참조하기 위해 동일한 연산을 중복 기재해서 사용

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 profit) ap,
LATERAL (SELECT (expected_sales * margin_rate) AS expected_profit) ep,
LATERAL (SELECT (profit / expected_profit * 100) AS sales_achievement_rate) sar,

WHERE sales_Date='2023-12-01`;

FROM 절에서 LATERAL 키워드를 사용해 연산 결과 값을 참조

처음 서비스에 가입하고 나서 일주일 내로 결제완료한 사용자의 비율

• 2024년 1월에 가입한 유저대상
• user_evenet테이블 활용(약 50만건)
• 일주일내로 결제까지 완료한 사용자의 비율

SELECT 중략..
FROM (
		SELECT asdf
        FROM user_events
        WHERE event_type='SIGN_up' ) 
        LEFT JOIN LATERAL (
        	SELECT 1 as complete_purchase
            FROM user_events
            WHERE user_id = e1.user_id
            AND event
            .
            .
            .
            ORDER BY event_type, user_ud, created_at
            LIMIT 1
       ) e2 ON TRUE;

TOP N 데이터 조회

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

SELECT c.name, a.title, a.views
FROM categories c
INNER JOIN LATERAL (
		SELECT category_id, titlem views
        FROM articles
        WHERE category_id=c.id
        ORDER BY category_id DESC, views DESC
        LIMIT 3
) a