azure-application-insights - Kusto - 按周分组，周末结束

Question

我经常遇到这个问题，但还没有弄明白。进行以下查询。我试图将其分为 7 天，但第一个和最后一个桶总是少于 7 天。中间的桶是整周(或 6.23 天，无论如何)。

如何编写可以在结束日期之前进行偏移的查询？此外，如何确保我的开始日期也不会被截断？

requests
| where timestamp > startofday(ago(90d))
        and timestamp < endofday(now()-1d)
| summarize 
        min(timestamp),
        max(timestamp)
        by 
        bin(timestamp, 7d)
| extend duration = max_timestamp - min_timestamp
| project-away timestamp
| order by max_timestamp

Answer 1

您可以使用 bin_at() 指定分箱的引用数据。请参阅下面的示例和文档:https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/binatfunction .

如果相关，您还可以考虑使用 startofweek() 和/或 endofweek()。

range timestamp from startofday(ago(30d)) to endofday(ago(1d)) step 1111ms
| summarize max(timestamp), min(timestamp) by timestamp = bin_at(timestamp, 7d, endofday(ago(1d)))
| extend duration = max_timestamp - min_timestamp
| project-away timestamp
| order by max_timestamp

-->

| max_timestamp               | min_timestamp               | duration           |
|-----------------------------|-----------------------------|--------------------|
| 2020-06-25 23:59:59.6630000 | 2020-06-19 00:00:00.1490000 | 6.23:59:59.5140000 |
| 2020-06-18 23:59:59.0380000 | 2020-06-12 00:00:00.6350000 | 6.23:59:58.4030000 |
| 2020-06-11 23:59:59.5240000 | 2020-06-05 00:00:00.0100000 | 6.23:59:59.5140000 |
| 2020-06-04 23:59:58.8990000 | 2020-05-29 00:00:00.4960000 | 6.23:59:58.4030000 |
| 2020-05-28 23:59:59.3850000 | 2020-05-27 00:00:00.0000000 | 1.23:59:59.3850000 |