如何用SQL将tableA近3分钟数据与tableB同属性均值对比？

Hey there! Let's work through this problem together. Your goal is to pull the last 3 minutes of data from tableA, then filter records where value is less than the average value of matching name and location entries in tableB. Let's break down what went wrong with your original query and fix it.

What's Wrong with the Original Query? #

Your subquery has an accidental self-join on tableB (inner join tableB tb on ...), which is unnecessary and causes incorrect calculations. You don't need to join tableB to itself—you just need to calculate the average for each name + location pair directly from tableB.

Correct SQL Solutions #

Option 1: Using a Correlated Subquery

This approach keeps the logic straightforward, matching each tableA record to its corresponding average in tableB:

SELECT ta.name, ta.location, ta.value
FROM tableA ta
-- Filter for records from the last 3 minutes
WHERE ta.time_stmp >= CURRENT_TIMESTAMP - INTERVAL '3 MINUTES'
AND ta.value < (
    -- Calculate the average value for the same name and location in tableB
    SELECT AVG(tb.value)
    FROM tableB tb
    WHERE tb.name = ta.name
      AND tb.location = ta.location
);

Option 2: Using a CTE (Common Table Expression) for Better Performance

If you're working with large datasets, precomputing the averages first with a CTE can be more efficient. This avoids recalculating the average for every row in tableA:

WITH tb_averages AS (
    -- Precompute average value per name + location pair in tableB
    SELECT name, location, AVG(value) AS avg_value
    FROM tableB
    GROUP BY name, location
)
SELECT ta.name, ta.location, ta.value
FROM tableA ta
JOIN tb_averages 
    ON ta.name = tb_averages.name 
    AND ta.location = tb_averages.location
WHERE ta.time_stmp >= CURRENT_TIMESTAMP - INTERVAL '3 MINUTES'
  AND ta.value < tb_averages.avg_value;

Handling Non-Standard Timestamp Formats #

Looking at your sample data, time_stmp is formatted like 2019-08-09-13.20 (a string, not a native timestamp type). You'll need to convert this to a timestamp to correctly filter the last 3 minutes. Here's how to adjust the WHERE clause for common databases:

• MySQL:

  WHERE STR_TO_DATE(ta.time_stmp, '%Y-%m-%d-%H.%i') >= DATE_SUB(NOW(), INTERVAL 3 MINUTE)
  

• PostgreSQL:

  WHERE TO_TIMESTAMP(ta.time_stmp, 'YYYY-MM-DD-HH24.MI') >= CURRENT_TIMESTAMP - INTERVAL '3 minutes'
  

Testing with Your Sample Data #

Using your sample values:

• tableB averages are: name1/loc1 = 475, name2/loc2 = 525, name3/loc3 = 435, name4/loc4 = 256, name5/loc5 = 420
• tableA records from the last 3 minutes (all your sample entries qualify) that have value below the average are:
  • name1/loc1 (450 < 475)
  • name5/loc5 (50 < 420)

Both queries above will return these two records correctly.

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