MySQL/MariaDB でストアドプロシージャとユーザー定義関数を使ってフロー結果を累積する

2024-06-20

MySQL/MariaDB でフロークエリ結果を累積 (ストック) する方法

方法 1: サブクエリを使用した累積

この方法は、サブクエリを使用して、前のレコードの累積値を取得し、現在のレコードに足すことで累積値を計算します。

SELECT
  id,
  transaction_amount,
  (
    SELECT SUM(transaction_amount)
    FROM transactions AS t2
    WHERE t2.id <= t1.id
  ) AS cumulative_amount
FROM transactions AS t1
ORDER BY id;

このクエリは、transactions テーブルのすべてのレコードを id 順に処理し、各レコードの transaction_amount と、そのレコードまでの累積 transaction_amount を計算します。

方法 2: ウィンドウ関数を使用した累積

この方法は、MySQL 8.0以降で利用可能なウィンドウ関数を使用して、累積値を計算します。

SELECT
  id,
  transaction_amount,
  SUM(transaction_amount) OVER (ORDER BY id) AS cumulative_amount
FROM transactions;

どちらの方法を選ぶべきか

サブクエリを使用した累積は、MySQL 5.7 以前のバージョンでも利用可能ですが、サブクエリが処理されるたびに毎回テーブルをスキャンする必要があるため、処理速度が遅くなります。
ウィンドウ関数を使用した累積は、MySQL 8.0以降でのみ利用可能ですが、サブクエリよりも処理速度が速く、コードも簡潔になります。

その他の注意点

上記の例では、transactions テーブルの id 列を主キーとして使用しています。主キー以外の列で累積したい場合は、ORDER BY 句を変更する必要があります。
累積値を計算する列は、数値型である必要があります。
累積値を計算する列に NULL 値が含まれている場合は、SUM() 関数に IGNORE NULL オプションを指定する必要があります。

CREATE TABLE transactions (
  id INT PRIMARY KEY AUTO_INCREMENT,
  transaction_date DATE,
  transaction_amount DECIMAL(10,2)
);

INSERT INTO transactions (transaction_date, transaction_amount)
VALUES
  ('2023-10-05', 100.00),
  ('2023-10-06', -50.00),
  ('2023-10-07', 200.00),
  ('2023-10-08', -150.00);

方法 1: サブクエリを使用した累積

SELECT
  id,
  transaction_date,
  transaction_amount,
  (
    SELECT SUM(transaction_amount)
    FROM transactions AS t2
    WHERE t2.id <= t1.id
  ) AS cumulative_amount
FROM transactions AS t1
ORDER BY id;

方法 2: ウィンドウ関数を使用した累積

SELECT
  id,
  transaction_date,
  transaction_amount,
  SUM(transaction_amount) OVER (ORDER BY id) AS cumulative_amount
FROM transactions;

出力結果

| id | transaction_date | transaction_amount | cumulative_amount |
|---|---|---|---|
| 1 | 2023-10-05 | 100.00 | 100.00 |
| 2 | 2023-10-06 | -50.00 | 50.00 |
| 3 | 2023-10-07 | 200.00 | 250.00 |
| 4 | 2023-10-08 | -150.00 | 100.00 |

説明

方法 1 では、サブクエリを使用して、前のレコードまでの累積 transaction_amount を取得しています。

どちらの方法でも、結果は同じになります。

MySQL/MariaDB でフロークエリ結果を累積 (ストック) するには、サブクエリまたはウィンドウ関数を使用することができます。状況に合わせて適切な方法を選択してください。

MySQL/MariaDB でフロークエリ結果を累積 (ストック) するその他の方法

方法 3: 変数を使用した累積

この方法は、ループの中で変数を使用して累積値を保持することで、累積値を計算します。

SET @cumulative_amount = 0;

SELECT
  id,
  transaction_date,
  transaction_amount,
  @cumulative_amount := @cumulative_amount + transaction_amount AS cumulative_amount
FROM transactions
ORDER BY id;

この方法は、ストアドプロシージャを使用して、累積処理をカプセル化します。

DELIMITER $$

CREATE PROCEDURE accumulate_transactions(
  INOUT p_cumulative_amount DECIMAL(10,2)
)
BEGIN
  DECLARE v_transaction_id INT;
  DECLARE v_transaction_date DATE;
  DECLARE v_transaction_amount DECIMAL(10,2);

  CURSOR c_transactions IS
    SELECT id, transaction_date, transaction_amount
    FROM transactions
    ORDER BY id;

  OPEN c_transactions;

  LOOP
    FETCH c_transactions INTO v_transaction_id, v_transaction_date, v_transaction_amount;

    IF FOUND THEN
      SET p_cumulative_amount := p_cumulative_amount + v_transaction_amount;
    END IF;

  END LOOP;

  CLOSE c_transactions;
END $$

DELIMITER ;

CALL accumulate_transactions(@cumulative_amount);

SELECT @cumulative_amount AS cumulative_amount;

このストアドプロシージャは、p_cumulative_amount という入力パラメータを使用して累積値を受け渡し、transactions テーブルのすべてのレコードを処理して累積値を計算します。

方法 5: ユーザー定義関数を使用した累積

この方法は、ユーザー定義関数を使用して、累積処理を再利用可能なモジュールとして定義します。

DELIMITER $$

CREATE FUNCTION accumulate(
  IN v_transaction_amount DECIMAL(10,2),
  INOUT p_cumulative_amount DECIMAL(10,2)
)
BEGIN
  SET p_cumulative_amount := p_cumulative_amount + v_transaction_amount;

  RETURN p_cumulative_amount;
END $$

DELIMITER ;

SELECT
  id,
  transaction_date,
  transaction_amount,
  accumulate(transaction_amount, @cumulative_amount) AS cumulative_amount
FROM transactions
ORDER BY id;