[Looker] Symmetric Aggregates ( https://help.looker.com/hc/en-us/articles/360023722974 )

README.md

1. Create Dataset

$ bq mk sandbox_masutaka

2. Setup orders Table

$ bq mk --table --schema=./orders.json sandbox_masutaka.orders
$ bq insert sandbox_masutaka.orders orders_data.json

2. Setup order_items Table

$ bq mk --table --schema=./order_items.json sandbox_masutaka.order_items
$ bq insert sandbox_masutaka.order_items order_items_data.json

Official document

BigQuery CLI Reference

order_items.json

[
 {
   "name": "order_id",
   "type": "INTEGER",
   "mode": "REQUIRED"
 },
 {
   "name": "item_id",
   "type": "INTEGER",
   "mode": "REQUIRED"
 },
 {
   "name": "quantity",
   "type": "INTEGER",
   "mode": "REQUIRED"
 },
 {
   "name": "unit_price",
   "type": "FLOAT",
   "mode": "REQUIRED"
 }
]

order_items.view.lkml

view: order_items {
  sql_table_name: `feedmatic-looker.sandbox_masutaka.order_items`
    ;;

  dimension: id {
    primary_key: yes
    type: string
    sql: CONCAT(${order_id}, ${item_id})  ;;
    hidden: yes
  }

  dimension: order_id {
    type: number
    sql: ${TABLE}.order_id ;;
  }

  dimension: item_id {
    type: number
    sql: ${TABLE}.item_id ;;
  }

  dimension: quantity {
    type: number
    sql: ${TABLE}.quantity ;;
  }

  dimension: unit_price {
    type: number
    sql: ${TABLE}.unit_price ;;
  }

  measure: count {
    type: count
    drill_fields: []
  }

  measure: total_quantity {
    type: sum
    sql: ${quantity} ;;
    label: "Quantity"
  }

  measure: total_unit_price {
    type: sum
    sql: ${unit_price} ;;
    label: "Unit Price"
    value_format_name: usd
  }
}

order_items_data.json

{"order_id": 1, "item_id": 50, "quantity": 1, "unit_price": 23.00}
{"order_id": 1, "item_id": 63, "quantity": 2, "unit_price": 13.68}
{"order_id": 2, "item_id": 63, "quantity": 1, "unit_price": 13.68}
{"order_id": 2, "item_id": 72, "quantity": 1, "unit_price": 5.08}
{"order_id": 2, "item_id": 79, "quantity": 1, "unit_price": 5.36}
{"order_id": 3, "item_id": 78, "quantity": 1, "unit_price": 50.36}

orders.json

[
 {
   "name": "order_id",
   "type": "INTEGER",
   "mode": "REQUIRED"
 },
 {
   "name": "user_id",
   "type": "INTEGER",
   "mode": "REQUIRED"
 },
 {
   "name": "total",
   "type": "FLOAT",
   "mode": "REQUIRED"
 },
 {
   "name": "order_date",
   "type": "DATE",
   "mode": "REQUIRED"
 }
]

orders.view.lkml

view: orders {
  sql_table_name: `feedmatic-looker.sandbox_masutaka.orders`
    ;;
  drill_fields: [order_id]

  dimension: order_id {
    primary_key: yes
    type: number
    sql: ${TABLE}.order_id ;;
  }

  dimension: user_id {
    type: number
    sql: ${TABLE}.user_id ;;
  }

  dimension: total {
    type: number
    sql: ${TABLE}.total ;;
    hidden: yes
  }

  dimension_group: order {
    type: time
    timeframes: [
      raw,
      date,
      week,
      month,
      quarter,
      year
    ]
    convert_tz: no
    datatype: date
    sql: ${TABLE}.order_date ;;
  }

  measure: count {
    type: count
    drill_fields: [order_id]
  }

  measure: total_total {
    type: sum
    sql: ${total} ;;
    value_format_name: usd
    label: "Total"
  }
}

orders_data.json

{"order_id": 1, "user_id": 100, "total": 50.36, "order_date": "2017-12-01"}
{"order_id": 2, "user_id": 101, "total": 24.12, "order_date": "2017-12-02"}
{"order_id": 3, "user_id": 137, "total": 50.36, "order_date": "2017-12-02"}

sandbox_masutaka.model.lkml

connection: "docs_bigquery_db"

include: "/views/order_items.view.lkml"                # include all views in the views/ folder in this project
include: "/views/orders.view.lkml"                # include all views in the views/ folder in this project

explore: orders {
  label: "Main"

  join: order_items {
    type: left_outer
    relationship: one_to_many
    sql_on: ${orders.order_id} = ${order_items.order_id} ;;
  }
}