2023-03-04

SQLでのネストされたWITH句

SQL におけるネストされた WITH 句の使い方

SQLにおけるネストされた WITH 句は、他の方法では実現が困難な複雑なクエリを作成できる強力なツールです。1つ以上のCTE（共通テーブル式）を他のCTEの内部にネストすることで、前のCTEの結果を後続のCTE内で参照するクエリを作成できます。

SQLにおけるネストされたWITH句を使用するには、まずCTEの基本を理解する必要があります。CTEは、単一のSQL文の範囲内で定義される一時的な結果セットです。サブクエリを定義して、同じクエリ内で後で参照できるようにします。

以下は、5年以上勤務している従業員を検索するサブクエリを定義する単純なCTEの例です。

sql

WITH long_term_employees AS (
   SELECT employee_id, employee_name
   FROM employees
   WHERE hire_date < DATEADD(YEAR, -5, GETDATE())
)
SELECT *
FROM long_term_employees;

CTEをネストするには、最初のCTEを参照する第二のCTEを定義することができます。以下は、5年以上勤務しており、給与が50000ドル以上の従業員を全て検索するネストされたCTEの例です。

sql

WITH long_term_employees AS (
   SELECT employee_id, employee_name
   FROM employees
   WHERE hire_date < DATEADD(YEAR, -5, GETDATE())
), high_salary_employees AS (
   SELECT employee_id, employee_name
   FROM long_term_employees
   WHERE salary > 50000
)
SELECT *
FROM high_salary_employees;

この例では、最初にlong_term_employees CTEが定義され、次にhigh_salary_employees CTEが定義され、その定義の中で最初のCTEの結果が使用されています。

このようにCTEをネストすることで、複数のサブクエリや一時的な結果セットを参照する複雑なクエリを構築し、複雑なSQLクエリを書くことが容易になります。

SQL で 3 回ネストされた WITH 句の例

以下は、SQLで3回ネストされたWITH句を使用した例です。

会社の顧客注文に関するデータ、注文日、顧客ID、商品ID、および注文数量を含むordersというテーブルがあるとします。注文の月ごとに、さらに顧客の国ごとに分けられた、各商品カテゴリーの総収益を示すクエリを作成したいとします。

これを実現するために、3回ネストされたWITH句を使用する方法を以下に示します。

sql

WITH recursive revenue_by_month AS (
    SELECT
        product_categories.category_name,
        date_trunc('month', order_date) AS order_month,
        customers.country,
        sum(orders.quantity_ordered * products.price) AS revenue
    FROM orders
    JOIN products ON orders.product_id = products.product_id
    JOIN product_categories ON products.category_id = product_categories.category_id
    JOIN customers ON orders.customer_id = customers.customer_id
    GROUP BY 1, 2, 3
),
revenue_by_country AS (
    SELECT
        revenue_by_month.category_name,
        revenue_by_month.order_month,
        revenue_by_month.country,
        sum(revenue_by_month.revenue) AS total_revenue
    FROM revenue_by_month
    GROUP BY 1, 2, 3
),
revenue_by_category AS (
    SELECT
        revenue_by_country.order_month,
        revenue_by_country.country,
        product_categories.category_name,
        sum(revenue_by_country.total_revenue) AS monthly_total_revenue
    FROM revenue_by_country
    JOIN product_categories ON revenue_by_country.category_name = product_categories.category_name
    GROUP BY 1, 2, 3
)
SELECT
    revenue_by_category.order_month,
    revenue_by_category.country,
    revenue_by_category.category_name,
    revenue_by_category.monthly_total_revenue
FROM revenue_by_category
ORDER BY 1, 2, 3;

このクエリでは、ネストされたWITH句の最初の部分で、revenue_by_monthというCTEが定義されます。このCTEは、注文の月、顧客の国によって分けられた各商品カテゴリーの収益を計算します。

ネストされたWITH句の2番目の部分では、revenue_by_countryというCTEが定義されます。このCTEは、商品カテゴリー、月、および国ごとに収益を集計します。

ネストされたWITH句の3番目の部分では、revenue_by_categoryというCTEが定義されます。このCTEは、注文の月、顧客の国によって分けられた各商品カテゴリーの月次総収益を計算します。

最後に、外部のクエリでは、revenue_by_category CTEから注文月、国、カテゴリ名、月次総収益を選択し、結果を月、国、カテゴリで並べ替えます。このクエリは、注文の月と顧客の国によって分類された各製品カテゴリの総収益を表示する結果セットを返します。

SQLでのネストされたWITH句

SQL におけるネストされた WITH 句の使い方

SQL で 3 回ネストされた WITH 句の例

SQLにおけるサブクエリ

SQLにおけるDDL、DML、DCL

Ryusei Kakujo