Zenn
🚗

【Looker】同一フィールドでのAND条件フィルター

2023/04/21に公開
Looker
LookML
tech

以前、【Looker】アソシエーション分析的なものを実現してみる という記事の中で、Lookerでアソシエーション分析的なことを実現する方法を紹介させていただきましたが、別の実装方法を考えたので紹介したいと思います。

実現したいこと

  • ユーザーID, 購入した商品（1:n）のようなデータがあったときに、商品Aと商品B、商品Cを買ったユーザーで絞り込みを行いたい
  • 前回の実装方法の場合、２つまでであれば実現可能だが、３つ以上になるとできない
  • 今回紹介する方法では、３つ以上の場合でもAND条件で絞り込みが可能

実装方法について紹介していきます。

利用するテーブル

前回同様にsales_orderをいう受注テーブルを元に解説していきます。

ベースとなるView

view: sales_order {
  sql_table_name: `sales_order` ;;

  # 注文ID
  dimension: order_id {
    type: number
    primary_key: yes
    sql: ${TABLE}.order_id ;;
  }

  # ユーザID
  dimension: user_id {
    type: number
    sql: ${TABLE}.user_id ;;
  }

  # 注文日時
  dimension_group: order_at {
    type: time
    timeframes: [
      raw,
      time,
      date,
      week,
      month,
      year
    ]
    sql: ${TABLE}.order_at ;;
  }

  # 商品ID
  dimension: product_id {
    type: number
    sql: ${TABLE}.product_id ;;
  }

  # 商品カテゴリ
  dimension: product_category {
    type: string
    sql: ${TABLE}.product_category ;;
  }

  # 売上
  measure: sales {
    type: sum
    sql: ${TABLE}.sales ;;
  }
  
  # ユーザー数
  measure: count_users {
    type: count_distinct
    sql: ${user_id} ;;
  }
}

実現のアイデア

ユーザーID単位に、ユニークな商品ID数をカウントします。
商品ID数をカウントする際に、指定した条件でフィルタします。例えば、A,B,Cの３つを商品で絞り込みたい場合、この３つでフィルタしたユニークな商品ID数をカウントします。
そうすると、A,B,Cのすべてを購入したユーザーの商品ID数は3となります。AとB、BとCなど２つを購入したユーザーの商品ID数は2に、A,B,Cのどれも購入していないユーザーの商品ID数は0となります。
この商品ID数が、3となるユーザー = 商品A,B,Cの３つを買ったユーザーとなります。

実現方法について

実際にLookMLとともに実装方法について解説していきます。

フィルタ用のviewを作成する

以下のようなviewを作成します。

view: sales_order_user_filter {
  derived_table: {
    sql:
      SELECT
        user_id,
        count(distinct
          IF({% condition filter_product_id %} product_id {% endcondition %}, product_id, NULL)
        ) as count_product
      FROM `sales_order`
      GROUP BY user_id
      HAVING
        count_product = {{ _filters['sales_order_user_filter.filter_product_id'] | split:"," | size }}

    ;;
  }

# 商品IDフィルタ
  filter: filter_product_id {
    type: number
    group_label: "User Level Filter"
    suggest_dimension: sales_order.product_id
  }

  # ユーザID
  dimension: user_id {
    type: number
    sql: ${TABLE}.user_id ;;
    primary_key: yes
    hidden: yes
  }
}

viewにINNER JOINする

exploreで元のsales_orderのviewに以下のようにINNER JOINします。

explore: sales_order {
  view_label: "sales order"
  join: sales_order_user_filter {
    view_label: "sales order"
    type: inner
    sql_on: ${sales_order.user_id} = ${sales_order_user_filter.user_id} ;;
  }
}

解説

以下の部分でfilter_product_idで指定した商品IDの場合にのみカウントをしています。

count(distinct
  IF({% condition filter_product_id %} product_id {% endcondition %}, product_id, NULL)
) as count_product

そして、HAVING句で上記でカウントした数と、filter_product_idで指定したIDの数をLiquidで取得しています。
例えば、商品A（ID=10）, 商品B（ID=21）, 商品B（ID=32）を指定した場合、
_filters['sales_order_user_filter.filter_product_id'] には、10,21,32が入ります。
それを、split:"," で分割し、sizeで値の数を返しています。

HAVING
  count_product = {{ _filters['sales_order_user_filter.filter_product_id'] | split:"," | size }}

実行されるSQL

商品A（ID=10）, 商品B（ID=21）, 商品B（ID=32）を指定した場合、実行されるSQLは以下のようになります。

WITH sales_order_user_filter AS (SELECT
        user_id,
        count(distinct
          IF(( product_id  IN (10,21,32)), product_id, NULL)
        ) as count_product
      FROM `sales_order`
      GROUP BY user_id
      HAVING
        count_product = 3

    )
SELECT
    COUNT(DISTINCT sales_order.user_id ) AS sales_order_count_users
FROM `sales_order`  AS sales_order
INNER JOIN sales_order_user_filter ON sales_order.user_id = sales_order_user_filter.user_id

まとめ

参考

Discussion