adk-samples/java/agents/time-series-forecasting/tools.yaml at main · enginer/adk-samples · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
sources:
  bigquery_forecasting_source:
    kind: "bigquery"
    project: "your-project-id"

tools:
  forecastIowaLiquorSalesTool:
    kind: bigquery-sql
    source: bigquery_forecasting_source
    description: |
      Forecasts future Iowa Liquor Sales using AI.FORECAST on aggregated daily sales data.
    templateParameters:
      - name: horizon
        type: integer
        description: The number of time points to forecast in days.
    statement: |
      SELECT * FROM AI.FORECAST(
        (SELECT date, SUM(bottles_sold) AS total_bottles_sold FROM `bigquery-public-data.iowa_liquor_sales.sales` GROUP BY date),
        data_col => 'total_bottles_sold',
        timestamp_col => 'date',
        horizon => {{.horizon}}
      )

  forecastTotalBikeRidesTool:
    kind: bigquery-sql
    source: bigquery_forecasting_source
    description: |
      Forecasts future total bike rides using AI.FORECAST on aggregated hourly rides data.
    templateParameters:
      - name: horizon
        type: integer
        description: The number of time points to forecast in hours.
    statement: |
      SELECT * FROM AI.FORECAST(
        (SELECT TIMESTAMP_TRUNC(start_date, HOUR) as trip_hour, COUNT(*) AS num_trips
        FROM `bigquery-public-data.san_francisco_bikeshare.bikeshare_trips`
        WHERE subscriber_type = 'Subscriber' AND start_date >= TIMESTAMP('2018-01-01')
        GROUP BY TIMESTAMP_TRUNC(start_date, HOUR)
        ORDER BY TIMESTAMP_TRUNC(start_date, HOUR)
        ),
        data_col => 'num_trips',
        timestamp_col => 'trip_hour',
        horizon => {{.horizon}},
        confidence_level => 0.95
      )

  forecastBikeRidesBySubscriberTypeTool:
    kind: bigquery-sql
    source: bigquery_forecasting_source
    description: |
      Forecasts future bike rides by subscriber type (Subscriber vs. Customer) using AI.FORECAST on aggregated hourly rides data.
    templateParameters:
      - name: horizon
        type: integer
        description: The number of time points to forecast in hours.
    statement: |
      SELECT * FROM AI.FORECAST(
        (SELECT TIMESTAMP_TRUNC(start_date, HOUR) as trip_hour, subscriber_type, COUNT(*) AS num_trips
        FROM `bigquery-public-data.san_francisco_bikeshare.bikeshare_trips`
        WHERE start_date >= TIMESTAMP('2018-01-01')
        GROUP BY TIMESTAMP_TRUNC(start_date, HOUR), subscriber_type),
        data_col => 'num_trips',
        timestamp_col => 'trip_hour',
        horizon => {{.horizon}},
        confidence_level => 0.95,
        id_cols => ['subscriber_type']
      )

  forecastAirQualityTool:
    kind: bigquery-sql
    source: bigquery_forecasting_source
    description: |
      Forecasts future air quality (PM2.5) using AI.FORECAST on aggregated daily air quality data, including temperature and wind speed as regressors.
    parameters:
      - name: city
        type: string
        description: The city to forecast air quality for.
    templateParameters:
      - name: horizon
        type: integer
        description: The number of time points to forecast in days.
    statement: |
      SELECT * FROM AI.FORECAST(
        (SELECT pm25_daily.date AS date, pm25, wind_speed, temperature FROM
          (SELECT avg(arithmetic_mean) AS pm25, date_local AS date FROM `bigquery-public-data.epa_historical_air_quality.pm25_nonfrm_daily_summary` WHERE UPPER(city_name) = UPPER(@city) AND parameter_name = 'Acceptable PM2.5 AQI & Speciation Mass' GROUP BY date_local) pm25_daily
          JOIN (SELECT avg(arithmetic_mean) AS wind_speed, date_local AS date FROM `bigquery-public-data.epa_historical_air_quality.wind_daily_summary` WHERE UPPER(city_name) = UPPER(@city) AND parameter_name = 'Wind Speed - Resultant' GROUP BY date_local) wind_speed_daily USING (date)
          JOIN (SELECT avg(first_max_value) AS temperature, date_local AS date FROM `bigquery-public-data.epa_historical_air_quality.temperature_daily_summary` WHERE UPPER(city_name) = UPPER(@city) AND parameter_name = 'Outdoor Temperature' GROUP BY date_local) temperature_daily USING (date)
        ),
        data_col => 'pm25',
        timestamp_col => 'date',
        horizon => {{.horizon}}
      )

toolsets:
  iowa_liquor_sales_toolset:
    - forecastIowaLiquorSalesTool
  san_francisco_bikeshare_toolset:
    - forecastTotalBikeRidesTool
    - forecastBikeRidesBySubscriberTypeTool
  air_quality_toolset:
    - forecastAirQualityTool