欠損値を含む期間の補完クエリ

例えば、2008年の日別注文件数を取得する場合を考えます。まず、注文テーブルから2008年の注文を集計する単純なクエリは次のようになります。

SELECT orderdate, COUNT(*) AS daily_orders
FROM sales.orders
WHERE orderdate BETWEEN '2008-01-01' AND '2008-12-31'
GROUP BY orderdate
ORDER BY orderdate;

しかし、この方法では注文が存在しない日付は結果に含まれません。財務レポートなどでは連続した日付で結果を表示する必要があるため、日付シーケンスを生成して補完する手法が有効です。

まず、連続した数値を保持する補助テーブルを作成します。

CREATE TABLE sequence_numbers (num INT PRIMARY KEY);

1から必要な数値までを挿入します。

DECLARE @counter INT = →;
WHILE @counter <= →@counter →;
BEGIN
    INSERT INTO sequence_numbers (num) VALUES (@counter);
    SET @counter = @counter + →;
END

この数値テーブルを利用して、2008年の全日付を生成できます。

SELECT DATEADD(DAY, num, '2007-12-31') AS full_date
FROM sequence_numbers
WHERE DATEADD(DAY, num, '2007-12-31') < '2009-01-01';

生成した日付シーケンスと注文テーブルを外部結合することで、注文の有無にかかわらず全ての日付をカバーする結果が得られます。

SELECT
    DATEADD(DAY, s.num, '2007-12-31') AS report_date,
    COUNT(o.orderid) AS order_count
FROM sequence_numbers s
LEFT JOIN sales.orders o ON DATEADD(DAY, s.num, '2007-12-31') = o.orderdate
WHERE DATEADD(DAY, s.num, '2007-12-31') < '2009-01-01'
GROUP BY DATEADD(DAY, s.num, '2007-12-31')
ORDER BY report_date;

基本サブクエリの活用

集約結果を条件として利用する典型的な例として、最年長の従業員情報の取得があります。単純な集約クエリでは詳細情報は取得できません。

-- これでは名前など他の列は取得できない
SELECT MAX(birthdate) FROM hr.employees;

集約関数の結果をサブクエリで条件として使用します。

SELECT firstname, lastname, birthdate
FROM hr.employees
WHERE birthdate = (SELECT MAX(birthdate) FROM hr.employees);

複数段階のサブクエリも可能です。最高額の注文を行った顧客を特定する例を示します。

SELECT custid, companyname, country
FROM sales.customers
WHERE custid = (
    SELECT custid
    FROM Sales.OrderValues
    WHERE amount = (SELECT MAX(amount) FROM Sales.OrderValues)
);

相関サブクエリによる分析

顧客ごとの注文件数を、相関サブクエリを用いて計算する方法があります。外部クエリの各行に対して、関連する内部クエリが実行されます。

SELECT
    c.custid,
    c.companyname,
    (
        SELECT COUNT(*)
        FROM sales.orders o
        WHERE o.custid = c.custid
    ) AS total_orders
FROM sales.customers c
ORDER BY c.custid;

この方法では、顧客テーブルの各行ごとに、対応する注文テーブルの該当行数をカウントするサブクエリが実行されます。

複数値サブクエリと存在確認

顧客は存在するが、仕入先が存在しない国を検索する場合、NOT IN演算子を使用できます。

SELECT DISTINCT country
FROM sales.customers
WHERE country NOT IN (SELECT country FROM production.suppliers);

同様の結果をEXISTS述語で得ることもできます。この場合、サブクエリは論理値（真/偽）を返します。

SELECT DISTINCT c.country
FROM sales.customers c
WHERE NOT EXISTS (
    SELECT → FROM production.suppliers s
    WHERE s.country = c.country
);

複雑な分析クエリの例

注文履歴において、各注文の前後の注文IDを特定するクエリを考えます。相関サブクエリを活用した実装例です。

SELECT
    o_current.orderid AS current_order,
    (
        SELECT MAX(orderid)
        FROM sales.orders o_prev
        WHERE o_prev.orderid < o_current.orderid
    ) AS previous_order,
    (
        SELECT MIN(orderid)
        FROM sales.orders o_next
        WHERE o_next.orderid > o_current.orderid
    ) AS next_order
FROM sales.orders o_current
ORDER BY current_order;

年間の累積集計も重要な分析手法です。年次売上データから累計を計算する例を示します。

SELECT
    year_data.sale_year,
    year_data.order_volume,
    (
        SELECT SUM(sub_year.order_volume)
        FROM Sales.OrderSummaryByYear sub_year
        WHERE sub_year.sale_year <= year_data.sale_year
    ) AS cumulative_total
FROM Sales.OrderSummaryByYear year_data
ORDER BY year_data.sale_year;