プロダクトデザイナーも知っておくと便利かもしれない【SQL 応用編】

これまで基本的なSQLやSQL JOINについて書きましたが、よく使われる複雑なクエリについて記載してなかったなと思ったのでこちらでまとめておきます。

DISTINCT

クエリ結果から重複した値を除去したい場合にDISTINCTを使用します。
例えば、以下の従業員テーブルに従業員名と部署名の列があるとします。1つの部署に複数の従業員がいるため、Departmentカラムに重複した値があることがわかっています。

Employeeテーブル

以下のSQL文は、EmployeeテーブルからDepartmentカラムの一意な値を取得します。

SELECT DISTINCT department FROM employee；

DISTINCTを使用することで同じ部署名は1つとして扱われ、部署のリストを出力するために使用することができます。

COUNT

SQLの COUNT 関数は行数を数えるために使用します。これは、特定の条件に一致する行数を確認したい場合や、テーブル内の行の総数を知りたい場合に便利です。

SELECT COUNT(*) FROM employee；

上記のクエリはemployeeテーブルの総行数を返します。

SELECT COUNT(column_name) FROM employee；

上記のクエリは、指定されたカラムcolumn_nameの値がNULLでない行の数をカウントします。

SELECT COUNT(*) FROM employee WHERE department = 'Sales'；

また、WHERE句を使用して、特定の条件を満たす行のみをカウントすることもできます。上記のクエリは、部署（Department）が'Sales'である従業員の数を返します。

SELECT COUNT(DISTINCT department) FROM employee；

重複を除外した値の数を数えるには、COUNT(DISTINCT column_name)を使用します。上記のクエリは、employeeテーブル内の異なる部門の数を数えます。

GROUP BY

GROUP BYは、選択したカラムの値に基づいて行をグループ化するために使用されます。これにより、集約関数（COUNT、SUM、AVG、MAX、MINなど）を各グループに適用し、集約データを得ることができます。

SELECT department, COUNT(*) AS num_employees
FROM employee
GROUP BY department;

上記のクエリは各部署の従業員数をカウントします。

SELECT department, AVG(salary) AS average_salary
FROM employee
GROUP BY department;

上記のクエリは各部署の平均給与を計算します。

集約関数

SQLでよく使われる集約関数には、SUM、AVG、MAX、MINがあります。これらの関数は、特定の列に含まれる数値に対して計算を実行し、データに関する統計情報を抽出するのに使われます。

SUM 関数

SUM関数は、指定した列の数値を合計します。例えば、売上合計や経費合計を計算するのに使われます。

SELECT SUM(salary) AS total_salary FROM employee;

上記のクエリは、employeeテーブルの全従業員の給与合計を計算します。

AVG 関数

AVG関数は与えられたカラムの平均値を計算します。これは例えば、従業員の平均給与や平均販売価格を求めるために使用することができます。

SELECT AVG(salary) AS average_salary FROM employee；

上記のクエリはemployeeテーブルの従業員の平均給与を計算します。

MAX関数

MAX関数は、与えられたカラムの最大値を求めます。最高給与額、最大売上高、最新の日付など、最大値が必要なあらゆる場面で使用できます。

SELECT MAX(salary) AS highest_salary FROM employee;

上記のクエリは、employeeテーブルで最も給与の高い従業員の給与を求めます。

MIN関数

MIN関数は、指定したカラムの最小値を求めます。例えば、給与の最小値、売上の最小値、日付の最小値などを知りたい場合に使用します。

SELECT MIN(salary) AS lowest_salary FROM employee;

上記のクエリは、employeeテーブルで最も給与が低い従業員の給与を検索します。

HAVING

HAVINGは、GROUP BYによって生成されたグループに対するフィルタリングに使用されます。HAVING句はWHERE句と似ていますが、WHERE句は集約関数を含むクエリでは使用できないため、HAVINGは集約データ（グループ化された結果）に条件を適用するために使用されます。

SELECT department, AVG(salary) AS average_salary
FROM employee
GROUP BY department
HAVING AVG(salary) > 30000;

上記のクエリは部署ごとの平均給与を計算し、平均給与が30,000を超える部署のみをリストアップします。AVG(salarly)は集約関数になるため、その関数を使用した条件を適用させたい場合はWHEREではなく、HAVINGになります。

SELECT department, COUNT(*) AS num_employee
FROM employee
GROUP BY department
HAVING COUNT(*) > 10;

上記のクエリは、各部署の従業員数をカウントし、10人以上の従業員を持つ部署のみをリストアップします。

【おまけ】これまでのおさらい

学んだことの総括として少し複雑な練習問題をもとに活用法をまとめていきます。

練習問題①

以下は、植物観察記録が含まれたデータベースの一部で、このテーブルには、異なる植物の観察データを保持しています。

$$
\begin{array}{|l|l|c|l|}\hline\textbf{Species} & \textbf{Date} & \textbf{Num　Sighted} & \textbf{Location} \\ \hline\text{Maple Tree} & 2022-04-15 & 5 & \text{Kyoto Park} \\ \hline\text{Cherry Blossom} & 2022-04-15 & 20 & \text{Tokyo Garden} \\ \hline\text{Sunflower} & 2022-06-20 & 12 & \text{Osaka Field} \\ \hline\text{Maple Tree} & 2022-10-10 & 7 & \text{Sapporo Forest} \\ \hline\text{Cherry Blossom} & 2023-04-10 & 25 & \text{Tokyo Garden} \\ \hline\text{Sunflower} & 2023-06-18 & 10 & \text{Nagoya Field} \\ \hline \end{array}
$$

データベースの名前は plant_sightings です。2022年の間に観察された、全ての植物の種類とそれぞれの観察回数を取得するSQLクエリを作成してみます。

SELECT Species, SUM(Num_Sighted) as Total_Sighted
FROM plant_sightings
WHERE Date BETWEEN '2022-01-01' AND '2022-12-31'
GROUP BY Species;

まず、SELECTの次に抽出したいデータのカラムを記述します。この場合植物の種類と観察回数を取得したいので、それに相当するカラムはSpeciesと、Num_Sightedになります。ただ、観察回数は2022年の間に観察されたということなので、SUMでその期間の合計を割り出す必要があるためSUM()の集約関数内に該当のカラム名を記述しています。次にFROM句を書き、該当のテーブルを指定します。
次に、該当期間を指定するためWHERE句で条件を指定します。Dateカラムを指定し、BETWEEN ANDを使用して具体的な時期を指定します。BETWEEN ANDの説明ができてなかったのですが、これは単純に期間を指定する際に使用します。（私は日付をクォーテーションで囲むのを忘れて、毎回エラーを出しがちです。）そしてそれを植物の種類ごとに分けるためにGROUP BYを使用し、該当のカラムを指定します。

以下は上記クエリに基づいて返されるデータの例になります。

$$
\begin{array}{|c|c|}\hline\textbf{Species} & \textbf{Total Sighted} \\ \hline\text{Maple Tree} & 12 \\ \hline\text{Cherry Blossom} & 20 \\ \hline\text{Sunflower} & 12 \\ \hline\end{array}
$$

練習問題②

以下は、花の観察記録が含まれたデータベースの一部で、このテーブルには異なる花の観察データを保持しています。

$$
\begin{array}{|l|l|c|l|}\hline\textbf{Species} & \textbf{Date} &\textbf{Num Sighted} & \textbf{Location} \\ \hline\text{Cherry Blossom} & 2022-04-01 & 40 & \text{Kyoto Park} \\ \hline\text{Tulip} & 2022-04-15 & 30 & \text{Tokyo Garden} \\ \hline \text{Sunflower} & 2022-06-20 & 50 & \text{Osaka Field} \\ \hline \text{Cherry Blossom} & 2023-04-01 & 35 & \text{Kyoto Park} \\ \hline\text{Tulip} & 2023-04-15 & 25 & \text{Tokyo Garden} \\ \hline\text{Sunflower} & 2023-06-20 & 55 & \text{Osaka Field} \\ \hline\end{array}
$$

データベースの名前は flower_sightings になります。全ての花の種類について、各花の種類が最も多く観察された日の観察数と日付を取得するSQLクエリを作成してみます。

SELECT Species, Date, Num_Sighted
FROM flower_sightings
WHERE (Species, Num_Sighted) IN (
    SELECT Species, MAX(Num_Sighted)
    FROM flower_sightings
    GROUP BY Species
);

まず、SELECTで取得するカラムデータを指定します。この場合、各花の種類があるSpecies、観察日を知りたいのでDate、Num_Sightedで観察数を指定します。FROMでこのデータテーブル名のflower_sightingsを指定します。
今回は「各花の種類が最も多く観察された」という条件があるため、WHERE INを使用します。WHEREは条件を指定する際に使用しますが、INを付けることでサブクエリを作成することができます。まず、WHEREの()内にサブクエリで使用するカラムを指定し、INの()内にサブクエリを記載します。

サブクエリ内は以下になります。

SELECT Species, MAX(Num_Sighted)
FROM flower_sightings
GROUP BY Species

サブクエリ内では各花の種類を返すSpeciesと観察された数が格納されているNum_Sightedを指定し、最も多く観察された数が欲しいのでMAX()の集約関数を使用しています。FROMでテーブルを指定し、GROUP BY Species を使って、各種類ごとに最大の Num_Sighted を求めています。これにより、各花の種類で最も多く観察された回数が得られます。

サブクエリで返ってくるデータは以下のような感じになるのかなと。

$$
\begin{array}{|c|c|}\hline\textbf{Species} & \textbf{Max Num Sighted} \\\hline\text{Cherry Blossom} & 40 \\\hline\text{Tulip} & 30 \\\hline\text{Sunflower} & 55 \\\hline\end{array}
$$

サブクエリから得られた最大値に一致する全てのレコードを選択します。これにより、各花の種類の観察回数が最大であった日のデータを取得できます。

最終的に返ってくるデータは以下のようになります。

$$
\begin{array}{|c|c|c|}\hline\textbf{Species} & \textbf{Date} & \textbf{Num Sighted} \\\hline\text{Cherry Blossom} & 2022-04-01 & 40 \\\hline\text{Tulip} & 2022-04-15 & 30 \\\hline\text{Sunflower} & 2023-06-20 & 55 \\\hline\end{array}
$$