みなさん、Snowflakeは使っていますか?
極めて合理的で高い性能を持っているDataWareHouse、Snowflake。
弊社ではメインのDWHとして採用し、その性能に非常に満足しています。
今回はSnowflakeで連番を作るのにサンプルとしてよく使われているSEQ関数について書きます。
SEQ関数の前にまずはGENERATOR
SEQ関数で連番を使うために、まずはGENERATOR関数について説明します。
GENERATOR関数は関数リファレンスでは「行を作成します」と書かれています。まさにこのとおりなのですが、「列を持たない」というキーワードを追加するともっとわかりやすいかも知れません。
たとえば、
SELECT * FROM TABLE(GENERATOR(ROWCOUNT => 12));
という式は、「列のないSELECT」というエラーになります。
列がない、行だけを生成する関数ですので、実際に使用する場合は
SELECT 1 FROM TABLE(GENERATOR(ROWCOUNT => 12));
のように、列を追加して使用する必要があります。なお、上記のSQLの結果は1,1,1,......,1,1,1と、1が12個返ってきます。
SEQ関数の使用
GENERATOR関数で作成した行の結果を連番にするためには、上記のドキュメントにも書かれていますが、SEQ関数を使用することが多いようです。具体的には
SELECT SEQ4()+1 FROM TABLE(GENERATOR(ROWCOUNT => 12));
とすることで、1,2,3....,10,11,12という12個の数字が取れます。なお、SEQ関数にはSEQ1,SEQ2,SEQ4,SEQ8の4種類があり、後ろの数字は表現可能な正数字のバイト数(符号付き整数)となります。
たとえばSEQ1は1バイトの符号付き整数なので、表現可能な数字は-128~127となり
select seq1() FROM TABLE(generator(rowcount => 256));
を実行した場合、0,1,2,3,......,125,126,127,0,1,2,3,......,125,126,127と、0~127の数字が循環します。SEQ4なら0~2147483647となりますので、よほどのことがない限り事足りるでしょう。
では、別のパターンを示しましょう。
ここに、そこそこ大きい、文字列型が7カラム分あるテーブル、SEGMENT_INFOというのがあるとします。
こちらのテーブルの件数は197,575件です。
SELECT COUNT(*) FROM SEGMENT_INFO; --197575
このテーブルにSEQ関数で作った連番をCROSS JOINした一時テーブルを作成してみます。
CREATE TEMP TABLE JOINED_SEQ_TABLE AS WITH SEQ_MASTER AS ( SELECT SEQ4()+1 TESTNUM FROM TABLE(GENERATOR(ROWCOUNT => 12)) ) SELECT SEQ_MASTER.*,SEGMENT_INFO.* FROM SEGMENT_INFO CROSS JOIN SEQ_MASTER ;
さて、この状態で以下のSQLの結果はどうなるでしょうか。
SELECT MIN(TESTNUM),MAX(TESTNUM) FROM JOINED_SEQ_TABLE; -- MIN | MAX -- 16 | 192
1~12ではなく、16~192という謎な数字となりました。
連番ではありません!
このままではどのような性質の値なのかわかりませんので、確認してみます。
SELECT DISTINCT TESTNUM FROM JOINED_SEQ_TABLE ORDER BY TESTNUM;
こちらの結果は、16,32,48,......,160,176,192、N*16(Nは1~12の整数)という数字になっています。
これはいったいどういうことでしょうか。
SEQ関数は連番を保証していません。
SEQ関数の関数リファレンスを見てみましょう。
この関数は、シーケンスを使用して増加する整数の一意のセットを生成しますが、必ずしもギャップのないシーケンスを生成するわけではありません。大量のデータを処理する場合、シーケンスにギャップが生じることがあります。
実はSEQ関数は、返ってくる数字が連番であることは保証されていないのです。
しかも、やっかいなことに、このSEQ関数は、基本的には0からの連番が返ってきます。たとえば、先ほどのCREATE文の中身、
WITH SEQ_MASTER AS ( SELECT SEQ4()+1 TESTNUM FROM TABLE(GENERATOR(ROWCOUNT => 12)) ) SELECT SEQ_MASTER.*,SEGMENT_INFO.* FROM SEGMENT_INFO CROSS JOIN SEQ_MASTER;
はN*16の数字となりますが、
SELECT DISTINCT TESTNUM FROM ( WITH SEQ_MASTER AS ( SELECT SEQ4()+1 TESTNUM FROM TABLE(GENERATOR(ROWCOUNT => 12)) ) SELECT SEQ_MASTER.*,SEGMENT_INFO.* FROM SEGMENT_INFO CROSS JOIN SEQ_MASTER );
と、FROM句の中に入れた途端に1から12の連番に変わります。
そのため、SEQを使用して思わぬ結果となったとき、なぜそうなったのか把握するのが極めて困難となります。
SEQ関数は、0からの連番が返ってくることは保証されておらず、予想されない数字が返ってくる危険性を甘受した上で使用する必要があります。
確実に連番が欲しいときはROW_NUMBER関数を使用しましょう。
確実に連番が欲しい場合は、SEQ関数リファレンスに書かれているとおりROW_NUMBER関数を使用するとよいでしょう。
具体的には
SELECT ROW_NUMBER() OVER (ORDER BY TRUE) FROM TABLE(GENERATOR(ROWCOUNT => 12))
とすることで、確実に1~12の数字を得ることができます。