英文:
How to divide my datetime data into series data along with its values in kusto
问题
我有一个包含日期数值和相应状态数值的数据集。
我想要生成缺失的日期数值，每隔一小时，并且附带其状态数值。
在Kusto中是否可以实现这个目标。
我尝试使用make-series生成它，但它只允许在输出中使用默认值，但我需要状态值，而不是默认的某个固定值。
英文:
I have a dataset with date values and and its corresponding status values.
I want to generate missing date values by one hour along with its status values.
Is it possible to do in kusto.
I have tried generating it with make-series but it's allowing only default values in output, but i need status value instead of default some constant value
答案1
得分: 0
datatable(Time:timespan, Status:int)
[
timespan(00:00), 4
,timespan(04:00), 2
,timespan(12:00), 4
,timespan(20:00), 2
]
| order by Time asc
| extend next_Time = coalesce(next(Time) - 1h, Time)
| mv-expand Time = range(Time, next_Time, 1h) to typeof(timespan)
| project-away next_Time
| Time | Status |
|---|---|
| 00:00:00 | 4 |
| 01:00:00 | 4 |
| 02:00:00 | 4 |
| 03:00:00 | 4 |
| 04:00:00 | 2 |
| 05:00:00 | 2 |
| 06:00:00 | 2 |
| 07:00:00 | 2 |
| 08:00:00 | 2 |
| 09:00:00 | 2 |
| 10:00:00 | 2 |
| 11:00:00 | 2 |
| 12:00:00 | 4 |
| 13:00:00 | 4 |
| 14:00:00 | 4 |
| 15:00:00 | 4 |
| 16:00:00 | 4 |
| 17:00:00 | 4 |
| 18:00:00 | 4 |
| 19:00:00 | 4 |
| 20:00:00 | 2 |
<details>
<summary>英文:</summary>
datatable(Time:timespan, Status:int)
[
timespan(00:00), 4
,timespan(04:00), 2
,timespan(12:00), 4
,timespan(20:00), 2
]
| order by Time asc
| extend next_Time = coalesce(next(Time) - 1h, Time)
| mv-expand Time = range(Time, next_Time, 1h) to typeof(timespan)
| project-away next_Time
| Time | Status |
|----------|--------|
| 00:00:00 | 4 |
| 01:00:00 | 4 |
| 02:00:00 | 4 |
| 03:00:00 | 4 |
| 04:00:00 | 2 |
| 05:00:00 | 2 |
| 06:00:00 | 2 |
| 07:00:00 | 2 |
| 08:00:00 | 2 |
| 09:00:00 | 2 |
| 10:00:00 | 2 |
| 11:00:00 | 2 |
| 12:00:00 | 4 |
| 13:00:00 | 4 |
| 14:00:00 | 4 |
| 15:00:00 | 4 |
| 16:00:00 | 4 |
| 17:00:00 | 4 |
| 18:00:00 | 4 |
| 19:00:00 | 4 |
| 20:00:00 | 2 |
[Fiddle][1]
[1]: https://dataexplorer.azure.com/clusters/help/databases/Samples?query=H4sIAAAAAAAAA22P0QrCIBSG7wd7h3Op4MCNXQ16irqLCKenWiwdarVBD5%2061iBS8MDn%20Y6/Sviw2x7Jrrth48PhBqEZbAO/u6bTnubZPs8grOWWcN5wThnUibOV1zOvfnhZ/e%20v%20Lf/kGcvMFahhXaCGAaEkxHi6FEr0KEeE9%20ANKJHJ5FEmJJTKKC8sCTSaN0eBY7hDQUfxwp9nn/J1lksSBS8AT8NaE5kSZZGDNZcUfpCPMW0Km8eIZ%20bMgEAAA==
</details>
# 答案2
**得分**: 0
```plaintext
| Status | Time |
|--------|----------|
| 4 | 00:00:00 |
| 4 | 01:00:00 |
| 4 | 02:00:00 |
| 4 | 03:00:00 |
| 2 | 04:00:00 |
| 2 | 05:00:00 |
| 2 | 06:00:00 |
| 2 | 07:00:00 |
| 2 | 08:00:00 |
| 2 | 09:00:00 |
| 2 | 10:00:00 |
| 2 | 11:00:00 |
| 4 | 12:00:00 |
| 4 | 13:00:00 |
| 4 | 14:00:00 |
| 4 | 15:00:00 |
| 4 | 16:00:00 |
| 4 | 17:00:00 |
| 4 | 18:00:00 |
| 4 | 19:00:00 |
| 2 | 20:00:00 |
英文:
datatable(Time:timespan, Status:int)
[
timespan(00:00), 4
,timespan(04:00), 2
,timespan(12:00), 4
,timespan(20:00), 2
]
| make-series take_any(Status) default=int(null) on Time step 1h
| mv-expand Time to typeof(timespan), series_fill_forward(Status) to typeof(int)
| Status | Time |
|---|---|
| 4 | 00:00:00 |
| 4 | 01:00:00 |
| 4 | 02:00:00 |
| 4 | 03:00:00 |
| 2 | 04:00:00 |
| 2 | 05:00:00 |
| 2 | 06:00:00 |
| 2 | 07:00:00 |
| 2 | 08:00:00 |
| 2 | 09:00:00 |
| 2 | 10:00:00 |
| 2 | 11:00:00 |
| 4 | 12:00:00 |
| 4 | 13:00:00 |
| 4 | 14:00:00 |
| 4 | 15:00:00 |
| 4 | 16:00:00 |
| 4 | 17:00:00 |
| 4 | 18:00:00 |
| 4 | 19:00:00 |
| 2 | 20:00:00 |
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论