按规则为时序表生成Keep列值的技术实现需求

First, let's confirm the retention rules to make sure we're aligned:

• Keep=1 if either:
  1. The time difference between the current row and the last valid (Keep=1) row is greater than 5 minutes, OR
  2. The current row's Value is different from the last valid row's Value.
• Keep=0 otherwise, and future rows will use the last valid row (not this one) as their comparison base.

The key challenge here is that we need to compare each row to the last valid (Keep=1) row, not just the immediate previous row. Below are practical solutions for both SQL (using recursive CTE) and Python (Pandas) since these are the most common environments for this kind of data processing.

SQL Solution (Recursive CTE) #

This approach uses a recursive Common Table Expression to track the last valid row's time and value as we iterate through the sorted data.

WITH RECURSIVE ranked_data AS (
    -- Assign row numbers to ensure we process rows in time order
    SELECT 
        time, 
        value, 
        ROW_NUMBER() OVER (ORDER BY time) AS rn
    FROM your_table_name  -- Replace with your actual table name
),
keep_calculation AS (
    -- Base case: first row is always kept
    SELECT 
        rn, 
        time, 
        value, 
        1 AS keep,
        time AS last_valid_time,
        value AS last_valid_value
    FROM ranked_data
    WHERE rn = 1
    
    UNION ALL
    
    -- Recursive step: compare each row to the last valid row
    SELECT 
        rd.rn,
        rd.time,
        rd.value,
        -- Determine if current row should be kept
        CASE 
            WHEN rd.value != kc.last_valid_value 
                 OR EXTRACT(EPOCH FROM (rd.time - kc.last_valid_time)) > 300  -- 5 minutes = 300 seconds
            THEN 1
            ELSE 0
        END AS keep,
        -- Update last valid time if current row is kept, else retain previous
        CASE 
            WHEN rd.value != kc.last_valid_value 
                 OR EXTRACT(EPOCH FROM (rd.time - kc.last_valid_time)) > 300
            THEN rd.time
            ELSE kc.last_valid_time
        END AS last_valid_time,
        -- Update last valid value if current row is kept, else retain previous
        CASE 
            WHEN rd.value != kc.last_valid_value 
                 OR EXTRACT(EPOCH FROM (rd.time - kc.last_valid_time)) > 300
            THEN rd.value
            ELSE kc.last_valid_value
        END AS last_valid_value
    FROM ranked_data rd
    JOIN keep_calculation kc ON rd.rn = kc.rn + 1
)
-- Final output: select the original columns plus Keep
SELECT time, value, keep
FROM keep_calculation
ORDER BY time;

How It Works: #

1. ranked_data: Assigns a sequential row number to each row ordered by time to ensure we process them in the correct order.
2. keep_calculation:
   • Base Case: The first row is always marked as Keep=1, and we initialize tracking variables for the last valid time and value.
   • Recursive Step: For each subsequent row, we compare it to the last valid row (from the previous iteration). If the value differs or the time difference exceeds 5 minutes, we mark it as Keep=1 and update our tracking variables. Otherwise, we mark it as Keep=0 and keep the last valid variables unchanged.

Python Pandas Solution #

If you're working with data in a Pandas DataFrame, you can iterate through the rows while tracking the last valid entry:

import pandas as pd

# Example data (replace with your actual data)
df = pd.DataFrame({
    'Time': ['11:34', '11:35', '11:40', '11:40', '11:41', '11:43', '11:44', '11:50'],
    'Value': [150, 150, 150, 151, 151, 152, 152, 152]
})

# Convert Time column to datetime for accurate time difference calculation
df['Time'] = pd.to_datetime(df['Time'], format='%H:%M')

# Initialize Keep column and set first row to 1
df['Keep'] = 0
df.loc[0, 'Keep'] = 1

# Track the last valid time and value
last_valid_time = df.loc[0, 'Time']
last_valid_value = df.loc[0, 'Value']

# Iterate through remaining rows to calculate Keep values
for idx in range(1, len(df)):
    current_time = df.loc[idx, 'Time']
    current_value = df.loc[idx, 'Value']
    
    # Calculate time difference in seconds
    time_diff_seconds = (current_time - last_valid_time).total_seconds()
    
    # Apply the retention rules
    if current_value != last_valid_value or time_diff_seconds > 300:
        df.loc[idx, 'Keep'] = 1
        # Update last valid tracking variables
        last_valid_time = current_time
        last_valid_value = current_value
    else:
        df.loc[idx, 'Keep'] = 0

# Optional: Convert Time back to string format if needed
df['Time'] = df['Time'].dt.strftime('%H:%M')

print(df)

Output: #

Time  Value  Keep
0  11:34    150     1
1  11:35    150     0
2  11:40    150     1
3  11:40    151     1
4  11:41    151     0
5  11:43    152     1
6  11:44    152     0
7  11:50    152     1

This matches exactly the example you provided.

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