Kusto,使用series_decompose_anomalies进行异常检测,然后基于分组运行。

huangapple go评论57阅读模式
英文:

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')

fiddle

参考: 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')

fiddle

Reference: make-series operator | Microsoft Learn

huangapple
  • 本文由 发表于 2023年6月29日 00:29:38
  • 转载请务必保留本文链接:https://go.coder-hub.com/76575094.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定