InfluxDB同一数据库不同measurement按时间戳关联查询方法

Hey there! Glad you're exploring joins in InfluxDB—let's get this sorted for different measurements.

Your existing query for the same measurement is on the right track; the only real change for different measurements is swapping out the single measurement name for your two distinct ones, while keeping the aliases to reference each dataset. InfluxDB's JOIN operation automatically aligns points by their timestamp by default, so as long as your two measurements have matching time points (or you handle gaps appropriately), this will work smoothly.

Example Query for Different Measurements #

Let's say your two measurements are named cpu_load_hosta and cpu_load_hostb (adjust these to your actual measurement names). Here's how to adapt your original query:

SELECT hosta.value + hostb.value 
FROM cpu_load_hosta AS hosta
INNER JOIN cpu_load_hostb AS hostb 
WHERE hosta.host = 'hosta.influxdb.orb' 
  AND hostb.host = 'hostb.influxdb.org'
  AND time >= now() - 1h -- Critical: Always add a time range to avoid full dataset scans

Key Notes to Keep in Mind #

• Timestamp Alignment: InfluxDB's INNER JOIN only returns points where both measurements have an exact timestamp match. If one measurement is missing a point at a given time, that timestamp will be excluded from the results.
• Time Range is Mandatory: Skipping the time filter will force InfluxDB to scan all historical data, which is slow and likely to time out. Always narrow down your query to a relevant window.
• Handling Missing Data: If you want to retain points from one measurement even when the other has no matching timestamp, use a LEFT JOIN and fill missing values (e.g., with 0). Here's how:

  SELECT hosta.value + COALESCE(hostb.value, 0) 
  FROM cpu_load_hosta AS hosta
  LEFT JOIN cpu_load_hostb AS hostb 
  WHERE hosta.host = 'hosta.influxdb.orb' 
    AND hostb.host = 'hostb.influxdb.org'
    AND time >= now() - 1h
  

  The COALESCE function replaces null values (from missing hostb points) with 0 so your calculation doesn't break.

Quick Comparison to Your Original Query #

Your original same-measurement query used cpu_load as hosta inner join cpu_load as hostb—the only difference here is that we're using two unique measurement names (cpu_load_hosta and cpu_load_hostb) instead of repeating the same one. The alias logic and filtering stay identical.

内容的提问来源于stack exchange，提问作者JOY