SQLのまとめ
はじめに
はじめまして。内定者研修中の新卒エンジニアです。
研修で習ったSQLについて大事そうなところをまとめていきたいと思います。
今回まとめるのは以下の4つです。
・group by
・集計関数
・order by
・内部結合
今回は以下の商品テーブルを例に考えます。
group by
GROUP BY句はテーブルを特定のカラムの値に基づいていくつかのグループに分ける働き、主に集計関数(SUM, COUNT, AVG, MIN, MAXなど)と組み合わせて使用され、指定したカラムの値ごとの合計値や平均値、最大値・最小値を取得したいような場合に利用します。
【例】
SELECT category, SUM(stock) as total_stock FROM products GROUP BY category;
【出力】
category | total_stock
+----------------------+
電化製品 | 15
スポーツ用品 | 20
食べ物 | 80
このクエリでは、category に基づいてデータをグループ化し、各カテゴリに属する商品の在庫数を合計しています。結果として、各カテゴリの総在庫数が表示されます。
集計関数
集計関数は指定された列の様々な値を集計する関数です.先ほどのgroup byと同時に使うことが多いです。
ここでは、5つの集計関数を紹介します。
・COUNT関数
COUNT関数は指定された項目Xの件数を返します。
【例】
SELECT COUNT(*) as total_products FROM products;
【出力】
5
このクエリでは、products テーブルの全行数をカウントしています。結果として、商品テーブルに登録されている商品の総数が表示されます。
・SUM関数
SUM関数は指定された項目Xの合計を返します。
ただし、項目Xの値がNULLの場合は除外して集計します。
【例】
SELECT category, SUM(stock) as total_stock FROM products GROUP BY category;
【出力】
category | total_stock
+----------------------+
電化製品 | 15
スポーツ用品 | 20
食べ物 | 80
・AVG関数
AVG関数は指定された項目Xの平均値を返します。
ただし、項目Xの値がNULLの場合は除外して集計します。
【例】
SELECT category, AVG(price) as average_price FROM products GROUP BY category;
【出力】
category | average_price
+----------------------+
電化製品 | 160
スポーツ用品 | 80
食べ物 | 32.5
このクエリでは、category に基づいてデータをグループ化し、各カテゴリに属する商品の平均価格を計算しています。結果として、各カテゴリの平均価格が表示されます。
・MAX関数
MAX関数は、指定された項目Xの最大値を返します。
【例】
SELECT MAX(price) as highest_price FROM products GROUP BY category;
【出力】
category | highest_price
+----------------------+
電化製品 | 200
スポーツ用品 | 80
食べ物 | 50
このクエリでは、category に基づいてデータをグループ化し、各カテゴリに属する商品の最高価格を取得しています。結果として、各カテゴリの最高価格が表示されます。
・MIN関数
MIN関数は、指定された項目Xの最大値を返します。
【例】
SELECT MIN(price) as lowest_price FROM products GROUP BY category;
【出力】
category | lowest_price
+----------------------+
電化製品 | 120
スポーツ用品 | 80
食べ物 | 15
このクエリでは、category に基づいてデータをグループ化し、各カテゴリに属する商品の最低価格を取得しています。結果として、各カテゴリの最低価格が表示されます。
order by
SQLの「ORDER BY」を使えば、抽出したデータをソート(並び替え)することができます。
ASC,DESCを指定することで昇順か降順にすることもできます。
【例】昇順の場合
SELECT id, name, price, category FROM products ORDER BY name;
このクエリでは、products テーブルから id、name、price、および category 列を選択し、name 列に基づいてアルファベット順に並べ替えています。
【例】降順の場合
SELECT id, name, price, category FROM products ORDER BY price DESC;
このクエリでは、products テーブルから id、name、price、および category 列を選択し、price 列に基づいて降順(高い順)に並べ替えています。DESC キーワードが降順並べ替えを指定しています。
内部結合
内部結合を使うとテーブルが見やすくなり、集計がしやすいです。
内部結合(INNER JOIN)とは、その名の通りテーブルとテーブルを結合するためのSQL構文です。そして、INNER(内部)であることの特徴は、テーブルとテーブルの互いの条件に一致するレコード(データ行)をのみを抽出するという点です。
【イメージ図】
今回は、従業員と部署のテーブルの内部結合を例に挙げます。
【従業員テーブル
CREATE TABLE employees ( employee_id INT PRIMARY KEY, employee_name VARCHAR(50), department_id INT );
【部署テーブル】
CREATE TABLE departments ( department_id INT PRIMARY KEY, department_name VARCHAR(50) );
【内部結合のクエリ】
SELECT customers.customer_id, customers.customer_name, orders.order_id, orders.order_date FROM customers INNER JOIN orders ON customers.customer_id = orders.customer_id;
上記のSQL文では、employeesテーブルとdepartmentsテーブルを結合し、従業員ID、名前、部署名を取得しています。INNER JOIN句で、employeesテーブルとdepartmentsテーブルをdepartment_idで結合しています。
【内部結合のクエリ(Where句を使う場合)】
SELECT employees.employee_id, employees.employee_name, departments.department_name FROM employees, departments WHERE employees.department_id = departments.department_id;
上記のSQL文では、employeesテーブルとdepartmentsテーブルをdepartment_idカラムで結合しています。WHERE句を使用して、employees.department_idとdepartments.department_idが一致する行だけを取得しています。そして、結合したテーブルから従業員ID、従業員名、部署名を取得しています。
【出力】
このように二つのテーブルが共通するdepartment_idで結合されました。
内部結合をすることでテーブルを一つにし、より見やすくなります。
Discussion