こんにちは、 @kz_morita です。 今回は、Athena で LIKE 検索するときに検索したい文字列をエスケープする方法について調べたのでまとめます。 対象のクエリ 以下のようなクエリから % を持つテキストを探し出したいようなユースケースを考えます。 WITHdatasetAS(SELECT*FROM(VALUES'aaa_%aaa','bbbbbb','cccccc@#$%^&*()^cc')ast("text"))SELECT*FROMdataset text aaa_%aaa bbbbbb cccccc@#$%^&*()^cc ESCAPE characterを指定する 普通に検索しようとすると、% が LIKE の Wildcard として予約されているので検索することができません。 そのため、Presto には ESCAPE character を指定する構文が用意されています。 以下は、# を ESCAPE character として使用する例です。 WITHdatasetAS(SELECT*FROM(VALUES'aaa_%aaa','bbbbbb','cccccc@#$%^&*()^cc')ast("text"))SELECT*FROMdatasetWHERE"text"LIKE'%#%%'ESCAPE'#'以下のように正しく実行することができます。 text aaa_%aaa cccccc@#$%^&*()^cc 全記号を検索したいなどの特殊ケース 上記の方法で、大体のケースはカバーできると思いますが例えば、検索したい文字が複数あって他のテーブルに格納されているなどといったケースではもう少し考慮が必要です。 WITHdatasetAS(SELECT*FROM(VALUES'aaa_%aaa','bbbbbb','cccccc@#$%^&*()^cc')ast("text")),symbol_dataAS(SELECTARRAY['!','@','#','$','%','^','&','*','(',')']assymbols),data_with_symbolsAS(SELECT*,(SELECTsymbolsFROMsymbol_dataLIMIT1)assymbolsFROMdataset)SELECT*,filter(symbols,s->"text"LIKE'%'||s||'%')ashitFROMdata_with_symbolsWHEREcardinality(filter(symbols,s->"text"LIKE'%'||s||'%'))>0上記は symbol_data というテーブルに格納されている記号で検索するようなケースを想定しています。 以下の部分は、配列に格納されている記号データを "text" フィールドに含まれているものだけ filter してその件数が 0 件以上という条件を WHERE 句で指定しています。

こんにちは、 @kz_morita です。 今回は、Athena で配列の重複した要素数を数える方法をメモします。 対象のデータ 以下のような配列データを想定します。 withdatasetas(select*from(VALUES'A','B','C','D','E','B','C','D','A','A')ast("data"))selectarray_agg("data")aslistfromdataset list {‘A’, ‘B’, ‘C’, ‘D’, ‘E’, ‘B’, ‘C’, ‘D’, ‘A’, ‘A’} これを以下のように集計したいケースを考えます。 key count ‘A’ 3 ‘B’ 2 ‘C’ 2 ‘D’ 2 ‘E’ 1 Presto の場合 array_frequency という関数が用意されているのでこれを利用すれば OK です。 https://prestodb.io/docs/current/functions/array.html withdatasetas(select*from(VALUES'A','B','C','D','E','B','C','D','A','A')ast("data"))selectarray_frequency(array_agg("data"))aslistfromdatasetただし、こちらの関数は Athena では用意されてなく実行することができません。

こんにちは、 @kz_morita です。 Presto で 配列として入っているデータを行に展開する方法をまとめます。 対象のデータ 以下のようなデータを type values A { 1,2,3 } B { 2,4,6 } C { 1,3,5} 以下のように行に展開したいときに、使用するクエリをまとめていきます。 type values A 1 A 2 A 3 B 2 B 4 B 6 C 1 C 3 C 5 CROSS JOIN と UNNEST 以下のようなクエリで上記のように展開することができます。

こんにちは、 @kz_morita です。 Athena でデータを分析する際に、GROUPING SETS を用いてスッキリとクエリがかけたのでまとめます。 Presto 0.268 Documentation 対象のデータとやりたい集計 今回は以下のような生徒の成績データ (5段階評価) のようなものを仮定します。 生徒 国語 数学 社会 理科 英語 A 5 3 4 2 2 B 2 1 3 2 4 C 2 4 2 4 3 D 4 4 3 1 3 E 3 2 5 1 5 このデータから以下のように各教科の各成績人数を集計したいものとします。

こんにちは， @kz_morita です。 データを見る際にデータの傾向を把握するための統計量をだすことはよくあり，pandas などであれば，DataFrame の describe メソッド を呼び出せば以下のようなデータを確認することができます． count: 要素数 unique: ユニークな要素数 top: 最頻値（mode） freq: 最頻値の出現回数 mean: 平均 std: 標準偏差 min: 最小値 max: 最大値 50%: 中央値（median） 25%, 75%: 1/4分位数、3/4分位数 以上のような統計量を，Amazon の Athena (Presto) で表示する方法についてまとめます． Presto で統計量をだす Amazon Athena では，Presto という，オープンソースの分散型SQLエンジンがしようされているため，Athena で分析などを行う際は基本的に Presto のドキュメントをみていくことになります． Presto Documentation データとしては，以下のようなユーザーテーブルを想定します． users user_id name age gender 1 Taro 24 Male 2 Hanako 30 Female 3 Bob 35 Male 4 Alice 15 Female 5 Taro 34 Male count: 要素数 要素数をだすためには，count を使用します．