(2024-12-12) TIL

 

1.데일리 루틴(SQL)

WITH JULY_TOTALS AS (
  SELECT
    FLAVOR,
    SUM(TOTAL_ORDER) AS JULY_ORDER_TOTAL
  FROM
    JULY
  GROUP BY
    FLAVOR
),
COMBINED_TOTALS AS (
  SELECT
    F.FLAVOR,
    F.TOTAL_ORDER + COALESCE(J.JULY_ORDER_TOTAL, 0) AS TOTAL_ORDER_SUM
  FROM
    FIRST_HALF F
    LEFT JOIN JULY_TOTALS J ON F.FLAVOR = J.FLAVOR
)
SELECT
  FLAVOR
FROM
  COMBINED_TOTALS
ORDER BY
  TOTAL_ORDER_SUM DESC
LIMIT 3;

 

 CTE (Common Table Expressions)

• 문법: WITH CTE_NAME AS (SUBQUERY)
• 설명:
  • 복잡한 쿼리를 이해하기 쉽게 서브쿼리의 결과를 이름 붙여 재사용할 수 있도록 함.
  • 여러 CTE를 사용해 데이터를 단계별로 처리할 수 있음.

WITH JULY_TOTALS AS (
  SELECT FLAVOR, SUM(TOTAL_ORDER) AS JULY_ORDER_TOTAL
  FROM JULY
  GROUP BY FLAVOR
)