基于T-SQL筛选Azure数据库中特定Unitid和Timestamp的测量数据

Got it, let's work through this T-SQL problem to get exactly the data you need for your Power BI visualizations. Your requirements boil down to prioritizing apparent-power over uncalibrated-current for the same unitid and timestamp, while keeping uncalibrated-current rows where there's no matching apparent-power. We'll also exclude all other datatype entries since you want only the target rows.

Approach 1: Using Window Functions (Most Efficient for Large Datasets) #

Window functions like ROW_NUMBER() are perfect for this kind of priority-based filtering because they let you rank rows within each group (here, each unitid + timestamp pair) and pick the highest-priority one.

WITH ranked_measurements AS (
    SELECT 
        -- Include all columns you need for Power BI (add/remove as needed)
        timestamp,
        unitid,
        datatype,
        value,
        -- Assign a rank: 1 = highest priority (apparent-power), 2 = next (uncalibrated-current)
        ROW_NUMBER() OVER (
            PARTITION BY unitid, timestamp
            ORDER BY 
                CASE datatype 
                    WHEN 'apparent-power' THEN 1
                    WHEN 'uncalibrated-current' THEN 2
                    ELSE 3 -- We'll filter these out later
                END
        ) AS row_rank
    FROM your_measurement_table
    -- Pre-filter to only target datatypes to reduce processing load
    WHERE datatype IN ('apparent-power', 'uncalibrated-current')
)
-- Keep only the highest-priority row per unitid + timestamp
SELECT 
    timestamp,
    unitid,
    datatype,
    value
FROM ranked_measurements
WHERE row_rank = 1;

How This Works: #

• The CTE (ranked_measurements) first narrows down to only your two target datatypes, which speeds up processing.
• ROW_NUMBER() groups rows by unitid and timestamp, then ranks them: apparent-power gets rank 1, uncalibrated-current gets rank 2.
• Finally, we select only rows with rank 1—so for any unitid + timestamp pair that has both datatypes, only apparent-power is kept. For pairs with only uncalibrated-current, that row is kept (since it's the only one, rank 1).

Approach 2: Using EXISTS (Simple, No Window Functions) #

If you prefer a more straightforward approach without CTEs or window functions, this EXISTS-based query works just as well:

SELECT 
    timestamp,
    unitid,
    datatype,
    value
FROM your_measurement_table t
WHERE 
    -- Keep all apparent-power rows
    datatype = 'apparent-power'
    -- OR keep uncalibrated-current rows only if no apparent-power exists for the same unitid + timestamp
    OR (
        datatype = 'uncalibrated-current'
        AND NOT EXISTS (
            SELECT 1
            FROM your_measurement_table t2
            WHERE 
                t2.unitid = t.unitid
                AND t2.timestamp = t.timestamp
                AND t2.datatype = 'apparent-power'
        )
    );

How This Works: #

• The first condition selects every apparent-power row directly.
• The second condition selects uncalibrated-current rows only when there's no matching apparent-power row for the same unitid and timestamp.
• This automatically excludes any other datatype entries, since the WHERE clause only targets your two desired types.

Key Notes: #

• Replace your_measurement_table with your actual table name.
• Adjust the selected columns (like value) to include all the data you need for your Power BI reports.
• If your timestamp column has high precision (e.g., milliseconds), you might want to cast it to a coarser type (e.g., CAST(timestamp AS DATETIME2(0))) in the PARTITION BY or WHERE clauses to avoid grouping by tiny time differences that shouldn't be separate.
• Both approaches are fully supported in Azure SQL Database and will perform well even with large datasets.

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