こんにちは、エニグモ 嘉松です。
BUYMAのプロモーションやマーケティングを担当している事業部に所属しており、その中のデータ活用推進室という部署で会社のデータ活用の推進やマーケティング・オートメーションツール（MAツール）を活用した販促支援、CRMなどを担当しています。
さて、SELECT文で得た結果のデータを（そのまま、直接）テーブルに挿入する INSERT SELECT や、SELECT文で得た結果からテーブルを作成して更にデータまで挿入するCREATE TABLE ASは何かと便利な機能（文法、技？）ですが、WITH句（SELECT文による結果を一時的に名前を付けてテーブルのように利用する便利な機能（文法、技？））と併用、一緒に利用しようとした時に、ちょっとした注意点があるので備忘録として記載しておきたいと思います。
例えば、以下のようなにWITH句で複数の（仮想的な）テーブルを定義して、それらを結合（JOIN）して結果を得るようなSQLがあったとします。（サンプルなのでとてもシンプルなSQLにしていますが、通常では WITH句を使う場合はもっと複雑なSQLになることが多いと思います。）
※以下のSQLはBigQueryで検証していますので、他のDBMSでは異なる結果やエラーになる場合があることをご了承ください。
WITH emp AS ( SELECT 7369 AS empno, 'SMITH' AS ename, 20 AS deptno, UNION ALL SELECT 7499 AS empno, 'ALLEN' AS ename, 10 AS deptno, UNION ALL SELECT 7521 AS empno, 'WARD' AS ename, 30 AS deptno, ), dept AS ( SELECT 10 AS deptno, 'DEVELOPMENT' AS dname, 'MAYNARD' AS loc UNION ALL SELECT 20 AS deptno, 'SALES' AS dname, 'HOUSTON' AS loc UNION ALL SELECT 30 AS deptno, 'RESEARCH' AS dname, 'PALO ALTO' AS loc ) SELECT e.empno, e.ename, e.deptno, d.dname, d.loc FROM emp e JOIN dept d ON e.deptno = d.deptno ;
このSQLの結果は以下のようになります。
| empno | ename | deptno | dname | loc |
|---|---|---|---|---|
| 7369 | SMITH | 20 | SALES | HOUSTON |
| 7499 | ALLEN | 10 | DEVELOPMENT | MAYNARD |
| 7521 | WARD | 30 | RESEARCH | PALO ALTO |
このSQLの結果をINSERT SELECTでテーブルに挿入しようとした時に、うっかり以下のようなSQLを書くとエラーになります。
WITH emp AS ( SELECT 7369 AS empno, 'SMITH' AS ename, 20 AS deptno, UNION ALL SELECT 7499 AS empno, 'ALLEN' AS ename, 10 AS deptno, UNION ALL SELECT 7521 AS empno, 'WARD' AS ename, 30 AS deptno, ), dept AS ( SELECT 10 AS deptno, 'DEVELOPMENT' AS dname, 'MAYNARD' AS loc UNION ALL SELECT 20 AS deptno, 'SALES' AS dname, 'HOUSTON' AS loc UNION ALL SELECT 30 AS deptno, 'RESEARCH' AS dname, 'PALO ALTO' AS loc ) INSERT dataset.emp_dept -- 最終的な結果を得るSELECTの直前に記載 SELECT e.empno, e.ename, e.deptno, d.dname, d.loc FROM emp e JOIN dept d ON e.deptno = d.deptno ;
BigQueryでは以下のようなメッセージが表示されます。
Syntax error: Unexpected keyword INSERT at [39:1]
最終的に結果を得るSELECT文の前に INSERTを記載するという、ごく自然な、直感的な、あたかも正しそうな方法ですが、エラーとなります。
正しくは、以下のようにWITH句の前にINSERTを記載する必要があります。
INSERT dataset.emp_dept -- WITH句の前にを記載する必要がある WITH emp AS ( SELECT 7369 AS empno, 'SMITH' AS ename, 20 AS deptno, UNION ALL SELECT 7499 AS empno, 'ALLEN' AS ename, 10 AS deptno, UNION ALL SELECT 7521 AS empno, 'WARD' AS ename, 30 AS deptno, ), dept AS ( SELECT 10 AS deptno, 'DEVELOPMENT' AS dname, 'MAYNARD' AS loc UNION ALL SELECT 20 AS deptno, 'SALES' AS dname, 'HOUSTON' AS loc UNION ALL SELECT 30 AS deptno, 'RESEARCH' AS dname, 'PALO ALTO' AS loc ) SELECT e.empno, e.ename, e.deptno, d.dname, d.loc FROM emp e JOIN dept d ON e.deptno = d.deptno ;
同じようにCREATE TABLE ASにおいても、WITH句の前にCREATE TABLE ASを指定する必要があります。
CREATE TABLE dataset.emp_dept AS -- WITH句の前にを記載する必要がある WITH emp AS ( SELECT 7369 AS empno, 'SMITH' AS ename, 20 AS deptno, UNION ALL SELECT 7499 AS empno, 'ALLEN' AS ename, 10 AS deptno, UNION ALL SELECT 7521 AS empno, 'WARD' AS ename, 30 AS deptno, ), dept AS ( SELECT 10 AS deptno, 'DEVELOPMENT' AS dname, 'MAYNARD' AS loc UNION ALL SELECT 20 AS deptno, 'SALES' AS dname, 'HOUSTON' AS loc UNION ALL SELECT 30 AS deptno, 'RESEARCH' AS dname, 'PALO ALTO' AS loc ) SELECT e.empno, e.ename, e.deptno, d.dname, d.loc FROM emp e JOIN dept d ON e.deptno = d.deptno ;
考え方としては、
- WITH句はあくまでもSELECT文の一部である。（WITH句も含めてSELECT文）
INSERT SELECT、CREATE TABLE ASの後にはSELECT文を記載する必要があるので、そのSQLの一部であるWITH句も同じくINSERT SELECT、CREATE TABLE ASの後に記載する必要がある。
とうことでしょうか。
以上です。
株式会社エニグモ すべての求人一覧
