SQLite データグループ化 | 集計関数、GROUP BY 句をマスター

はじめに

この実験（Lab）では、集計関数とグループ化句を使用して、SQLite でデータを要約および分析する方法を学びます。計算には COUNT と SUM を使用し、単一の列でデータをグループ化し、HAVING でグループをフィルタリングし、グループ化された出力をソートする方法を学びます。この実践的な経験を通して、SQLite のデータ操作に不可欠なスキルを習得できます。

Skills Graph

%%%%{init: {'theme':'neutral'}}%%%% flowchart RL sqlite(("SQLite")) -.-> sqlite/SQLiteGroup(["SQLite"]) sqlite/SQLiteGroup -.-> sqlite/init_db("Create SQLite Database") sqlite/SQLiteGroup -.-> sqlite/make_table("Create New Table") sqlite/SQLiteGroup -.-> sqlite/get_all("Select All Rows") sqlite/SQLiteGroup -.-> sqlite/query_where("Filter With WHERE") sqlite/SQLiteGroup -.-> sqlite/sort_data("Sort With ORDER BY") subgraph Lab Skills sqlite/init_db -.-> lab-552547{{"SQLite データグループ化"}} sqlite/make_table -.-> lab-552547{{"SQLite データグループ化"}} sqlite/get_all -.-> lab-552547{{"SQLite データグループ化"}} sqlite/query_where -.-> lab-552547{{"SQLite データグループ化"}} sqlite/sort_data -.-> lab-552547{{"SQLite データグループ化"}} end

Orders テーブルの作成とデータの挿入

このステップでは、sales.db という名前のデータベースと、その中に orders テーブルを作成します。次に、サンプルデータをテーブルに挿入します。このテーブルは、この実験（Lab）全体を通して、データのグループ化手法を練習するために使用されます。

まず、LabEx VM でターミナルを開きます。デフォルトのパスは /home/labex/project です。

最初に、次のコマンドを使用して sales.db データベースを作成し、接続します。

sqlite3 sales.db

このコマンドを実行すると、SQLite シェルが開き、sqlite> のようなプロンプトが表示されます。

次に、order_id、customer_id、product_name、quantity、および price の列を持つ orders テーブルを作成します。次の SQL コマンドを実行します。

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER,
    product_name TEXT,
    quantity INTEGER,
    price REAL
);

このコマンドは、指定された列とデータ型を持つ orders テーブルを作成します。order_id 列は主キーとして設定されます。

次に、サンプルデータを orders テーブルに挿入します。次の INSERT ステートメントを 1 つずつ実行します。

INSERT INTO orders (customer_id, product_name, quantity, price) VALUES
(1, 'Laptop', 1, 1200.00),
(1, 'Mouse', 2, 25.00),
(2, 'Keyboard', 1, 75.00),
(2, 'Monitor', 1, 300.00),
(3, 'Laptop', 1, 1200.00),
(3, 'Headphones', 1, 100.00),
(1, 'Keyboard', 1, 75.00);

これらのコマンドは、異なる顧客の注文を表す 7 行のデータを orders テーブルに挿入します。

データが正しく挿入されたことを確認するには、簡単な SELECT クエリを実行します。

SELECT * FROM orders;

このコマンドは、orders テーブルのすべての行と列を表示します。

COUNT と SUM による集計

このステップでは、COUNT および SUM 集計関数を使用して、orders テーブルのデータに対して計算を実行します。集計関数を使用すると、複数の行からのデータを 1 つの結果に要約できます。

前のステップから sales.db データベースに接続した状態になっているはずです。そうでない場合は、以下を使用して再接続します。

sqlite3 sales.db

まず、COUNT 関数を使用して、テーブル内の注文の総数を決定しましょう。次の SQL コマンドを実行します。

SELECT COUNT(*) FROM orders;

このクエリは、orders テーブル内の行の総数を返し、これは注文の総数を表します。

COUNT(*) 関数は、列に NULL 値が含まれているかどうかに関係なく、テーブル内のすべての行をカウントします。

次に、SUM 関数を使用して、注文されたすべての製品の合計数量を計算しましょう。次の SQL コマンドを実行します。

SELECT SUM(quantity) FROM orders;

このクエリは、orders テーブルのすべての行の quantity 列の合計を返します。

SUM 関数は、指定された列の値を合計します。

最後に、すべての注文から生成された総収益を計算しましょう。次の SQL コマンドを実行します。

SELECT SUM(quantity * price) FROM orders;

このクエリは、各行の quantity 列と price 列を乗算し、その結果を合計して、総収益を算出します。

単一の列によるグループ化

このステップでは、GROUP BY 句を使用して、1 つまたは複数の列の値に基づいて行をグループ化する方法を学習します。これは、各グループの要約統計量を計算するために、集計関数と組み合わせてよく使用されます。

前のステップから sales.db データベースに接続した状態になっているはずです。そうでない場合は、以下を使用して再接続します。

sqlite3 sales.db

orders テーブルを customer_id でグループ化し、各顧客の注文数をカウントしましょう。次の SQL コマンドを実行します。

SELECT customer_id, COUNT(*) AS order_count FROM orders GROUP BY customer_id;

このクエリは、customer_id と、各一意の顧客の注文数 (order_count) を返します。GROUP BY customer_id 句は、customer_id 列の値に基づいて行をグループ化するように SQLite に指示します。次に、COUNT(*) 関数が各グループ内の行数をカウントします。

次に、orders テーブルを product_name でグループ化し、各製品の注文された合計数量を計算しましょう。次の SQL コマンドを実行します。

SELECT product_name, SUM(quantity) AS total_quantity FROM orders GROUP BY product_name;

このクエリは、product_name と、各一意の製品の注文された合計数量 (total_quantity) を返します。GROUP BY product_name 句は、product_name 列の値に基づいて行をグループ化するように SQLite に指示します。次に、SUM(quantity) 関数が各グループの quantity 列の合計を計算します。

最後に、orders テーブルを customer_id でグループ化し、各顧客によって生成された総収益を計算しましょう。次の SQL コマンドを実行します。

SELECT customer_id, SUM(quantity * price) AS total_revenue FROM orders GROUP BY customer_id;

このクエリは、customer_id と、各顧客によって生成された総収益 (total_revenue) を返します。

グループへの HAVING の適用

このステップでは、HAVING 句を使用して、GROUP BY 句によってグループが作成された後に、それらのグループをフィルタリングする方法を学習します。HAVING 句は WHERE 句に似ていますが、個々の行ではなくグループに対して動作します。

前のステップから sales.db データベースに接続した状態になっているはずです。そうでない場合は、以下を使用して再接続します。

sqlite3 sales.db

orders テーブルを customer_id でグループ化し、各顧客の注文数をカウントしましょう。次に、HAVING 句を使用して、結果をフィルタリングし、複数の注文を行った顧客のみを含めます。次の SQL コマンドを実行します。

SELECT customer_id, COUNT(*) AS order_count FROM orders GROUP BY customer_id HAVING COUNT(*) > 1;

このクエリは、複数の注文を行った各顧客の customer_id と注文数 (order_count) を返します。GROUP BY customer_id 句は、行を customer_id でグループ化し、HAVING COUNT(*) > 1 句は、注文のカウントが 1 より大きいグループのみを含めるようにグループをフィルタリングします。

次に、orders テーブルを product_name でグループ化し、各製品の注文された合計数量を計算しましょう。次に、HAVING 句を使用して、結果をフィルタリングし、注文された合計数量が 1 より大きい製品のみを含めます。次の SQL コマンドを実行します。

SELECT product_name, SUM(quantity) AS total_quantity FROM orders GROUP BY product_name HAVING SUM(quantity) > 1;

このクエリは、注文された合計数量が 1 より大きい各製品の product_name と注文された合計数量 (total_quantity) を返します。

最後に、orders テーブルを customer_id でグループ化し、各顧客によって生成された総収益を計算しましょう。次に、HAVING 句を使用して、結果をフィルタリングし、1000 ドルを超える収益を上げた顧客のみを含めます。次の SQL コマンドを実行します。

SELECT customer_id, SUM(quantity * price) AS total_revenue FROM orders GROUP BY customer_id HAVING SUM(quantity * price) > 1000;

このクエリは、1000 ドルを超える収益を上げた各顧客の customer_id と総収益 (total_revenue) を返します。

グループ化された出力のソート

このステップでは、GROUP BY 句を含むクエリの出力をソートするために ORDER BY 句を使用する方法を学習します。グループ化された出力をソートすると、データの分析と理解が容易になります。

前のステップから sales.db データベースに接続した状態になっているはずです。そうでない場合は、以下を使用して再接続します。

sqlite3 sales.db

orders テーブルを customer_id でグループ化し、各顧客の注文数をカウントしましょう。次に、ORDER BY 句を使用して、注文数に基づいて結果を降順にソートします。次の SQL コマンドを実行します。

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

このクエリは、各顧客の customer_id と注文数 (order_count) を、order_count に基づいて降順にソートして返します。GROUP BY customer_id 句は、行を customer_id でグループ化し、ORDER BY order_count DESC 句は、結果を order_count エイリアスに基づいて降順にソートします。

次に、orders テーブルを product_name でグループ化し、各製品の注文された合計数量を計算しましょう。次に、ORDER BY 句を使用して、製品名に基づいて結果を昇順にソートします。次の SQL コマンドを実行します。

SELECT product_name, SUM(quantity) AS total_quantity FROM orders GROUP BY product_name ORDER BY product_name ASC;

このクエリは、各製品の product_name と注文された合計数量 (total_quantity) を、product_name に基づいて昇順にソートして返します。

最後に、orders テーブルを customer_id でグループ化し、各顧客によって生成された総収益を計算しましょう。次に、ORDER BY 句を使用して、総収益に基づいて結果を降順にソートします。次の SQL コマンドを実行します。

SELECT customer_id, SUM(quantity * price) AS total_revenue FROM orders GROUP BY customer_id ORDER BY total_revenue DESC;

このクエリは、各顧客の customer_id と総収益 (total_revenue) を、total_revenue に基づいて降順にソートして返します。

SQLite シェルを終了するには、次を実行します。

.exit

まとめ

この実験（Lab）では、SQLite でデータを要約するために、COUNT や SUM などの集計関数を使用する方法を学びました。orders テーブルを持つ sales.db データベースを作成し、サンプルデータを挿入しました。次に、COUNT(*) を使用して注文の総数を決定し、SUM(quantity * price) を使用して総収益を計算しました。また、GROUP BY 句を使用してデータをグループ化し、HAVING 句を使用してグループをフィルタリングし、ORDER BY 句を使用して出力をソートする方法も学びました。これらのスキルは、SQLite でのデータ分析のための確固たる基盤を提供します。