クエリの「型」を作る：パラメタライズドテンプレートで再現性を担保

from jinja2 import Template
template = Template("""SELECT  DATE_TRUNC({{ date_column }}, {{ granularity }}) AS period,  {% if dimension %}{{ dimension }},{% endif %}  COUNT(*) AS cnt,  SUM(amount_jpy) AS revenueFROM {{ table }}WHERE _PARTITIONTIME BETWEEN '{{ start_date }}' AND '{{ end_date }}'  {% if filters %}{% for f in filters %}AND {{ f }}{% endfor %}{% endif %}GROUP BY 1{% if dimension %}, 2{% endif %}ORDER BY 1LIMIT 1000;""")
sql = template.render(    table="proj.dwh.orders",    date_column="created_at",    granularity="month",    dimension="category",    start_date="2027-01-01",    end_date="2027-12-31",    filters=["region = 'east'"],)print(sql)

view: orders {  sql_table_name: `proj.dwh.orders` ;;
  dimension: order_id { primary_key: yes }  dimension_group: created {    type: time    timeframes: [date, week, month, quarter, year]    sql: ${TABLE}.created_at ;;  }  dimension: region { type: string sql: ${TABLE}.region ;; }  dimension: category { type: string sql: ${TABLE}.category ;; }
  measure: count { type: count }  measure: revenue {    type: sum    sql: ${TABLE}.amount_jpy ;;    value_format_name: jpy_0  }  measure: aov {    type: number    sql: ${revenue} / NULLIF(${count}, 0) ;;  }}
explore: orders {  description: "注文データ。期間 / 地域 / カテゴリで自由に切れます"}

version: 2metrics:  - name: revenue    label: "売上 (JPY, 税込)"    model: ref('orders')    description: "確定済み注文の合計金額。返金は減算済み"    type: simple    type_params:      measure:        name: amount_jpy        agg: sum    filter: "{{ Dimension('orders__status') }} = 'completed'"
  - name: monthly_active_customers    label: "MAC"    type: simple    type_params:      measure:        name: customer_id        agg: count_distinct    time_granularity: month

import streamlit as stimport pandas as pdfrom datetime import datefrom google.cloud import bigquery
st.title("📊 期間別売上テンプレート")
# 入力start = st.date_input("開始日", date(2027, 1, 1))end = st.date_input("終了日", date(2027, 12, 31))granularity = st.selectbox("粒度", ["day", "week", "month"])region = st.selectbox("地域", ["全国", "east", "west", "central"])
# テンプレ展開where_region = "" if region == "全国" else f"AND region = '{region}'"sql = f"""SELECT  DATE_TRUNC(created_at, {granularity.upper()}) AS period,  SUM(amount_jpy) AS revenueFROM proj.dwh.ordersWHERE _PARTITIONTIME BETWEEN '{start}' AND '{end}'  {where_region}GROUP BY 1 ORDER BY 1"""
if st.button("実行"):    bq = bigquery.Client()    df = bq.query(sql).to_dataframe()    st.line_chart(df.set_index("period")["revenue"])    st.dataframe(df)