Halaman ini menjelaskan struktur yang direkomendasikan untuk menulis perintah yang efektif bagi agen data Conversational Analytics API yang terhubung ke data BigQuery. Perintah ini adalah konteks yang dibuat yang Anda tentukan sebagai string menggunakan parameter system_instruction.
Contoh komponen utama petunjuk sistem
Bagian berikut berisi contoh komponen utama petunjuk sistem di BigQuery. Kunci ini mencakup:
tablesfieldsmeasuresgolden_queriesgolden_action_plansrelationshipsglossariesadditional_descriptions
Untuk mengetahui deskripsi komponen utama ini, lihat halaman dokumentasi Memandu perilaku agen dengan konteks yang dibuat.
Mendeskripsikan data Anda dengan tables
Blok kode YAML berikut menunjukkan struktur dasar untuk kunci tables untuk tabel bigquery-public-data.thelook_ecommerce.orders:
- tables:
- table:
- name: bigquery-public-data.thelook_ecommerce.orders
- description: Data for customer orders in The Look fictitious e-commerce store.
- synonyms:
- sales
- orders_data
- tags:
- ecommerce
- transaction
Mendeskripsikan kolom yang umum digunakan dengan fields
Contoh kode YAML berikut menjelaskan kolom utama seperti order_id, status, created_at, num_of_items, dan earnings untuk tabel orders:
- tables:
- table:
- name: bigquery-public-data.thelook_ecommerce.orders
- fields:
- field:
- name: order_id
- description: The unique identifier for each customer order.
- field:
- name: user_id
- description: The unique identifier for each customer.
- field:
- name: status
- description: The current status of the order.
- sample_values:
- complete
- shipped
- returned
- field:
- name: created_at
- description: The timestamp when the order was created.
- field:
- name: num_of_items
- description: The total number of items in the order.
- aggregations:
- sum
- avg
- field:
- name: earnings
- description: The sales amount for the order.
- aggregations:
- sum
- avg
Menentukan metrik bisnis dengan measures
Sebagai contoh, Anda dapat menentukan ukuran profit sebagai penghitungan penghasilan dikurangi biaya sebagai berikut:
- tables:
- table:
- name: bigquery-public-data.thelook_ecommerce.orders
- measures:
- measure:
- name: profit
- description: Raw profit (earnings minus cost).
- exp: earnings - cost
- synonyms: gains
Meningkatkan akurasi dengan golden_queries
Misalnya, Anda dapat menentukan kueri utama untuk analisis umum data dalam tabel orders sebagai berikut:
- tables:
- table:
- golden_queries:
- golden_query:
- natural_language_query: How many orders are there?
- sql_query: SELECT COUNT(*) FROM sqlgen-testing.thelook_ecommerce.orders
- golden_query:
- natural_language_query: How many orders were shipped?
- sql_query: >-
SELECT COUNT(*) FROM sqlgen-testing.thelook_ecommerce.orders
WHERE status = 'shipped'
Menguraikan tugas multi-langkah dengan golden_action_plans
Sebagai contoh, Anda dapat menentukan rencana tindakan untuk menampilkan perincian pesanan menurut kelompok usia dan menyertakan detail tentang kueri SQL dan langkah-langkah terkait visualisasi:
- tables:
- table:
- golden_action_plans:
- golden_action_plan:
- natural_language_query: Show me the number of orders broken down by age group.
- action_plan:
- step: >-
Run a SQL query that joins the table
sqlgen-testing.thelook_ecommerce.orders and
sqlgen-testing.thelook_ecommerce.users to get a
breakdown of order count by age group.
- step: >-
Create a vertical bar plot using the retrieved data,
with one bar per age group.
Tentukan gabungan tabel dengan relationships
Sebagai contoh, Anda dapat menentukan hubungan orders_to_user antara tabel bigquery-public-data.thelook_ecommerce.orders dan tabel bigquery-public-data.thelook_ecommerce.users sebagai berikut:
- relationships:
- relationship:
- name: orders_to_user
- description: >-
Connects customer order data to user information with the user_id and id fields to allow an aggregated view of sales by customer demographics.
- relationship_type: many-to-one
- join_type: left
- left_table: bigquery-public-data.thelook_ecommerce.orders
- right_table: bigquery-public-data.thelook_ecommerce.users
- relationship_columns:
- left_column: user_id
- right_column: id
Menjelaskan istilah bisnis dengan glossaries
Sebagai contoh, Anda dapat menentukan istilah seperti status bisnis umum dan "OMPF" sesuai dengan konteks bisnis spesifik Anda sebagai berikut:
- glossaries:
- glossary:
- term: complete
- description: Represents an order status where the order has been completed.
- synonyms: 'finish, done, fulfilled'
- glossary:
- term: shipped
- description: Represents an order status where the order has been shipped to the customer.
- glossary:
- term: returned
- description: Represents an order status where the customer has returned the order.
- glossary:
- term: OMPF
- description: Order Management and Product Fulfillment
Sertakan petunjuk lebih lanjut dengan additional_descriptions
Sebagai contoh, Anda dapat menggunakan kunci additional_descriptions untuk memberikan informasi tentang organisasi Anda sebagai berikut:
- additional_descriptions:
- text: All the sales data pertains to The Look, a fictitious ecommerce store.
- text: 'Orders can be of three categories: food, clothes, and electronics.'
Contoh: Petunjuk sistem di BigQuery
Contoh berikut menunjukkan contoh petunjuk sistem untuk agen analis penjualan fiktif sebagai berikut:
- system_instruction: >-
You are an expert sales analyst for a fictitious ecommerce store. You will answer questions about sales, orders, and customer data. Your responses should be concise and data-driven.
- tables:
- table:
- name: bigquery-public-data.thelook_ecommerce.orders
- description: Data for orders in The Look, a fictitious ecommerce store.
- synonyms: sales
- tags: 'sale, order, sales_order'
- fields:
- field:
- name: order_id
- description: The unique identifier for each customer order.
- field:
- name: user_id
- description: The unique identifier for each customer.
- field:
- name: status
- description: The current status of the order.
- sample_values:
- complete
- shipped
- returned
- field:
- name: created_at
- description: >-
The date and time at which the order was created in timestamp
format.
- field:
- name: returned_at
- description: >-
The date and time at which the order was returned in timestamp
format.
- field:
- name: num_of_items
- description: The total number of items in the order.
- aggregations: 'sum, avg'
- field:
- name: earnings
- description: The sales revenue for the order.
- aggregations: 'sum, avg'
- field:
- name: cost
- description: The cost for the items in the order.
- aggregations: 'sum, avg'
- measures:
- measure:
- name: profit
- description: Raw profit (earnings minus cost).
- exp: earnings - cost
- synonyms: gains
- golden_queries:
- golden_query:
- natural_language_query: How many orders are there?
- sql_query: SELECT COUNT(*) FROM sqlgen-testing.thelook_ecommerce.orders
- golden_query:
- natural_language_query: How many orders were shipped?
- sql_query: >-
SELECT COUNT(*) FROM sqlgen-testing.thelook_ecommerce.orders
WHERE status = 'shipped'
- golden_action_plans:
- golden_action_plan:
- natural_language_query: Show me the number of orders broken down by age group.
- action_plan:
- step: >-
Run a SQL query that joins the table
sqlgen-testing.thelook_ecommerce.orders and
sqlgen-testing.thelook_ecommerce.users to get a
breakdown of order count by age group.
- step: >-
Create a vertical bar plot using the retrieved data,
with one bar per age group.
- table:
- name: bigquery-public-data.thelook_ecommerce.users
- description: Data for users in The Look, a fictitious ecommerce store.
- synonyms: customers
- tags: 'user, customer, buyer'
- fields:
- field:
- name: id
- description: The unique identifier for each user.
- field:
- name: first_name
- description: The first name of the user.
- tag: person
- sample_values: 'alex, izumi, nur'
- field:
- name: last_name
- description: The first name of the user.
- tag: person
- sample_values: 'warmer, stilles, smith'
- field:
- name: age_group
- description: The age demographic group of the user.
- sample_values:
- 18-24
- 25-34
- 35-49
- 50+
- field:
- name: email
- description: The email address of the user.
- tag: contact
- sample_values: '222larabrown@gmail.com, cloudysanfrancisco@gmail.com'
- golden_queries:
- golden_query:
- natural_language_query: How many unique customers are there?
- sql_query: >-
SELECT COUNT(DISTINCT id) FROM
bigquery-public-data.thelook_ecommerce.users
- golden_query:
- natural_language_query: How many users in the 25-34 age group have a cymbalgroup email address?
- sql_query: >-
SELECT COUNT(DISTINCT id) FROM
bigquery-public-data.thelook_ecommerce.users WHERE users.age_group =
'25-34' AND users.email LIKE '%@cymbalgroup.com';
- relationships:
- relationship:
- name: orders_to_user
- description: >-
Connects customer order data to user information with the user_id and id fields to allow an aggregated view of sales by customer demographics.
- relationship_type: many-to-one
- join_type: left
- left_table: bigquery-public-data.thelook_ecommerce.orders
- right_table: bigquery-public-data.thelook_ecommerce.users
- relationship_columns:
- left_column: user_id
- right_column: id
- glossaries:
- glossary:
- term: complete
- description: Represents an order status where the order has been completed.
- synonyms: 'finish, done, fulfilled'
- glossary:
- term: shipped
- description: Represents an order status where the order has been shipped to the customer.
- glossary:
- term: returned
- description: Represents an order status where the customer has returned the order.
- glossary:
- term: OMPF
- description: Order Management and Product Fulfillment
- additional_descriptions:
- text: All the sales data pertains to The Look, a fictitious ecommerce store.
- text: 'Orders can be of three categories: food, clothes, and electronics.'