InfluxDB如何统计字段中各唯一值的出现次数？

Great question—dealing with high-cardinality categorical fields like URL paths in InfluxDB is tricky because turning them into tags can tank performance, but grouping by fields isn't straightforward in older versions. Let's break down why your initial query failed and the best ways to fix this.

Why Your Initial Query Didn't Work #

In InfluxDB 1.x, the query engine doesn't allow grouping by fields—only tags and time. Your query SELECT count(path) AS path FROM "line" GROUP BY path tries to group by path, which is a field, so it gets rejected. Even if it were allowed, grouping by a high-cardinality field directly in 1.x would be inefficient.

Best Solution: Use Flux (InfluxDB 2.x or 1.8+) #

Flux, InfluxDB's newer query language, is designed to handle flexible grouping including fields, making it perfect for this use case. Here's a query that will give you exactly the output you want (time, path, count):

// Replace "your-bucket" with your actual bucket name
from(bucket: "your-bucket")
  // Set your desired time range (adjust start/stop as needed)
  |> range(start: 2015-08-18T00:00:00Z, stop: 2015-08-19T00:00:00Z)
  // Filter for your measurement "line"
  |> filter(fn: (r) => r._measurement == "line")
  // Filter to only include the "path" field
  |> filter(fn: (r) => r._field == "path")
  // Aggregate counts into your desired time window (e.g., 1 hour)
  |> aggregateWindow(every: 1h, fn: count, column: "count")
  // Group by both time and the path value
  |> group(columns: ["_time", "_value"])
  // Keep only the columns we care about
  |> keep(columns: ["_time", "_value", "count"])
  // Rename "_value" to "path" for clarity
  |> rename(columns: {_value: "path"})

How This Works: #

• range() sets the time window you want to analyze.
• filter() narrows down to your measurement and field.
• aggregateWindow() counts occurrences of each path within your chosen time interval (e.g., hourly).
• group(columns: ["_time", "_value"]) groups the results by both time and the path value, so you get a count per path per time window.
• Finally, we clean up the column names to match your desired output format.

Workaround for InfluxDB 1.x (No Flux) #

If you're stuck on InfluxDB 1.x without Flux support, options are more limited:

• Top N Paths: Use the TOP() function to get the most frequent paths in each time window:

  SELECT TOP(path, 10) FROM "line" GROUP BY time(1h)
  

  This gives you the top 10 paths per hour, but not all unique paths.
• Explicit Path Counts: If you have a fixed set of common paths, you can query counts for each individually:

  SELECT COUNT(*) AS "/" FROM "line" WHERE path = '/' GROUP BY time(1h),
         COUNT(*) AS "/api" FROM "line" WHERE path = '/api' GROUP BY time(1h)
  

  This is not scalable for dynamic, unknown paths, but works if you only care about specific routes.

Final Recommendation #

Flux is by far the best approach here—it handles high-cardinality fields gracefully without forcing you to use tags, and gives you the full flexibility to group and aggregate exactly how you need. If you're still on 1.x, consider upgrading to 1.8+ (which supports Flux) or 2.x to take advantage of this functionality.

内容的提问来源于stack exchange，提问作者L. Batalha