InfluxDB Query Mastery: Filter, Aggregate & Statistics in Three Practical Scenarios

1. Quick Recap: Basic Query Framework

The previous article introduced core time-series concepts, InfluxDB installation, data writing, and a basic query for the last hour of CPU data. The foundational command structure uses three pipeline stages:

# Basic query command (recap)
influx query 
'from(bucket:"your-bucket") 
|> range(start:-1h) 
|> filter(fn: (r) => r._measurement == "cpu")'

Remember these three building blocks: from(bucket:"your-bucket"): Specifies the bucket (equivalent to a database in MySQL). Required. |> range(start:-1h): Defines the time range; -1h means the past hour. Time filtering is central to time-series queries. |> filter(fn: (r) => r._measurement == "cpu"): Filters by measurement (table name).

InfluxDB queries chain operations with the pipe operator ( |>), executing left-to-right like a pipeline.

2. Advanced Query Practice: Three Scenarios Covering Most Needs

The three most common query scenarios are: filtering data, time-window aggregation, and computing max/min/mean values. Each scenario includes a complete command with plain-language explanations; just replace your bucket, measurement, and field names.

Scenario 1: Filtering Data — Query Only What You Need

Real-world queries rarely pull all data. Examples: "only server01's CPU data" or "only CPU idle rate" — both are filtering operations.

Filter by Tag (e.g., a Specific Server)

# Query only server01 CPU data
influx query 
'from(bucket:"your-bucket") 
|> range(start:-24h)
|> filter(fn: (r) => r._measurement == "cpu" 
and r.host == "server01")'

Explanation: r.host == "server01" uses the host tag written during data ingestion. If your device tag is named deviceId or meterId, adjust to r.deviceId == "xxx". Multiple conditions are joined with and, e.g., and r._value < 90.

Filter by Field (e.g., CPU Idle Rate Only)

# Query only CPU idle rate (usage_idle)
influx query 
'from(bucket:"your-bucket") 
|> range(start:-24h)
|> filter(fn: (r) => r._measurement == "cpu" 
and r._field == "usage_idle")'

_field

is the field name (e.g., usage_idle), and _value is the field's numeric value (e.g., 95.5). Both are InfluxDB default keywords.

Scenario 2: Time-Window Aggregation — See Trends, Not Raw Points

Querying 24 hours of CPU data may return thousands of rows. Typically you want "hourly average CPU usage" or "daily maximum" — this uses the aggregateWindow() function.

Hourly Aggregation with Mean

# Hourly average CPU idle rate
influx query 
'from(bucket:"your-bucket") 
|> range(start:-24h)
|> filter(fn: (r) => r._measurement == "cpu" 
and r._field == "usage_idle")
|> aggregateWindow(every: 1h, fn: mean, createEmpty: false)'

Parameter details: every: 1h: Aggregation window; can be 1m (per minute), 1d (per day), 30m (per 30 minutes). fn: mean: Aggregation function; mean computes average. Max/min covered next. createEmpty: false: Suppresses empty rows for windows with no data; beginners should include it.

Daily Aggregation with Max

# Daily maximum CPU idle rate (past 7 days)
influx query 
'from(bucket:"your-bucket") 
|> range(start:-7d)
|> filter(fn: (r) => r._measurement == "cpu" 
and r._field == "usage_idle")
|> aggregateWindow(every: 1d, fn: max, createEmpty: false)'

Tip: Match the aggregation window to the time range — 7 days with daily windows, 24 hours with hourly windows — for clearer results.

Scenario 3: Compute Max/Min/Mean — Quickly Find Key Metrics

Common needs: "lowest CPU idle rate in the last 24 hours" or "average memory usage this week". Swap the aggregation function; three common functions: mean: average max: maximum min: minimum

The following command returns hourly mean, max, and min in one query using union() to merge results. Copy and replace the bucket name:

influx query 
'from(bucket:"your-bucket") 
|> range(start:-24h)
|> filter(fn: (r) => r._measurement == "cpu" 
and r._field == "usage_idle")
|> aggregateWindow(every: 1h, fn: mean, createEmpty: false) 
|> yield(name: "平均值")
|> union(tables: [
from(bucket:"your-bucket") |> range(start:-24h) 
|> filter(fn: (r) => r._measurement == "cpu" 
and r._field == "usage_idle") 
|> aggregateWindow(every: 1h, fn: max, createEmpty: false) 
|> yield(name: "最大值"),
from(bucket:"your-bucket") |> range(start:-24h) 
|> filter(fn: (r) => r._measurement == "cpu" 
and r._field == "usage_idle") 
|> aggregateWindow(every: 1h, fn: min, createEmpty: false) 
|> yield(name: "最小值")])'

Beginners need not overthink union() internals; run it, observe the output, and understanding will come with practice.

3. Three Common Beginner Pitfalls

Forgetting to replace bucket name and measurement. Every "your-bucket" must become your actual bucket (e.g., my-bucket), and _measurement must match your table name (e.g., cpu or meter).

Reversing the time range syntax. To query the past hour, write start:-1h; writing start:1h (missing minus sign) returns no data.

Defaulting to SQL habits. InfluxDB uses a pipe-and-function model, not SELECT *. Stick to the templates shown here.

4. Hands-On Exercise (Must Do)

Using the test data from the previous article, fulfill this requirement:

Query the past 24 hours of server01's CPU idle rate (usage_idle), aggregate every 30 minutes, compute the average and maximum for each 30-minute window, and only show rows where the average is less than 95.

Write the command yourself first, then compare with the reference answer:

# Reference answer
influx query 
'from(bucket:"your-bucket") 
|> range(start:-24h)
|> filter(fn: (r) => r._measurement == "cpu" 
and r.host == "server01" 
and r._field == "usage_idle")
|> aggregateWindow(every: 30m, fn: mean, createEmpty: false) 
|> yield(name: "平均值")
|> filter(fn: (r) => r._value < 95)
|> union(tables: [
from(bucket:"your-bucket") |> range(start:-24h) 
|> filter(fn: (r) => r._measurement == "cpu" 
and r.host == "server01" 
and r._field == "usage_idle") 
|> aggregateWindow(every: 30m, fn: max, createEmpty: false) 
|> yield(name: "最大值")])'

Summary

Today we mastered InfluxDB advanced queries — three core tasks: filter data, aggregate by time window, compute max/min/mean. Don't memorize syntax; save the three scenario templates, substitute your bucket, measurement, and field names, run them a few times, and you'll be productive. (Honest truth: every production job starts with copy-paste; some just have stronger fundamentals and adapt faster. Run it first, understand later — no shame.)

Next article: bulk data writing (no more one-by-one commands) and Grafana + InfluxDB integration for professional monitoring dashboards.