如何使用make-series为查询添加默认值？补全无数据月份行

要让没有活跃行动项的年月也显示数值0，核心思路是先生成覆盖目标时间范围的完整年月序列，再将你的统计结果与这个序列做左外连接，最后把缺失的数值填充为0。以下是修改后的查询语句：

// 1. 生成完整的年月序列：可自行调整起止时间范围
let date_range = range date from startofyear(datetime(2018-01-01)) to endofyear(now()) step 30d
| summarize by year_month = bin(date, 1d)
| extend yy = datetime_part("Year", year_month), mm = datetime_part("Month", year_month)
| distinct yy, mm;

// 2. 保留原统计逻辑作为子查询
let action_item_stats = ActiveActionItems
| summarize by ActionItemId, Created_Date, Due_Date
| extend Thirty_Days_Past_Due_Date = datetime_diff('day', now(), Due_Date) > 30
| extend yy = datetime_part("Year", Created_Date), mm = datetime_part("Month", Created_Date)
| summarize 
    ['Action Items Past SLA'] = countif(Thirty_Days_Past_Due_Date == true), 
    Total = count() 
by yy, mm;

// 3. 左连接并填充缺失值为0
date_range
| left join action_item_stats on yy, mm
| extend 
    ['Action Items Past SLA'] = coalesce(['Action Items Past SLA'], 0),
    Total = coalesce(Total, 0),
    Month = case(
        mm==1, strcat('Jan/',yy), mm==2, strcat('Feb/',yy), mm==3, strcat('Mar/',yy),
        mm==4, strcat('Apr/',yy), mm==5, strcat('May/',yy), mm==6, strcat('Jun/',yy),
        mm==7, strcat('Jul/',yy), mm==8, strcat('Aug/',yy), mm==9, strcat('Sep/',yy),
        mm==10, strcat('Oct/',yy), mm==11, strcat('Nov/',yy), mm==12, strcat('Dec/',yy), "error"
    ),
    ['Percentage 30 Days Past SLA'] = iif(Total == 0, 0, ['Action Items Past SLA'] * 100.0 / Total)
| order by yy asc, mm asc
| project Month, ['Action Items Past SLA'], ['Total Active Action Items'] = Total, ['Percentage 30 Days Past SLA']

关键修改说明： #

• 完整年月序列：用range函数生成时间范围，提取唯一的年和月，确保所有需要的年月都被包含。你可以修改startofyear(datetime(2018-01-01))和endofyear(now())来调整时间区间。
• 缺失值填充：通过left join关联完整序列与统计结果，用coalesce函数将空值替换为0。
• 百分比容错：用iif判断当总行动项数为0时，直接将百分比设为0，避免除以0的报错。

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