按条件聚合列：主页Uptime Report的数据处理需求

Got it, let's walk through how to aggregate your uptime status data to build that homepage uptime report—these are the most common aggregations you'll need, and I'll cover both SQL (for database-stored data) and Python Pandas (for local/CSV data) since you didn't specify your tool stack.

Your dataset has two key columns: Status (0 = offline, 1 = online) and Duration (seconds in that status). Below are practical ways to aggregate this data for meaningful uptime metrics.

1. Using SQL (For Database-Stored Data) #

If your data lives in a SQL database, GROUP BY and conditional aggregation will handle most of your needs.

a. Calculate Total Duration per Status #

This gives you the total time your homepage was online vs. offline:

SELECT
    Status,
    SUM(Duration) AS total_seconds,
    ROUND(SUM(Duration)/3600, 2) AS total_hours -- Convert to hours for readability
FROM
    your_uptime_table
GROUP BY
    Status;

Sample Output:

b. Compute Uptime/Downtime Percentage #

Get the overall availability of your homepage with this conditional aggregation:

SELECT
    ROUND((SUM(CASE WHEN Status = 1 THEN Duration ELSE 0 END) / SUM(Duration)) * 100, 2) AS uptime_percent,
    ROUND((SUM(CASE WHEN Status = 0 THEN Duration ELSE 0 END) / SUM(Duration)) * 100, 2) AS downtime_percent,
    SUM(Duration) AS total_monitor_seconds
FROM
    your_uptime_table;

Sample Output:

c. Count Status Switch Events (Optional) #

If you want to track how often your homepage flipped between online/offline, use a window function to compare consecutive rows:

WITH status_sequence AS (
    SELECT
        Status,
        Duration,
        LAG(Status) OVER (ORDER BY your_timestamp_column) AS previous_status -- Replace with your time column
    FROM
        your_uptime_table
)
SELECT
    COUNT(*) AS status_switch_count
FROM
    status_sequence
WHERE
    Status != previous_status; -- Count rows where status changed from the previous entry

2. Using Python Pandas (For Local/CSV Data) #

If you're working with a local file (like CSV), Pandas makes aggregation simple.

First, Load Your Data #

import pandas as pd

# Example data (replace with pd.read_csv("your_file.csv") for real data)
data = {
    "Status": [0, 1, 1, 1],
    "Duration": [50, 10, 20, 50]
}
df = pd.DataFrame(data)

a. Aggregate Total Duration per Status #

status_totals = df.groupby("Status")["Duration"].agg(
    total_seconds="sum",
    total_hours=lambda x: round(x.sum()/3600, 2)
).reset_index()

print(status_totals)

Output:
Status total_seconds total_hours
0 0 50 0.01
1 1 80 0.02

b. Calculate Uptime/Downtime Metrics #

total_duration = df["Duration"].sum()
uptime_seconds = df[df["Status"] == 1]["Duration"].sum()

report_summary = pd.DataFrame({
    "Metric": ["Total Monitor Seconds", "Uptime %", "Downtime %"],
    "Value": [
        total_duration,
        round((uptime_seconds / total_duration)*100, 2),
        round(100 - (uptime_seconds / total_duration)*100, 2)
    ]
})

print(report_summary)

Output:
Metric Value
0 Total Monitor Seconds 130.00
1 Uptime % 61.54
2 Downtime % 38.46

c. Count Status Switches (Optional) #

Ensure your data is sorted by time first, then compare consecutive rows:

# Sort by your timestamp column first (uncomment if needed)
# df = df.sort_values("timestamp_column")

# Compare current status to the previous row
df["previous_status"] = df["Status"].shift(1)
switch_count = df[df["Status"] != df["previous_status"]].shape[0]

print(f"Total status switches: {switch_count}")

Output: Total status switches: 1 (matches the example data's single switch from offline to online)

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