Amazon Athenaのクエリ【メモ】

スポンサーラベル

Code

2023.09.172026.08.22

Amazon Athanaで利用するクエリの関数のメモです。

本記事の内容

日付関連
case文
計算する
ビューを作成する
AWSを効率的に学習する方法
- ①AWS基礎学習
- ②AWS資格取得
あわせて読みたい

日付関連

datetimeをそれぞれyearやmonthなどに分解してGroup化してます。

select year("input_datetime") as year, month("input_datetime") as year, day("input_datetime") as day, hour("input_datetime") as hour, minute("input_datetime") as minute
from quicksight_test_table
group by  year("input_datetime"), month("input_datetime") , day("input_datetime") , hour("input_datetime") , minute("input_datetime")

case文

先ほどのSQL文にCASEで条件を追加しました。resultがHighの数を算出します。

select year("input_datetime") as year, month("input_datetime") as year,
day("input_datetime") as day, hour("input_datetime") as hour, minute("input_datetime") as minute, 
sum (case when result = 'High' then 1 else 0 end)  as high_sum
from quicksight_test_table
group by  year("input_datetime"), month("input_datetime") , day("input_datetime") , hour("input_datetime") , minute("input_datetime")

計算する

ここでさらに追加、計算を追加します。先ほどのHighの割合を追加しました。

select year("input_datetime") as year, month("input_datetime") as month, day("input_datetime") as day,
hour("input_datetime") as hour, minute("input_datetime") as minute, 
count(result) as cnt,
sum (case when result = 'High' then 1 else 0 end)  as high_sum, 
cast(sum (case when result = 'High' then 1 else 0 end) as real)/ cast(count(result) as real) *100 as per_high

from quicksight_test_table
group by  year("input_datetime"), month("input_datetime") , day("input_datetime") , hour("input_datetime") , minute("input_datetime")

ビューを作成する

ここまでに作成したSQLを使ってビューを作成してみます。

CREATE OR REPLACE VIEW "per_minute" AS 
select year("input_datetime") as year, month("input_datetime") as month, day("input_datetime") as day,hour("input_datetime") as hour, minute("input_datetime") as minute, count(result) as cnt, sum (case when result = 'High' then 1 else 0 end)  as high_sum, cast(sum (case when result = 'High' then 1 else 0 end) as real)/ cast(count(result) as real) *100 as per_high
from quicksight_test_table
group by  year("input_datetime"), month("input_datetime") , day("input_datetime") , hour("input_datetime") , minute("input_datetime");

今回は以上となります。