分钟级时间序列数据每两分钟提取一次的技术处理需求及表格说明

Got it, let's work through how to extract your time series data every two minutes. Depending on whether you want to keep raw rows from every other minute or aggregate data over two-minute windows, here are practical solutions using common tools:

Assuming your table is named time_series_data, here are two common workflows:

Extract Raw Rows Every Two Minutes #

If you want to keep the exact rows from every other minute (starting with your first timestamp at 14:51), we can use the minute value modulo 2 to filter:

WITH first_minute_context AS (
    SELECT EXTRACT(MINUTE FROM MIN("Date")) % 2 AS target_remainder
    FROM time_series_data
)
SELECT tsd.*
FROM time_series_data tsd
CROSS JOIN first_minute_context fmc
WHERE EXTRACT(MINUTE FROM tsd."Date") % 2 = fmc.target_remainder;

This dynamically uses the first minute's parity (odd/even) to filter, so it works even if your dataset starts on an even minute later on.

Aggregate Data Over Two-Minute Windows #

If you want to compute stats (like average, max, or sum) for each ID across two-minute intervals instead of keeping raw rows:

SELECT 
    ID,
    DATE_TRUNC('minute', "Date") - INTERVAL '1 minute' * (EXTRACT(MINUTE FROM "Date") % 2) AS window_start,
    AVG(Value) AS avg_value,
    MAX(Value) AS max_value,
    MIN(Value) AS min_value
FROM time_series_data
GROUP BY ID, window_start
ORDER BY window_start, ID;

This groups your data into clean two-minute blocks (e.g., 14:51-14:52, 14:53-14:54) and calculates metrics per ID.

For data processing in Python, we can use Pandas to handle filtering or aggregation easily:

Extract Raw Rows Every Two Minutes #

First, convert your Date column to datetime, then filter using minute parity:

import pandas as pd

# Load your data (replace with your actual data source)
df = pd.read_csv('your_data.csv')
df['Date'] = pd.to_datetime(df['Date'])

# Get the parity (odd/even) of the first minute in your dataset
first_minute_parity = df['Date'].dt.minute.iloc[0] % 2

# Filter rows where minute matches the target parity
filtered_df = df[df['Date'].dt.minute % 2 == first_minute_parity]

# View the result
print(filtered_df)

This will keep rows from 14:51, 14:53, 14:55, etc., matching your original start time.

Aggregate Over Two-Minute Windows #

If you need aggregated stats per ID and two-minute window:

# Create a column for two-minute window start times
df['two_minute_window'] = df['Date'].dt.floor('2min')

# Aggregate metrics per ID and window
aggregated_df = df.groupby(['ID', 'two_minute_window']).agg({
    'Value': ['mean', 'max', 'first']  # Pick the aggregations you need
}).reset_index()

# Clean up column names for readability
aggregated_df.columns = ['ID', 'window_start', 'avg_value', 'max_value', 'first_value']

print(aggregated_df)

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