複数の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()

DataSource Athena

Redashにおいて表題の通り使えるように設定した。

■ 環境

  • Amazon Athena
  • Redash 3.0.0

■ Data Sources

画面右上のデータベース(?)のマークで”Settings > DATA SOURCES“。

+New Data Source“から追加する。

  • Type: Amazon Athena
  • Name: 自身がわかりやすい名前で。athenaとつけておいた。
  • AWS Region: 使用するAthenaのリージョンを。ap-northeast-1。
  • AWS Access Key: Athenaの権限を持つアクセスキーを登録。
  • AWS Secret Key: AccessKeyと対のものを。
  • S3 Staging Path: Athenaの実行結果やメタ情報が出力されるS3のパスを指
  • 定。
  • Schema Name: デフォルトでは”default”のようだ。無記入でもOK。
  • KMS Key: 無記入

上記で設定し、”Test Connection“も試し”Success“となればOKである。

設定後ではあるが下記のようになった。

以上。

■ 関連

redash 3.0.0 upgrade

CONCAT & CAST & LPAD

Amazon Athenaで表題の関数について。Amazon Athenaに限った話ではなくSQLの話だけれども…。

■ 環境

  • Amazon Athena

■ きっかけ

やりたいこととしては単純に『カラムの値を連結したい』ということだけのはずだった。もうずっと単純なSQLしか書いていなかったのでここら辺の関数についていろいろ忘れておりはまってしまった。

■ CONCAT

文字列として連結したいんだから、”CONCAT“でいけるだろうと思い試してみたところ、下記のエラーが出た。

Error running query: SYNTAX_ERROR: line 1:185: Unexpected parameters (integer) for function concat. Expected: concat(array(E), E) E, concat(E, array(E)) E, concat(array(E)) E, concat(varchar)

あれ?と思いドキュメントを見ていると下記を発見。

Concatenating Arrays
https://docs.aws.amazon.com/ja_jp/athena/latest/ug/concatenating-arrays.html

いややりたいことは”文字列“としての連結であって、配列の連結ではない。ということでこれは違う。もう一度エラーメッセージをよく見てみると下記の通り。

concat(varchar)

できるはずである。としばし考えて気がついた。対象のカラムの型が”int“であった。では”CAST“を挟めば良いのか。ということで下記のような感じになった。

select concat(cast(year as varchar), cast(month as varchar), cast(day as varchar)) ...

しかし得られた結果がこれでは微妙であった。”2018316“。”0“パディングしなければならない。”LPAD“の出番である。”LPAD“だから戻ってくるのは”varchar“だよね、と思い下記のように書き換えた。

select concat(cast(year as varchar), lpad(month, 2, '0'), lpad(day, 2, '0')) ...

実行すると下記のようなエラーが…。

Error running query: SYNTAX_ERROR: line 1:215: Unexpected parameters (integer, integer, varchar(1)) for function lpad. Expected: lpad(varchar(x), bigint, varchar(y))

これもエラーログをよく読んでみれば解決した。ここも”CAST“が必要であった。

最終的に下記で思い通りの結果を得られた。

select concat(cast(year as varchar), lpad(cast(month as varchar), 2, '0'), lpad(cast(day as varchar), 2, '0')) ...

以上。

show databases on Athena

表題の通り。

■ 環境

  • Amazon Athena

■ show databases

SHOW DATABASES | Amazon Athena
https://docs.aws.amazon.com/athena/latest/ug/show-databases.html

互換ありということであればもちろんあるか。”Management Console“でアクセスしている場合には左側のメニューに”DATABASE“のプルダウンもあるので今まで気にもしていなかった。クエリの実行結果も実に質素なものである。

show databases

以上。