如何在Kusto中动态生成最近7天的每日统计列？

核心思路 #

通过bin()按天分组统计，再用pivot()将日期维度转为横向列，彻底摆脱手动编写countif的繁琐，支持动态调整统计天数。

方案一：日期列名直观版 #

此版本自动生成日期格式的列名（如2024-05-20），清晰易懂，维护成本最低：

let usg_events = dynamic(["click_event"]);
let stats_days = 7; // 修改此处调整统计天数
customEvents
| where timestamp > ago(stats_days + 1d) and isempty(operation_SyntheticSource)
| extend name = replace("\n", "", name)
| where '*' in (usg_events) or name in (usg_events)
| extend dimension = tostring(customDimensions["click_event"])
| where dimension != ""
| extend date = bin(timestamp, 1d) // 按天对齐时间戳
| summarize daily_count = count() by dimension, date
| pivot(date, sum(daily_count)) // 将日期转为横向统计列
| order by dimension asc

方案二：兼容原列名版 #

如果需要和原查询的列名（count_24h、count_y、count_2等）保持一致，可通过计算天数偏移来生成对应列名：

let usg_events = dynamic(["click_event"]);
let stats_days = 7; // 修改此处调整统计天数
customEvents
| where timestamp > ago(stats_days + 1d) and isempty(operation_SyntheticSource)
| extend name = replace("\n", "", name)
| where '*' in (usg_events) or name in (usg_events)
| extend dimension = tostring(customDimensions["click_event"])
| where dimension != ""
| // 计算当前时间与事件时间的天数偏移，0=最近24h，1=昨天，以此类推
| extend day_offset = floor((now() - timestamp) / 1d)
| where day_offset < stats_days
| // 生成匹配原查询的列名
| extend col_name = case(
    day_offset == 0, "count_24h",
    day_offset == 1, "count_y",
    strcat("count_", tostring(day_offset))
)
| summarize daily_count = count() by dimension, col_name
| pivot(col_name, sum(daily_count))
| order by dimension asc

优势说明 #

• 仅需修改stats_days变量即可扩展统计天数，无需手动添加任何countif条件
• 逻辑简洁，避免重复代码，降低维护出错概率
• 方案一的日期列名更直观，方案二兼容原有报表格式，可按需选择

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