【Presto】よく使うコマンドのメモ

2018-12-04 2021-11-29

Prestoで利用するコマンドでよく使うものをまとめていきます。

テーブルの項目の属性とコメントを確認する

SHOW COLUMNS FROM table1
　　
>Column,  Type, Extra, Comment
 id        , varchar,      , ID
 Time   , varchar,       , Time
 flg       , Bigint  ,       , flg

2つのテーブルからflg情報を取得し、値が存在したものを設定する。

SELECT
　 ID
　 , COALESCE(t1.flg, t2.flg, 0) AS status  -- # 左の値がnullでなければ、それを設定する
FROM 
  table1 AS t1
FULL OUTER JOIN
  table2 AS t2
ON
  t1.id = t2.id

2文字列の中から複数の値をワイルドカード検索する　

-- 普通のLIKE指定だと文字列が含むもののみだが、この指定だと複数範囲の値も抽出可能
SELECT
  *
FROM
  table1
WHERE
  REGEXP_LIKE(ID, '[2-5]' )  -- # IDに2,3,4,5が含まれるもののみ抽出する

rownumを使って連番をつける方法　

-- 以下ではid毎に連番をつける、その際にid, typeで並び替えてから連番をつける
SELECT 
  ROW_NUMBER() OVER( PARTITION BY id ORDER BY id, type) AS rownum
  , id
  , type
FROM 
  table1

IFを利用して一つの項目から比率を求める　

-- 以下では、kounyu_kbn に１（購入者）が入っている顧客とそれ以外の顧客を判定し、日別で購入率を算出している
select 
  target_date
  , COUNT(IF(kounyu_kbn=1,1,NULL)) AS kounyu_UU
  , COUNT(kounyu_kbn) AS  all_UU
  , COUNT(IF(kounyu_kbn=1,1,NULL)) * 1.0 / COUNT(kounyu_kbn) AS kounyu_rate
from 
  table1
group by 
  target_date