Snowflake マイナークエリ構文集

# はじめに
この記事は[Snowflake Advent Calendar 2022](https://qiita.com/advent-calendar/2022/snowflake)の20日目の記事です。
18日目の[rnikiさんの記事](https://zenn.dev/rniki/articles/66715d6c9f0d90)を読んで、普段あまり使っていないSnowflakeのクエリ構文が気になったので調べてみました。

# 引用元
Snowflakeの公式ドキュメントを参考にしています。

https://docs.snowflake.com/ja/sql-reference/constructs.html

# CHANGES

https://docs.snowflake.com/ja/sql-reference/constructs/changes.html

rnikiさんの記事をご参照ください。

https://zenn.dev/rniki/articles/66715d6c9f0d90

# CONNECT BY

https://docs.snowflake.com/ja/sql-reference/constructs/connect-by.html

下記のような階層化されたテーブルに対して、自己結合を行います。

```mermaid
erDiagram
    employees  ||--|{ employees  : manage 
    employees  {
        varchar title
        varchar employee_id
        varchar manager_id
    }
```

## クエリ例
以下のテーブルからmanagerを`President`から辿ります。
![employnee.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/326612/ae370904-3f20-73b8-fa43-845080b2672e.png)

```SQL
select 
    sys_connect_by_path(title, ' -> '),
    connect_by_root title as root_title,
    level,
    employee_id,
    manager_id,
    title
from employees
    start with title = 'President'
    connect by
      manager_id = prior employee_id
order by employee_id;
```

![connect.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/326612/2153b452-649a-84b2-ce79-1429739d2752.png)
再帰CTEと似ていますが、`sys_connect_by_path`, `connect_by_root`, `level`などの疑似列が使えます。
[OBJECT_DEPENDENCIES](https://docs.snowflake.com/ja/sql-reference/account-usage/object_dependencies.html)などで、ビューのリネージを見る際などに使えそうですね。

# LATERAL

https://docs.snowflake.com/ja/sql-reference/constructs/join-lateral.html

こちらはindigoさんが解説記事を出されています。

https://zenn.dev/indigo13love/articles/450d4d58654b43

# MATCH_RECOGNIZE

https://docs.snowflake.com/ja/sql-reference/constructs/match_recognize.html

演算子を自分で定義して、それにあてはまるパターンにマッチングする行を取得します。パターンとしては以下のようなものがあります。

- 前行から増加/減少している
- ウィンドウ内の平均より大きい/小さい

## クエリ例
ドキュメントの例そのままですが、株価がv字になっている時期を集計します。

```SQL
select * from stock_price_history
  match_recognize(
    partition by company
    order by price_date
    measures
      match_number() as match_number,
      first(price_date) as start_date,
      last(price_date) as end_date,
      count(*) as rows_in_sequence,
      count(row_with_price_decrease.*) as num_decreases,
      count(row_with_price_increase.*) as num_increases
    one row per match
    after match skip to last row_with_price_increase
    pattern(row_before_decrease row_with_price_decrease+ row_with_price_increase+)
    define
      row_with_price_decrease as price < lag(price),
      row_with_price_increase as price > lag(price)
  )
order by company, match_number;
```
`pattern`句で増減のパターン（減少2回以上->増加1回以上）、defineで増加、減少の演算子を指定している感じですね。
with句で地道にクエリするのとあまり変わらない気もしますが、、、パターンが柔軟に指定できるのはよさそうです。

# SAMPLE

https://docs.snowflake.com/ja/sql-reference/constructs/sample.html

テーブルからランダムにサンプリングします。

## クエリ例
test_tableから10%サンプリングする
```
select * from test_table sample (10);
```

`sample system (10)`で行をいくつかのブロックに分割してサンプリングするみたいです。
連番で何回か試してみましたが、特に通常と差があるようには見えませんでした。
開発環境のデータ生成などによさそうです。

# GROUP BY ...
以下のようなテーブルを例に考えます。

![group.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/326612/ad7e6519-debf-faf9-bd11-9b5ff0359710.png)


## GROUP BY GROUPING SETS

https://docs.snowflake.com/ja/sql-reference/constructs/group-by-grouping-sets.html

`group by a union all group by b`と等価です

### クエリ例
```SQL
select
    state,
    city,
    sum(quantity)
from
    sales
group by grouping sets (state, city);
```

![group_by_sets.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/326612/66d39f58-13a4-1047-4312-295d96b31383.png)

count distinctなどでそれぞれの列で足し上げが不可能な場合で集計をしたい場合に便利ですね。

## GROUP BY ROLLUP

https://docs.snowflake.com/ja/sql-reference/constructs/group-by-rollup.html#

通常のgroup byに加えて集計列で合計した行を生成します。

### クエリ例
```SQL
select
    coalesce(state, 'all') as state,
    coalesce(city, 'all') as city,
    sum(quantity)
from
    sales
group by rollup (state, city);
```

![group_rollup.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/326612/458cac9a-651d-0976-ae84-f0655c6a66ea.png)

小計行はNULLで表示されます（今回は`all`で埋めました）。通常のNULLとは意味合いが異なるので、事前のNULL埋めがマストになります。
rollupは右から行われるため`group by rollup (大項目, 中項目, 小項目)`のように並べる必要があります。

### NULLがある場合の挙動
適当にNULLが入った列を追加します。
![null.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/326612/3fbdb1f4-0c4c-640c-0bfe-756080e688ee.png)
もう一度同じクエリを実行します。
![duplicate.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/326612/387e1eef-1393-14b8-5657-2cb8e322ec9e.png)
10行目と11行目に重複が発生していますね。こういった場合は事前にcteでNULL埋めの必要がありそうです。

## GROUP BY CUBE

https://docs.snowflake.com/ja/sql-reference/constructs/group-by-cube.html

group by rollupと似ていますが、クロス集計を行った行を追加します。

### クエリ例
```SQL
select
    coalesce(state, 'all') as state,
    coalesce(city, 'all') as city,
    sum(quantity)
from
    sales
group by cube (state, city);
```

![cube.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/326612/3b25075b-cccb-d1c6-fdd3-be7b61ef6bfb.png)
group by rollupでは小計列は`STATE`列での集計と全体のみでしたが、こちらは`CITY`列での集計も行われています。こちらは(STATE, CITY)のように包含関係のある列では使わずに、それぞれが独立した属性を持った列どうしで集計する際に有用ですね。

# QUALIFY

https://docs.snowflake.com/ja/sql-reference/constructs/qualify.html

ウィンドウ関数でのフィルタを行います。

## クエリ例
![family.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/326612/4a3c48a4-c56a-e1a9-afc1-edb938d0dd8a.png)
山田家、田中家から一番年齢の高い人物を取得します。

```SQL
select
    *
from
    family
qualify row_number() over (partition by last_name order by age desc) = 1;
```

![qualify.PNG](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/326612/ce0ca52f-7113-d434-d63b-7ce6188671af.png)
cte内で`row_number()`を書いてからwhere句で絞り込まなくてもよくなるので、少しクエリが短くなりますね。
実行計画は同じだったので、速度は特に変わらないと思います。