Hierarchy Management

When dealing with hierarchical time series (e.g., region/store/item), you often need to combine multiple ID columns into a single unique_id for forecasting. These functions provide a complete workflow.

Function	Description
ts_validate_separator	Check if separator conflicts with data values
ts_combine_keys	Merge multiple ID columns into unique_id
ts_aggregate_hierarchy	Create aggregated series at all hierarchy levels
ts_split_keys	Split unique_id back into component columns

Showing 4 of 4

---

Workflow Overview

┌─────────────────┐    ┌─────────────────┐    ┌─────────────────┐    ┌─────────────────┐
│  1. Validate    │ -> │  2. Combine     │ -> │  3. Forecast    │ -> │  4. Split       │
│  Separator      │    │  Keys           │    │  (any model)    │    │  Keys           │
└─────────────────┘    └─────────────────┘    └─────────────────┘    └─────────────────┘

---

ts_validate_separator

Checks if a separator character exists in any ID column values before combining keys.

ts_validate_separator(
    source,
    id_col1, [id_col2], [id_col3], [id_col4], [id_col5],
    separator := '|'
)

Parameters:

• source - Source table or subquery name
• id_col1 through id_col5 - ID columns to check (up to 5, at least 1 required)
• separator - The separator character to validate (default: '|')

Returns:

Column	Type	Description
separator	VARCHAR	The separator being validated
is_valid	BOOLEAN	True if separator is safe to use
n_conflicts	INTEGER	Number of values containing separator
conflicting_values	VARCHAR[]	List of problematic values
message	VARCHAR	Helpful message with alternatives if invalid

Example:

-- Check if pipe separator is safe
SELECT * FROM ts_validate_separator('sales', region_id, store_id, item_id);

-- Check with custom separator
SELECT * FROM ts_validate_separator('sales', region_id, store_id, separator := '::');

---

ts_combine_keys

Merges multiple ID columns into a single unique_id column without creating aggregated series.

ts_combine_keys(
    source,
    date_col,
    value_col,
    id_col1, [id_col2], [id_col3], [id_col4], [id_col5],
    params := MAP{}
)

Parameters:

• source - Source table or subquery name
• date_col - The date/timestamp column
• value_col - The value column to preserve
• id_col1 through id_col5 - ID columns to combine (up to 5, at least 1 required)
• params - Optional MAP of parameters

Returns:

Column	Type	Description
unique_id	VARCHAR	Combined ID (e.g., `'EU
date_col	(input type)	Date column
value_col	(input type)	Value column

Example:

-- Combine region, store, and item into single ID
SELECT * FROM ts_combine_keys(
    'sales',
    sale_date,
    quantity,
    region_id, store_id, item_id
);

-- Result:
-- unique_id        | sale_date  | quantity
-- EU|STORE001|SKU42| 2024-01-01 | 100
-- EU|STORE001|SKU42| 2024-01-02 | 105
-- EU|STORE002|SKU42| 2024-01-01 | 50

---

ts_aggregate_hierarchy

Combines ID columns AND creates aggregated series at all hierarchy levels. Useful for hierarchical reconciliation.

ts_aggregate_hierarchy(
    source,
    date_col,
    value_col,
    id_col1, id_col2, id_col3,
    params := MAP{}
)

Parameters:

• source - Source table or subquery name
• date_col - The date/timestamp column
• value_col - The value column to aggregate
• id_col1, id_col2, id_col3 - ID columns defining hierarchy levels
• params - Optional MAP of parameters

Returns:

Table with aggregated series at each level, identified by unique_id containing aggregation markers.

Example:

-- Create aggregated series at all levels
SELECT * FROM ts_aggregate_hierarchy(
    'sales',
    sale_date,
    quantity,
    region_id, store_id, item_id
);

-- Result includes:
-- EU|STORE001|SKU42    (leaf level)
-- EU|STORE001|_ALL_    (store total)
-- EU|_ALL_|_ALL_       (region total)
-- _ALL_|_ALL_|_ALL_    (grand total)

---

ts_split_keys

Reverses the combination process, splitting unique_id back into component columns after forecasting.

ts_split_keys(
    source,
    id_col,
    date_col,
    value_col,
    separator := '|'
)

Parameters:

• source - Source table or subquery name (typically forecast results)
• id_col - The combined unique_id column to split
• date_col - The date column name
• value_col - The value column name (e.g., forecast values)
• separator - The separator used when combining (default: '|')

Returns:

Column	Type	Description
id_part_1	VARCHAR	First component
id_part_2	VARCHAR	Second component
id_part_3	VARCHAR	Third component
date_col	(input type)	Date column
value_col	(input type)	Value column

Example:

-- Split forecast results back into components
SELECT * FROM ts_split_keys(
    'forecast_results',
    unique_id,
    forecast_date,
    point_forecast
);

-- Result:
-- id_part_1 | id_part_2 | id_part_3 | forecast_date | point_forecast
-- EU        | STORE001  | SKU42     | 2024-02-01    | 110.5
-- EU        | STORE001  | SKU42     | 2024-02-02    | 112.3

---

Complete Workflow Example

-- Step 1: Validate separator won't conflict with data
SELECT * FROM ts_validate_separator('sales', region, store, product);
-- Ensure is_valid = TRUE before proceeding

-- Step 2: Combine keys for forecasting
CREATE TABLE sales_combined AS
SELECT * FROM ts_combine_keys(
    'sales',
    date,
    revenue,
    region, store, product
);

-- Step 3: Generate forecasts
CREATE TABLE forecasts AS
SELECT * FROM anofox_fcst_ts_forecast_by(
    'sales_combined',
    'unique_id',
    'date',
    'revenue',
    'AutoETS',
    28,
    MAP{}
);

-- Step 4: Split keys back for analysis
CREATE TABLE forecasts_detailed AS
SELECT
    id_part_1 AS region,
    id_part_2 AS store,
    id_part_3 AS product,
    forecast_date,
    point_forecast
FROM ts_split_keys(
    'forecasts',
    unique_id,
    forecast_date,
    point_forecast
);

-- Now analyze forecasts by region, store, or product
SELECT region, SUM(point_forecast) AS total_forecast
FROM forecasts_detailed
GROUP BY region;

---

Use Cases

Retail Hierarchy

-- Region > Store > Category > SKU
SELECT * FROM ts_combine_keys(
    'retail_sales',
    sale_date,
    units,
    region, store_id, category, sku
);

Geographic Hierarchy

-- Country > State > City
SELECT * FROM ts_combine_keys(
    'location_data',
    date,
    value,
    country, state, city
);

Product Hierarchy

-- Division > Department > Class > SKU
SELECT * FROM ts_combine_keys(
    'inventory',
    week_date,
    quantity,
    division, department, class_id
);

---

Best Practices

1. Always validate first - Run ts_validate_separator before combining to avoid data corruption

2. Choose unique separator - Use | (pipe) or :: that won't appear in your data

3. Preserve original columns - Keep the original table for reference

4. Document hierarchy - Note the column order used for combining (important for splitting)

5. Consider aggregation - Use ts_aggregate_hierarchy when you need forecasts at multiple levels