FROM Clause

Use from when you already computed something in a helper query (with block) and want to summarize or reshape those results — without going back to raw factory data.

Typical uses:

  • Average hourly totals into a daily number
  • Find the best or worst day from daily values
  • Apply window calculations after grouping (see Window Functions)

Syntax

WITH <helper_name> AS (
    <first query>
)
AGGREGATE <expressions using helper_name.column> as <column name>
FROM <helper_name>
[GROUP BY <column name>, bucket <duration> as <time column name>]
  • The with query runs first and produces a table of results.
  • The outer query reads that table with from.
  • Reference helper columns as helper_name.column_name.

Example 1: Daily average from hourly totals

First sum linespeed each hour per line, then average those hourly sums:

with hourly_totals as (
    aggregate
        sum(metric_group('linespeed_uuid')) as total
    where time from now()-7D to now()
    group by line.name as linename, bucket 1h as hour
)
aggregate
    avg(hourly_totals.total) as avg_hourly_total
from hourly_totals
group by linename

Example 2: Change time bucket size

The helper query uses one-hour buckets; the outer query rolls them up to days:

with hourly_totals as (
    aggregate
        sum(metric_group('linespeed_uuid')) as total
    where time from now()-7D to now()
    group by line.name as linename, bucket 1h as hour
)
aggregate
    avg(hourly_totals.total) as daily_avg
from hourly_totals
group by linename, bucket 1D as day

Example 3: Best, worst, and average day

Apply min, max, and avg to daily values already stored in a helper query:

with daily_totals as (
    aggregate
        sum(metric_group('performance_uuid')) as perf
    where time from now()-30D to now()
    group by line.name as linename, bucket 1D as day
)
aggregate
    min(daily_totals.perf) as worst_day,
    max(daily_totals.perf) as best_day,
    avg(daily_totals.perf) as average
from daily_totals
group by linename

Key points

  • from only works with a table defined in a with block.
  • Name columns in the helper query with as so the outer query stays readable.
  • The outer query often does not need its own where time from ... — it inherits the time range from the helper query.
  • To compare two different time windows side by side in one row, you may need join instead — see JOIN Clause.