LoginSignup
3
3

More than 1 year has passed since last update.

@nijigen_plotin株式会社リサーチ・アンド・イノベーション

【SQL】AWS Athena データ抽出入門 Vol.1【初心者向け】

Last updated at Posted at 2022-04-07

内容

AWS Athenaへ用意されているテーブルのデータを、SQLを使って取得できるよう目指す方向け「入門編」です。

その他ボリュームページ

公式説明書

Athena エンジンバージョン2は Presto 0.217 に基づいています。
Athenaへ用意されているテーブルからデータを取得するSQLが書けるようになるには、6. Funcsiont and Operatorsのページが参考になります。

SQL基本操作

SQLは表で管理しているデータから、該当データを抽出して取得できます。

以下のような表Aがあるとします。

商品コード 商品名 単価
001 消しゴム 80
002 鉛筆 70
003 筆箱 750
004 ボールペン 120
005 電卓 780
006 三角定規 200

SQLは以下のような形でデータを取得できます。

SELECT 列名 FROM 表名 WHERE 条件

列を取り出す(SELECT)

SELECT句はどの列を取り出すかを決めることができます。
表Aから「商品コード」の列を取り出したい場合、SELECT 商品コードとなります。

（結果はこんな感じ）

商品コード
001
002
003
004
005
006

全ての列を取り出したい場合、SELECT * と書きます。

表を選択する(FROM)

FROM句ではどの表を対象とするかを選択することができます。
今回は表Aが対象ですので、FROM 表Aとなります。

行を選択する(WHERE)

WHERE句は特定の条件に合致する行を取り出すことができます。
表Aから「単価が100未満」の行を取り出したい場合、WHERE 単価 < 100となります。

（結果はこんな感じ）

商品コード 商品名 単価
001 消しゴム 80
002 鉛筆 70

データを操作する基本の句として

  • SELECT
  • FROM
  • WHERE

の3つがあります。
以降は、オリジナルのデータを使って解説していきます。

練習用データ

Athenaで以下のクエリを「実行」すると、テーブルを作ることができます。
このテーブルデータを使って、以降は解説しますので、Athenaのクエリ実行環境がある方は、是非作って試してみてください。

テーブル1(practice_user)

CREATE TABLE practice_user AS
SELECT
    user_id,
    sex,
    age,
    pref,
    cast(created_at as timestamp) as created_at
FROM (
    VALUES
        (1, '女性', '32', '東京都','2021-10-06 12:20:45'),
        (2, '男性', '26', '青森県','2021-10-24 14:47:20'),
        (3, '女性', '49', '岐阜県','2021-11-18 02:50:33'),
        (4, '女性', '48', '東京都','2021-09-03 01:11:07'),
        (5, '男性', '20', '大阪府','2021-08-30 01:43:22'),
        (6, '男性', '70', '京都府','2021-08-12 21:15:59'),
        (7, '女性', '不明', '東京都','2021-09-03 03:38:01'),
        (8, '女性', '18', '石川県','2021-08-16 18:40:18'),
        (9, '男性', '22', '山口県','2021-12-21 08:55:13'),
        (10, '女性', '53', '鹿児島県','2021-08-13 20:37:22')
) AS t (user_id, sex, age, pref, created_at)

Athenaコンソールプレビュー結果
SnapCrab_NoName_2022-4-7_22-20-9_No-00.png

テーブル2(practice_product)

CREATE TABLE practice_product AS
SELECT
    item_id,
    product_name,
    unit_price,
    product_group
FROM (
    VALUES
        ('001', '消しゴム', 80, 0),
        ('002', '鉛筆', 70, 0),
        ('003', '筆箱', 750, 1),
        ('004', 'ボールペン', 120, 0),
        ('005', '電卓', 780, 1),
        ('006', '三角定規', 200, 1),
        ('007', 'ノート', 180, 0),
        ('008', 'コンパス', 600, 1),
        ('009', '下敷き', 320, 0),
        ('010', 'シャーペン', 150, 0)
) AS t (item_id, product_name, unit_price, product_group)

Athenaコンソールプレビュー結果
SnapCrab_NoName_2022-4-7_0-44-9_No-00.png

テーブル3(practice_sales)

CREATE TABLE practice_sales AS
SELECT
    sale_id,
    cast(sales_at as timestamp) as sales_at,
    substr(sales_at,1,10) as sales_date,
    item_id,
    quantity,
    user_id
FROM (
    VALUES
        (0,'2022-02-23 22:41:24','006',4, 8),
        (1,'2021-09-20 17:30:11','001',4, 5),
        (2,'2022-01-28 17:48:15','010',5, 3),
        (3,'2021-09-22 23:59:06','009',3, 4),
        (4,'2022-01-19 04:41:30','010',3, 2),
        (5,'2021-08-21 05:27:01','007',1, 10),
        (6,'2021-09-22 14:43:39','003',5, 4),
        (7,'2021-11-29 03:45:11','010',4, 3),
        (8,'2021-11-04 13:03:38','007',5, 1),
        (9,'2022-02-15 05:38:25','001',4, 10),
        (10,'2022-02-03 17:39:21','009',4, 3),
        (11,'2022-01-26 06:57:37','005',1, 9),
        (12,'2021-11-27 13:56:16','002',1, 7),
        (13,'2021-08-26 21:59:46','004',1, 8),
        (14,'2021-11-12 13:55:27','009',4, 2),
        (15,'2021-12-22 05:27:00','001',3, 5),
        (16,'2022-03-12 14:23:05','009',5, 4),
        (17,'2021-08-20 13:17:50','006',5, 10),
        (18,'2021-09-17 16:55:10','010',3, 5),
        (19,'2021-09-20 16:55:53','005',3, 7),
        (20,'2022-02-23 18:09:06','007',3, 8),
        (21,'2021-09-13 20:33:34','004',1, 10),
        (22,'2022-02-22 14:21:04','001',4, 9),
        (23,'2022-03-05 02:18:10','009',5, 10),
        (24,'2022-01-13 19:43:05','005',4, 4),
        (25,'2021-11-29 20:24:37','010',4, 3),
        (26,'2021-09-06 04:58:59','006',3, 4),
        (27,'2021-12-26 02:54:35','006',2, 7),
        (28,'2021-11-23 14:32:08','006',3, 4),
        (29,'2021-10-26 07:44:13','009',4, 5),
        (30,'2021-09-22 06:00:45','009',5, 10),
        (31,'2021-10-27 10:54:27','009',4, 6),
        (32,'2021-11-14 08:48:17','005',3, 5),
        (33,'2022-01-31 17:15:35','009',4, 4),
        (34,'2021-09-15 03:41:44','006',1, 4),
        (35,'2022-01-27 05:43:28','003',5, 8),
        (36,'2021-12-13 06:00:52','001',3, 8),
        (37,'2022-03-28 22:45:28','004',4, 2),
        (38,'2021-10-25 01:48:08','007',1, 6),
        (39,'2022-02-22 17:28:21','006',1, 6),
        (40,'2022-03-19 18:34:07','004',2, 4),
        (41,'2022-01-06 07:27:04','010',1, 9),
        (42,'2022-03-10 10:30:38','010',3, 3),
        (43,'2022-03-22 04:07:53','009',3, 5),
        (44,'2022-02-07 00:27:35','004',5, 6),
        (45,'2021-09-30 04:26:58','004',2, 5),
        (46,'2022-02-13 16:10:24','006',3, 8),
        (47,'2021-12-17 08:52:51','001',1, 6),
        (48,'2022-02-18 05:34:58','002',2, 1),
        (49,'2021-12-23 23:58:23','009',1, 9)
) AS t (sale_id, sales_at, item_id, quantity, user_id)

Athenaコンソールプレビュー結果
SnapCrab_NoName_2022-4-7_0-44-50_No-00.png

論理演算子

AND

ANDは「かつ」を意味していて、aとbの条件にANDを付けて a AND b とすると、aかつbを満たすという意味になります。

【例】practice_productテーブルから、unit_priceが100以上かつproduct_groupが0のデータを出したい

SELECT
	*
FROM
	practice_product
WHERE
	unit_price >= 100
AND
	product_group = 0

結果
SnapCrab_NoName_2022-4-7_0-48-34_No-00.png

unit_priceが100以上でproduct_groupが0の4商品が出てきました。

OR

ORは「または」を意味していて、aとbの条件にORを付けて a OR b とすると、aまたはbを満たすという意味になります。

【例】practice_productテーブルから、unit_priceが100以上またはproduct_groupが0のデータを出したい

SELECT
	*
FROM
	practice_product
WHERE
	unit_price >= 100
OR
	product_group = 0

結果
SnapCrab_NoName_2022-4-7_0-50-17_No-00.png

unit_priceが100以上またはproduct_groupが0の全ての商品が出てきました。
今回の例で出てこない場合があるのは、unit_priceが100未満のproduct_groupが1の商品となります。（存在しないのですべての商品が出てきました）

NOT

NOTは「ではない」を意味していて、aの条件にNOTをつけて not a とすると、aではないという意味になります。

【例】practice_productテーブルから、unit_priceが100以上ではないデータを出したい

SELECT
	*
FROM
	practice_product
WHERE
	NOT unit_price >= 100

結果
SnapCrab_NoName_2022-4-7_0-55-4_No-00.png

unit_priceが100以上ではない2商品が出てきました。

比較演算子/範囲演算子

論理演算子にて、unit_priceが100以上を>=100としましたが、この他、以下のような比較演算子があります

コマンド 内容
< 未満
> より大きい
<= 以下
>= 以上
= 等しい
<> 等しくない
!= 等しくない
BETWEEN a AND b aとbの間

こちらについては、比較のコマンドを変更して結果を試してみてください。

BETWEEN

BETWEENのみ説明します。

BETWEEN a AND bは
a <= 対象列 <= bと同等です。

【例】practice_productテーブルから、unit_priceが200以上600以下のデータを出したい

SELECT
	*
FROM
	practice_product
WHERE
	unit_price BETWEEN 200 AND 600

結果
SnapCrab_NoName_2022-4-7_1-12-54_No-00.png

データの整列

データを昇順または降順で並べたい場合、ORDER BY 列名 ASC or DESC と書きます

ORDER BYはWHERE句の後、つまり一番最後に書きます。
ASCは昇順、DESCは降順です。昇順の場合、ASCは除いても構わないです。

昇順

【例】practice_productテーブルを、unit_price昇順に並べたい

SELECT
	*
FROM
	practice_product
ORDER BY unit_price ASC

結果

SnapCrab_NoName_2022-4-7_1-20-58_No-00.png

降順

【例】practice_productテーブルを、unit_price昇順に並べたい

SELECT
	*
FROM
	practice_product
ORDER BY unit_price DESC

結果
SnapCrab_NoName_2022-4-7_1-22-0_No-00.png

昇順・降順複合

ORDER BYは複数列選択することができ、それぞれ昇順降順を選択できます。

【例】practice_productテーブルを、product_groupを降順、unit_price昇順に並べたい

SELECT
	*
FROM
	practice_product
ORDER BY product_group DESC, unit_price ASC

結果
SnapCrab_NoName_2022-4-7_1-24-9_No-00.png

注意点

昇順降順ですが、データの型に気を付けないと、意に反する結果となる事があります。
主には文字列型(varchar)に対してORDER BYすると起こります。
型については後述しますが、Athenaのテーブルのプラスボタンから見ることができます。

SnapCrab_NoName_2022-4-7_2-15-3_No-00.png

practice_userテーブルのuser_idをわざとvarcharに変換して降順にしてみます。

SELECT * FROM practice_user
ORDER BY cast(user_id as varchar) desc

結果

SnapCrab_NoName_2022-4-7_2-23-3_No-00.png

9が一番大きく、10は2番目に小さい値となってしまいました。
varchar型での並べ替えの場合、1文字目のUnicodeコードポイントでの並べ替えになるので、このような形になります。

別名を付ける

表や列に別名を付ける場合 AS を使います。
次の集計でASを用いますので。説明します。

【例】practice_productテーブルのproduct_groupをgroup_numberという列名に変更して、全列を表示したい

SELECT
	item_id,
	product_name,
	unit_price,
	product_group as group_number
FROM
	practice_product

結果
SnapCrab_NoName_2022-4-7_1-35-42_No-00.png

注意点

別名を付ける際、分かりやすさのために日本語を付けたくなりますが、通常とは違う書き方をする必要がありますので、お勧めしません。
※ 以下のように”で括る必要があります。

SELECT
	item_id,
	product_name,
	unit_price,
	product_group as "製品グループ"
FROM
	practice_product

集計

SQLではデータを取り出す際に、平均を取ったり、最大値を取ったりと集計を行うことができます。

代表的な(よく使う)ものとしては以下があります。

コマンド コマンド
MAX(列名) 列の最大値を求める
MIN(列名) 列の最小値を求める
AVG(列名) 列の平均値を求める
SUM(列名) 列の合計を求める
COUNT(列名) 列のデータのある行数を求める
COUNT(DISTINCT 列名) 列のデータの重複値を除いた行数を求める
APPROX_PERCENTILE(列名, 0.5) 列の中央値を求める(パーセンタイル値を用いる)

MAX

【例】practice_productテーブルの、unit_price最大値を求めたい

SELECT
	MAX(unit_price) as max_unit_price
FROM
	practice_product

結果
SnapCrab_NoName_2022-4-7_1-40-33_No-00.png

MIN

【例】practice_productテーブルの、unit_price最小値を求めたい

SELECT
	MIN(unit_price) as min_unit_price
FROM
	practice_product

結果

SnapCrab_NoName_2022-4-7_1-41-21_No-00.png

AVG

【例】practice_productテーブルの、unit_price平均値を求めたい

SELECT
	AVG(unit_price) as avg_unit_price
FROM
	practice_product

結果
SnapCrab_NoName_2022-4-7_1-44-8_No-00.png

SUM

【例】practice_productテーブルの、unit_price合計値を求めたい

SELECT
	SUM(unit_price) as sum_unit_price
FROM
	practice_product

結果
SnapCrab_NoName_2022-4-7_1-45-27_No-00.png

COUNT

【例】practice_productテーブルの、product_groupの行数を求めたい

SELECT
	COUNT(product_group) as count_product_group
FROM
	practice_product

結果
SnapCrab_NoName_2022-4-7_1-46-56_No-00.png

選択した列のデータにNULLがある場合、その行はカウントされません。

COUNT(DISTINCT 列名)

【例】practice_productテーブルの、product_groupのデータ重複を除いた行数を求めたい

SELECT
	COUNT(DISTINCT product_group) as distinct_count_product_group
FROM
	practice_product

結果
SnapCrab_NoName_2022-4-7_1-48-25_No-00.png
選択した列のユニークなデータの個数を求めるような形です。

APPROX_PERCENTILE(列名, 0.5)

【例】practice_productテーブルの、unit_price中央値を求めたい

SELECT
	APPROX_PERCENTILE(unit_price, 0.5) as median_unit_price
FROM
	practice_product

結果
SnapCrab_NoName_2022-4-7_1-52-49_No-00.png

Athenaの場合、中央値を求める関数が無いため、パーセンタイル値の50%点で代用します。
よって、総数が偶数の場合でも真ん中に最も近い2つの値の平均を取るようなことをしませんので、200が返ってきます。

グループ化

集計を「特定のグループごと」に行いたい場合、GROUP BY を用います。
GROUP BYは、WHERE句などで条件を書いた後に書きます。

【例】practice_productテーブルの、product_groupごとに、unit_price平均値を求めたい

SELECT
	AVG(unit_price) as avg_unit_price
FROM
	practice_product
GROUP BY product_group

結果
SnapCrab_NoName_2022-4-7_2-2-36_No-00.png

これだと、出てきた2つのどちらがproduct_groupの0,1なのかが分からないですね。
SELECTで列にも追加してあげるのがよく使う方法です。

SELECT
	product_group,
	AVG(unit_price) as avg_unit_price
FROM
	practice_product
GROUP BY product_group

結果
SnapCrab_NoName_2022-4-7_2-4-13_No-00.png

このようにしてSELECTで「特定のグループごと」の列も呼び出す場合、呼び出した列は必ずGROUP BYに入れる必要があります。

  • 実行できない例
SELECT
	product_group,
	item_id,
	AVG(unit_price) as avg_unit_price
FROM
	practice_product
GROUP BY product_group

グループ化からの条件を付ける

GROUP BYをした後の結果に対してWHEREのように条件を付けたい場合があります。
その場合はHAVINGを用います。

【例】practice_productテーブルの、product_groupごとに、unit_price平均値を求めて、unit_price平均値が500以上のデータを出したい

SELECT
	product_group,
	AVG(unit_price) as avg_unit_price
FROM
	practice_product
GROUP BY product_group
HAVING AVG(unit_price) >= 500

結果
SnapCrab_NoName_2022-4-7_2-10-17_No-00.png

Athenaでは HAVING AVG(unit_price)の部分をASで変更した列名にすることはできません。
つまり、HAVING avg_unit_price >= 500 とすることはできません。

表の結合

表を選択するFROMでは、複数の表を選ぶことができます。

練習用データの表同士の関係は、以下の図のようになっています。

  • practice_productとpractice_salesはitem_id同士で結合することができる
    SnapCrab_NoName_2022-4-7_11-56-55_No-00.png

  • practice_usersとpractice_salesはuser_id同士で結合することができる
    SnapCrab_NoName_2022-4-7_12-3-0_No-00.png

表と表は、特定の列を結合してくっつけることができます。
くっつける場合は、どの列と列が結びついているのかをWHERE句に書く必要があります。

【例】practice_salesとpractice_userの表を選択して結合したい

SELECT
	*
FROM
	practice_sales,practice_user
WHERE
	practice_sales.user_id = practice_user.user_id

結果

SnapCrab_NoName_2022-4-7_2-38-16_No-00.png

practice_salesのデータとpractice_userのデータをくっつけることができました。
ただ、列名に同じ名前が含まれていることは好ましくありません。

practice_sales.user_idとpractice_user.user_idで結合したので、「user_id」という列が2つ存在してしまっています。
表を結合したときは、同じ列名のものが無いようにして、どの列を選択してきているのかきちんと書いてあげるのが良いです。

SELECT
	practice_sales.sale_id,
	practice_sales.sales_at,
	practice_sales.item_id,
	practice_user.user_id,
	practice_user.pref
FROM
	practice_sales,practice_user
WHERE
	practice_sales.user_id = practice_user.user_id

列の選択では、「表名.列名」のようにしていますが、「列名」だけでSELECTすると、どちらの表にも存在する列名の場合、エラーを吐いてしまいます。
結合時は「表名.列名」とかいてSELECTして上げるのが良いと思います。

  • どちらの表にも存在する列名の為、エラーを吐いてしまう
SELECT
	user_id
FROM
	practice_sales,practice_user
WHERE
	practice_sales.user_id = practice_user.user_id

SnapCrab_NoName_2022-4-7_2-46-43_No-00.png

結合の種類

この他、表と表を結合する操作として以下があります。
下の表では分かりづらいかもしれません。図でこの後説明しますので、そちらもご覧ください。

コマンド 内容
(INNER) JOIN 両方に存在するデータを結合
LEFT (OUTER) JOIN 左側に書いた表をベースとして結合
RIGHT (OUTER) JOIN 右側に書いた表をベースとして結合
FULL (OUTER) JOIN 両方に存在するデータは結合し、存在しないデータはそのままくっつく
CROSS JOIN 結合に選択した列名と列名の全てのパターンを生成（クロス積）

この後に、以下の句を書いて、結合する列名を定義します。

コマンド 内容
ON 互いの表で結合する列名をそれぞれ記載
USING 互いの表に共通する列名を1つ記載

書き方は

SELECT
	列名
FROM 表名
INNER JOIN 表名
ON 表名.列名 = 表名.列名 (もしくは USING 列名)

のような形です

INNER JOIN

JOINまたはINNER JOINと書くと、両方に存在するデータを結合することができます。

【例】practice_productとpractice_salesの先頭3行の表を使って、INNER JOINする

SELECT
	*
FROM practice_product
INNER JOIN (SELECT * FROM practice_sales limit 3) as practice_sales -- practice_salesから先頭3行取得した表をpractice_salesと定義
ON practice_product.item_id = practice_sales.item_id

結果
SnapCrab_NoName_2022-4-7_12-12-16_No-00.png

表の結合で貼った図の赤線同士が繋がっているデータのみ抽出できました。

ちなみにこれは、FROM句で2つの表を書いてWHERE句で条件を書いた

SELECT
	user_id
FROM
	practice_sales,practice_user
WHERE
	practice_sales.user_id = practice_user.user_id

この例も、動作としてはINNER JOINと同じです。

また、例ではON句を使っていましたが、互いに列名が同じ場合はUSING句を使うことができます。

SELECT
	*
FROM practice_product
INNER JOIN (SELECT * FROM practice_sales limit 3) as practice_sales -- practice_salesから先頭3行取得した表をpractice_salesと定義
USING (item_id)

結果
SnapCrab_NoName_2022-4-7_12-16-26_No-00.png

USING句の場合、結合後に同名の列が出てくるのを避けることができます
ON句の場合と比べて、item_id列が1つ減っています。

LEFT JOIN

LEFT JOINは左側、つまりFROM句で選択した表をベースとして、LEFT JOINで選択した表を結合します。

【例】practice_productとpractice_salesの先頭3行の表を使って、LEFT JOINする

SELECT
	*
FROM practice_product
LEFT JOIN (SELECT * FROM practice_sales limit 3) as practice_sales -- practice_salesから先頭3行取得した表をpractice_salesと定義
ON practice_product.item_id = practice_sales.item_id

結果
SnapCrab_NoName_2022-4-7_12-19-53_No-00.png

左側をベースとして右側を結合するので、右側にデータが無いものはNULL(データなし)として結合されます。

RIGHT JOIN

RIGHT JOINは右側、つまり RIGHT JOINで選択した表をベースとして、FROM句で選択した表を結合します。

【例】practice_productとpractice_salesの先頭3行の表を使って、RIGHT JOINする

SELECT
	*
FROM practice_product
RIGHT JOIN (SELECT * FROM practice_sales limit 3) as practice_sales -- practice_salesから先頭3行取得した表をpractice_salesと定義
ON practice_product.item_id = practice_sales.item_id

結果
SnapCrab_NoName_2022-4-7_12-25-6_No-00.png

右側のitem_idは左側にもすべて存在するので、結果としてはINNER JOINと同じになりました。
右側にしかないitem_idがもしあった場合は、左側のデータとしてはその行はNULL(データなし)として結合されます。

FULL JOIN

FULL JOINは両側にデータがあるものはINNER JOIN、片側データが無いものについては、もう片側がNULL(データなし)となります。
結果を見たほうが理解が早いかと思います。

practice_product表も先頭5行のみにして、以下のような関係にします。

SnapCrab_NoName_2022-4-7_12-28-32_No-00.png

これらについて、FULL JOINしてみます。

【例】practice_productの先頭5行の表とpractice_salesの先頭3行の表を使って、FULL JOINする

SELECT
	*
FROM (SELECT * FROM practice_product limit 5) as practice_product -- practice_productから先頭5行取得した表をpractice_productと定義
FULL JOIN (SELECT * FROM practice_sales limit 3) as practice_sales -- practice_salesから先頭3行取得した表をpractice_salesと定義
ON practice_product.item_id = practice_sales.item_id

結果

SnapCrab_NoName_2022-4-7_12-43-36_No-00.png

両側にデータがあるものは互いに結合され、片方にしかないものは、もう片方がNULL(データなし)として結合されます。

CROSS JOIN

CROSS JOINは結合する表同士の全ての組み合わせを返します。

ONやUSING句は使いません。

【例】practice_productの先頭5行の表とpractice_salesの先頭3行の表を使って、CROSS JOINする

SELECT
	*
FROM (SELECT * FROM practice_product limit 5) as practice_product -- practice_productから先頭5行取得した表をpractice_productと定義
CROSS JOIN (SELECT * FROM practice_sales limit 3) as practice_sales -- practice_salesから先頭3行取得した表をpractice_salesと定義

結果
SnapCrab_NoName_2022-4-7_12-49-27_No-00.png

色のついた枠を見ると分かりやすいかもしれませんが、右側の表のitem_id列1つ1つに対して左側の表のitem_idの全てのパターンが結合されています。
左側5行と右側3行のCROSS JOINなので、5 x 3で15行のデータが抽出されます。

3
3
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
3
3