MariaDB Window関数：マスターすれば最強！SQLの達人になるためのガイド

MariaDBのWindow関数：SQLステートメントと構造における概要

MariaDBのWindow関数は、範囲内のデータに基づいて計算を実行する特殊な関数です。従来のSQL関数とは異なり、単一の行ではなく、複数の行にわたって計算を行うことができます。これは、データ分析、集計、レポート作成などに非常に便利な機能です。

Window関数の利点

• 複雑な分析を簡潔に記述できる
• 複数回のSELECTクエリを削減できる
• パフォーマンスを向上できる

Window関数の種類

MariaDBでは、さまざまな種類のWindow関数が用意されています。

• 集計関数：SUM、AVG、COUNTなど
• 順序関数：ROW_NUMBER、RANK、PERCENTILE_CONTなど
• 移動平均関数：MOVING_AVG、FIRST_VALUE、LAST_VALUEなど

Window関数は、次の構文を使用して記述されます。

SELECT
  expression,
  window_function(expression) OVER (window_definition)
FROM table;

• expression: 計算対象となる列
• window_function: 使用するWindow関数
• window_definition: ウィンドウ範囲を定義するオプション

Window定義では、ウィンドウ範囲を指定します。

• PARTITION BY: データをグループ化する列
• ORDER BY: データを並べ替える列
• ROWS BETWEEN: ウィンドウの開始行と終了行を指定

例

次の例では、従業員の部門別に平均給与を計算します。

SELECT
  department_id,
  AVG(salary) OVER (PARTITION BY department_id) AS avg_salary
FROM employees;

MariaDBのWindow関数について詳しくは、以下のリソースを参照してください。

補足

• 上記はMariaDB 10.2以降で利用可能な機能です。
• Window関数は複雑なため、理解と習得に時間がかかる場合があります。
• 性能上の理由から、Window関数は大量のデータに対して使用する場合、注意が必要です。

MariaDB Window関数 サンプルコード

SELECT
  department_id,
  AVG(salary) OVER (PARTITION BY department_id) AS avg_salary
FROM employees;

従業員の給与ランク

SELECT
  name,
  salary,
  RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
FROM employees;

過去3ヶ月の移動平均売上

SELECT
  date,
  SUM(sales) OVER (
    ORDER BY date
    ROWS BETWEEN 3 PRECEDING AND CURRENT ROW
  ) AS moving_average
FROM sales_data;

各商品の累計販売数

SELECT
  product_id,
  SUM(quantity) OVER (
    ORDER BY date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS cumulative_sales
FROM sales_data;

各顧客の購入金額上位3件

SELECT
  customer_id,
  product_id,
  quantity,
  RANK() OVER (PARTITION BY customer_id ORDER BY quantity DESC) AS rank
FROM sales_data
WHERE rank <= 3;

各部門の給与中央値

SELECT
  department_id,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median_salary
FROM employees;

過去1年間の売上成長率

SELECT
  date,
  (SUM(sales) OVER (
    ORDER BY date
    ROWS BETWEEN 12 PRECEDING AND CURRENT ROW
  ) - SUM(sales) OVER (
    ORDER BY date
    ROWS BETWEEN 13 PRECEDING AND CURRENT ROW
  )) / SUM(sales) OVER (
    ORDER BY date
    ROWS BETWEEN 13 PRECEDING AND CURRENT ROW
  ) AS growth_rate
FROM sales_data;

各商品の平均売上と標準偏差

SELECT
  product_id,
  AVG(sales) OVER (PARTITION BY product_id) AS avg_sales,
  STDDEV(sales) OVER (PARTITION BY product_id) AS stddev_sales
FROM sales_data;

各顧客の購入頻度

SELECT
  customer_id,
  COUNT(*) OVER (PARTITION BY customer_id) AS purchase_count
FROM sales_data;

各商品の在庫回転率

SELECT
  product_id,
  SUM(quantity_sold) OVER (
    ORDER BY date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) / AVG(quantity_in_stock) OVER (
    ORDER BY date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS inventory_turnover
FROM sales_data;

各従業員の直近3ヶ月の給与変化

SELECT
  name,
  salary,
  (salary - LAG(salary, 3) OVER (ORDER BY date)) / LAG(salary, 3) OVER (ORDER BY date) AS salary_change
FROM employees;

各商品の販売傾向

SELECT
  product_id,
  SUM(sales) OVER (
    ORDER BY date
    ROWS BETWEEN 7 PRECEDING AND CURRENT ROW
  ) AS

その他のWindow関数サンプルコード

SELECT
  name,
  salary,
  salary - LAG(salary, 1) OVER (ORDER BY date) AS salary_diff
FROM employees;

累計平均

SELECT
  date,
  SUM(sales) OVER (
    ORDER BY date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) / COUNT(*) OVER (
    ORDER BY date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS cumulative_average
FROM sales_data;

N位相関

SELECT
  date,
  CORR(sales, LAG(sales, 1) OVER (ORDER BY date)) AS n_lag_correlation
FROM sales_data;

移動標準偏差

SELECT
  date,
  STDDEV(sales) OVER (
    ORDER BY date
    ROWS BETWEEN 3 PRECEDING AND CURRENT ROW
  ) AS moving_stddev
FROM sales_data;

パーセンタイルランク

SELECT
  name,
  salary,
  PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY salary) OVER (PARTITION BY department_id) AS salary_percentile
FROM employees;

ウィンドウフレームの境界値

SELECT
  name,
  salary,
  FIRST_VALUE(salary) OVER (ORDER BY salary) AS first_salary,
  LAST_VALUE(salary) OVER (ORDER BY salary) AS last_salary
FROM employees;

グループ内の順位

SELECT
  name,
  salary,
  RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank_within_department
FROM employees;

N番目の最大値

SELECT
  name,
  salary,
  NTH_VALUE(salary, 2) OVER (ORDER BY salary DESC) AS second_highest_salary
FROM employees;

集計関数の使用

SELECT
  department_id,
  COUNT(*) AS employee_count,
  SUM(salary) AS total_salary,
  AVG(salary) AS average_salary
FROM employees
GROUP BY department_id;

サブクエリとの組み合わせ

SELECT
  name,
  salary,
  (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id) AS department_average
FROM employees AS e;

CTE (Common Table Expressions) との組み合わせ

WITH salary_ranks AS (
  SELECT
    name,
    salary,
    RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
  FROM employees
)
SELECT * FROM salary_ranks WHERE rank <= 3;

仮想列との組み合わせ

SELECT
  name,
  salary,
  salary * 1.2 AS increased_salary
FROM employees;

CASE式との組み合わせ

SELECT
  name,
  salary,
  CASE WHEN salary > 100000 THEN 'High' ELSE 'Low' END AS salary_category
FROM employees;

結合との組み合わせ

SELECT
  e.name,
  e.salary,
  d.name AS department_name
FROM employees AS e
JOIN departments AS d ON e.department_id = d.id;

サブクエリと結合の組み合わせ

SELECT
  e.name,
  e.salary,
  (SELECT AVG(salary) FROM employees WHERE department_id = e.department_id) AS department_average
FROM employees AS e;

CTEと結合の組み合わせ

WITH salary_ranks AS (
  SELECT
    name,
    salary,
    RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
  FROM employees
)
SELECT * FROM salary_ranks AS s
JOIN departments AS d ON s.department_id = d.id;

仮想列と結合の組み合わせ

SELECT
  e.name,
  e.salary,
  e.salary * 1.2 AS increased_