「最新の」値だけをメジャーにする方法

この記事は、Only measuring the “latest” values:us: の翻訳記事です

メジャーとは何か?

非常に多くのケースで、私たちは、自分自身に不必要な制約をかけてしまって、メジャーやメトリック、あるいは集約するということは、SQLで提供される基本的な集約であるSUM、COUNT、AVERAGE(あるいは方言がサポートされている場合はMEDIANも含む)のことであると考えてしまいます。

しかしここでちょっと立ち戻って、なんのメジャーが本質的なのかを考えてみましょう - 通常、あるグループやディメンションの中で、潜在的に多数の基礎データポイントを取得して、それらを利用可能なデータに凝縮するメジャーを選びましょう。数千の注文を集約する際は? メジャーには総売上が良いです。数百万のセッションでは? サイトの平均滞在時間が良さそうです。何百万人ものセグメントあたりユーザー数は? 訪問頻度のヒストグラムになります。多数のキャンペーンと、たくさんの日付、訪問データは? 直近30日間の訪問回数のスパークラインなど。

上記の例はちょっと言い過ぎかもしれませんが、要点はつまり、あなたの想像力が制約になっているということです。ここでは、最新のデータポイントのみを使用する必要があるデータを要約するときの簡単なメジャー使用方法を次に示します。例としては、在庫のスナップショットや、バランス・シート、タイムスタンプ付きの変更リストが与えられた属性の状態履歴などのデータです。

「最新の」メジャー

今、以下のような在庫のスナップショットデータで、日毎にそれぞれ在庫数を持っているとします。

INVENTORY_SNAPSHOT
inventory_id     | taken_at  | amount
------------------------------------
1                | 2018-01-01 | 17
2                | 2018-01-01 | 9
3                | 2018-01-01 | 29
1                | 2018-01-02 | 13
2                | 2018-01-02 | 37
3                | 2018-01-02 | 19
...

特定の時間枠について、最新の行を選択して、特定のインベントリIDのすべての行を集約します。これには色々な方法がありますが、複雑さが悪化する並べ替え/順序付けに基づくアプローチとは対照的に、短くて再利用可能であり、時間の複雑さがO(n)で、メモリーの複雑さがO(1)で済むので、私はこの方法が気に入っています:

SELECT
    inventory_id, 
    SUBSTRING(MAX( taken_at::varchar(10) || amount ),11)::int
...
GROUP BY 1
--これはRedshiftでの書き方です。「::」演算子はキャスト、「||」演算子は文字列の結合です

少し説明しましょう-フィルターに一致するすべての行と特定のインベントリIDグループで、最初に固定長バージョンの並べ替えフィールド(taken_at)と欲しいデータのフィールド(amount)から連結フィールドを作成します。次に、この計算フィールドを使用して、MAX集計関数を使用すると、各インベントリIDの最新の行が取得され、SUBSTRINGで日付を破棄して値のみを残します。

LookMLでの利用

とりあえずこれで良さそうですが、このSQLをLookMLにどのように変換すれば良いでしょうか? 「最新の」集計は1つのグループ(この例ではinventory_id)に緊密に結びついているため、ユーザーが任意のディメンションとペアにすることができるビューのメジャーとして直接使用することは適していません。その代わりに派生テーブルではペアの組み合わせがハードコーディングされますので、その集計とグループ化を行うことは理にかなっています。

一方、集計は時間グループとは疎に結合しています。ユーザーがさまざまなレベルの粒度で切り替えることができるようにしたいから - あるいは、絶対的な最新データだけを対象として時間グループをまったく作成しないようにするためです。さらに、このinventory-x-time集計は、多くの場合、他の非結合レベルのグループにさらに集計する必要があります。これらすべてを念頭に置いて、_in_query機能を使用してこれらすべてを連携させ、「動的な」SQLを記述する1つの方法例を以下に示します。

explore: inventory {
  join: inventory_snapshot_dynamic {
    type: left_join
    relationship: one_to_many
    sql_on: ${inventory_snapshot_dynamic.inventory_id} = ${inventory.id} ;;
  }
}
view: inventory_snapshot_dynamic{
  derived_table:{
    sql:
        SELECT inventory_id,
        {% if    inventory_snapshot_dynamic.dynamic_granularity_date._in_query %}
             DATE_TRUNC('day',taken_at)
        {% elsif inventory_snapshot_dynamic.dynamic_granularity_week._in_query %}
             DATE_TRUNC('week',taken_at)
        {% elsif inventory_snapshot_dynamic.dynamic_granularity_month._in_query %}
             DATE_TRUNC('month',taken_at)
        {% elsif inventory_snapshot_dynamic.dynamic_granularity_quarter._in_query %}
             DATE_TRUNC('quarter',taken_at)
        {% elsif inventory_snapshot_dynamic.dynamic_granularity_year._in_query %}
             DATE_TRUNC('year',taken_at)
        {% else %} 0
        {% endif %} as dynamic_granularity
        --------
        {% if inventory_snapshot_dynamic.sum_ending_amount._in_query %}
             , SUBSTRING(MAX( taken_at::varchar(10) || amount ),11)::int as ending_amount
        {% endif %}
        FROM inventory_snapshot
        WHERE {% condition inventory_snapshot_dynamic.dynamic_granularity_date %} taken_at {% endcondition %}
          AND {% condition inventory_snapshot_dynamic.dynamic_granularity_week %} taken_at {% endcondition %}
          AND {% condition inventory_snapshot_dynamic.dynamic_granularity_month %} taken_at {% endcondition %}
          AND {% condition inventory_snapshot_dynamic.dynamic_granularity_quarter %} taken_at {% endcondition %}
          AND {% condition inventory_snapshot_dynamic.dynamic_granularity_year %} taken_at {% endcondition %}
        GROUP BY 1,2
    ;;
  }
  dimension: inventory_id{}
  dimension_group: dynamic_granularity {
    group_label: "History Granularity"
    type: time
    datatype: date
    sql: {$TABLE}.dynamic_granularity ;;
    timeframes: [year,quarter,month,week,date]
  }
  measure: sum_ending_amount{
    type:sum
    sql: ${TABLE}.ending_amount
  }
}

共有は思いやり

上記は単独で十分に機能しますが、これと他の同様の動的ビューを1つのExploreに合成できるようにしたい場合、複数のビューが参照できるように日付フィールドを分離および外部化するのに役立ちます。以下に、部分的な例を示します。

explore: inventory {
  join: dynamic_granularity {sql: ;; relationship: one_to_one} #Field-only view
  join: inventory_snapshot_dynamic {
    type: left_join
    sql_on: ${inventory_snapshot_dynamic.inventory_id} = ${inventory.id} ;;
  }
  join: sales_dynamic {
    type: left_join
    sql_on: ${inventory_snapshot_dynamic.inventory_id} = ${inventory.id}
    {% if inventory_snapshot_dynamic._in_query %}
       AND sales_dynamic.dynamic_granularity = inventory_snapshot_dynamic.dynamic_granularity
    {% endif %} ;;
  }
}
view: dynamic_granularity {
  label: "[History]"
  dimension_group: dynamic_granularity {
    group_label: "Granularity"
    type: time
    datatype: date
    sql: COALESCE(
      {% if inventory_snapshot_dynamic._in_query %}
        inventory_snapshot_dynamic.taken_at, 
      {%endif%}
      {% if sales_dynamic._in_query %}
        sales_dynamic.sale_at, 
      {%endif%}
      NULL ) ;;
    timeframes: [year,quarter,month,date]
  }
}
view: inventory_snapshot_dynamic{
  derived_table:{
    sql:
        SELECT inventory_id,
        {% if    dynamic_granularity.dynamic_granularity_date._in_query %}
             DATE_TRUNC('day',taken_at)
        {% elsif dynamic_granularity.dynamic_granularity_week._in_query %}
             DATE_TRUNC('week',taken_at)
...

脚注

この種の議論では、「半加算的メジャー(semi-additive measure)」という用語がよく使用されます

1 Like