Lookerで小計を出して「全行に割り振る」方法

こんにちは、北見です🌼
今回は、Lookerの標準テーブル計算（Table Calculation）を使用して小計を出す方法と、その応用をご紹介します！

Lookerでサブカテゴリ単位の小計を出したいことってありますよね。一方で、テーブル計算で小計を作成すると、小計が付くのはサブカテゴリの最終行のみで、それ以外の行は NULL になることが多いです。

小計行が最終行のみだと、以下のような場合に困ってしまいます。

• 各行で「小計に対する割合（構成比）」を出したい
• サブカテゴリ内の行数が毎回バラバラで、「N行目に小計がある前提」が崩れる

今回は 「小計を作成する」から作ったものを「全行に割り振る」 までを、Lookerのテーブル計算のみで実現します！

1.サブカテゴリ内の行番号を作成する

🧂前提

テーブル計算は表示順に依存するため、サブカテゴリ（例：${dim_subcategory.subcategory}）で並び替えて、同じサブカテゴリが連続する状態にしておきます。

また今回は「サブカテゴリごとの売上（sales）を出す」ことを例に説明します。

🔨設定

まず「同一サブカテゴリ内での行番号（1,2,3…）」を作ります。

カラム名：partition_row_number↓

if(
  match($${dim_subcategory.subcategory}, $${dim_subcategory.subcategory})
  = offset(match($${dim_subcategory.subcategory}, $${dim_subcategory.subcategory}), -1),
  1 + row() - match($${dim_subcategory.subcategory}, $${dim_subcategory.subcategory}),
  1
)

何を実現したいのか

「この行は、同じサブカテゴリの中で何行目なのか？」を算出したいです。

サブカテゴリが変わったら 1 に戻し、同じなら 2,3…と増やします。

どのように動いているのか

• match(subcategory, subcategory)→ “このサブカテゴリが最初に登場した行番号” を返します（= グループの先頭行）
• offset(match(...), -1)→ 1行上の “グループ先頭行番号” を見ます
• if( match(...) = offset(match(...), -1), ... , 1)
  • 上の行と「同じグループ先頭行番号」なら、今の行も同じサブカテゴリ（グループ継続）
  • 違うなら、今の行は新しいサブカテゴリの先頭行 → 1
• グループ継続のとき：1 + row() - match(subcategory, subcategory)
  • row() は現在行
  • match(...) はグループ先頭行
  • なので row() - 先頭行 が「先頭から何行進んだか」
  • それに +1 して 1始まりの連番にします

2.小計を出す（最終行だけ値が入る）

🔨設定

まず「同一サブカテゴリ内での行番号（1,2,3…）」を作ります。

カラム名：subcategory_subtotal_last_row↓

if(
  NOT(subcategory = offset(subcategory, 1)),
  sum(
    offset_list(
      sales,
      -(partition_row_number - 1),
      partition_row_number
    )
  ),
  null
)

何を実現したいのか

「サブカテゴリ売上小計」を作成したいです。ただし、まずは定石どおり、最終行だけに小計を置きます（それ以外はNULL）。

どのように動いているのか

（前半：最終行判定）

• offset(subcategory, 1)：次の行のサブカテゴリ
• NOT(subcategory = offset(subcategory, 1))→ 次行とサブカテゴリが異なる = 今の行がこのサブカテゴリの最終行

（後半：小計の計算）

• partition_row_number は「グループ内で何行目か」
  • 例えば最終行がグループ内3行目なら partition_row_number = 3
• offset_list(sales, -(partition_row_number - 1), partition_row_number)
  • start = (3-1)=-2、length=3 のようにして
  • 「現在行（最終行）から2行上に戻ったところから、3個分」＝グループ全行分の値のリストを作成します
• sum(...) でそのリストを合計 → サブカテゴリ小計

3.小計を全行に割り振る（ランダム行数でもOK）

🔨設定

まず「同一サブカテゴリ内での行番号（1,2,3…）」を作ります。

カラム名：sales_subtotal_all_rows↓

index(
  ${table_calculation_1},
  count(subcategory)
  - match(
      subcategory,
      offset(
        subcategory,
        count(subcategory) - row() * 2 + 1
      )
    )
  + 1
)

※${table_calculation_1}は「②の最終行だけ小計が入っている列（それ以外NULL）」を指す想定。

何を実現したいのか

各行で「自分のサブカテゴリの小計」を参照したいです。一方で、小計は最終行のみにある状態のため、「自分のサブカテゴリの最終行」が結果セットの何行目かを求めて、index()でその行数の値を取りに行きます。

どのように動いているのか

• count(subcategory)

結果セットの総行数（N）です。後ろから探すための基準にしています。

• count(subcategory) - row() * 2 + 1

行が下に行くほど row() が大きくなり、この数値は小さくなります。

これで offset(subcategory, 何行か) に渡す「ずらし量」を作成しており、後ろ側の行を参照するためのトリックになっています。

• offset(subcategory, count(subcategory) - row()*2 + 1)

「この行から見て、ある程度“後ろ側”のサブカテゴリ値」を取ります。

（“後ろから見たときに、同じサブカテゴリが最初に出る位置” を見つけるための足がかり）

• match(subcategory, offset(...))

ここが直感とズレやすいですが、おこなっていることは、「今の行の subcategory」と「後ろ側から取ってきた subcategory 値」 を使用して、最終行の位置を逆算できる形にしている、という理解が近いです。

• count(...) - match(...) + 1

match() が返す「前から数えた位置」を、count() を使って 後ろから数えた位置 にひっくり返しています。

この結果が「参照すべき行番号（=小計が入っている行番号）」になります。

• index(${table_calculation_1}, その行番号)

${table_calculation_1}（小計が最終行にだけ入っている列）の、その行を参照して値を取る。

→ この計算を各行でおこなうため 全行に同じ小計が入る ようになります。

4.小計に対する割合（構成比）を出す

🔨設定

手順3までで小計を全行に配ることができたので、各行でsales/sales_subtotal_all_rowsが成立して、構成比を全行で出すことが可能です。

おわりに

今回は、サブカテゴリ内の行数がランダムでも対応可能な、「小計とその構成比を出す方法」を紹介しました。
何段階かに分かれて計算を書くため、適宜列を非表示にして使ってくださいね。

それでは、よきLookerライフを〜！