KQL如何排除首尾不完整分箱？如何用移动平均替代分箱统计？

一、优化首尾分箱不完整的更优方法 #

1. 精确对齐分箱时间边界 #

通过对齐到周起始/结束时间，确保所有分箱都是完整的7天周期：

let start_time = startofweek(ago(90d));  // 将起始时间对齐到90天前的周开始
let end_time = startofweek(now());       // 将结束时间对齐到当前周开始，排除不完整的当前周
AppRequests
| where AppRoleName contains '-apim'
| where TimeGenerated between (start_time .. end_time)
| summarize count() by bin(TimeGenerated, 7d)
| render timechart

2. 用make-series补全空分箱并过滤不完整周期 #

make-series会自动补全无数据的分箱，再通过计算分箱实际时长过滤不完整的周期：

AppRequests
| where AppRoleName contains '-apim'
| where TimeGenerated > ago(90d)
| make-series request_count = count() on TimeGenerated from ago(90d) to now() step 7d
| mv-expand TimeGenerated, request_count
| extend bin_actual_days = datetime_diff('day', next(TimeGenerated, 1), TimeGenerated)
| where bin_actual_days == 7  // 仅保留完整7天的分箱
| project TimeGenerated, request_count
| render timechart

3. 动态计算过滤规则 #

避免硬编码天数，根据分箱大小动态排除首尾不完整的分箱：

let bin_size = 7d;
AppRequests
| where AppRoleName contains '-apim'
| where TimeGenerated > ago(90d)
| summarize count() by bin(TimeGenerated, bin_size)
| where TimeGenerated >= startofweek(ago(90d)) 
  and TimeGenerated <= now() - bin_size  // 自动排除最后一个不完整分箱
| render timechart

二、改为移动平均统计的实现方式 #

方法1：make-series + series_moving_avg #

先按天聚合数据，再计算7天滚动平均：

AppRequests
| where AppRoleName contains '-apim'
| where TimeGenerated > ago(90d)
| make-series daily_count = count() on TimeGenerated from ago(90d) to now() step 1d
| extend weekly_moving_avg = series_moving_avg(daily_count, 7, true)  // true表示窗口中心对齐
| mv-expand TimeGenerated, weekly_moving_avg
| project TimeGenerated, weekly_moving_avg
| render timechart

方法2：使用rolling函数（Kusto新版本支持） #

基于日聚合结果直接计算7天滚动平均：

AppRequests
| where AppRoleName contains '-apim'
| where TimeGenerated > ago(90d)
| summarize daily_count = count() by bin(TimeGenerated, 1d)
| rolling avg(daily_count) over (TimeGenerated between 6d ago and now)  // 滚动窗口覆盖7天
| project TimeGenerated, weekly_moving_avg = avg_daily_count
| render timechart

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