MySQLとSQL Serverで最頻値を見つける方法を比較！サンプルコード付き

2024-05-19

SQL列の最頻値を見つける方法

SQLデータベースの列における最頻値とは、その列の中で最も多く出現する値のことです。この値を知ることは、データの分布や傾向を理解する上で役立ちます。

方法

最頻値を見つける方法はいくつかありますが、ここでは最も一般的な2つの方法をご紹介します。

方法1： GROUP BY と COUNT を使用する

まず、分析対象の列を GROUP BY 句でグループ化します。
次に、各グループ内の行数を COUNT 関数でカウントします。
最後に、 ORDER BY 句でカウント数を降順にソートし、最初の行の値を最頻値として取得します。

SELECT column_name, COUNT(*) AS count
FROM table_name
GROUP BY column_name
ORDER BY count DESC
LIMIT 1;

例

SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
ORDER BY order_count DESC
LIMIT 1;

このクエリは、orders テーブルにおいて、注文数が多い顧客IDとその注文数を1件だけ返します。

方法2：ウィンドウ関数を使用する

一部のデータベースでは、ウィンドウ関数を使用して最頻値を直接取得することができます。この方法は、上記の方法よりも簡潔に記述できます。

SELECT column_name,
       COUNT(*) OVER (PARTITION BY column_name ORDER BY column_name) AS count
FROM table_name;

SELECT customer_id,
       COUNT(*) OVER (PARTITION BY customer_id ORDER BY customer_id) AS order_count
FROM orders;

このクエリは、orders テーブルの各行に、その顧客IDにおける注文数を表示します。最頻値は、order_count 列の最大値となります。

補足

上記のクエリは、列に重複する値がある場合を想定しています。重複する値がない場合は、DISTINCT キーワードを追加することで重複を除外することができます。
最頻値が複数存在する場合は、上記の方法で取得した結果をさらに処理する必要があります。

上記以外にも、ライブラリや外部ツールを利用して最頻値を求める方法もあります。

ご自身の目的に合った方法を選択してください。

MySQL

-- テーブルの作成
CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  name VARCHAR(255),
  email VARCHAR(255)
);

-- データの挿入
INSERT INTO customers VALUES (1, 'John Doe', '[email protected]');
INSERT INTO customers VALUES (2, 'Jane Doe', '[email protected]');
INSERT INTO customers VALUES (1, 'Peter Jones', '[email protected]');
INSERT INTO customers VALUES (2, 'Mary Smith', '[email protected]');
INSERT INTO customers VALUES (3, 'David Williams', '[email protected]');

-- 最頻値の顧客IDと注文数を取得
SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id
ORDER BY order_count DESC
LIMIT 1;

出力

customer_id | order_count
-----------+------------
2           | 2

SQL Server

-- テーブルの作成
CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  name NVARCHAR(255),
  email NVARCHAR(255)
);

-- データの挿入
INSERT INTO customers VALUES (1, N'John Doe', N'[email protected]');
INSERT INTO customers VALUES (2, N'Jane Doe', N'[email protected]');
INSERT INTO customers VALUES (1, N'Peter Jones', N'[email protected]');
INSERT INTO customers VALUES (2, N'Mary Smith', N'[email protected]');
INSERT INTO customers VALUES (3, N'David Williams', N'[email protected]');

-- 最頻値の顧客IDと注文数を取得
SELECT customer_id, COUNT(*) OVER (PARTITION BY customer_id ORDER BY customer_id) AS order_count
FROM orders;

customer_id | order_count
-----------+------------
1           | 2
2           | 2
3           | 1

説明

上記のコードは、以下の手順を実行します。

テーブルの作成：customers テーブルを作成します。このテーブルには、顧客ID、名前、メールアドレスの列が含まれます。
データの挿入：customers テーブルにデータを追加します。
最頻値の取得：以下の方法で最頻値を取得します。
- MySQL：GROUP BY 句と COUNT 関数を使用して、各顧客IDにおける注文数をカウントし、その最大値を持つ顧客IDを最頻値として取得します。
- SQL Server：COUNT ウィンドウ関数を使用して、各行にその顧客IDにおける注文数を表示し、order_count 列の最大値を最頻値として取得します。

注意事項

上記のコードはあくまで一例です。ご自身の環境に合わせて変更してください。

SQLで最頻値を見つけるその他の方法

方法3：サブクエリを使用する

この方法は、HAVING 句でサブクエリを使用して、最頻値を持つ行を抽出します。

SELECT column_name
FROM table_name
GROUP BY column_name
HAVING COUNT(*) = (
  SELECT MAX(count)
  FROM (
    SELECT column_name, COUNT(*) AS count
    FROM table_name
    GROUP BY column_name
  ) AS subquery
);

方法4： COMMON TABLE EXPRESSION (CTE) を使用する

この方法は、CTEを使用して、最頻値とカウントを算出した中間テーブルを作成し、そのテーブルから最頻値のみを抽出します。

WITH cte AS (
  SELECT column_name, COUNT(*) AS count
  FROM table_name
  GROUP BY column_name
)
SELECT column_name
FROM cte
WHERE count = (
  SELECT MAX(count)
  FROM cte
);

この方法は、RANK() 関数を使用して、各行にその列内の順位を割り当て、最頻値を持つ行を抽出します。

SELECT column_name
FROM table_name
ORDER BY column_name,
       RANK() OVER (ORDER BY column_name) DESC
WHERE RANK() OVER (ORDER BY column_name) = 1;

SELECT column_name
FROM table_name
ORDER BY column_name,
       DENSE_RANK() OVER (ORDER BY column_name) DESC
WHERE DENSE_RANK() OVER (ORDER BY column_name) = 1;