PRESTOでのSQL作業用メモ

PRESTOでのSQLの使い方について、作業で使ったコードを備忘として残します。
マニアックなものから、普段使いのものまで。並びは適当です。ネタ増えてきたら整理したりします。

細かな説明は一切入れないです。コード忘れた時に、ここからコピーして内容をちょっと変えれば使える。
という感じに特化した感じです。

【形式変換】

json形式のデータから項目を抽出する

  SELECT
      json_info
      -- 以下コードでJSON形式の項目から対象項目を抽出する
      , JSON_EXTRACT_SCALAR(json_info, '$.shohin_id') AS shohin_id
    FROM
      table
  LIMIT 3
;

出力結果例

json_info	shohin_id
{"id":"1","shohin_id":"12345","date_ms":"1583295890000"}	12345
{"id":"2","shohin_id":"67890","date_ms":"1583295890000"}	67890
{"id":"3","shohin_id":"23456","date_ms":"1583295890000"}	23456

【サンプリング】

テーブルから、データをランダムサンプリングする(割合）

-- 30%のデータをランダムで取得する方法
select 
  *
from
  table TABLESAMPLE BERNOULLI(30)

テーブルから、データをランダムサンプリングする(割合＆件数）

-- 30%のデータをランダムで取得して、さらに件数を3件に絞る
select 
  *
from
  table TABLESAMPLE BERNOULLI(30) LIMIT 3

【window関数】

指定した項目ごとに連番を取得する

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

累積和を算出する

SELECT 
  target_date
  , shohin_id
  -- 日別の金額を表示　
  , price
  -- 日別の金額に関して累積和を算出する
  , SUM(price) OVER (PARTITION BY shohin_id ORDER BY target_date ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as price_sum  
FROM
(
  SELECT
    target_date
    , shohin_id
    , price
  FROM
    shohin_table
)
ORDER BY 
  target_date
  , shohin_id

group byを使って抽出したレコード内の別項目を抽出する方法

-- MIN_BYの利用例
SELECT
  商品ID
  , MIN(購入日時) AS '一番最初に購入された日時'
  , MIN_BY(商品名, 購入日時) AS '一番最初に購入された日時の商品名' 
FROM
  商品テーブル
GROUP BY
  商品ID
；

-- MAX_BYの利用例
SELECT
  商品ID
  , MAX(購入日時) AS '一番最後に購入された日時'
  , MAX_BY(商品名, 購入日時)) AS '一番最後に購入された日時の商品名' 
FROM 
  商品テーブル
GROUP BY
  商品ID

この機能はwindow関数らしいのですが、PRESTO独自のようで、初めて知った時にはえらく感動しました。
bigqueryとかにも標準で実装してほしい。

移動平均値を算出する

SELECT
  日付
  , 商品CD
  , 価格
  -- ここで移動平均値を取得する
  , AVG(価格) OVER (PARTITION BY CD ORDER BY 日付 ROWS BETWEEN 7 PRECEDING AND CURRENT ROW) AS avg_price_7days
FROM
  商品テーブル
WHERE
  商品CD = 10001
ORDER BY
  日付
  , 商品CD

【集合関数】

テーブルの差分情報を取得する

-- 差集合の取得  idを基準にして、　table1に存在して、table2に存在しないレコードを取得
SELECT
  id
FROM
  table1

EXCEPT

SELECT
  id
FROM
  table2

【日付操作】

曜日を指定してデータ取得する

SELECT  
  *
FROM
  table1
WHERE
  -- # 毎週日曜日のデータを取得 # 1:月/ 2:火 / 3:水 / 4;木 / 5:金 / 6:土 / 7:日
  DAY_OF_WEEK(DATE(target_date)) = 7  --日曜日のデータ取得

月末や月初など特定日付のデータを取得

SELECT 
  *
FROM 
  table1
WHERE 
  -- # 月初１日のデータのみ取得 # 1:1日 / 2:2日  / 〜 / 30:30日 / 31:31日
  DAY_OF_MONTH(DATE(target_date)) = 1  --　月初1日目のデータ取得 

月末や月初など特定日付のデータを取得(PRESTOのtimeから抽出する場合）

SELECT
  *
FROM
  table1
WHERE
  TD_TIME_FORMAT(time,'dd','JST') = '01'

明示的に日付指定してデータ取得(PRESTOのtimeから抽出する場合）

SELECT
  *
FROM
  table1
WHERE
  -- prestoでは月の指定は MM を大文字にする必要あり。他SQLだと mm でいけたりするので間違えやすいポイント
  TD_TIME_FORMAT(time,'yyyy-MM-dd','JST') = '2020-03-10'

前日までのデータを取得

SELECT
  *
FROM
  table1
WHERE
  target_date <= TD_TIME_FORMAT(TD_TIME_ADD(TD_SCHEDULED_TIME(), '-1d'),'yyyy-MM-dd','JST') 

日付差分情報（経過日数とか）を取得する

-- DATE型かTIMESTAMP型しかDATE_DIFF関数使えないのでCASTでの出方変換
SELECT
 *
FROM
 TABLE1
WHERE
 DATE_DIFF('day', CAST(created_at AS TIMESTAMP), CAST(updated_at AS TIMESTAMP))

日付項目を一度JST変換して抽出する

-- 日付（UTC)を日付(JST)に基準統一してからデータ取得する
SELECT
  *
FROM
  TABLE1
WHERE
  TD_TIME_FORMAT(TD_TIME_PARSE(created_time), 'yyyy-MM-dd', 'JST') = '2020-04-01'

日付項目を週次（月曜日）に変換する

SELECT
  TD_TIME_FORMAT(TD_DATE_TRUNC('week', TD_TIME_PARSE(created_time, 'jst'), 'jst'),'yyyy-MM-dd', 'jst') as contracted_week
FROM
  TABLE1

VARCHARの日付項目を日付型に変換して、日付差分を取得する方法

SELECT
  -- VARCHAR→日付型へ
  DATE_PARSE(start_date,'%Y/%m/%d') AS start_date
  , DATE_PARSE(end_date,'%Y/%m/%d') AS end_Date

  -- 日付型に変換した項目で日付差分を算出
  , DATE_DIFF('month', CAST(DATE_PARSE(start_date,'%Y/%m/%d') AS TIMESTAMP), CAST(DATE_PARSE(end_date,'%Y/%m/%d') AS TIMESTAMP))
FROM
  TABLE1

スプレッドシートなどからデータをDBへ取込んだ場合、日付項目がVARCHARになってる場合があったりしますが、その場合は上記のようにDATE_PARSEで変換可能。

基準日をベースにして日付操作を行う

SELECT 
  target_date
  , CAST(target_date AS timestamp)  -- timestamp型に変換
  , DATE_ADD('hour' , 9 , CAST(target_date AS timestamp)) -- target_dateの9時間後のデータを取得(UTC→JST変換などで利用）
  , DATE_ADD('day' , 3 , CAST(target_date AS timestamp))  -- target_dateの3日後のデータを取得
  , DATE_ADD('month', 1, CAST(target_date AS timestamp)) -- target_dateの1ヶ月後のデータを取得
  , DATE_ADD('year', 1, CAST(target_date AS timestamp)) -- target_dateの1年後のデータを取得
FROM
  TABLE1

日付を年や年月などに変換する

SELECT 
  target_date
  , SUBSTR(target_date, 1, 4) AS y  -- target_dateを年に変換
  , SUBSTR(target_date, 1, 7) AS ym  -- target_dateを年月に変換
  , SUBSTR(target_date, 1, 10) AS ymd  -- target_dateを年月日に変換
FROM
  TABLE1

実行結果

【ステートメント構文】

テーブルの項目や属性情報を参照する

-- テーブルの項目の属性とコメントを確認する
SHOW COLUMNS FROM table1

【関数】

対象項目にnullが入っていた場合、別値を取得する

-- 2つのテーブルからflg情報を取得し、値が存在したものを設定する。
SELECT
  ID
  -- # 左から参照し、値にnull以外が入っていたものを設定する
  , COALESCE(t1.flg, t2.flg, 0) AS status 
FROM 
  table1 AS t1
FULL OUTER JOIN
  table2 AS t2
ON
  t1.id = t2.id

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

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

条件分岐して集計を行う

-- IFを利用して一つの項目から比率を求める
-- 以下では、購入区分 に１（購入者）が入っている顧客とそれ以外の顧客を判定し、日別で購入率を算出している
select 
  日付
  , COUNT(IF(購入区分=1,1,NULL)) AS 購入人数
  , COUNT(購入区分) AS 顧客数
  , COUNT(IF(購入区分=1,1,NULL)) * 1.0 / COUNT(購入区分) AS 購入率
from 
  table1
group by 
  日付

抽出結果を並び替える（order by の省略系)

-- 出力結果の並び替えを行う対象が複雑なコードの時は、 order by 1, 2, 3, 4と数字指定でも同じ結果が得られる
select 
  日付
  , CASE WHEN 購入月 = '2020-01' THEN 1 ELSE 0 END) AS status1
  , CASE WHEN 購入月 = '2020-0２' THEN 1 ELSE 0 END) AS status2
  , CASE WHEN 購入日 = '2020-03' THEN 1 ELSE 0 END) AS status3
  , COALESCE(flg, flg1, flg2, 0) AS flg1
  , COALESCE(flg3, flg4, flg5, 0) AS flg2
  , COALESCE(flg6, flg7, flg8, 0) AS flg3
from 
  table1
order by
  1, 2, 3, 4  -- 項目の並び順は数字に変更しても大丈夫

上記の order by句の部分は、本来は以下のような指定が必要だが、省略可能です。

order by  
  日付
  , CASE WHEN 購入月 = '2020-01' THEN 1 ELSE 0 END)
  , CASE WHEN 購入月 = '2020-0２' THEN 1 ELSE 0 END)
  , CASE WHEN 購入日 = '2020-03' THEN 1 ELSE 0 END)

ただし、一時的なデータ抽出時のみに利用する方が良くて、本番に実装して動かす処理の場合、省略しすぎるとわかりづらくなる場合があるので注意。

項目内の前後に空白があった場合に取り除く

SELECT
  TRIM('  あああ  ')

これで前後のスペースを削る事が可能

VARCHARの数値項目（区切り文字付き）があった場合に除外して合算する方法

select 
  shohin_cd
  , SUM(CAST(replace(sales, ',', '') AS INTEGER)) AS sales
from
  table1
group by
  shohin_cd

salesという項目には、1,000,000という値がVARCHARで保存されていたとすると、
１）replace関数でカンマをスペースに変換 : 1,000,000→1000000
２）CAST関数でVARCHARをINTEGERに変換
３）SUM関数で合計値を算出する
→1),2)を行わないでSUMすると以下のようなエラーが発生します。

Unexpected parameters (varchar) for function sum. Expected: sum(double) , sum(real) , sum(bigint) , sum(interval day to second) , sum(interval year to month) , sum(decimal(p,s)) 

【テーブル操作】

一時的にテーブルを保存しておく

DROP TABLE IF EXISTS
  temp_DB.table_bkup20200814;

CREATE TABLE
  temp_DB.table_bkup20200814 AS
  SELECT * FROM DB.table

データの切り替えなどを行う場合などに、切り替え前のデータをバックアップしておく。
データマートなどを作る仕事をしてる場合、結構使用頻度高めになると思います。

テーブルのカラム名を一覧取得する

SELECT
    column_name
FROM
    information_schema.columns
WHERE
    table_schema = '{スキーム名}'  -- DBスキーム名を入れる
AND
    table_name = '{table名}'  -- テーブル名を入れる

トレジャーデータとかツールを使っている場合SQLの実行結果をそのままスプレッドシートなどにコピペしたい時がありますが、カラム名だけコピーできなかったのですが、その場合このクエリで取得可能になります。縦横変換だけ必要ですが、スプレッドシートはその辺りの操作簡単にできるので問題なし。