SAP SQL Anywhere で RowGenerator を使用する Part 1 （過去のブログより）

このページは、以下の英語ページの抄訳です。最新の情報については、英語ページを参照してください。

https://blogs.sap.com/2014/06/25/using-rowgenerator/

この記事のオリジナルは、Glenn Paulley が sybase.com に 2009 年 9 月に掲載したものです。その中で、Glenn は SQL Anywhere のビルトインの RowGenerator テーブルの使用について解説しています。

不等条件だけを含む Join 条件というのはほとんど存在しません。なぜならば、join の大半が参照整合性の制限をとおして関連するテーブル間の join だからです。

しかしながら、今週いくつかの分析を行ったところ、不等条件でも join が役立つ例に遭遇しました。

私が遭遇した例は、「作成」と「完了」のタイムスタンプが存在するプロジェクトのタスク分析が含まれており、以下と似たようなものです。

CREATE TABLE projects (   project_id INTEGER NOT NULL PRIMARY KEY,
   short_desc VARCHAR(255),
   long_desc LONG VARCHAR,
   project_status VARCHAR(20),
   creation_ts TIMESTAMP NOT NULL,
   completion_ts TIMESTAMP )

私がクエリを実行している実際のスキーマは、これよりもずっと複雑です。しかしこのシンプルな例で、基本的な考え方を理解いただけると思います。

私がやりたかったのは、進行中のプロジェクトの数と、その週に完了したプロジェクトの数を含む、毎週の結果セットを作成することでした。

そこで、SQL Anywhere のビルトインの OLAP 関数をいくつか使用して、週ごとにデータを要素分解してみると、その中間の結果の履歴分析を実行することができました。

関数 DATEDIFF( WEEK, completion_ts, creation_ts ) によって、2 つのタイムスタンプ間の週の違いが得られるので、部分的には直接的ですが、プロジェクトが年を跨ってしまうものもあります。

このような複雑性にも関わらず、ここで私にとってより重要な問題は、プロジェクトが完了しなかったローを毎週生成したかったということです。私は、プロジェクトのテーブルと、追加のローを生成する何かとを join する必要がありました。しかし何と join すればよいのか？

データベースには、まさにこの目的のための RowGenerator テーブルというものが含まれています。これは、単一のカラムのテーブル (row_num) で、1 からスタートする値のローが 255 行含まれています。

私がほしい結果セットを生成するには、以下のようなクエリになります。

SELECT p.project_id, p.short_desc, p.creation_ts, p.completion_ts,
       (IF p.completion_ts IS NULL THEN
           ABS(DATEDIFF( WEEK, NOW(), p.creation_ts ))
       ELSE
           ABS(DATEDIFF( WEEK, p.completion_ts, p.creation_ts ))
       ENDIF ) AS weeks_outstanding,
       (IF p.project_status != 'Complete' OR weeks_outstanding = 0 OR weeks_outstanding > week_number THEN 1 ELSE 0 ENDIF) AS incomplete_projects,
       (IF p.completion_ts IS NOT NULL AND (weeks_outstanding = 0 OR weeks_outstanding = week_number) THEN 1 ELSE 0 ENDIF) AS completed_projects,
       (IF weeks_outstanding = 0 THEN
           DATEPART( YEAR, p.creation_ts )
       ELSE
           DATEPART( YEAR, DATEADD( WEEK, RG.week_number, p.creation_ts) )
       ENDIF) AS calendar_year,
       (IF weeks_outstanding = 0 THEN
           DATEPART( WEEK, p.creation_ts )
       ELSE
           DATEPART( WEEK, DATEADD( WEEK, RG.week_number, p.creation_ts) )
       ENDIF) AS calendar_week
 FROM
     ( SELECT (row_num - 1) AS week_number FROM RowGenerator) AS RG,
     projects p
 WHERE
     weeks_outstanding >= RG.week_number

このクエリは、ビルトインの RowGenerator テーブルを weeks_outstanding 値をベースにしたプロジェクトテーブルに join します。そのため、それぞれの週で、プロジェクトが未完であれば、(weeks_outstanding がゼロの) 同じ週に作成され完了したプロジェクトを含め、output にローが生成されます。

DATEPART 関数を WEEK とともに使用するのは、1 年に 54 週まで可能であることを意味します。なぜならば DATEPART は、週が日曜日からスタートすると定義するからです。

この結果セットが得られれば、これを派生テーブルに埋め込み、例えば、カレンダーウィーク毎に直接的な方法でオープンプロジェクトと完了したプロジェクトの数をまとめることができます。

このクエリの正確性は、一つの条件に依存します。それは、完了までに 255 週を超えるプロジェクトは存在しないということです。

なぜならば、RowGenerator テーブルの最大値は 255 の為、必要な数のローを生成することができないからです。

それが問題になる場合のために SQL Anywhere では、別の row generator のメカニズムを提供しています。

sa_rowgenerator() システムプロシージャーです。

sa_rowgenerator() プロシージャーには、3 種類のパラメーターが必要です。スタートの値、終了の値、そして、step increment (デフォルトは1)です。

sa_rowgenerator() をプロジェクトテーブルに join するのは、RowGenerator ベースのテーブルを使用するのと同じです。なぜならば、SQL Anywhere は、テーブル関数 ( FROM 句で利用できるプロシジャー)　をサポートしているからです。