# Subquery Patterns And Union

> Use OPAL subquery syntax (@labels) and union operations to combine multiple datasets or time periods. Essential for period-over-period comparisons, multi-dataset analysis, and complex data transformations. Covers @label <- @ syntax, timeshift for temporal shifts, union for combining results, and any_not_null() for collapsing grouped data.

- Skill: `majiayu000/subquery-patterns-and-union` (Agent Skill, multi-file: 2 files)
- Install (CLI): `npx skillmds add majiayu000/subquery-patterns-and-union`
- Raw SKILL.md: https://api.skillmd.com/api/skills/majiayu000/subquery-patterns-and-union/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: AI & ML
- Author: majiayu000 (https://skillmd.com/u/majiayu000)
- Updated: 2026-09-09
- Page: https://skillmd.com/skills/majiayu000/subquery-patterns-and-union

---


# Subquery Patterns and Union Operations

## Overview

OPAL subqueries using `@label` syntax enable powerful multi-dataset and multi-period analysis. This skill covers:
- Subquery syntax with `@label <- @dataset`
- Union operations to combine multiple result sets
- Timeshift for period-over-period comparisons
- Best practices for complex data transformations

## When to Use This Skill

Use subqueries and union when you need to:
- **Period-over-period comparison**: Compare current vs previous hour/day/week (metrics or events)
- **Time-series comparison**: Chart trends over time with period-over-period data
- **Complex transformations**: Build intermediate results for multi-stage calculations (e.g., SLO tracking, error budgets)

## Core Concepts

### Subquery Syntax

```opal
# Basic pattern
@label <- @dataset_reference {
    # OPAL pipeline
}

# Reference primary input
@current <- @ {
    # Process primary dataset
}

# Reference named dataset (requires dataset_aliases parameter)
@other <- @dataset_name {
    # Process other dataset
}

# Use subquery results
<- @label {
    # Continue processing
}
```

**Key Points**:
- `@` alone references the primary input dataset
- `@dataset_name` references a named dataset via aliases
- `@label` creates a reusable intermediate result
- `<- @label` continues the pipeline from that subquery

### Union Operation

Union combines multiple result sets with matching columns:

```opal
@set_a <- @ {
    # First result set
}

@set_b <- @ {
    # Second result set with same column structure
}

<- @set_a {
    union @set_b
    # Combined results
}
```

**Important**:
- Union requires matching column names
- Rows from both sources appear in output
- Use `any_not_null()` to collapse sparse union results
- Union happens AFTER aggregation, not before

### Timeshift Verb

Timeshift moves row timestamps forward (positive) or backward (negative):

```opal
timeshift 1h          # Move 1 hour forward
timeshift -1d         # Move 1 day backward
timeshift 30m         # Move 30 minutes forward
```

**Critical Rule**: Apply timeshift BEFORE align when working with metrics!

```opal
# CORRECT
timeshift 1h
align rate:sum(m("metric"))

# WRONG
align rate:sum(m("metric"))
timeshift 1h  # Too late! Align already processed time buckets
```

## Pattern 1: Period-Over-Period Comparison (Metrics)

**Use Case**: Compare current metrics to previous period (hour, day, week)

**Strategy**:
1. Create `@current` subquery with current period aggregation
2. Create `@previous` subquery with timeshift + same aggregation
3. Union both, then collapse with `any_not_null()`
4. Calculate change and percentage change

### Example: Compare Current Hour vs Previous Hour

```opal
# Current period (last 1h)
@current <- @ {
    align rate:sum(m("span_call_count_5m"))
    aggregate current_sum:sum(rate), group_by(service_name)
}

# Previous period (1h before that)
@previous <- @ {
    timeshift 1h                        # Shift BEFORE align!
    align rate:sum(m("span_call_count_5m"))
    aggregate prev_sum:sum(rate), group_by(service_name)
}

# Combine both periods
@combined <- @current {
    union @previous
    aggregate current:any_not_null(current_sum),
              previous:any_not_null(prev_sum),
              group_by(service_name)
}

# Calculate changes
<- @combined {
    make_col change:current - previous
    make_col pct_change:if(previous > 0, (change / previous) * 100, 0)
    make_col abs_pct_change:if(pct_change < 0, -pct_change, pct_change)
    filter abs_pct_change > 50          # Show only significant changes
    sort desc(abs_pct_change)
}
```

**Why This Works**:
- `timeshift 1h` moves the "previous" data timestamps forward by 1 hour
- When combined with current data, both align to same time buckets
- `any_not_null()` picks the non-null value from each period
- Result: side-by-side comparison in same row

**Sample Output**:
```
service_name        current  previous  change    pct_change
frontend-service    45000    15000     30000     200.0
checkout-api        8000     15000     -7000     -46.7
payment-service     500      10000     -9500     -95.0
```

### Day-Over-Day Comparison

```opal
@today <- @ {
    align rate:sum(m("span_call_count_5m"))
    aggregate today_sum:sum(rate), group_by(service_name)
}

@yesterday <- @ {
    timeshift 1d                        # 24 hours
    align rate:sum(m("span_call_count_5m"))
    aggregate yesterday_sum:sum(rate), group_by(service_name)
}

@combined <- @today {
    union @yesterday
    aggregate today:any_not_null(today_sum),
              yesterday:any_not_null(yesterday_sum),
              group_by(service_name)
}

<- @combined {
    make_col change:today - yesterday
    make_col pct_change:if(yesterday > 0, (change / yesterday) * 100, 0)
    sort desc(pct_change)
}
```

### Week-Over-Week Comparison

```opal
@this_week <- @ {
    align 1h, rate:sum(m("span_call_count_5m"))
    aggregate week_sum:sum(rate), group_by(service_name)
}

@last_week <- @ {
    timeshift 7d                        # One week
    align 1h, rate:sum(m("span_call_count_5m"))
    aggregate last_week_sum:sum(rate), group_by(service_name)
}

@combined <- @this_week {
    union @last_week
    aggregate this_week:any_not_null(week_sum),
              last_week:any_not_null(last_week_sum),
              group_by(service_name)
}

<- @combined {
    make_col growth:this_week - last_week
    make_col growth_pct:if(last_week > 0, (growth / last_week) * 100, 0)
    sort desc(growth_pct)
}
```

## Pattern 2: Period-Over-Period Comparison (Events/Intervals)

**Use Case**: Compare raw event/span counts across time periods

**Strategy**: Same union pattern, but use `statsby` instead of `align` + `aggregate`

### Example: Error Count This Hour vs Last Hour

```opal
@current <- @ {
    filter error = true
    statsby current_errors:count(), group_by(service_name)
}

@previous <- @ {
    timeshift 1h
    filter error = true
    statsby prev_errors:count(), group_by(service_name)
}

@combined <- @current {
    union @previous
    aggregate current:any_not_null(current_errors),
              previous:any_not_null(prev_errors),
              group_by(service_name)
}

<- @combined {
    make_col error_change:current - previous
    make_col pct_change:if(previous > 0, (error_change / previous) * 100, 0)
    filter current > 10                 # Only services with significant errors
    sort desc(current)
}
```

**Key Difference**: Use `statsby` for event datasets, `align` + `aggregate` for metrics.

## Pattern 3: Time-Series Period Comparison

**Use Case**: Chart current vs previous period trends over time

### Example: Current vs Previous Week (Hourly Buckets)

```opal
@current <- @ {
    align 1h, rate:sum(m("span_call_count_5m"))
    aggregate current_rate:sum(rate), group_by(service_name)
}

@previous <- @ {
    timeshift 7d
    align 1h, rate:sum(m("span_call_count_5m"))
    aggregate prev_rate:sum(rate), group_by(service_name)
}

@combined <- @current {
    union @previous
    aggregate current:any_not_null(current_rate),
              previous:any_not_null(prev_rate),
              group_by(service_name, _c_bucket)
}

<- @combined {
    make_col change:current - previous
    make_col pct_change:if(previous > 0, (change / previous) * 100, 0)
}
```

**Output**: Time-series with both periods aligned by bucket, suitable for line charts showing trends over time.

**Note**: This pattern returns multiple rows per service (one per time bucket). For summary comparisons, use Pattern 1 instead.

## Pattern 4: Building Intermediate Results

**Use Case**: Complex calculations requiring multiple steps

### Example: Calculate Error Budget Consumption

```opal
# Step 1: Get total requests and errors
@base <- @ {
    align options(bins: 1), rate:sum(m("span_call_count_5m")),
                             errors:sum(m("span_error_count_5m"))
    aggregate total_requests:sum(rate),
              total_errors:sum(errors),
              group_by(service_name)
}

# Step 2: Calculate SLO metrics
@slo <- @base {
    make_col error_rate:if(total_requests > 0, total_errors / total_requests, 0)
    make_col success_rate:1 - error_rate
    make_col slo_target:0.999           # 99.9% SLO
    make_col error_budget:1 - slo_target
}

# Step 3: Calculate budget consumption
<- @slo {
    make_col budget_consumed:if(error_budget > 0, error_rate / error_budget, 0)
    make_col budget_remaining:1 - budget_consumed
    make_col status:if(budget_consumed > 1, "VIOLATED",
                    if(budget_consumed > 0.8, "WARNING", "HEALTHY"))
    filter total_requests > 1000        # Only services with traffic
    sort desc(budget_consumed)
}
```

**Sample Output**:
```
service_name     total_requests  error_rate  budget_consumed  status
adservice        870             0.0276      27.6             VIOLATED
cartservice      2303            0.0091      9.1              VIOLATED
frontend         15108           0.0016      1.6              VIOLATED
productcatalog   8838            0.0000      0.0              HEALTHY
```

**Why This Works**:
- `@base` subquery aggregates raw metrics (requests + errors)
- `@slo` subquery builds on `@base`, adding calculated SLO fields
- Final stage uses `@slo` results to compute budget status
- Each stage can reference all columns from previous stages

## Understanding any_not_null()

The `any_not_null()` function is crucial for union patterns:

```opal
# After union, you typically have sparse data:
# Row 1: current_sum=100, prev_sum=null
# Row 2: current_sum=null, prev_sum=80

aggregate current:any_not_null(current_sum),
          previous:any_not_null(prev_sum),
          group_by(service_name)

# Result:
# Row 1: current=100, previous=80
```

**How it works**:
- Groups by service_name (or other dimensions)
- For each group, finds any non-null value across all union rows
- Collapses sparse union into single row per group

**Alternative functions**:
- `any()` - Picks arbitrary value (may be null)
- `min()` / `max()` - Numeric min/max (only for numbers)
- `any_not_null()` - Best for union collapse (picks any non-null)

## Common Patterns Summary

| Use Case | Subqueries Needed | Key Verbs |
|----------|-------------------|-----------|
| Period-over-period (metrics) | 2+ | `timeshift`, `align`, `union`, `any_not_null()` |
| Period-over-period (events) | 2+ | `timeshift`, `statsby`, `union`, `any_not_null()` |
| Time-series comparison | 2+ | `timeshift`, `align`, `union`, `any_not_null()`, `group_by(_c_bucket)` |
| Complex calculations | 1-3 | `make_col`, pipeline stages |

**Note**: For A/B comparisons across different filter conditions, use conditional columns with `if()` statements instead of subqueries. For multi-dataset joins, use `lookup` or `join` verbs (see **working-with-resources** skill).

## Troubleshooting

### Issue: "Columns don't match in union"

**Cause**: Union requires exact column name matches

**Solution**: Ensure both subqueries produce same column names

```opal
# WRONG - column names don't match
@a <- @ { aggregate count_a:count() }
@b <- @ { aggregate count_b:count() }
<- @a { union @b }  # Error!

# CORRECT - same column names
@a <- @ { aggregate cnt:count() }
@b <- @ { aggregate cnt:count() }
<- @a { union @b }  # Works!
```

### Issue: "All nulls after any_not_null()"

**Cause**: group_by dimensions don't align across union sources

**Solution**: Verify both subqueries group by same dimensions

```opal
# WRONG - different group_by
@a <- @ { aggregate cnt:count(), group_by(service_name) }
@b <- @ { aggregate cnt:count(), group_by(namespace) }
<- @a { union @b; aggregate total:any_not_null(cnt), group_by(service_name) }
# Result: Nulls (no matching groups)

# CORRECT - same group_by
@a <- @ { aggregate cnt:count(), group_by(service_name) }
@b <- @ { aggregate cnt:count(), group_by(service_name) }
<- @a { union @b; aggregate total:any_not_null(cnt), group_by(service_name) }
```

### Issue: "Timeshift has no effect"

**Cause**: Timeshift applied AFTER align (too late!)

**Solution**: Always timeshift BEFORE align

```opal
# WRONG - timeshift after align
@previous <- @ {
    align rate:sum(m("metric"))
    timeshift 1h                # Too late!
}

# CORRECT - timeshift before align
@previous <- @ {
    timeshift 1h                # First!
    align rate:sum(m("metric"))
}
```

### Issue: "Can't reference @label"

**Cause**: Trying to use label before it's defined

**Solution**: Define subquery first, then reference it

```opal
# WRONG - @combined used before definition
<- @combined { ... }
@combined <- @ { ... }

# CORRECT - define first
@combined <- @ { ... }
<- @combined { ... }
```

## Performance Considerations

### When to Use Subqueries vs Single Query

**Use subqueries when**:
- Period-over-period comparison (timeshift required)
- Building complex intermediate results
- Readability improves significantly

**Avoid subqueries when**:
- Simple A/B comparison (use conditional columns)
- Single metric aggregation
- Performance is critical (subqueries add overhead)

### Optimization Tips

1. **Filter early**: Apply filters in subqueries, not after union
   ```opal
   # GOOD
   @current <- @ {
       filter service_name = "frontend"  # Filter early
       align rate:sum(m("metric"))
   }

   # BAD
   @current <- @ {
       align rate:sum(m("metric"))
   }
   <- @current {
       filter service_name = "frontend"  # Filter late (processes all services)
   }
   ```

2. **Use options(bins: 1) for summaries**: Reduces data volume in union
   ```opal
   @current <- @ {
       align options(bins: 1), rate:sum(m("metric"))  # Single row per service
       aggregate total:sum(rate), group_by(service_name)
   }
   ```

3. **Limit union sources**: Each union source adds processing cost

## Comparison: Subquery Union vs Window Functions

Both can solve period-over-period comparison, but have different tradeoffs:

| Aspect | Subquery + Union | Window(lag) |
|--------|------------------|-------------|
| **Syntax** | More verbose | More concise |
| **Flexibility** | Can compare any time periods | Limited to adjacent rows |
| **Performance** | Processes data twice | Single pass |
| **Time buckets** | Aligns arbitrary periods | Only sequential buckets |
| **Use case** | Day-over-day, week-over-week | Row-to-row change detection |

**Example: Hour-over-hour rate of change**

```opal
# Window approach (simpler for sequential buckets)
align 1h, rate:sum(m("span_call_count_5m"))
| make_col prev_rate:window(lag(rate, 1), group_by(service_name))
| make_col change:rate - prev_rate
| make_col pct:if(prev_rate > 0, (change / prev_rate) * 100, 0)

# Union approach (more flexible, can compare any offset)
@current <- @ {
    align 1h, rate:sum(m("span_call_count_5m"))
    aggregate current:sum(rate), group_by(service_name)
}
@previous <- @ {
    timeshift 1h
    align 1h, rate:sum(m("span_call_count_5m"))
    aggregate prev:sum(rate), group_by(service_name)
}
@combined <- @current {
    union @previous
    aggregate current:any_not_null(current), prev:any_not_null(prev), group_by(service_name)
}
<- @combined {
    make_col change:current - prev
    make_col pct:if(prev > 0, (change / prev) * 100, 0)
}
```

**Recommendation**:
- Use **window(lag)** for simple sequential comparisons (previous hour, previous bucket)
- Use **union + timeshift** for arbitrary period comparisons (same hour yesterday, last week)

## Related Skills

- **window-functions-deep-dive** - Covers window(lag/lead/avg) patterns
- **detecting-anomalies** - Uses both union and window patterns
- **aggregating-gauge-metrics** - Foundation for metric aggregation
- **time-series-analysis** - Time bucketing with timechart

## Key Takeaways

1. **Subquery syntax**: `@label <- @dataset { pipeline }`
2. **Union combines**: Same schema, different filters/time periods
3. **Timeshift before align**: Critical for metric comparisons
4. **any_not_null()**: Collapses sparse union results
5. **Not always best**: Consider conditional columns for simple A/B tests
6. **Performance matters**: Union processes data multiple times

When in doubt about subquery syntax or complex union patterns, use `learn_observe_skill("OPAL subquery")` for official documentation.