英文:
Kusto, doing anomaly detection using series_decompose_anomalies and run that based on groupie
问题
我正在尝试在Kusto上编写异常检测代码,例如检查不良苹果的销售率与分销商之间的所有苹果的比率。我想要创建一系列数据,并且想要每天基于分销商得到不良苹果占所有苹果的比率。但是它给我报错:
> 语义错误:无效的 summarize 聚合表达式使用:
> summarize 必须至少有一个聚合函数。错误出自
> make-series 行
有什么建议吗?
以下是示例代码:
datatable(table_name: string, date: datetime, total_apples: int, total_bad_apples: int)
[
["distributor1", datetime(2023-06-01), 200, 10],
["distributor1", datetime(2023-06-02), 300, 20],
["distributor1", datetime(2023-06-03), 400, 30],
["distributor1", datetime(2023-06-04), 500, 40],
["distributor1", datetime(2023-06-05), 600, 50],
["distributor1", datetime(2023-06-06), 700, 60],
["distributor2", datetime(2023-06-01), 100, 5],
["distributor2", datetime(2023-06-02), 200, 10],
["distributor2", datetime(2023-06-03), 150, 8],
["distributor2", datetime(2023-06-04), 250, 12],
["distributor2", datetime(2023-06-05), 180, 9],
["distributor2", datetime(2023-06-06), 220, 11]
]
let start_date=(now()-70d);
let end_date=now();
table_name
|where date between (start_date .. end_date )
|summarize all_apples=sum(total_apples), bad_apples=sum(total_5g_drops) by date, distributor1, distributor2
|where all_apples>=100
|project bad_apples_per_all=round(iff(all_apples!=0, todouble(bad_apples)/todouble(all_apples), 0.000000000),10), date, distributor1, distributor2
|make-series bad_apples_per_all_t=bad_apples_per_all on date from start_date to end_date step 1d by distributor1, distributor2
|extend (anomalies, score, baseline) = series_decompose_anomalies(bad_apples_per_all_t, 1.5,-1,'linefit')
谢谢!
英文:
I am trying to write an anomaly detection code on Kusto, say checking the rate of selling bad apples compared to all between distributors. I want to make the series and have the bad_apples_per_all_t to get bad_apples_per_all per distributor on daily basis. It is giving me error :
> Semantic error: Invalid summarize aggregation expressions usage:
> summarize must have at least one aggregate function. error comes from
> make-series line
any suggestions?
Here is the sample code:
datatable(table_name: string, date: datetime, total_apples: int, total_bad_apples: int)
[
[“distributor1", datetime(2023-06-01), 200, 10],
["distributor1", datetime(2023-06-02), 300, 20],
["distributor1", datetime(2023-06-03), 400, 30],
["distributor1", datetime(2023-06-04), 500, 40],
["distributor1", datetime(2023-06-05), 600, 50],
["distributor1", datetime(2023-06-06), 700, 60],
["distributor2", datetime(2023-06-01), 100, 5],
["distributor2", datetime(2023-06-02), 200, 10],
["distributor2", datetime(2023-06-03), 150, 8],
["distributor2", datetime(2023-06-04), 250, 12],
["distributor2", datetime(2023-06-05), 180, 9],
["distributor2", datetime(2023-06-06), 220, 11]
]
let start_date=(now()-70d);
let end_date=now();
table_name
|where date between (start_date .. end_date )
|summarize all_apples=sum(total_apples), bad_apples=sum(total_5g_drops) by date, distributor1, distributor2
|where all_apples>=100
|project bad_apples_per_all=round(iff(all_apples!=0, todouble(bad_apples)/todouble(all_apples), 0.000000000),10), date, distributor1, distributor2
|make-series bad_apples_per_all_t=bad_apples_per_all on date from start_date to end_date step 1d by distributor1, distributor2
|extend (anomalies, score, baseline) = series_decompose_anomalies(bad_apples_per_all_t, 1.5,-1,'linefit')
Thanks
答案1
得分: 1
你收到的错误消息表明在 make-series
查询中的 summarize
聚合函数存在问题。错误消息明确指出 summarize
函数必须至少有一个聚合函数。
要解决此错误,您需要向 summarize
函数添加一个聚合函数。聚合函数是一个接受一组值并返回单个值的函数。聚合函数的示例包括 count()
、sum()
、avg()
、min()
和 max()
。
以下是如何在 summarize
函数中使用 sum()
聚合函数的示例:
table
| make-series bad_apples_per_all_t = sum(bad_apples_per_all) on date_col from start_date to end_date step 1d by table_name
由于要求是按日获取每个分销商的所有坏苹果数量,因此代码中应使用 sum()
聚合。
示例代码:
let table_name1 = datatable(table_name: string, date_col: datetime, total_apples: int, total_bad_apples: int)
[
"distributor1", datetime(2023-06-01), 200, 10,
"distributor1", datetime(2023-06-02), 300, 20,
"distributor1", datetime(2023-06-03), 400, 30,
"distributor1", datetime(2023-06-04), 500, 40,
"distributor1", datetime(2023-06-05), 600, 50,
"distributor1", datetime(2023-06-06), 700, 60,
"distributor2", datetime(2023-06-01), 100, 5,
"distributor2", datetime(2023-06-02), 200, 10,
"distributor2", datetime(2023-06-03), 150, 8,
"distributor2", datetime(2023-06-04), 250, 12,
"distributor2", datetime(2023-06-05), 180, 9,
"distributor2", datetime(2023-06-06), 220, 11,
];
let start_date = (now() - 70d);
let end_date = now();
table_name1
| where date_col between (start_date .. end_date )
| summarize all_apples = sum(total_apples), bad_apples = sum(total_bad_apples) by date_col, table_name
| where all_apples >= 100
| project bad_apples_per_all = round(iff(all_apples != 0, todouble(bad_apples) / todouble(all_apples), 0.000000000), 10), date_col, table_name
| make-series bad_apples_per_all_t = sum(bad_apples_per_all) on date_col from start_date to end_date step 1d by table_name
| extend (anomalies, score, baseline) = series_decompose_anomalies(bad_apples_per_all_t, 1.5, -1, 'linefit')
参考: make-series operator | Microsoft Learn
英文:
The error message you received indicates that there is an issue with the summarize
aggregation function in the make-series
query. The error message specifically states that the summarize
function must have at least one aggregate function.
To fix this error, you need to add an aggregate function to the summarize
function. An aggregate function is a function that takes a set of values and returns a single value. Examples of aggregate functions include count()
, sum()
, avg()
, min()
, and max()
.
Here is an example of how to use the sum()
aggregate function in the summarize
function:
table
|make-series bad_apples_per_all_t=sum(bad_apples_per_all) on date_col from start_date to end_date step 1d by table_name
Since the requirement is to get all bad apples per distributor on daily basis, sum()
aggregation is to be used in the code.
Sample code:
let table_name1 = datatable(table_name: string, date_col: datetime, total_apples: int, total_bad_apples: int)
[
"distributor1", datetime(2023-06-01), 200, 10,
"distributor1", datetime(2023-06-02), 300, 20,
"distributor1", datetime(2023-06-03), 400, 30,
"distributor1", datetime(2023-06-04), 500, 40,
"distributor1", datetime(2023-06-05), 600, 50,
"distributor1", datetime(2023-06-06), 700, 60,
"distributor2", datetime(2023-06-01), 100, 5,
"distributor2", datetime(2023-06-02), 200, 10,
"distributor2", datetime(2023-06-03), 150, 8,
"distributor2", datetime(2023-06-04), 250, 12,
"distributor2", datetime(2023-06-05), 180, 9,
"distributor2", datetime(2023-06-06), 220, 11,
];
let start_date=(now()-70d);
let end_date=now();
table_name1
|where date_col between (start_date .. end_date )
|summarize all_apples=sum(total_apples), bad_apples=sum(total_bad_apples) by date_col, table_name
|where all_apples>=100
|project bad_apples_per_all=round(iff(all_apples!=0, todouble(bad_apples)/todouble(all_apples), 0.000000000),10), date_col, table_name
|make-series bad_apples_per_all_t=sum(bad_apples_per_all) on date_col from start_date to end_date step 1d by table_name
|extend (anomalies, score, baseline) = series_decompose_anomalies(bad_apples_per_all_t, 1.5,-1,'linefit')
Reference: make-series operator | Microsoft Learn
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论