SQLメモ：SELECT文の基本形、応用、集計などの例
■基本形
SELECT (カラム名) FROM (テーブル名) WHERE (条件文)ORDER BY (カラム名) ASCまたはDESC;
カラム名＝ *：全列、◯◯：カラム指定、◯,△：複数カラム指定
テーブル名＝スキーマ名.テーブル名
条件文＝◯◯ 記号 値
記号 =：等しい、<：小さい、>：大きい、<=：下、>=：以上、!=：等しくない
ASC：昇順（デフォルト）、DESC：降順
注：それぞれ半角スペースで区切る。最後の「;」を忘れるな！
■応用：最後の100レコードの取得
例：publicにあるテーブルdata_sub_mqttからtime_subの新しい順に100レコード全列データを取得
SELECT *
FROM public.data_sub_mqtt
ORDER BY time_sub DESC
LIMIT 100;
以下は出典からのコピペ＆略化です。自作していません。
出典元さまに感謝！
なお、実用途に合わせて十分解読してから流用すること。
■複数データ結合
出典：3分間データベース講座 第3回: 複数のデータを組み合わせる～結合（JOIN）と集約関数～
１：INNER JOIN：2つのテーブルの共通する列に一致する値があるデータだけを組み合わせて表示
-- ordersテーブルとproductsテーブルをINNER JOINで結合する
SELECT
o.id AS order_id,
o.user_id,
p.name AS product_name, -- productsテーブルの商品名
o.quantity
FROM
orders AS o -- ordersテーブルに'o'という別名をつける
INNER JOIN
products AS p ON o.product_id = p.id; -- productsテーブルを'p'という別名で結合
２：LEFT JOIN：左側のテーブルのデータをすべて残して結合、一致データなければNULL表示
-- ordersテーブルのデータをすべて残してproductsテーブルと結合する
SELECT
o.id AS order_id,
o.user_id,
p.name AS product_name,
o.quantity
FROM
orders AS o
LEFT JOIN
products AS p ON o.product_id = p.id;
■データの集計
●代表的な集約関数とGROUP BY
COUNT(): データ数
SUM(): 合計値
AVG(): 平均値
MAX(): 最大値
MIN(): 最小値
●GROUP BY
指定列の値が同じとなる行を一つのグループとし、
そのグループごとに集約関数を適用する機能。
例：
-- 商品ごとの注文数量の合計を算出する
SELECT
p.name AS product_name, -- 商品名
SUM(o.quantity) AS total_quantity -- 注文数量の合計
FROM
orders AS o
INNER JOIN
products AS p ON o.product_id = p.id
GROUP BY
p.name; -- 商品名ごとにグループ化する
■最新データ抽出＆必要データ取得
出典：SQLで最新レコードを取得する3つの方法
元のテーブル
product_id name price updated_at
1 スマートフォン 50000 2023-11-01 09:30:00
2 ノートPC 80000 2023-11-02 10:15:00
3 イヤホン 15000 2023-11-01 11:20:00
1 スマートフォンX 55000 2023-11-03 08:45:00
2 ノートPC Pro 120000 2023-11-05 12:00:00
4 タブレット 45000 2023-11-04 14:30:00
3 イヤホンPlus 20000 2023-11-03 16:00:00
ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー
●Group ByとJOINを使用するやり方
例：製品IDごとに最新の名前と価格を取得
ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー
SELECT p.product_id, p.name, p.price
FROM product p
JOIN (
SELECT product_id, MAX(updated_at) AS latest_update
FROM product
GROUP BY product_id
) latest_product ON p.product_id = latest_product.product_id AND p.updated_at = latest_product.latest_update
ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー
●Order ByとLIMITを使用
例：更新日時で降順にソートし、LIMITを使って特定製品の最新の1件を取得
ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー
SELECT product_id, name, price
FROM product
WHERE product_id = [特定の製品ID]
ORDER BY updated_at DESC
LIMIT 1;
ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー
●ウィンドウ関数を使用するやり方
ウィンドウ関数のROW_NUMBER() で
「製品IDでグルーピングし、更新日時が新しい順に並べ、先頭の1件だけを取得」を高速に実現
その手順：
ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY updated_at DESC) で、
各グループ内の最新レコードに「1番」を振る。
WHERE row_number = 1 で最新だけを取り出す。
ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー
SELECT product_id, name, price
FROM (
SELECT product_id, name, price,
ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY updated_at DESC) AS rn
FROM product
) ranked
WHERE rn = 1;