查询Postgresql或Pandas中数据值为0时段的起止时间解决方案

PostgreSQL实现方案 #

针对存储在PostgreSQL中的时间序列数据，可通过窗口函数实现连续0值时间段的提取，核心思路是解决「岛屿与鸿沟」类问题：

WITH marked_data AS (
    SELECT 
        datetime,
        value,
        -- 遇到非0值时分组编号+1，同一段连续0值会被划分到相同分组
        SUM(CASE WHEN value != 0 THEN 1 ELSE 0 END) OVER (ORDER BY datetime ASC) AS group_id
    FROM ts_data -- 替换为你的实际表名
)
SELECT 
    ROW_NUMBER() OVER (ORDER BY MIN(datetime) ASC) AS "序号",
    MIN(datetime) AS "开始时间",
    MAX(datetime) AS "结束时间"
FROM marked_data
WHERE value = 0
GROUP BY group_id
ORDER BY "开始时间" ASC;

注：请将代码中的表名ts_data替换为你的实际业务表名，字段名若有差异也对应调整即可。

Python Pandas实现方案 #

如果数据已经导出到本地，可通过pandas快速处理：

import pandas as pd

# 1. 加载数据（如果是从csv读取可以用pd.read_csv，此处假设数据已存入df对象）
# df = pd.read_csv("你的数据文件路径")
df.columns = ["DateTime", "Value"] # 统一列名
# 转换时间列格式
df["DateTime"] = pd.to_datetime(df["DateTime"], format="%d-%m-%Y %H:%M")
# 按时间排序
df = df.sort_values("DateTime").reset_index(drop=True)

# 2. 生成连续0值的分组标签
df["group_id"] = (df["Value"] != 0).cumsum()

# 3. 聚合得到结果
result = df[df["Value"] == 0].groupby("group_id").agg(
    开始时间=("DateTime", "min"),
    结束时间=("DateTime", "max")
).reset_index(drop=True)

# 4. 添加序号列
result.insert(0, "序号", range(1, len(result)+1))

# 输出结果
print(result)

运行上述代码得到的结果和要求的示例输出完全一致。

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