経過日数を取得

Athenaで表題を行いたい。

■ 環境

  • AWS Athena

■ MySQL

MySQLでいうところの下記をやりたい。

MySQL :: 12.7 日付および時間関数
https://dev.mysql.com/doc/refman/5.6/ja/date-and-time-functions.html#function_datediff

Athenaでもあるかと思ったが、そんな関数はないとエラーとなってしまった。

■ presto

そういえば”Athena“は”presto“であったっけ?と思い出してこちらで確認してみる。

6.13 Date and Time Functions and Operators
https://prestodb.io/docs/current/functions/datetime.html

`date_diff()`が存在した。

presto“のマニュアルによると下記のように行けるかと思ったがエラーとなった。

date_diff('d', day1, day2)

試行錯誤してみると下記で無事実行できた。

date_diff('day', day1, day2)

以上。

■ 関連

AthenaでDATE型

AthenaでDATE型

表題の通り。使用できたのでメモ。

■ 環境

  • Amazon Athena

■ DATE型

パーティションは年月日で作成している。またテーブル作成時の定義では、DATE型の選択肢が無いので使えないものだと思い込んでいた。

select 
  id
  :
  , cast(concat(year, '-', month, '-', day) as date) as ymd
from example
  :

ということができた。

以上。

■ 関連

経過日数を取得

AthenaでDIVが使えない

AWS Athenaで表題の通り。数値計算で`DIV`を使いたかったのだがダメだった。

■ 環境

  • AWS Athena

■ DIV

Athenaではパーティションを年月日で分けている。この月の値を元に、該当の月は何Q(四半期)であるのかを一緒に算出させたかったので`DIV`でいけるかな?と思ったところ、下記のようなエラーとなった。

Error running query: An error occurred (InvalidRequestException) when calling the StartQueryExecution operation: Queries of this type are not supported

`QUARTER`関数は使えるようなのだが、年月を元にtimestampに変換して…というのが少々面倒だったのとAthenaではやったことがなかったので下記のようにしてしまった。

select month, (month - 1) / 3 + 1 as quarter from ...

上記でとりあえず欲しい値は取得できたので良しとする。

以上。

複数のcount()の結果を四則演算

SQLにおいて表題のことをやりたかった。

■ 環境

  • Amazon Athena

■ count()

単純に”count()“の結果を計算するのであれば特に問題なくできるのだが、1つのクエリで複数の”count()“を実行し、その結果を使って四則演算したかった。

SELECT
  regist_date,
  count(1) as total,
  count(column1 < 10 or null) as count1, count(column1 >= 10 or null) as count2
FROM testtable
GROUP BY regist_date

以前使用した上記のSQLであれば、下記のようなことを行いたい。

count1 + count2

これを一度のクエリでは書けないので下記のようにすれば一応実行はできる。

SELECT regist_date, total, count1 + count2 as count3
FROM (
  SELECT
    regist_date,
    count(1) as total,
    count(column1 < 10 or null) as count1, count(column1 >= 10 or null) as count2
  FROM testtable
  GROUP BY regist_date
)

まぁ確かにできるのだがもっとスマートな書き方はないのかな。

以上。

■ 関連

複数条件のcount()

count(distinct (case when … then … end))

SQLにおいて表題の件。自身はAthenaでとても重宝した。条件に合致した中での一意な数を取得したい。

■ 環境

  • Amazon Athena

■ count(distinct)

Athenaを利用しているので、読み込むデータ量も減らしたいしクエリを実行する対象は減らしたい。なので1回のクエリ実行で必要な情報を得たい。複数の条件で`count()`を取得したいが、これは以前のエントリで述べた通り。今回はさらに条件を絞った状態で`distinct`の値を取得したかった。

SELECT
  regist_date,
  count(1) as total,
  count(column1 < 10 or null) as count1, count(column1 >= 10 or null) as count2,
  count(distinct groupid) as group_count1,
  count(distinct (case when column1 < 10 then groupid end)) as group_count2, count(distinct (case when column1 >= 10 then groupid end)) as group_count3,
FROM testtable
GROUP BY regist_date

上記のようなSQLにすることで1度のクエリ実行で自身が得たいデータは全て取得できるようになった。

group_count1“は全体における”groupid“の`distinct`の値。一意な`groupid`の数を取得できる。

group_count2“は、`column1 < 10`の条件に合致した中での一意な`groupid`の数を取得できる。

group_count3“は…以下略。

以上。

■ 関連

複数条件のcount()