SQLについて学ぶ(CTE, Window関数など) - nakaoka3のなんでもブログ

これまでモバイルアプリとwebアプリケーション開発をしてきて、生のSQLを直接書く機会は少なかった。最近はBigQueryやAmazon Athenaなど、分析系のツールの環境が揃ってきて、アプリケーションでDBを操作するのとは別にSQLを書く機会が増えた。そこで最近のSQLについて初歩的な使い方を学ぶことにした。

CTE(Common Table Expression, With句)

CTE(With句)を使うとサブクエリを複数の箇所で使ったり、サブクエリが複雑な時に読みやすく、扱いやすくなる。

下のSQLでは2つのサブクエリにwith句で名前をつけている。通常だとここで GROUP BY とか WHERE をつけて下のテーブルとは違うテーブルを作ることになる。

WITH
foo2 AS (
    SELECT * FROM foo
),
bar2 AS (
    SELECT * FROM bar
)
SELECT *,
FROM foo2 JOIN ON foo2.account_id = bar2.account_id
;

サブクエリをネストして書くより読みやすくて良い。

Window関数(分析関数)

標準 SQL の分析関数のコンセプト | BigQuery | Google Cloud

Window関数(分析関数)を使うことにより、テーブルをさらに小さな window に分割して、その window の中で順番をつけたり(RANK関数など)、合計を積み上げたりできる。一行で説明することが難しいけど、SQLでできることは増える。いかにも分析で使いたそうなことができる。

下のSQLでは月ごとに区切って、その中で作成日で並べている。

SELECT total_sales,
       SUM(total_sales) OVER (
            PARTITION BY date_trunc('month', created_at)
            ORDER BY created_at
       ) AS accumulation_sales_total
FROM orders

標準SQL規格と実装(標準SQL, MySQL, BigQuery, Athena)

標準SQLでは CTEはSQL 99で、Window関数はSQL 2003で追加された。

MySQLでは8.0から CTEとWindow関数を使うことができる。つまり5系では使えない。

BigQueryは2.0からSQL 2011に準拠した標準SQLを使うことができる。

AthenaはPrestoの一部をサポートしていて、Prestoは標準SQLに準拠している。どのバージョンの標準SQLかわからなかったけど、CTEとWindow関数は使えるみたいだ。

感想

（自分にとって）目新しい機能だったのでCTEとWindow関数だけ名前を挙げた。地味なところだと as が省略可能だったり、order by などで指定する列は列番号でいいとか、知らなかった。coalesce, ntile, lag, leadなどの便利な関数も色々と知った。

ちなみに学習のリソースはここで見つけた。 SQL for Data Analysis - Google Digital Garage