PostgreSQL时间序列数据：符合特定数值条件的Subject查询及时长计算

Alright, let's tackle this problem step by step. First, let's confirm the requirements clearly:
We need to identify SubjectIDs where:

• There’s at least one pair of consecutive records with Value < 60 (we only need the first such pair)
• After that low-value pair, there’s at least one pair of consecutive records with Value > 60
• Calculate the time interval between the first record of the low pair and the second record of the high pair

The Query #

WITH ranked_subject_records AS (
    -- Assign sequential row numbers to each record per subject, ordered by timestamp
    SELECT 
        SubjectID,
        Value,
        TimeStamp,
        ROW_NUMBER() OVER (PARTITION BY SubjectID ORDER BY TimeStamp) AS record_rank
    FROM your_time_series_table -- Replace with your actual table name
),
first_low_pair AS (
    -- Find the first occurrence of two consecutive low values (<60) per subject
    SELECT 
        r1.SubjectID,
        r1.TimeStamp AS first_low_timestamp,
        r2.record_rank AS low_pair_end_rank
    FROM ranked_subject_records r1
    JOIN ranked_subject_records r2 
        ON r1.SubjectID = r2.SubjectID 
        AND r2.record_rank = r1.record_rank + 1
    WHERE r1.Value < 60 
        AND r2.Value < 60
    -- Keep only the first low pair for each subject
    QUALIFY ROW_NUMBER() OVER (PARTITION BY SubjectID ORDER BY r1.record_rank) = 1
),
first_high_pair_after_low AS (
    -- Find the first consecutive high values (>60) that come after the low pair
    SELECT 
        flp.SubjectID,
        flp.first_low_timestamp,
        r2.TimeStamp AS second_high_timestamp
    FROM first_low_pair flp
    JOIN ranked_subject_records r1 
        ON flp.SubjectID = r1.SubjectID 
        AND r1.record_rank > flp.low_pair_end_rank
    JOIN ranked_subject_records r2 
        ON r1.SubjectID = r2.SubjectID 
        AND r2.record_rank = r1.record_rank + 1
    WHERE r1.Value > 60 
        AND r2.Value > 60
    -- Keep only the first high pair after the low pair for each subject
    QUALIFY ROW_NUMBER() OVER (PARTITION BY flp.SubjectID ORDER BY r1.record_rank) = 1
)
-- Final output matching your desired format
SELECT 
    SubjectID,
    1 AS DidEventOccur,
    (second_high_timestamp - first_low_timestamp)::INTERVAL AS Duration
FROM first_high_pair_after_low
ORDER BY SubjectID;

How It Works #

Let's break down each part of the query:

• ranked_subject_records: We add a row number to each record grouped by SubjectID, ordered by TimeStamp. This makes it easy to find consecutive records by checking adjacent row numbers.
• first_low_pair: We join each record with the next one (using row numbers) to find pairs where both values are below 60. The QUALIFY clause ensures we only keep the first such pair per subject, capturing the timestamp of the first low record and the row number of the second low record.
• first_high_pair_after_low: We join the low pair results with records that come after the low pair, then look for the first consecutive pair of values above 60. We capture the timestamp of the second high record here.
• Final Select: We calculate the time interval between the first low and second high timestamp, format it as an interval (which will automatically show as HH:MI:SS like your example), and output the required columns.

Quick Notes #

• Replace your_time_series_table with the actual name of your time series table.
• The QUALIFY clause requires PostgreSQL 13 or newer. If you're using an older version, you can rewrite the CTEs with subqueries that filter on ROW_NUMBER() = 1.
• The interval output will match the format you provided (e.g., 03:15:00 for Subject 1, 01:00:00 for Subject 2).

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