13. SQL の実践（集計と可視化）¶
第 13 回では、前回学んだ SQL の基礎をもとに、データ分析でよく使う「集計（集約）」と「グループ化」、そしてその集計結果の可視化について学びます。
13.1 サンプルデータの準備¶
本章では、第 12 回に登場したおでん屋さんの、ある日の売上（値上げ後の価格）を記録した sales テーブルを例に進めます。1 行が 1 つの注文に対応しており、この日に「はんぺん」は 3 つ売れて完売しました。まずは前回学んだ CREATE TABLE と INSERT（Cursor.executemany()）で、第 12 回と同じ oden.db にサンプルデータを用意しましょう。
import sqlite3
# データベースに接続
conn = sqlite3.connect("oden.db")
cursor = conn.cursor()
# 何度実行しても同じ状態になるよう、いったん削除してから作成
cursor.execute("DROP TABLE IF EXISTS sales")
cursor.execute("""
CREATE TABLE sales (
id INTEGER PRIMARY KEY,
item TEXT,
category TEXT,
price INTEGER
)
""")
# 売上データ（1 行 = 1 つの注文）
data = [
("ちくわ", "練り物", 130),
("ちくわ", "練り物", 130),
("はんぺん", "練り物", 120),
("はんぺん", "練り物", 120),
("はんぺん", "練り物", 120),
("つみれ", "練り物", 140),
("かまぼこ", "練り物", 150),
("かまぼこ", "練り物", 150),
("だいこん", "野菜", 100),
("だいこん", "野菜", 100),
("だいこん", "野菜", 100),
("だいこん", "野菜", 100),
("たまご", "たまご", 90),
("たまご", "たまご", 90),
("たまご", "たまご", 90),
]
cursor.executemany("INSERT INTO sales (item, category, price) VALUES (?, ?, ?)", data)
conn.commit()
これで、15 件の売上データを持つ sales テーブルが用意できました。以降のクエリは、この cursor を使って実行していきます。
13.2 集約関数¶
集約関数 (aggregate function) は、複数の行の値をまとめて 1 つの値にする関数です。代表的なものは以下のとおりです。
| 関数 | 説明 |
|---|---|
COUNT() |
行数 |
SUM() |
合計 |
AVG() |
平均 |
MAX() |
最大値 |
MIN() |
最小値 |
例えば、売上全体の「件数」「売上合計」「平均価格」「最高価格」「最低価格」をまとめて求めるには、以下のように書きます。
Python では、これまでと同様に Cursor.execute() で実行し、結果を取得します。1 行だけの結果なので Cursor.fetchone() を使います。
cursor.execute("SELECT COUNT(*), SUM(price), AVG(price), MAX(price), MIN(price) FROM sales")
print(cursor.fetchone())
全部で 15 件、売上合計 1730 円、平均価格 約 115.3 円、最高 150 円、最低 90 円であることがわかります。
別名 (AS) と丸め (ROUND)
集約した列には AS で別名を付けると、結果が読みやすくなります。また、平均などで桁数が多くなる場合は ROUND(値, 桁数) で丸めることができます。
13.3 グループ化（GROUP BY）¶
GROUP BY を使うと、ある列の値ごとにグループに分けて集約することができます。例えば、item（品目）ごとに「販売数」を求めるには、以下のように書きます。
cursor.execute("SELECT item, COUNT(*) FROM sales GROUP BY item")
for row in cursor.fetchall():
print(row)
品目ごとに、販売数が集計されました。
集約結果の並べ替え（ORDER BY）¶
集約した結果も、ORDER BY で並べ替えることができます。品目ごとの売上合計（別名 total）の多い順に並べるには、以下のようにします。
この日は「だいこん」が最も売上が多かったことがわかります。さらに LIMIT を付ければ、上位だけを取り出すこともできます（例：... ORDER BY total DESC LIMIT 1 で 1 位のみ）。
集約結果への条件（HAVING）¶
WHERE は「集約する前の各行」に対する条件でしたが、「集約した後の結果」に対して条件を付けたい場合は HAVING を使います。例えば、3 つ以上売れた品目だけを取り出すには、以下のようにします。
WHERE と HAVING の違い
WHERE：グループ化の 前 に、個々の行を絞り込む（例：WHERE price >= 100）HAVING：グループ化の 後 に、集約結果を絞り込む（例：HAVING COUNT(*) >= 3）
両方を組み合わせて、「WHERE で行を絞り → GROUP BY で集約 → HAVING で結果を絞る」という流れで使うこともできます。
13.4 集計結果の可視化¶
集計（GROUP BY）の結果は、これまで学んできた Matplotlib で可視化することができます。Cursor.fetchall() で取得した結果から、品目のリストと売上合計のリストを作り、棒グラフにしてみましょう。
import sqlite3
import matplotlib.pyplot as plt
# データベースに接続
conn = sqlite3.connect("oden.db")
cursor = conn.cursor()
# 品目別の売上合計を取得
cursor.execute("""
SELECT item, SUM(price) AS total
FROM sales
GROUP BY item
ORDER BY total DESC
""")
rows = cursor.fetchall()
# 取得結果を「品目」と「売上合計」のリストに分ける
items = [row[0] for row in rows]
totals = [row[1] for row in rows]
# 棒グラフで可視化
fig, ax = plt.subplots(figsize=(5, 3))
ax.bar(items, totals)
ax.set_ylabel("売上合計（円）")
ax.set_title("品目別の売上合計")
plt.tight_layout()
plt.show()
conn.close()
SQL で集計した結果を、グラフとして直感的に把握できるようになりました。このように、SQL でデータを集計し、Python で可視化するという流れは、実際のデータ分析でも頻繁に用いられます。
pandas との連携
pandas を使うと、pandas.read_sql_query() 関数で SQL の実行結果をそのまま DataFrame として取得することができ、これまで学んだ pandas の集計・可視化の機能を活用できます。
演習¶
以下の演習では、「13.1 サンプルデータの準備」で作成した sales テーブル（oden.db）を使用します。先にサンプルデータを作成してから取り組んでください。
演習 13-1（初級）：集約関数と絞り込み
おでん屋さんの売上データベース（sales テーブル）から、100 円以上の具材に限定して売れ行きを調べます。価格が 100 円以上の売上データの「件数（COUNT）」と「平均価格（AVG）」を求めて表示してください。
WHERE句（プレースホルダ?を使用）で価格が 100 円以上の行に絞り込む- 件数と平均価格を集約関数で求めて表示する
演習 13-2（中級）：カテゴリ別の売上ランキング
おでん屋さんの売上データベース（sales テーブル）から、カテゴリ（練り物・野菜・たまご）単位での売れ行きを調べます。GROUP BY を使ってカテゴリ別の「件数（COUNT）」と「売上合計（SUM）」を求め、売上合計の高い順に並べて表示してください。
GROUP BYでカテゴリごとの件数と売上合計を求める- 売上合計に
ASで別名を付け、高い順（降順）に並べ替える
演習 13-3（中級）：カテゴリ別件数の可視化
おでんのカテゴリごとの売れ行きを、ひと目で把握できるようにします。おでん屋さんの売上データベース（sales テーブル）から、カテゴリ別の 件数 を GROUP BY で集計し、その結果を棒グラフで可視化してください。
- カテゴリ別の件数を集計する
- 取得結果をカテゴリのリストと件数のリストに分ける
- 棒グラフで可視化する