SQL Serverで集計関数を極める！UPDATEクエリをパワーアップするテクニック集

SQL UPDATE クエリにおける集計関数：詳細ガイド

SQL UPDATE クエリで集計関数を使用すると、データベース内のデータを効率的に更新できます。このガイドでは、SQL Server（T-SQL を含む）における UPDATE クエリでの集計関数の使用方法について、詳細な説明と実用的例を交えて解説します。

従来の UPDATE クエリでは、個々のレコード値を直接更新していましたが、集計関数を使用すると、条件に基づいて複数レコードを一括更新することができ、大幅な処理効率化と記述量の削減を実現できます。

よく使用される集計関数

• COUNT: 指定された条件に一致するレコード数をカウントします。
• SUM: 指定された列の値の合計を算出します。

基本的な構文

UPDATE table_name
SET column_name = aggregate_function(column_name)
WHERE condition;

使用例

例 1：特定のカテゴリーの全商品価格を10%値上げする

UPDATE products
SET price = price * 1.1
WHERE category = 'Electronics';

例 2：各顧客の注文個数合計を「注文履歴」テーブルに追加

INSERT INTO order_history (customer_id, total_orders)
SELECT customer_id, COUNT(order_id)
FROM orders
GROUP BY customer_id;

例 3：在庫切れ商品のステータスを更新

UPDATE products
SET stock_status = 'Out of Stock'
WHERE quantity = 0;

注意事項

• 集計関数は、UPDATE 対象となる列と同じデータ型である必要があります。
• WHERE 句を使用して、更新対象レコードを絞り込むことができます。
• NULL 値は、集計関数によっては無視される場合があります。
• 更新処理を実行する前に、必ずバックアップを取るようにしてください。

高度な応用例

• GROUP BY 句と組み合わせて、グループごとの集計結果に基づいて更新を実行できます。
• HAVING 句と組み合わせて、集計結果に基づいて更新対象レコードをさらに絞り込むことができます。
• サブクエリを使用して、集計関数の引数に動的な値を渡すことができます。

注記:

• 上記の例はあくまで基本的な使用方法であり、より複雑な処理には応用できます。
• 具体的な構文やオプションは、使用している SQL Server のバージョンによって異なる場合があります。

この例では、orders テーブルから特定の期間における注文合計金額を算出し、customers テーブルの total_spent 列に更新します。

UPDATE customers
SET total_spent = (
    SELECT SUM(amount)
    FROM orders
    WHERE orders.customer_id = customers.customer_id
        AND orders.order_date BETWEEN '2023-01-01' AND '2023-12-31'
);

各商品の在庫状況を更新：在庫切れの場合は「在庫切れ」、在庫がある場合は「在庫あり」

この例では、products テーブルの stock_status 列を、在庫数量に基づいて「在庫切れ」または「在庫あり」に更新します。

UPDATE products
SET stock_status = CASE
    WHEN quantity = 0 THEN 'Out of Stock'
    ELSE 'In Stock'
END;

顧客の平均注文金額を「顧客履歴」テーブルに追加

この例では、orders テーブルから顧客ごとの平均注文金額を算出し、customer_history テーブルに新しいレコードとして挿入します。

INSERT INTO customer_history (customer_id, average_order_amount)
SELECT customer_id, AVG(amount)
FROM orders
GROUP BY customer_id;

売上ランキング上位10商品を「ベストセラー」テーブルに抽出

この例では、products テーブルから売上ランキング上位10商品を抽出し、bestsellers テーブルに新しいレコードとして挿入します。

INSERT INTO bestsellers (product_id, sales_rank)
SELECT product_id, ROW_NUMBER() OVER (ORDER BY quantity DESC) AS sales_rank
FROM products
WHERE quantity > 0
ORDER BY sales_rank
LIMIT 10;

各部門の給与総額を「部門別給与」テーブルに更新

この例では、employees テーブルから各部門の給与総額を算出し、department_salaries テーブルに新しいレコードとして挿入または更新します。

MERGE INTO department_salaries AS target
USING (
    SELECT department_id, SUM(salary) AS total_salary
    FROM employees
    GROUP BY department_id
) AS source
ON target.department_id = source.department_id
WHEN MATCHED THEN
    UPDATE SET total_salary = source.total_salary
WHEN NOT MATCHED THEN
    INSERT (department_id, total_salary)
    VALUES (source.department_id, source.total_salary);

• 上記の例はあくまで応用例であり、実際の使用状況に合わせて調整する必要があります。

サブクエリを使用すると、UPDATE 対象となる列の値を動的に計算することができます。

例: 特定の顧客の注文合計金額を orders テーブルの total_amount 列に更新する

UPDATE orders
SET total_amount = (
    SELECT SUM(order_details.quantity * order_details.unit_price)
    FROM order_details
    WHERE order_details.order_id = orders.order_id
);

CTE（共通表式）を使用した更新

CTE（共通表式）を使用すると、複雑なクエリをより読みやすく、再利用しやすいモジュールに分割することができます。

例: 売上ランキング上位10商品を bestsellers テーブルに抽出する

WITH bestsellers_data AS (
    SELECT product_id, ROW_NUMBER() OVER (ORDER BY quantity DESC) AS sales_rank
    FROM products
    WHERE quantity > 0
)
INSERT INTO bestsellers (product_id, sales_rank)
SELECT * FROM bestsellers_data
LIMIT 10;

窓関数を使用した更新

窓関数を使用すると、特定の行グループ内の他の行を参照した値を計算することができます。

例: 各商品の前月比売上変化を products テーブルの sales_change 列に更新する

UPDATE products
SET sales_change = (
    SELECT (current_month_sales - previous_month_sales) / NULLIF(previous_month_sales, 0) * 100
    FROM (
        SELECT product_id, SUM(quantity) AS sales, MONTH(order_date) AS order_month
        FROM orders
        GROUP BY product_id, MONTH(order_date)
        ORDER BY product_id, order_month
    ) AS sales_data
    OVER (PARTITION BY product_id ORDER BY order_month ROWS BETWEEN PRECEDING 1 AND PRECEDING 2)
)
WHERE quantity > 0;

トランザクションを使用した更新

トランザクションを使用すると、複数の UPDATE 操作を原子的に実行することができます。

例: 顧客の注文ステータスを変更し、注文履歴を更新する

BEGIN TRANSACTION;

UPDATE orders
SET order_status = 'Shipped'
WHERE order_id = 12345;

INSERT INTO order_history (order_id, status_change_date)
VALUES (12345, GETDATE());

COMMIT TRANSACTION;

これらの方法は、SQL UPDATE クエリにおける集計関数の高度な機能と、複雑なデータ操作を効率的に実行する能力を示しています。

• 上記の例はあくまで高度な方法であり、基本的な概念を理解した上で適用する必要があります。