基于SQL计算加速度计数据中每日体位转换次数（含60s阈值）

Got it, let's walk through how to solve this problem step by step—since you're dealing with accelerometer data tracking daily lying/standing durations per minute, and need to count the number of lying-to-standing transitions (defined as lying periods meeting a 60-second threshold) over 24 hours.

First, let's lock down the core logic: we need to identify continuous periods of lying where the total duration adds up to at least 60 seconds, then count how many times these periods end with a switch to standing.

Step 1: Preprocess Minute-by-Minute Data #

Your data gives lying/standing seconds per minute—first, we'll label each minute with its dominant state. You can adjust this rule if your use case has edge cases (like mixed 30/30 seconds):

• If a minute has 60 seconds of lying: mark as 'lying'
• If a minute has 60 seconds of standing: mark as 'standing'
• For mixed minutes: use whichever duration is longer (or ignore them, based on your business rules)

def label_minute_state(minute_entry):
    lying_sec = minute_entry['lying_seconds']
    standing_sec = minute_entry['standing_seconds']
    
    if lying_sec >= 60:
        return 'lying'
    elif standing_sec >= 60:
        return 'standing'
    # Handle mixed states: prioritize whichever is longer
    return 'lying' if lying_sec > standing_sec else 'standing'

Step 2: Track Lying Periods and Count Transitions #

We'll iterate through the daily 24-hour data (1440 minutes total) to track ongoing lying periods. When a period hits the 60-second threshold and switches to standing, we increment our transition count.

def count_lying_standing_transitions(daily_data, threshold=60):
    current_lying_total = 0
    in_lying_period = False
    transition_count = 0
    
    for minute in daily_data:
        state = label_minute_state(minute)
        
        if state == 'lying':
            in_lying_period = True
            current_lying_total += minute['lying_seconds']
        else:
            # Check if we just exited a qualifying lying period
            if in_lying_period and current_lying_total >= threshold:
                transition_count += 1
            # Reset tracking for next potential period
            in_lying_period = False
            current_lying_total = 0
    
    # Optional: Uncomment below if you want to count a final lying period that ends the day
    # if in_lying_period and current_lying_total >= threshold:
    #     transition_count += 1
    
    return transition_count

Step 3: Test with Sample Data #

Let's use a simplified sample to see how this works:

# Example: 7 minutes of data for a single individual
sample_daily_data = [
    {'timestamp': '2024-05-20 00:00', 'lying_seconds': 60, 'standing_seconds': 0},
    {'timestamp': '2024-05-20 00:01', 'lying_seconds': 60, 'standing_seconds': 0},
    {'timestamp': '2024-05-20 00:02', 'lying_seconds': 0, 'standing_seconds': 60},
    {'timestamp': '2024-05-20 00:03', 'lying_seconds': 30, 'standing_seconds': 30},
    {'timestamp': '2024-05-20 00:04', 'lying_seconds': 60, 'standing_seconds': 0},
    {'timestamp': '2024-05-20 00:05', 'lying_seconds': 60, 'standing_seconds': 0},
    {'timestamp': '2024-05-20 00:06', 'lying_seconds': 0, 'standing_seconds': 60},
]

# Output should be 2 (two qualifying lying periods ending in standing)
print(count_lying_standing_transitions(sample_daily_data))

Key Notes to Adjust for Your Use Case #

• Mixed state handling: If your data has lots of partial lying/standing minutes, refine the label_minute_state function to match your exact business rules (e.g., only count minutes with ≥40 seconds lying as part of a period).
• Threshold flexibility: If you ever need to adjust the 60-second threshold, just pass a different value to the count_lying_standing_transitions function.
• Cross-day data: Make sure your daily datasets are strictly 24 hours (1440 minutes) to avoid splitting periods across days.

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