MariaDBでGROUP BYとMEDIAN関数を使って中央値を計算する方法

MariaDBでGROUP BYとMEDIAN関数を使う

例：従業員の給与の中央値を部署ごとに計算する

従業員の給与データテーブル employees があるとします。このテーブルには、従業員ID (id)、名前 (name)、部署 (department)、給与 (salary) などの列が含まれています。

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(255),
  department VARCHAR(255),
  salary DECIMAL(10,2)
);

このテーブルのデータ例をいくつか挿入します。

INSERT INTO employees (id, name, department, salary)
VALUES
  (1, 'John Doe', 'Sales', 40000.00),
  (2, 'Jane Smith', 'Marketing', 50000.00),
  (3, 'Peter Jones', 'Sales', 35000.00),
  (4, 'Mary Brown', 'Marketing', 45000.00),
  (5, 'David Williams', 'Sales', 42000.00);

各部署の中央値を計算するには、次のSQLクエリを使用します。

SELECT department, MEDIAN(salary) AS median_salary
FROM employees
GROUP BY department;

このクエリは次のような結果を返します。

department | median_salary
----------|--------------
Marketing | 47500.00
Sales     | 40000.00

クエリ解説:

• SELECT: この句は、クエリで取得する列を指定します。この例では、department と MEDIAN(salary) AS median_salary を選択しています。
• FROM: この句は、クエリで使用するテーブルを指定します。この例では、employees テーブルを使用しています。
• GROUP BY: この句は、データをグループ化するための列を指定します。この例では、department 列でデータをグループ化しています。
• MEDIAN(salary) AS median_salary: この式は、各グループの中央値を計算し、median_salary という名前の列として格納します。

補足:

• MEDIAN 関数は、NULL 値を無視します。
• 複数の列でグループ化したい場合は、GROUP BY 句に複数の列をカンマ区切りで指定できます。
• HAVING 句を使用して、グループ化条件を指定できます。

MariaDBでGROUP BYとMEDIAN関数を使う：サンプルコード

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(255),
  department VARCHAR(255),
  salary DECIMAL(10,2)
);

INSERT INTO employees (id, name, department, salary)
VALUES
  (1, 'John Doe', 'Sales', 40000.00),
  (2, 'Jane Smith', 'Marketing', 50000.00),
  (3, 'Peter Jones', 'Sales', 35000.00),
  (4, 'Mary Brown', 'Marketing', 45000.00),
  (5, 'David Williams', 'Sales', 42000.00);

SELECT department, MEDIAN(salary) AS median_salary
FROM employees
GROUP BY department;

department | median_salary
----------|--------------
Marketing | 47500.00
Sales     | 40000.00

1. テーブルの作成:

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(255),
  department VARCHAR(255),
  salary DECIMAL(10,2)
);

このクエリは、employees という名前のテーブルを作成します。このテーブルには、id (主キー)、name、department、salary の4つの列が含まれます。

1. データの挿入:

INSERT INTO employees (id, name, department, salary)
VALUES
  (1, 'John Doe', 'Sales', 40000.00),
  (2, 'Jane Smith', 'Marketing', 50000.00),
  (3, 'Peter Jones', 'Sales', 35000.00),
  (4, 'Mary Brown', 'Marketing', 45000.00),
  (5, 'David Williams', 'Sales', 42000.00);

このクエリは、employees テーブルに5つのレコードを挿入します。各レコードには、従業員ID、名前、部署、給与の情報が含まれます。

1. GROUP BYとMEDIAN関数を使ったクエリ:

SELECT department, MEDIAN(salary) AS median_salary
FROM employees
GROUP BY department;

このクエリは、employees テーブルから、department 列でグループ化された各グループの中央値を計算します。結果は、department 列と median_salary 列を含む新しいテーブルとして返されます。

このサンプルコードを参考に、MariaDBでGROUP BYとMEDIAN関数を使ってデータ分析を行ってください。

• 上記のサンプルコードは、あくまでも一例です。必要に応じて、列名、テーブル名、クエリを調整してください。

MariaDBでGROUP BYとMEDIAN関数以外で中央値を計算する方法

従来の GROUP BY と MEDIAN 関数以外にも、MariaDBでグループごとの中央値を計算する方法はいくつかあります。状況によっては、これらの代替方法の方が高速、効率的、柔軟性に優れている場合があります。

代替方法

1. ウィンドウ関数を使用する:

SELECT department,
       PERCENTILE_CONT(0.5) OVER (PARTITION BY department ORDER BY salary) AS median_salary
FROM employees;

このクエリは、employees テーブルの salary 列の中央値を department ごとに計算し、median_salary という名前の列として格納します。

SELECT department,
       (
           SELECT salary
           FROM employees AS e2
           WHERE e2.department = e.department
           ORDER BY salary
           LIMIT 1 OFFSET (COUNT(*) - 1) DIV 2
       ) AS median_salary
FROM employees AS e
GROUP BY department;

1. ユーザー定義関数を使用する:

CREATE FUNCTION percentile_cont(
  fraction DOUBLE,
  order_by_clause ORDER BY
)
RETURNS DECIMAL(20,10)
DETERMINISTIC
BEGIN
  DECLARE percentile_value DECIMAL(20,10);

  SELECT PERCENTILE_CONT(fraction)
  INTO percentile_value
  FROM (
      SELECT salary
      FROM employees
      ORDER BY order_by_clause
  ) AS subquery;

  RETURN percentile_value;
END;

SELECT department,
       percentile_cont(0.5, salary) AS median_salary
FROM employees
GROUP BY department;

各方法の比較

MariaDBでグループごとの中央値を計算するには、状況に応じて最適な方法を選択する必要があります。最新のバージョンの MariaDB を使用している場合は、ウィンドウ関数が最良の選択肢となる可能性が高いです。古いバージョンの MariaDB を使用している場合、または高度な制御が必要な場合は、サブクエリやユーザー定義関数を使用する必要があるかもしれません。