AutoNumber関数周辺についての本当に詳しい解説1（カウンタ編）

2017年2月16日 · by KE · in 上級者向け

QlikViewやQlik Senseの関数である「AutoNumber関数」について、深く掘り下げてまとめました。
実用的なテクニックなども一部載せていますが、それよりも技術的な理解を深め、いままで何となく分かった気になっていたものを、本当の意味で理解することに主眼を置いています。
QlikViewやQlik Senseの上級者の方に、ぜひご一読いただきたい内容です。

QlikViewやQlik Senseには、連番やキーを作成するために、AutoNumber関数をはじめさまざまな関数が用意されています。
具体的には以下の関数です。
RowNo関数、RecNo関数、AutoNumber関数、AutoNumberHash128関数、AutoNumberHash256関数、Hash128関数、Hash160関数、Hash256関数

これらの関数は結果が分かりやすいため、裏側の動作を知らなくとも使い方に困ることはあまりありません。
しかし、たとえばRowNo関数とRecNo関数の違いは何なのか、AutoNumber関数はどんな基準で連番を振っているのかなど、疑問を持ちながら使っている方も多いのではないでしょうか。
この記事では、みなさまが普段何となく気になっているであろう疑問に、できるかぎり詳しくお答えします。

なお、記事が長くなってしまったため、この記事は「カウンタ編」としてRowNo関数、RecNo関数、AutoNumber関数の3つの関数についてまとめます。
残りの関数については、「ハッシュ編」として次の記事にまとめますので、ご期待ください。

この記事は、AutoNumber関数の概要や、データの関連付け、またQVDファイルについて理解されている方を対象に書かれています。
もし基礎から学習したい方は、先に以下の記事をご覧いただくことをおすすめいたします。
データの関連付け
 大量データを超高速に読み込むQVDファイルの使い方
 QlikViewの高速性を支えるアーキテクチャ（シンボルテーブルとビットスタッフト・ポインター）

ではまず、RowNo関数とRecNo関数について見ていきましょう。

RowNo関数、RecNo関数

RowNo関数とRecNo関数はどちらも連番を作成する関数ですが、RowNo関数が結果のテーブルの行数であるのに対し、RecNo関数は読み込みもとのテーブルの行数という違いがあります。
言い換えると、RowNo関数は出力時の行数、RecNo関数は入力時の行数ということです。
実際に結果に違いが出るのは、Where句で条件を掛けている場合や、複数のテーブルを縦に連結（Concatenate)している場合です。

RowNo関数	RecNo関数
結果のテーブルの行数	読み込みもとのテーブルの行数
Where句によってデータが絞り込まれた後の行数	Where句によってデータが絞り込まれる前の行数
複数のテーブルを連結した後の行数	複数のテーブルを連結する前の行数

Where句によるデータの除外

複数のテーブルの連結

以上でRowNo関数、RecNo関数の説明は終了です。単純に行数を出すものですが、出力時と入力時どちらの行数なのかに注意しましょう。

RowNo関数とRecNo関数は、それほど難しくなかったと思います。
では、AutoNumber関数について深く掘り下げていきましょう。

AutoNumber関数とはなにか？

AutoNumber関数は、ある値をもとに一意の連番を作成する関数です。
RowNo関数とRecNo関数が単純にテーブルの行数をもとめるのに対し、AutoNumber関数はある項目の値をもとに一意の連番を作成します。
一意の連番ですので、複数のテーブルを関連付けるキーとして使用できます。

項目の値が「A」の場合は常に「1」が、「B」の場合は常に「2」が振られていますね。
このように、おなじ値にはおなじ番号が振られていきます。

実際の用途としては、桁数の多いコードや複合キー（複数の項目がキーになっている）を、単純なキーに置き換えてデータのサイズを小さくするというのが、代表的な使用例です。
※実際は複合キーの処理には、AutoNumberHash128(256)関数を使うことも多いのですが、AutoNumberHash128(256)関数については別の記事にまとめます。

さらに、AutoNumber関数で作成された連番はサイズが小さくなるだけでなく、サイズがゼロになるという特長があります。
この点に関しては、以下の記事にまとめていますのでぜひ見てみてください。
QlikViewの高速性を支えるアーキテクチャ（シンボルテーブルとビットスタッフト・ポインター）

以上がAutoNumbre関数の概要です。ではここからは、AutoNumber関数を使用する際の注意事項やTipsについて見ていきましょう。

注意事項1 複数同時に使用するときは識別子を追加する

では、AutoNumber関数がどのような基準で連番を振っているのか確認してみましょう。
まずはヘルプで、AutoNumber関数のページを確認してみてください。
autonumber – スクリプト関数窶鈀 Qlik Sense
「!」で書かれている注意事項に、以下のようにあります。
以下はヘルプからの引用です。

autonumberキーは、テーブルが読み込まれた順番で生成されるため

AutoNumber関数は単純にデータを読み込んだ順に、連番を振っているということです。

ここで注意しなければならないのは、AutoNumber関数を複数同時に使用している場合です。
例を見てみましょう。

LOAD 項目1,
     項目2,
     AutoNumber(項目1) as AutoNumber1,
     AutoNumber(項目2) as AutoNumber2
:

結果は下図のようになります。

項目1と項目2、交互に連番が振られていますね。
このように、AutoNumber関数は単純にデータを読み込んだ順に連番を振っていきます。

もちろん、一意の連番となるように、すでに読み込み済みの値にはおなじ番号が振られます。
上図の例で、たとえば項目2の4行目の値が「D」だった場合（項目1とおなじ値だった場合）、結果は下図のようになります。

キーとして使いたいだけであれば、このままでも問題ないのかもしれませんが、それぞれの項目で連番になっていた方が分かりやすいでしょう。
複数のキーをそれぞれ連番にしたい場合は、AutoNumber関数に追加の指定が必要です。

では、あらためて上記のヘルプで、AutoNumber関数の構文を確認してみてください。

autonumber(expression[ , AutoID])

数式（expression）のうしろに「AutoID」とあります。
※カギ括弧（[ ]）の表記は、「AutoID」の指定が省略可能であることをあらわしています。

このAutoIDが、AutoNumber関数を複数使用する場合に、それぞれを区別する識別子になります。
先ほどの例で、2つのキーをそれぞれ連番にしたい場合は、以下のようにAutoIDを追加します。

LOAD 項目1,
     項目2,
     AutoNumber(項目1, 1) as AutoNumber1,
     AutoNumber(項目2, 2) as AutoNumber2
:

AutoIDには文字列も指定できるので、もとの項目名などを指定して、識別子を分かりやすくしておいた方がいいでしょう。

LOAD 項目1,
     項目2,
     AutoNumber(項目1, '項目1') as AutoNumber1,
     AutoNumber(項目2, '項目2') as AutoNumber2
:

2つのキーがそれぞれ連番になりました。
このように、AutoNumber関数を1つのロードスクリプト中で複数使用するときは、識別子（AutoID）を追加してください。

ここまでで確認したAutoNumber関数の動作を、図にあらわすと下図のようになります。

識別子（AutoID）ごとに、カウンタを管理する領域をもうけ（ヘルプではこれをカウンタインスタンスと呼んでいます）、値を読み込んだ順に追加していきます。このとき値が無ければ追加し、すでに値があれば新たに追加せずにカウンタを取得します。
厳密にこういった仕様であるとは言い切れませんが、動作のイメージとしては間違っていないでしょう。

キーの作成以外での利用用途

このようにAutoIDは、連番を区別するための識別子として使用します。
これは言い換えると、連番にする範囲を指定できるということです。または、連番をリセットするタイミングを制御できるとも言えます。
これを利用することで、RowNo関数やRecNo関数では難しい、項目の値ごとに連番を振る処理ができます。

たとえば、売上明細のテーブルがあったとします。顧客ごと日付ごとに並べ替えた上で、顧客ごとに連番を振っていくことで累積の購入回数（リピート回数）がもとめられます。

LOAD 顧客番号,
     日付,
     売上金額,
     AutoNumber(RowNo(), 顧客番号) as リピート回数; //顧客番号ごとに連番を作成する
SQL SELECT *
FROM `売上明細`
Order By 顧客番号, 日付; //Order By句で、顧客番号ごと日付ごとに並べ替える

以上でAutoIDの説明は終了です。

前述のとおり、AutoNumber関数はデータを読み込んだ順に連番を振っていきます。
これは、もう一つ重要な注意事項があることを示唆しています。それではもう一つの注意事項を見ていきましょう。

注意事項2 おなじロードスクリプトから実行した場合のみ、キーとして使用できる

先ほど確認したヘルプの注意事項を、もう一度確認してみましょう。
autonumber – スクリプト関数窶鈀 Qlik Sense
「!」の注意事項を再確認してください。以下はヘルプからの引用です。

autonumberキーは、テーブルが読み込まれた順番で生成されるため、同じデータロードで生成された場合のみ結合できます。ソースデータのソート処理から独立してデータロード間で恒久的に維持されるキーを使用する必要がある場合は、hash128関数、hash160 関数、hash256 関数を使用する必要があります。

しつこいようですが、AutoNumber関数はデータを読み込んだ順に連番を振っていきます。
そのため、別のロードスクリプトで作成されたキーでは関連付けができません。
これは言い換えると、別のアプリ（QlikViewのqvw、Qlik Senseのqvf）で作成したキーでは、関連付けできないということです。

実際にどんなときに起こり得るかと言うと、QVDファイルを使用して差分更新をする場合が代表例として挙げられます。
下図をご覧ください。
この例では、変更のないデータをQVDファイルにあらかじめ出力しておくことで、差分更新を効率的におこなおうとしています。
このとき、QVDファイル出力時にAutoNumber関数でキーを作成すると、あとから作成したキーと整合性が取れなくなってしまいます。

そのため、AutoNumber関数によるキーの作成は、テーブルの結合が必要になったときに一括で（おなじロードスクリプト内で）おこなうようにしてください。

悪い例と良い例、それぞれロードスクリプトの例を掲載しておきます。
以下は悪い例です。

//QVD出力用ロードスクリプト

売上明細:
LOAD 販売月,
     商品名,
     販売金額,
     AutoNumber(商品名) as 商品キー //キーを作成する ×
SQL SELECT ...
;

商品マスター:
LOAD 商品名,
     AutoNumber(商品名) as 商品キー //キーを作成する ×
SQL SELECT ...
;

//QVDファイルに出力する
STORE 売上明細 into 売上明細.qvd;
STORE 商品マスター into 商品マスター.qvd;

//QVD読み込み用ロードスクリプト

//QVDファイルからデータを取り込む
売上明細:
LOAD *
FROM 売上明細.qvd;

//最新のデータをデータベースから取り込む
LOAD 販売月,
     商品名,
     販売金額,
     AutoNumber(商品名) as 商品キー //キーを作成する
SQL SELECT ...
;

//QVDファイルからデータを取り込む
商品マスター:
LOAD *
FROM 商品マスター.qvd;

以下は良い例です。

//QVD出力用ロードスクリプト

//QVDファイル作成前には、キーを作成しない
売上明細:
LOAD 販売月,
     商品名,
     販売金額
SQL SELECT ...
;

商品マスター:
LOAD 商品名
SQL SELECT ...
;

//QVDファイルに出力する
STORE 売上明細 into 売上明細.qvd;
STORE 商品マスター into 商品マスター.qvd;

//QVD読み込み用ロードスクリプト

//QVDファイルからデータを取り込む
売上明細:
LOAD *,
     AutoNumber(商品名) as 商品キー //キーを作成する ○
FROM 売上明細.qvd;

//最新のデータをデータベースから取り込む
LOAD 販売月,
     商品名,
     販売金額,
     AutoNumber(商品名) as 商品キー //キーを作成する
SQL SELECT ...
;

//QVDファイルからデータを取り込む
商品マスター:
LOAD *,
     AutoNumber(商品名) as 商品キー //キーを作成する ○
FROM 商品マスター.qvd;

このようにキーの作成は、一つのロードスクリプト中でおこなう必要があります。

AutoNumber関数とQVDファイルの超高速モードの併用

ここからは、さらに上級者向けの内容です。
このまでの説明を読んで、QVDファイルの超高速モード（QVD Optimizedモード）はどうなるのかと思った方は、非常に鋭いご指摘です。
QVDファイルからもっとも高速にデータを取り込むには、項目の追加などはせずに、もとの構造のままデータを取り込む必要があります。（これを超高速モードと呼びます。）

キーの作成をQVDファイル読み込み時におこなえば、当然超高速モードは無効になります。
これに対する万能な解決策は、わたしの知る限りありません。
ですが、一つの方法としてQVDファイルを超高速モードで読み込んだあと、キーの作成のみをあとからおこなう方法が考えられます。

//QVD出力用ロードスクリプト

売上明細:
LOAD 販売月,
     商品名,
     販売金額
SQL SELECT ...
;

商品マスター:
LOAD 商品名
SQL SELECT ...
;

//QVDファイルに出力する
STORE 売上明細 into 売上明細.qvd;
STORE 商品マスター into 商品マスター.qvd;

//QVD読み込み用ロードスクリプト

//キーは作成せずに、QVDファイルからデータを取り込む
売上明細:
LOAD *
FROM 売上明細.qvd;

//最新のデータをデータベースから取り込む。ここでもキーは作成しない
LOAD 販売月,
     商品名,
     販売金額
SQL SELECT ...
;

//商品マスターなど、マスターのデータはデータ量が少なければ、超高速モードは妥協する
//データ量が多ければ、売上明細とおなじようにキーはあとから作成する
商品マスター:
LOAD *,
     AutoNumber(商品名) as 商品キー
FROM 商品マスター.qvd;

//売上明細を再読み込みしてキーを作成し、もとの売上明細にJOINする
JOIN (売上明細)
LOAD Distinct //Distinctで一意の値を読み込む
     商品名,
     AutoNumber(商品名) as 商品キー //キーを作成する
Resident 売上明細; //Residentで売上明細を再読み込みする

以上で解説は終了です。お疲れ様でした。

最後に…

今回の記事では、AutoNumber関数について詳しく説明いたしましたが、いかがだったでしょうか。
AutoNumber関数がどのように連番を振っているか、キーを作成するときの注意事項についてご理解いただけましたでしょうか。

後半部分はとくに難解だったと思います。最後までお読みいただいた方、ありがとうございました。
実際ここまで深く理解しておく必要はないのかもしれませんが、裏側の動作を理解することでモヤモヤを解消し、ストレスなく開発をしていただけたらと思います。

よく理解できなかったという方も、いつか必要になったときに今回の記事を見直していただければ幸いです。

外部リンク

データの関連付け
https://qlik-training.ashisuto.co.jp/associate-data/

大量データを超高速に読み込むQVDファイルの使い方
https://qlik-training.ashisuto.co.jp/what-is-qvd-file/

QlikViewの高速性を支えるアーキテクチャ（シンボルテーブルとビットスタッフト・ポインター）
https://qlik-training.ashisuto.co.jp/qlikview-architecture-symbol-tables-and-bit-stuffed-pointers/

Autonumber in qvd | Qlik Community
https://community.qlik.com/thread/171322
「AutoNumber関数とQVDファイルの超高速モードの併用」について書く際、参考にした記事です。（英語）

検証用のロードスクリプトを以下にまとめます。
インラインロードを使用して、データ値をスクリプト中に埋め込んでいるため、ロードスクリプトの編集画面にそのまま貼り付けてご利用いただけます。

検証用ロードスクリプト

RowNo関数、RecNo関数の検証用ロードスクリプト

RowNo関数とRecNo関数の違いについて、確認するためのロードスクリプトです。

/Where句によるデータの除外
WhereSource:
LOAD * INLINE [
    項目1
    A
    B
    C
    D
    E
];

WhereResult:
LOAD 項目1,
     RowNo() as RowNo_Where,
     RecNo() as RecNo_Where
Resident WhereSource
Where 項目1 <> 'C';

DROP Table WhereSource;

//テーブルを縦に連結
ConcatenateSource1:
LOAD * INLINE [
    項目2
    A
    B
    C
];

NoConcatenate

ConcatenateSource2:
LOAD * INLINE [
    項目2
    D
    E
];

ConcatenateResult:
LOAD 項目2,
     RowNo() as RowNo_Concatenate,
     RecNo() as RecNo_Concatenate
Resident ConcatenateSource1;

LOAD 項目2,
     RowNo() as RowNo_Concatenate,
     RecNo() as RecNo_Concatenate
Resident ConcatenateSource2;

DROP Tables ConcatenateSource1, ConcatenateSource2;

AutoNumber関数の識別子（AutoID）検証用ロードスクリプト

AutoNumber関数の識別子（AutoID）の動作を、確認するためのロードスクリプトです。
識別子（AutoID）の指定なし

Table1:
LOAD *,
    AutoNumber(項目1) as AutoNumber1,
    AutoNumber(項目2) as AutoNumber2;
LOAD * INLINE [
    項目1, 項目2
    A, AA
    B, BB
    C, CC
    D, D
    E, EE
];

識別子（AutoID）の指定あり

Table1:
LOAD *,
    AutoNumber(項目1) as AutoNumber1,
    AutoNumber(項目2) as AutoNumber2;
LOAD * INLINE [
    項目1, 項目2
    A, AA
    B, BB
    C, CC
    D, D
    E, EE
];

AutoNumber関数の検証用ロードスクリプト

AutoNumber関数によるキーの不整合を、確認するためのロードスクリプトです。

1つ目のアプリ（QlikViewのqvw、Qlik Senseのqvf）で以下を実行し、QVDファイルを作成する。

Table1:
LOAD *,
    AutoNumber(項目1) as AutoNumber1;
LOAD * INLINE [
    項目1
    A
    B
    C
    D
    E
];

STORE Table1 into Table1.qvd;

DROP Table Table1;

2つ目のアプリ（QlikViewのqvw、Qlik Senseのqvf）で以下を実行する。
Table1のAutoNnumber1と、Table2のAutoNumber2は、おなじ項目1から連番を作成しているにもかかわらず、おなじ値に異なる番号が振られる。
Table1とTable2でデータの並び順が異なる（Table2の項目1はCから始まっている）ため、このようなことがおこる。

LOAD 項目1, 
     AutoNumber1
FROM
Table1.qvd
(qvd);

Table2:
LOAD *,
    AutoNumber(項目1) as AutoNumber2;
LOAD * INLINE [
    項目1, 項目2
    C, 300
    B, 200
    A, 100
    B, 180
    A, 90
    E, 500
    C, 290
    A, 100
];

AutoNumber関数周辺についての本当に詳しい解説1（カウンタ編）

RowNo関数、RecNo関数

AutoNumber関数とはなにか？

注意事項1 複数同時に使用するときは識別子を追加する

キーの作成以外での利用用途

注意事項2 おなじロードスクリプトから実行した場合のみ、キーとして使用できる

AutoNumber関数とQVDファイルの超高速モードの併用

最後に…

外部リンク

検証用ロードスクリプト

RowNo関数、RecNo関数の検証用ロードスクリプト

AutoNumber関数の識別子（AutoID）検証用ロードスクリプト

AutoNumber関数の検証用ロードスクリプト

関連記事

検索

最近の投稿

カテゴリー

AutoNumber関数周辺についての本当に詳しい解説1（カウンタ編）

RowNo関数、RecNo関数

AutoNumber関数とはなにか？

注意事項1 複数同時に使用するときは識別子を追加する

キーの作成以外での利用用途

注意事項2 おなじロードスクリプトから実行した場合のみ、キーとして使用できる

AutoNumber関数とQVDファイルの超高速モードの併用

最後に…

外部リンク

検証用ロードスクリプト

RowNo関数、RecNo関数の検証用ロードスクリプト

AutoNumber関数の識別子（AutoID）検証用ロードスクリプト

AutoNumber関数の検証用ロードスクリプト

関連記事

検索

タグ

人気の投稿

人気の投稿

最近の投稿

カテゴリー