row_number()とOVER句で帳票印刷をコントロール

データベースを扱っていると付いて回るのが帳票印刷。

中でも伝票は金額の集計がややこしくなりがちです。

そこで上手く利用したいのがrow_number()関数。

OVER句で指定されたパーティションに基づいて行数を返す関数ですが、パーティションをグループと捕らえて、その中の行Noを利用することでこんな力技ができます。

元の集計は以下のようなSQLでこのようなイメージでした。(便宜上、列名・テーブル名を変更しています)

SELECT
     管理No
    ,数
    ,単位
    ,金額
    ,税額
    ,伝票金額
    ,伝票税額
FROM 伝票テーブル

このままだと帳票(Report定義ファイル)編集がとてもやりにくいです。Sum()とFirst()を駆使すればいいのですが、複雑な帳票になると途端にコントロールが難しくなるのです。

そこで、データ側を制御することにしました。

SELECT
     管理No
    ,数
    ,単位
    ,金額
    ,税額
    ,case when row_number() over (PARTITION BY 管理No order by 管理No) = 1 then 伝票金額 else 0 end 伝票金額
    ,case when row_number() over (PARTITION BY 管理No order by 管理No) = 1 then 伝票税額 else 0 end 伝票税額
FROM 伝票テーブル

CASE～WHEN句も組み合わせていますが、パーティション(グルーピング後)の1行目にだけ伝票金額を残すことができました。このような配置にすると各列をSum()することで明細／伝票の総計が得られることになり、作業が楽になります。

消費税計算が、明細単位だったり、伝票単位だったり、果てまた請求単位だったりと伝票類には複雑なことが多いですが、SQLを駆使して業務に対応しましょう。