英文:
display column even when. the count is zero/does not appear
问题
我试图根据事件ID（EID）总结一些事件，但结果只显示eve_1，而不显示eve_2，因为（根据我的数据）eve_2的计数为0。
在这种情况下，如何能够显示eve_2，即使它的计数为0？
如何确保无论计数是多少，都能显示eve_1和eve_2？
更新：我意识到我的问题表述可能有误。我要找的是，我试图为没有计数的任何列设置默认值（0），如果有计数，应该像通常一样显示。
所以，类似于这样：
| Date_time | Evet_cat | count_ |
|---|---|---|
| 2023-02-24 13 | eve_1 | 10 |
| 2023-02-24 13 | eve_2 | 0 |
| 2023-02-24 12 | eve_1 | 5 |
| 2023-02-24 12 | eve_2 | 0 |
我最初提出的查询会生成类似于以下内容的内容：
| Date_time | Evet_cat | count_ |
|---|---|---|
| 2023-02-24 13 | eve_1 | 10 |
| 2023-02-24 12 | eve_1 | 5 |
最终，我希望数据显示如下：
| Date_time | eve_1 | eve_2 |
|---|---|---|
| 2023-02-24 13 | 10 | 0 |
| 2023-02-24 12 | 5 | 0 |
英文:
I am trying to summarize a few events based on EIDs, but the result is only displaying for eve_1 and not eve_2 since (based on my data) since eve_2 count is 0.
How can I display eve_2 even when the count is 0 in this case?
Table
| where DateTime > ago(7d)
| where EID in (11, 22, 33)
| extend
Evet_cat = case(EID in (11 22), "eve_1",
EID == 33, "eve_2",
"others"
),
Date_time = format_datetime(DateTime, 'yyyy-MM-dd HH')
| summarize count() by Date_time, Evet_cat
| order by Date_time desc
How can I make sure both eve_1 and eve_2 are displayed even if their count is 0?
Update: I realize my wording of the question may be wrong. What I'm looking for is that. I'm trying to set a default (0) for any column that does not have a count, if there is a count, that should be displayed as usual.
So, something like:
| Date_time | Evet_cat | count_ |
|---------------|----------|--------|
| 2023-02-24 13 | eve_1 | 10 |
| 2023-02-24 13 | eve_2 | 0 |
| 2023-02-24 12 | eve_1 | 5 |
| 2023-02-24 12 | eve_2 | 0 |
the original query I had would have produced something like this:
| Date_time | Evet_cat | count_ |
|---------------|----------|--------|
| 2023-02-24 13 | eve_1 | 10 |
| 2023-02-24 12 | eve_1 | 5 |
Ultimately, I am looking to get the data to display like this:
| Date_time | eve_1 | eve_2 |
|---------------|----------|--------|
| 2023-02-24 13 | 10 | 0 |
| 2023-02-24 12 | 5 | 0 |
答案1
得分: 1
// 示例数据生成。不是解决方案的一部分。
let Table = materialize(range i from 1 to 200 step 1 | extend DateTime = ago(7d * rand()), EID = tolong((rand(3) + 1) * 11));
// 解决方案从这里开始。
Table
| where DateTime > ago(7d)
| where EID in (11, 22, 33)
| extend Evet_cat = case(EID in (11, 22), "eve_1", EID == 33, "eve_2", "others")
,Date_time = format_datetime(DateTime, 'yyyy-MM-dd HH')
| evaluate pivot(Evet_cat, count(), Date_time)
| Date_time | eve_1 | eve_2 |
|---|---|---|
| 2023-02-17 20 | 0 | 2 |
| 2023-02-22 23 | 1 | 1 |
| 2023-02-23 23 | 1 | 2 |
| 2023-02-18 11 | 0 | 1 |
| 2023-02-18 08 | 1 | 3 |
| 2023-02-22 08 | 2 | 0 |
| 2023-02-21 14 | 2 | 0 |
| 2023-02-18 17 | 2 | 1 |
| 2023-02-19 17 | 2 | 0 |
| 2023-02-24 10 | 1 | 0 |
| 2023-02-23 12 | 3 | 1 |
| ... | ... | ... |
| 2023-02-24 03 | 1 | 0 |
| 2023-02-23 01 | 1 | 0 |
| 2023-02-22 15 | 0 | 1 |
| 2023-02-20 08 | 1 | 0 |
| 2023-02-20 00 | 0 | 1 |
[Fiddle][1]
[1]: https://dataexplorer.azure.com/clusters/help/databases/Samples?query=H4sIAAAAAAAAA1WQPW/CQAyG9/yKV1m4a8PHhYGhohNIdKBL2aMrMeGkJBddTPohfnwdCKB682u/9mNPp/iwVVMScssWBdUULDtfT/DuGY0NDH8AHwmtL0+XSlQSY2c/xbREZZmCs6X7JRVsXRAcDsFXMGCPdDZDy9RIdgZ9M9U5VuLYuao328KrRY4niDNXWidYv61EZ1/6ulDqIs81nmG0dBmj9Us0FeQBRWYLYIsjBZpEV6TojK8+f+x5HfboR61f42ooYxKkaYL5XEd3wHVHnO0tC8jetqT+NwtkTB1lJh5ol+IetFS02MuzQhvrCEMkPUnG15MPPsjLMvk29Yq6USYY/UiMt9txnmOzGV2AOluepAGN6zyrG1iCvT/VrATlPlr/AQeECSjKAQAA
<details>
<summary>英文:</summary>
// Sample data generation. Not part of the solution.
let Table = materialize(range i from 1 to 200 step 1 | extend DateTime = ago(7d * rand()), EID = tolong((rand(3) + 1) * 11));
// Solution starts here.
Table
| where DateTime > ago(7d)
| where EID in (11, 22, 33)
| extend Evet_cat = case(EID in (11, 22), "eve_1", EID == 33, "eve_2", "others")
,Date_time = format_datetime(DateTime, 'yyyy-MM-dd HH')
| evaluate pivot(Evet_cat, count(), Date_time)
| Date_time | eve_1 | eve_2 |
|---------------|-------|-------|
| 2023-02-17 20 | 0 | 2 |
| 2023-02-22 23 | 1 | 1 |
| 2023-02-23 23 | 1 | 2 |
| 2023-02-18 11 | 0 | 1 |
| 2023-02-18 08 | 1 | 3 |
| 2023-02-22 08 | 2 | 0 |
| 2023-02-21 14 | 2 | 0 |
| 2023-02-18 17 | 2 | 1 |
| 2023-02-19 17 | 2 | 0 |
| 2023-02-24 10 | 1 | 0 |
| 2023-02-23 12 | 3 | 1 |
| ... | ... | ... |
| 2023-02-24 03 | 1 | 0 |
| 2023-02-23 01 | 1 | 0 |
| 2023-02-22 15 | 0 | 1 |
| 2023-02-20 08 | 1 | 0 |
| 2023-02-20 00 | 0 | 1 |
[Fiddle][1]
[1]: https://dataexplorer.azure.com/clusters/help/databases/Samples?query=H4sIAAAAAAAAA1WQPW/CQAyG9/yKV1m4a8PHhYGhohNIdKBL2aMrMeGkJBddTPohfnwdCKB682u/9mNPp/iwVVMScssWBdUULDtfT/DuGY0NDH8AHwmtL0+XSlQSY2c/xbREZZmCs6X7JRVsXRAcDsFXMGCPdDZDy9RIdgZ9M9U5VuLYuao328KrRY4niDNXWidYv61EZ1/6ulDqIs81nmG0dBmj9Us0FeQBRWYLYIsjBZpEV6TojK8+f+x5HfboR61f42ooYxKkaYL5XEd3wHVHnO0tC8jetqT+NwtkTB1lJh5ol+IetFS02MuzQhvrCEMkPUnG15MPPsjLMvk29Yq6USYY/UiMt9txnmOzGV2AOluepAGN6zyrG1iCvT/VrATlPlr/AQeECSjKAQAA
</details>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论