SQL iwami

【Presto / Trino】sequence() と UNNEST の活用術｜時系列補填・カレンダー生成・行複製をSQLのみで実装する

機械学習・CDP・インフラ・データサイエンスと、データにまつわる技術をマルチに扱うエンジニアのiwamiです。

先日、プロジェクトのPMより以下の要件について相談を受けました。

Presto（Trino）のSQLを用いて、1行ごとに格納されているトランザクションデータに対し、指定したカラムの数値分だけ行を複製し、複製された行ごとに1からの連番カラムを付与したい。

詳しく話を聞くと、ロケーションごとにサマリされた商品売り上げデータをベースに、商品マスタなどの他情報を付与してBI向けのOBT（One Big Table）を作るため、売り上げデータを商品種類ごとに分割したいとのことでした（※実際は10テーブル程度を組み合わせる複雑な内容でしたが、主題から外れるため簡略化しています）。

この要件に対し、以前取り組んだ「BIツール向けに、行データが保持する開始時間・終了時間カラムから1秒ごとのシーケンスを生成して行を展開し、欠損秒を補填する」という時系列データの整形処理が応用できると考えました。

使用したのは、Prestoの組み込み関数である sequence() と UNNEST の組み合わせです。このアプローチにより、今回も簡潔なクエリで要件を満たすことができました。

本記事では、基礎となる「sequence() と UNNEST による時系列補填・カレンダー生成」から、それを応用した「指定数値に基づく行複製と連番付与」の実装パターンまで解説します。

1. 基礎編：時系列データの補填とカレンダー生成

まずは、基盤となる「開始日時〜終了日時から連続した時系列を生成し、行として展開する」手法です。

sequence() 関数を使用すると、数値や日付（DATE）、日時（TIMESTAMP）などの連続したシーケンスを生成できます。日付や日時の場合は、第3引数に INTERVAL を指定して間隔を設定します。

1-1. 開始・終了時間に基づく行展開（時系列の補填）

テーブル内の開始時間（start_time）から終了時間（end_time）までの時間軸を行として展開する場合、以下のクエリとなります。

sql

SELECT
    t.session_id,
    s.sec AS record_time
FROM
    session_logs AS t
CROSS JOIN
    UNNEST(sequence(t.start_time, t.end_time, INTERVAL '1' SECOND)) AS s(sec)

解説：

上記により、指定範囲の秒単位の時間軸データが行として展開されます。

展開後のテーブルに対して秒単位のトランザクション情報を結合することで、欠損秒部分を補填した時系列データを作成できます。

データが存在しない時間帯も含めて連続した時間軸を用意できるため、BIツールで折れ線グラフや時系列チャートを描画する際に役立ちます。

1-2. SQLのみによるカレンダーマスタの動的生成

この仕組みを応用すると、外部マスタテーブルに依存せず、SQLのみで属性付きのカレンダーデータを生成できます。

sql

SELECT
    dt AS calendar_date,
    year(dt) AS year,
    month(dt) AS month,
    day(dt) AS day_of_month,
    ARRAY['月', '火', '水', '木', '金', '土', '日'][day_of_week(dt)] AS day_name,
    date_format(dt, '%Y-%m') AS year_month
FROM
    UNNEST(
        sequence(
            DATE '2026-01-01',
            DATE '2026-12-31',
            INTERVAL '1' DAY
        )
    ) AS t(dt)
ORDER BY
    calendar_date

このクエリでは、2026年1月1日から12月31日までの日付を1日ごとに生成し、年・月・日・曜日・年月の属性を付与しています。

このように、sequence() で日付の配列を生成し、UNNEST で行に展開することで、カレンダーマスタをSQLのみで動的に作成できます。

2. 応用編：指定数値に基づく行複製と連番付与

時系列生成のロジックを応用し、冒頭の「指定した数値の数だけ行を複製し、1からの連番を割り当てる」処理を構築します。

日時の代わりに「1から指定カラムの数値まで」の数値配列を動的に生成します。

2-1. 基本クエリ

sql

SELECT
    t.id,
    t.num,
    u.seq
FROM
    tgt_table AS t
CROSS JOIN
    UNNEST(sequence(1, t.num)) AS u(seq)

sequence(1, t.num) で1から t.num までの数値配列を生成し、UNNEST で行に展開します。

これにより、元データの各行を指定数だけ複製し、複製された行に1からの連番を付与できます。

2-2. 実行結果イメージ

入力データ（tgt_table）

id

num

A

3

B

2

出力結果

id

num

seq

A

3

1

A

3

2

A

3

3

B

2

1

B

2

2

sequence(1, num) で生成された配列（例：[1, 2, 3]）を UNNEST で展開することで、行の複製と連番（seq）の採番が同時に完了します。

2-3. 複製数が0や不正値である場合のハンドリング

複製数が0、負数、またはNULLの場合に、複製対象から除外したいことがあります。

その場合は、CASE 式で複製数を判定し、1以上の場合のみシーケンスを生成するようにします。

sql

SELECT
    t.id,
    t.num,
    u.seq
FROM
    tgt_table AS t
CROSS JOIN
    UNNEST(
        CASE
            WHEN t.num >= 1 THEN sequence(1, t.num)
            ELSE CAST(ARRAY[] AS ARRAY(INTEGER))
        END
    ) AS u(seq)

この例では、複製数が1以上の場合に連番を生成し、それ以外の場合は空配列を返します。CROSS JOIN UNNEST を使用することで、空配列に該当する行は出力結果から除外されます。

※この例では、複製数をINTEGER型として想定しています。使用するカラムの型に応じて、配列の型を調整してください。

おわりに

sequence() と UNNEST を組み合わせた行展開の手法は、欠損データの補填やカレンダーマスタの生成にとどまらず、指定数での行複製やテストデータの作成など、データ前処理のさまざまな場面で応用できます。

プログラミング言語によるループ処理や外部テーブルの準備を行わずとも、SQLのみで簡潔に実装できる点が、この手法のメリットです。

時系列データの整形やBI向けデータマートの構築など、データ加工の選択肢のひとつとして、ぜひ活用してみてください。

ブログ一覧に戻る