DatabricksでAI Forecastを利用したダッシュボードを作成してみた

CREATE OR REPLACE TABLE workspace.forecast_demo.us_retail_sales_monthly AS
WITH clean AS (
  SELECT
    TO_DATE(`date`) AS sales_month,
    CAST(RSAFS AS DOUBLE) AS retail_sales_million_usd
  FROM workspace.forecast_demo.fred_retail_sales_raw
)
SELECT
  sales_month,
  retail_sales_million_usd,
  retail_sales_million_usd / 1000.0 AS retail_sales_billion_usd,
  YEAR(sales_month) AS sales_year,
  MONTH(sales_month) AS sales_month_num,
  DATE_FORMAT(sales_month, 'MMM') AS sales_month_name,
  ROUND(
    (
      retail_sales_million_usd
      / LAG(retail_sales_million_usd) OVER (ORDER BY sales_month)
      - 1
    ) * 100,
    2
  ) AS mom_pct,
  ROUND(
    (
      retail_sales_million_usd
      / LAG(retail_sales_million_usd, 12) OVER (ORDER BY sales_month)
      - 1
    ) * 100,
    2
  ) AS yoy_pct
FROM clean
WHERE retail_sales_million_usd IS NOT NULL;

WITH observed AS (
  SELECT
    sales_month,
    retail_sales_billion_usd
  FROM main.forecast_demo.us_retail_sales_monthly
),
forecast AS (
  SELECT
    sales_month,
    retail_sales_billion_usd_forecast,
    retail_sales_billion_usd_upper,
    retail_sales_billion_usd_lower
  FROM AI_FORECAST(
    TABLE(observed),
    horizon => (
      SELECT ADD_MONTHS(MAX(sales_month), 13)
      FROM observed
    ),
    time_col => 'sales_month',
    value_col => 'retail_sales_billion_usd',
    frequency => 'MS',
    prediction_interval_width => 0.9,
    parameters => '{"global_floor": 0}'
  )
)
SELECT
  sales_month,
  retail_sales_billion_usd AS original,
  CAST(NULL AS DOUBLE) AS prediction,
  CAST(NULL AS DOUBLE) AS prediction_upper,
  CAST(NULL AS DOUBLE) AS prediction_lower
FROM observed

UNION ALL

SELECT
  sales_month,
  CAST(NULL AS DOUBLE) AS original,
  retail_sales_billion_usd_forecast AS prediction,
  retail_sales_billion_usd_upper AS prediction_upper,
  retail_sales_billion_usd_lower AS prediction_lower
FROM forecast;