如何在Power Query中基于传感器读数的时间间隔分组日期时间事件？

我刚好处理过类似的大规模传感器数据分组需求，这个问题完全可以在你现有分组逻辑的基础上高效实现，不用从头重构。核心思路是先生成动态的分组标识，再用这个标识来拆分事件，具体步骤如下：

步骤1：确保数据按时间（和传感器ID）排序 #

首先必须保证你的数据是按SensorID（如果有多传感器的话）和ReadingDateTime升序排列的——这是对比前后读数时间的前提。在Power Query里可以用Table.Sort完成：

// 假设你的表名为Source，包含SensorID和ReadingDateTime列
sortedTable = Table.Sort(Source,{{"SensorID", Order.Ascending}, {"ReadingDateTime", Order.Ascending}})

步骤2：计算当前读数与上一次的时间间隔 #

添加一个自定义列，计算当前行和上一行的时间差。这里用Table.AddColumn结合列表操作完成：

withTimeDiff = Table.AddColumn(sortedTable, "TimeSinceLastReading", 
    each if [Index] = 0 then null 
         else Duration.TotalHours([ReadingDateTime] - sortedTable[ReadingDateTime]{[Index]-1}),
    type number
)

注意：第一行的TimeSinceLastReading会是null，因为没有上一行数据，我们后续会把它当作第一个事件的起始。

步骤3：生成动态分组ID（核心高效操作） #

这一步是关键，我们用List.Accumulate来批量生成分组标识——这个函数是基于列表的向量操作，比逐行循环快得多，适合百万级别的大数据量。逻辑是：如果当前行的时间间隔超过2小时，或者传感器ID发生变化，就新建一个组ID，否则继承上一行的组ID：

// 先把需要的列转成列表
sensorList = withTimeDiff[SensorID],
timeDiffList = withTimeDiff[TimeSinceLastReading],
// 生成分组ID列表
groupIDList = List.Accumulate(
    List.Zip({sensorList, timeDiffList}),
    {1}, // 初始值：第一个组ID为1
    (state, current) => 
        let
            lastGroup = List.Last(state),
            lastSensor = List.Last(List.RemoveLastN(sensorList, List.Count(sensorList)-List.PositionOf(sensorList, current{0})-1))?
        in
            if current{1} = null or current{1} > 2 or current{0} <> lastSensor then
                state & {lastGroup + 1}
            else
                state & {lastGroup}
),
// 把分组ID列加回表格
withGroupID = Table.FromColumns(
    Table.ToColumns(withTimeDiff) & {groupIDList},
    Table.ColumnNames(withTimeDiff) & {"EventGroupID"}
)

步骤4：按分组ID聚合起止时间 #

现在你就可以用这个EventGroupID（结合SensorID）来分组，提取每个事件的起止时间和其他需要的字段，完全兼容你现有的分组逻辑：

finalGrouped = Table.Group(withGroupID, {"SensorID", "EventGroupID"}, {
    {"EventStart", each List.Min([ReadingDateTime]), type datetime},
    {"EventEnd", each List.Max([ReadingDateTime]), type datetime},
    // 可以添加其他需要聚合的字段，比如读数的平均值、总数量等
    {"TotalReadings", each Table.RowCount(_), type number}
})

为什么这个方案高效？ #

• 避免了逐行循环的低效操作：List.Accumulate是Power Query中处理批量列表的高效函数，比自定义逐行函数快一个数量级以上；
• 基于已排序的数据操作，不需要重复排序；
• 所有操作都是内存内的向量运算，适合大规模数据集。

关于你提到的“日期分组方案” #

那个方案只适用于固定周期的定期读数，而我们这个方法是动态判断时间间隔——不管你的传感器读数是1小时2分钟、58分钟还是其他不规则间隔，只要和上一次的间隔超过2小时，就会自动拆分新事件，完全适配你的场景。

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