【BQとSnowflakeのSQL】で格闘した話

# 概要
BigQueryとSnowflakeのSQLとで書き方や関数の仕様の違いによりSQLと格闘したので備忘録として。


# UNNESTとFLATTEN（配列の展開方法）

**BQの場合**
``` SQL
SELECT
	user_id
	,unnest_column
FROM
	table
,UNNEST(nested_column) AS unnest_column

```

**Snowflakeの場合**
TABLE関数とFLATTEN関数を組み合わせて、名前を付けておき、「名前.value」で配列を展開することができる
``` SQL
SELECT
	user_id
	,tn.value AS unnest_column
FROM
	table
TABLE(FLATTEN(table.nested_column)) tn

```

# 関数定義（UDF）

**BQの場合**
``` SQL
create temporary function FUNC() as ('処理の中身');

```

**Snowflakeの場合**
型を定義し、「$$」で囲む必要がある

```SQL
create or replace function FUNC() RETURNS varchar as $$ ('処理の中身') $$;

```

# 時刻の差分計算の違い

BQとSnowflakeの時刻の差分掲載は動作が異なるため要注意。個人的にSnowflake側の動作がクセあるなと思いました。

「DAY」単位での差分を計算することを例にしてBQ、Snowflakeの動作をそれぞれ示します。

### BQ

**BQの場合（23時間59分59秒のズレがある時）**

```SQL
SELECT
  DATE_DIFF('2023-05-09 T 23:39:19'
          , '2023-05-08 T 23:39:20'
          , DAY) AS time_diffence

```
アウトプットは、「time_diffence --> 0」

**BQの場合（24時間0分0秒のズレがある時）**

```SQL
SELECT
  DATE_DIFF('2023-05-09 T 23:39:20'
          , '2023-05-08 T 23:39:20'
          , DAY) AS time_diffence

```
アウトプットは、「time_diffence --> 1」

**BQの場合（24時間0分1秒のズレがある時）**

```SQL
SELECT
  DATE_DIFF('2023-05-09 T 23:39:21'
          , '2023-05-08 T 23:39:20'
          , DAY) AS time_diffence

```
アウトプットは、「time_diffence --> 1」


### Snowflake

**Snowflakeの場合（23時間59分59秒のズレがある時）**

```SQL
SELECT
  DATEDIFF(DAY
  		  ,'2023-05-08 T 23:39:20'
          , '2023-05-09 T 23:39:19'
          ) AS time_diffence

```
アウトプットは、「time_diffence --> 1」

1日経っていないが、time_differenceが1になってしまう。これがややこしい。


**Snowflakeの場合（24時間0分0秒のズレがある時）**

```SQL
SELECT
  DATEDIFF(DAY
  		  ,'2023-05-08 T 23:39:20'
          , '2023-05-09 T 23:39:20'
          ) AS time_diffence

```
アウトプットは、「time_diffence --> 1」

**Snowflakeの場合（24時間0分1秒のズレがある時）**

```SQL
SELECT
  DATEDIFF(DAY
  		  ,'2023-05-08 T 23:39:20'
          , '2023-05-09 T 23:39:21'
          ) AS time_diffence

```
アウトプットは、「time_diffence --> 1」


**SnowflakeのDATEDIFF関数では、指定している単位（今回は「DAY」）の数値のズレを計算するため、BQとは異なる結果が出力される。**

そのため、例えば、5月8日の23時39分20秒と日付が変わった瞬間の時刻の差分を計算すると、実際には20分程度しかたっていないにもかかわらず、

```SQL
SELECT
  DATEDIFF(DAY
  		  ,'2023-05-08 T 23:39:20'
          , '2023-05-09 T 00:00:00'
          ) AS time_diffence

```
アウトプットは、「time_diffence --> 1」となってしまう。

ちなみに、次の場合はDAYが変わっていないので、time_differenceは0となる。

```SQL
SELECT
  DATEDIFF(DAY
  		  ,'2023-05-08 T 23:39:20'
          , '2023-05-08 T 23:59:59'
          ) AS time_diffence

```
アウトプットは、「time_diffence --> 0」となる。


**BQと同じような出力結果を出す**のであれば、
①時間単位を秒単位(SECOND)に設定して何秒の時間差があるかを計算
②①で出した秒数を24×60×60（=24時間の秒数）で割り算する。
③割り算結果の整数部分（例えば2.3であれば、”2”）をtime_differenceに保持する
という処理をすることでBQと同じ結果を出すことができる。

ので、例えば、23時間59分59秒のズレがある時
```SQL
SELECT
TRUNCATE (
  DIV0NULL (
      DATEDIFF( SECOND
              , '2023-05-08 T 23:39:20'
              , '2023-05-09 T 23:39:19'
              ), 24*60*60
            ), 0
         ) AS time_diffence

```
こうすれば、アウトプットは「time_diffence --> 0」となり、BQと同じ結果を得ることができる。

また、日付が変わった瞬間の時刻の差分でも
```SQL
SELECT
TRUNCATE (
  DIV0NULL (
      DATEDIFF( SECOND
              , '2023-05-08 T 23:39:20'
          		'2023-05-09 T 00:00:00'
              ), 24*60*60
            ), 0
         ) AS time_diffence

```
こうすれば、アウトプットは「time_diffence --> 0」となり、BQと同じ結果を得ることができる。


# BOOLEANの違い
WHERE句でBOOLEANのカラムで絞り込みをする場合、BQでは「IS TRUE」と書けるがSnowflakeでは「= TRUE」にしないといけない。

**BQ**
```SQL
SELECT COUNT(*)
FROM table
WHERE column1 IS TRUE AND column2 IS NULL

```

**Snowflake**
```SQL
SELECT COUNT(*)
FROM table
WHERE column1 = TRUE AND column2 IS NULL

```