← Documentation

SQLの読み方

私たちの結果を検証するのに、SQLを自分で書ける必要はありません ——いくつかの繰り返し出てくるパターンを見分けられれば十分です。 このページでは、実際に公開したレポートの本物のクエリを1本使って、 そのパターンを一通り解説します。読み終えれば、他の検証用スクリプト もブラックボックスとして扱わずに読めるようになるはずです。

何度も出てくるパターン

このシリーズのほぼ全てのクエリは、同じ少数の部品の組み合わせで できています：

• SELECT ... FROM ... WHERE ... ——クエリの基本形。どの列を取得するか（SELECT）、 どの表から取得するか（FROM）、どの行を残すか （WHERE）を指定する。
• WITH 名前 AS (...) ——一時的な結果に名前を付けて、後からその名前で参照できるように するもの。長いクエリを、1つの巨大なネスト構造ではなく、読みやすい 段階に分割できる。「これから使う対象の行」を先に変数として 定義しておく、と考えるとわかりやすい。
• 集計関数——COUNT(*)（行数）、 AVG()／MIN()／MAX() （ある列の平均・最小・最大）、SUM()（合計）。 多数の行を1つの数値にまとめる。
• CASE WHEN ... THEN ... ELSE ... END ——行ごとに行うif/elseの判定。SUM()と組み合わせて、 ある条件を満たす行数を数えるのによく使う （SUM(CASE WHEN x < 0.5 THEN 1 ELSE 0 END)は 「xが0.5未満の行を数える」という意味）。
• UNION ALL ——複数の小さなクエリの結果を、1つの出力表に縦に積み重ねる。 件数・割合・最小値・最大値といった複数の要約統計量を、4つの 別々の出力ではなく、1つの結果の中の整った行として出すために 頻繁に使う。
• .open --readonly some_file.duckdb ——SQL自体の一部ではなく、DuckDBのコマンド。以降のクエリが どのデータベースファイルに対して実行されるかを切り替える。 常に読み取り専用で開いており、検証の実行がチェック対象の データを書き換えてしまうことはない。

実際の例を、一行ずつ読む

これはTechnical Report v1.16のTable 1の元になった検証クエリの Part 1です——復元結果の局所的な分散が、一致する必要はないのに たまたまground truthと厳密に一致しているブロックを見つける クエリです：

WITH candidates AS (
    SELECT sigma_gt
    FROM blocks
    WHERE sigma_gt > 1e-6 AND ssim_c_notta = 1.0
),
total AS (
    SELECT COUNT(*) AS n_total_blocks FROM blocks WHERE sigma_gt > 1e-6
)
SELECT 'n_found' AS metric, CAST(COUNT(*) AS VARCHAR) AS value FROM candidates
UNION ALL SELECT 'pct_of_blocks', CAST(ROUND(100.0*COUNT(*)/(SELECT n_total_blocks FROM total),4) AS VARCHAR) FROM candidates
UNION ALL SELECT 'max_sigma_gt', CAST(MAX(sigma_gt) AS VARCHAR) FROM candidates
UNION ALL SELECT 'min_sigma_gt', CAST(MIN(sigma_gt) AS VARCHAR) FROM candidates;

上から順に読むと：

• WITH candidates AS (...)——「candidates」を、 ground truthの分散がほぼゼロではなく（sigma_gt > 1e-6、浮動小数点ノイズを除外）、かつコントラスト項 が厳密に1に等しい（ssim_c_notta = 1.0）ブロック、 と定義している——これはレポートの別の箇所で証明されている、 復元結果の分散がground truthの分散と厳密に一致していることの シグネチャである。
• total AS (...)——「total」を、単一の数値として 別に定義している：データベース全体で、ground truthの分散が 無視できないほど大きいブロックが何件あるか。後で割合を計算する 際の分母になる。
• 4つのSELECT ... UNION ALL SELECT ...行は、それぞれ ラベル付きの1行を出力する：見つかった候補の件数 （COUNT(*)）、それが全ブロックの何パーセントか （上で定義した「total」で割り、小数点以下4桁に丸める）、 見つかった候補の中でのground truthの分散の最大値・最小値。
• CAST(... AS VARCHAR)は、数値をテキストに変換 しているだけ。件数・割合・分散という全く種類の異なる値を、 DuckDBが型の不一致を怒ることなく1つの出力列にまとめるための 処理。

パターンとしてはこれで全部です。私たちの検証用クエリの多くが 長く見えるのは、この同じ形——対象を絞り込むWITH句に 続けて、いくつかのUNION ALLで積み重ねた要約行——を、 条件やモデルの数だけ繰り返しているだけだからです。

後の報告書に出てくる、もう少し進んだパターン

DOI 1.4以降の報告書では、いくつか追加のパーツが登場します。 ここで一度だけ解説しておき、SQL リファレンスの各報告書の解説からは、毎回説明し直すのではなく このページを参照するだけで済むようにしています。

• ATTACH 'パス' AS 別名 (READ_ONLY) ——追加のデータベースファイルを現在のセッションに開き、短い 別名を付けるコマンドです。これにより、1つのクエリで複数の データベースを同時に参照できます（例：db_std.blocks・ db_swinir.blocks）——.openでいちいち 開くファイルを切り替える代わりです。ここでも常に読み取り専用で 開きます。
• COUNT(*) FILTER (WHERE ...) ——条件ごとに別のクエリを丸ごと書かなくても、追加条件に合う 行数だけを数えられます。これを複数並べることで、1つのクエリの 1行の中に、条件の異なる複数のカウントを列として出力できます。
• ウィンドウ関数： ROW_NUMBER() OVER (ORDER BY x)と 集計関数() OVER () ——集計関数の後にOVER (...)を付けると、通常の GROUP BY集計のように行をまとめて減らすのではなく、 全ての行を残したまま、行のグループ全体にわたる値を計算します。 ROW_NUMBER() OVER (ORDER BY x)はxの 順に行へ1, 2, 3…と番号を振り、COUNT(*) OVER () （空の括弧）は全行の合計件数を、どの行にも同じ値として付与します。
• Two-Segment Optimal Split ——DOI 1.4以降、μ_stat・σ_statの 計算に繰り返し登場するパターンです。ビンごとの値の曲線が与えられたとき、その 曲線を2つの平坦なセグメントに最もうまく分割する境界を、両 セグメント内の分散の合計を最小化することで見つけます。具体的 には：まず各ビンに番号を振り（ROW_NUMBER() OVER）、 あり得る分割点kそれぞれについて VAR_POP(...) FILTER (WHERE rn <= k) * k + VAR_POP(...) FILTER (WHERE rn > k) * (n-k) ——そのkで「前半」と「後半」に分けた場合の分散 合計——を計算し、最も小さい合計を持つkを採用 します。データベースから一歩も出ずに、あり得る全てのしきい値を 試して一番良いものを選ぶ、SQLでのやり方です。
• UNPIVOT ... ON a, b, c INTO NAME metric VALUE value ——複数の列を、複数の行に変換します：avg・ min・maxという列を持つ結果が、 どの列から来たかのラベル付きの3行になります。出力される 値を常に短く保ち、DuckDB CLIが横に長い結果行をターミナル 幅で切り詰めてしまう問題を避けるために使っています。
• PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY x) ——グループ内でのxの中央値（任意のパーセンタイルも 指定可能：0.25でP25、0.90でP90など）。
• CORR(x, y)——2つの列の間の ピアソン相関係数。−1（完全に逆）から+1（完全に一致） までの値を取ります。
• 自己結合（self-JOIN）——1つのテーブルに 2つの異なる別名を付けて自分自身と結合すること（例： FROM blocks n JOIN blocks t ON ...）。同じ テーブル内の関連する2つの行を比較するために使います——ここでは 主に、あるブロックのnoTTA行を、その同じ座標のTTA行と対応 付けるのに使われています。