# AIでのサマリー生成

ダッシュボードに付随するテキストサマリーはユーザーがデータを理解する手助けになります。しかし、人が記載するサマリーではパラメータ指定などのユーザー側でのアクションを反映させることができません。

この記事ではBigQueryを介してVertex AIを利用し、ユーザーがパラメータで指定したデータに応じたサマリーの生成を行う方法を紹介します。BigQuery以外のコネクションでも対応するAI機能を利用することで実現可能です。

Codatumにはマークダウン記法のテキスト表示のためのテキストパネルがあるので、ここではテキストパネルを利用してチャートへ表示します。

## 設定方法

* Vertex AIのセットアップ
* モデルの作成
* 集計データをモデルに渡す
* テキストパネルチャートを選択し、データを表示する

上記の流れで、ユーザーがパラメータを変更してクエリ実行されるたびに表示内容が異なるサマリーの追加が可能です。

## 実装例

* BigQueryパブリックデータの Chicago Taxi Trip を利用します
* パラメータで表示するデータの対象を変更するたびにサマリーを生成し、表示データに応じたサマリーを作成します

### モデルの作成

* この作業はBigQueryのクエリエディタ側でも実行できます。

```sql
CREATE OR REPLACE MODEL `codatum-example.example.vertex_ai`
REMOTE WITH CONNECTION `us.vertex_ai_test`
OPTIONS(
  ENDPOINT = 'gemini-2.0-flash'
)
```

上記クエリの完了後、BigQueryの `example` データセットに `vertex_ai` モデルが追加されます。

### データの集計

* 月次で企業ごと、支払い方法毎の乗車回数、乗車時間、乗車距離、乗車料金を集計します
  * 企業名、支払い方法は[パラメータ](https://docs.codatum.jp/data-exploration/parameter-overview)で変更できるように設定します

```sql
SELECT
  DATE_TRUNC(trip_start_timestamp, month) as month,
  company,
  payment_type,
  COUNT(distinct unique_key) as trip_count,
  SUM(trip_seconds) / 60 as total_trip_min,
  AVG(trip_seconds) / 60 as average_trip_min,
  MAX(trip_seconds) / 60 as max_trip_min,
  MIN(trip_seconds) / 60 as min_trip_min,
  SUM(trip_miles) as total_trip_miles,
  AVG(trip_miles) as average_trip_miles,
  MAX(trip_miles) as max_trip_miles,
  MIN(trip_miles) as min_trip_miles,
  SUM(fare) as total_fare,
  AVG(fare) as average_fare,
  MAX(fare) as max_fare,
  MIN(fare) as min_fare
FROM
  `bigquery-public-data.chicago_taxi_trips.taxi_trips`
WHERE trip_start_timestamp >= '2017-01-01' AND trip_start_timestamp < '2018-01-01' -- 2017年のデータ
--外れ値除外
AND trip_seconds != 0 AND fare != 0 AND trip_miles != 0
AND trip_seconds < 21600
AND CASE {Company} WHEN '' THEN TRUE ELSE company = {Company} END
AND CASE {PaymentType} WHEN '' THEN TRUE ELSE payment_type = {PaymentType} END
GROUP BY DATE_TRUNC(trip_start_timestamp, month), company, payment_type
ORDER BY month
```

### AIモデルへのデータの受け渡し

Codatumでは、他のSQLブロックの結果を参照することが可能なので、データを集計するSQLブロックとデータを受け渡すSQLブロックを分けて管理することができます。

* 集計したデータをJSON形式に変換します

```sql
SELECT
  TO_JSON_STRING(STRUCT(
  average_fare,average_trip_miles,average_trip_min,company,max_fare,max_trip_miles,max_trip_min,min_fare,min_trip_miles,min_trip_min,month,payment_type,total_fare,total_trip_miles,total_trip_min,trip_count
  )) as input_text
FROM
  月毎利用状況サマリー
```

* 行毎のJSONデータを1つに結合します

```sql
SELECT
  CONCAT('[', STRING_AGG(input_text, ','), ']') AS all_input_texts
FROM
  JSON形式_利用状況サマリー
```

* 結合したJSONデータをAIモデルに渡します
  * プロンプトとJSONデータを結合して、作成したモデルに受け渡します

```sql
SELECT 
  JSON_VALUE(ml_generate_text_result.candidates[0].content.parts[0].text) as output,
FROM ML.GENERATE_TEXT(
  MODEL `codatum-example.example.vertex_ai`,
  (
    SELECT
      CONCAT(
        '以下のデータはCompany別、Payment_Type別の月次のタクシーの乗車データのサマリーです。',
        'サマリーの支払い方法別、月別の傾向をサマライズして、Markdown形式のレポートにしてください。',
        '総乗車回数や最多乗車月などのデータの概要を説明した後、特徴的な項目について複数項目をセクションを分けて説明して下さい。'
        'Markdown全体を囲う必要はありません。',
        'markdownの色々な要素を使うようにしてください。',
        '生成したサマリーで完結するようにしてください。サマリーを追加で編集することはありません。ただし追加の分析提案は可能です。',
        'サマリー内ではコードの実行ができないため、コードは埋め込まないでください。'
        '対象のタクシー乗車データの支払い方法は', CASE WHEN {PaymentType} = '' THEN '全て' ELSE {PaymentType} END,'で、',
        '対象企業は', CASE WHEN {Company} = '' THEN '全社' ELSE {Company} END, 'です。インプットデータ：',
        all_input_texts
      ) as prompt,
      *,
    FROM All_JSON形式_利用状況サマリー
  ),
    STRUCT(
      0.3 AS temperature,
      1000 AS max_output_tokens)
)
```

* モデルに渡すプロンプトの内容によって、サマリーの安定度が変わるので工夫してみてください

### テキストパネルチャートの追加

* チャートタイプを選択から`テキストパネル`を選択し追加します
  * テキストパネルは1行目の値を表示するので、複数のテキストデータが存在する場合は1つのみが表示されます

## 注意点

* Markdownが囲われている場合、テキストパネルでは適切に展開することができないので、AIのアウトプットがMarkdownを囲わないようにしてください