Amazon Athanaで利用するクエリの関数のメモです。
日付関連
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");今回は以上となります。
コメント