使用OPENQUERY查询Historian数据库，识别行中新值的技术问题

Hey there! Let's figure out how to get that desired VALUE column from your Historian data using OPENQUERY. This approach uses window functions to reliably handle those back-and-forth value switches you're concerned about, no messy self-joins required.

First, Let's Break Down the Pattern #

Looking at your input data and desired output, we can spot clear rules for determining each row's VALUE:

• The first row's value is the MIN (since it's the starting state)
• For subsequent rows:
  • If the current row's MIN/MAX range matches the previous row's exactly, we switch to the MIN (since the prior value was the MAX of that range)
  • If the current range's upper bound equals the previous range's lower bound (e.g., previous MIN=1300, current MAX=1300), we take the current MIN (a downward state change)
  • If the current range's lower bound equals the previous range's upper bound (e.g., previous MAX=940, current MIN=940), we take the current MAX (an upward state change)
  • If only the upper bound changes while the lower bound stays the same, we take the new upper bound
  • If only the lower bound changes while the upper bound stays the same, we take the new lower bound

The SQL Implementation #

We'll use the LAG() window function to grab the previous row's MIN and MAX values, then use a CASE statement to apply the rules above. Here's the full query:

WITH ranked_data AS (
    SELECT 
        TS,
        MIN_val = MIN,
        MAX_val = MAX,
        -- Get the MIN and MAX from the immediately preceding row (ordered by timestamp)
        prev_MIN = LAG(MIN) OVER (ORDER BY TS),
        prev_MAX = LAG(MAX) OVER (ORDER BY TS)
    FROM OPENQUERY(YourHistorianLinkedServer, 
                   'SELECT TS, MIN, MAX FROM YourHistorianTable ORDER BY TS')
)
SELECT 
    TS,
    VALUE = CASE
        -- Handle the first row (no previous data)
        WHEN prev_MIN IS NULL THEN MIN_val
        -- Same range as previous row: switch to MIN_val
        WHEN MIN_val = prev_MIN AND MAX_val = prev_MAX THEN MIN_val
        -- Current range's MAX matches previous MIN: downward change, take current MIN
        WHEN MAX_val = prev_MIN THEN MIN_val
        -- Current range's MIN matches previous MAX: upward change, take current MAX
        WHEN MIN_val = prev_MAX THEN MAX_val
        -- Same MIN as previous, new MAX: take the new upper bound
        WHEN MIN_val = prev_MIN THEN MAX_val
        -- Same MAX as previous, new MIN: take the new lower bound
        WHEN MAX_val = prev_MAX THEN MIN_val
        -- Fallback for unexpected edge cases
        ELSE COALESCE(MIN_val, MAX_val)
    END
FROM ranked_data
ORDER BY TS;

Let's Verify This Against Your Data #

Let's walk through each row to confirm it matches your desired output:

1. 16:25: No previous row, so VALUE=1300 ✔️
2. 16:27: Current MAX=1300 matches previous MIN=1300, so VALUE=1150 ✔️
3. 16:31: Current MAX=1150 matches previous MIN=1150, so VALUE=760 ✔️
4. 16:33: Current MIN=760 matches previous MIN=760, new MAX=940, so VALUE=940 ✔️
5. 16:35: Current MIN=940 matches previous MAX=940, so VALUE=1240 ✔️
6. 16:37: Same range as previous row, so switch to MIN=940 ✔️
7. 16:39: Current MAX=940 matches previous MIN=940, so VALUE=849 ✔️

This approach works even when values toggle back and forth (like the 940 ↔ 1240 switch in rows 5-6) because it always compares to the immediate prior state, no matter how many times values swing.

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