PRESTOでのSQL作業用メモ

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

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

# 【形式変換】 
### json形式のデータから項目を抽出する

```sql

  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 |


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

```sql

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

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

```sql

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


# 【window関数】
### 指定した項目ごとに連番を取得する

```SQL

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


### 累積和を算出する

```SQL

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を使って抽出したレコード内の別項目を抽出する方法

```SQL

-- 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とかにも標準で実装してほしい。


### 移動平均値を算出する

```SQL

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
```


#  【集合関数】
### テーブルの差分情報を取得する

```SQL

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

EXCEPT

SELECT
  id
FROM
  table2
```

#  【日付操作】
### 曜日を指定してデータ取得する

```SQL

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


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

```SQL

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


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

```SQL

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


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

```SQL

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

### 前日までのデータを取得

```SQL

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

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

```SQL

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

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

```SQL

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

```


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

```SQL

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の日付項目を日付型に変換して、日付差分を取得する方法

```SQL

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**で変換可能。


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

```SQL

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

```

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

```SQL

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
```

実行結果
![スクリーンショット 2021-01-21 13.21.48.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/242038/7a197dcb-e618-c290-98b8-f9eb382e5489.png)





# 【ステートメント構文】
### テーブルの項目や属性情報を参照する

```SQL

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

```


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

```SQL

-- 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
```


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

```SQL

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


### 条件分岐して集計を行う

```SQL

-- 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 の省略系)

```SQL

-- 出力結果の並び替えを行う対象が複雑なコードの時は、 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句の部分は、本来は以下のような指定が必要だが、省略可能です。


```SQL

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)
```

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


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

```SQL

SELECT
  TRIM('  あああ  ')
```

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


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

```SQL

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すると以下のようなエラーが発生します。

```sql

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)) 
```



# 【テーブル操作】 
### 一時的にテーブルを保存しておく

```sql

DROP TABLE IF EXISTS
  temp_DB.table_bkup20200814;

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

```

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

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

```sql

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

``` 

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