COUNT(DISTINCT name)
SQLiteでカウント、グループ化、並べ替えを行う
概要
SQLiteは、軽量で使いやすいデータベース管理システムです。ここでは、SQLiteでデータの集計と分析を行うために役立つ、COUNT関数、GROUP BY句、ORDER BY句の使い方について、分かりやすく説明します。
COUNT関数
COUNT関数は、指定された列または式の値の個数をカウントします。構文は以下の通りです。
COUNT([ALL | DISTINCT] expression);
- ALL (省略可):すべての値をカウントします。重複もカウントされます。
- DISTINCT:重複する値を除いた個数をカウントします。
例
以下の表 customers
に、顧客の名前と購入件数が格納されています。
customer_id | name | purchase_count |
---|---|---|
1 | 田中太郎 | 5 |
2 | 佐藤次郎 | 3 |
3 | 鈴木三郎 | 2 |
1 | 田中太郎 | 7 |
すべての顧客の合計購入件数をカウントするには、以下のクエリを実行します。
SELECT COUNT(*) AS total_purchases
FROM customers;
このクエリは、17という結果を返します。これは、すべての顧客の購入件数の合計です。
SELECT COUNT(DISTINCT name) AS unique_customers
FROM customers;
GROUP BY句
GROUP BY句は、結果セットを列の値に基づいてグループ分けします。構文は以下の通りです。
SELECT expression1, expression2, ...
FROM table_name
GROUP BY column1, column2, ...;
上記の表 customers
を、購入件数に基づいてグループ分けし、各グループの顧客の名前と合計購入件数を表示するには、以下のクエリを実行します。
SELECT name, COUNT(*) AS purchase_count
FROM customers
GROUP BY name
ORDER BY purchase_count DESC;
このクエリは、以下の結果を返します。
name | purchase_count |
---|---|
田中太郎 | 12 |
佐藤次郎 | 3 |
鈴木三郎 | 2 |
ORDER BY句
SELECT expression1, expression2, ...
FROM table_name
ORDER BY column1, column2, ... ASC | DESC;
- ASC:昇順で並べ替えます。
上記のクエリ結果を、購入件数の降順に並べ替えるには、以下のクエリを実行します。
SELECT name, COUNT(*) AS purchase_count
FROM customers
GROUP BY name
ORDER BY purchase_count DESC;
name | purchase_count |
---|---|
田中太郎 | 12 |
佐藤次郎 | 3 |
鈴木三郎 | 2 |
SQLiteでカウント、グループ化、並べ替えを行うサンプルコード
前提条件
このサンプルコードを実行するには、SQLiteデータベースと、以下の表 customers
が作成されている必要があります。
CREATE TABLE customers (
customer_id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
purchase_count INTEGER NOT NULL
);
コード
-- すべての顧客の合計購入件数をカウント
SELECT COUNT(*) AS total_purchases
FROM customers;
-- 重複する値を除いた顧客の名前の個数をカウント
SELECT COUNT(DISTINCT name) AS unique_customers
FROM customers;
-- 購入件数に基づいて顧客をグループ分けし、各グループの顧客の名前と合計購入件数を表示
SELECT name, COUNT(*) AS purchase_count
FROM customers
GROUP BY name
ORDER BY purchase_count DESC;
説明
- 最初のクエリは、COUNT関数を使用してすべての顧客の合計購入件数をカウントします。
- 2番目のクエリは、COUNT関数とDISTINCTキーワードを使用して、重複する値を除いた顧客の名前の個数をカウントします。
- 3番目のクエリは、GROUP BY句とCOUNT関数を使用して、購入件数に基づいて顧客をグループ分けし、各グループの顧客の名前と合計購入件数を表示します。ORDER BY句を使用して、結果を降順に並べ替えています。
実行結果
17
3
田中太郎, 12
佐藤次郎, 3
鈴木三郎, 2
補足
- このサンプルコードは、基本的な操作のみを示しています。より複雑な集計や分析を行うには、WHERE句やHAVING句などの他のクエリ構文と組み合わせて使用することができます。
- 詳細については、SQLiteの公式ドキュメントを参照してください。
SQLiteでカウント、グループ化、並べ替えを行うその他の方法
サブクエリ
サブクエリを使用して、COUNT関数、GROUP BY句、ORDER BY句を実行することができます。構文は以下の通りです。
SELECT expression1, expression2, ...
FROM table_name
WHERE condition
ORDER BY (
SELECT expression
FROM subquery
WHERE condition
);
SELECT name, purchase_count
FROM customers
ORDER BY (
SELECT COUNT(*)
FROM customers
WHERE c.name = customers.name
);
name | purchase_count |
---|---|
田中太郎 | 12 |
佐藤次郎 | 3 |
鈴木三郎 | 2 |
ウィンドウ関数
SELECT expression1, expression2, ...
FROM table_name
OVER (PARTITION BY column1, column2, ... ORDER BY column3, column4, ...)
AS window_name;
SELECT name, COUNT(*) OVER (PARTITION BY name ORDER BY purchase_count DESC) AS purchase_count
FROM customers;
name | purchase_count |
---|---|
田中太郎 | 12 |
佐藤次郎 | 3 |
鈴木三郎 | 2 |
外部ライブラリ
SQLite FTS は、全文検索機能を提供するSQLite拡張です。FTSには、COUNT関数、GROUP BY句、ORDER BY句などの集計関数と分析機能が含まれています。
SQLクエリビルダー
SQLクエリビルダーは、GUIを使用してSQLクエリを作成するツールです。SQLクエリビルダーを使用すると、COUNT関数、GROUP BY句、ORDER BY句などの複雑なクエリを簡単に作成することができます。
上記以外にも、SQLiteでカウント、グループ化、並べ替えを行う方法はいくつかあります。自分に合った方法を選択してください。
sorting sqlite grouping