time_weighted_val_fl()
函数 time_weighted_val_fl()
是一个“用户定义函数 (UDF)”,它通过对上一点和下一点的值进行时间加权平均来线性插值度量值。
语法
T | invoke time_weighted_avg_fl(
t_col,
y_col,
key_col,
stime,
etime,
dt)
详细了解语法约定。
参数
客户 | 类型 | 必需 | 说明 |
---|---|---|---|
t_col | string |
✔️ | 包含记录时间戳的列的名称。 |
y_col | string |
✔️ | 包含记录指标值的列的名称。 |
key_col | string |
✔️ | 包含记录分区键的列的名称。 |
stime | datetime |
✔️ | 聚合窗口的开始时间。 |
etime | datetime |
✔️ | 聚合窗口的结束时间。 |
dt | timespan |
✔️ | 聚合时间箱。 |
函数定义
可以通过将函数的代码嵌入为查询定义的函数,或将其创建为数据库中的存储函数来定义函数,如下所示:
使用以下 let 语句定义函数。 不需要任何权限。
let time_weighted_val_fl=(tbl:(*), t_col:string, y_col:string, key_col:string, stime:datetime, etime:datetime, dt:timespan)
{
let tbl_ex = tbl | extend _ts = column_ifexists(t_col, datetime(null)), _val = column_ifexists(y_col, 0.0), _key = column_ifexists(key_col, '');
let gridTimes = range _ts from stime to etime step dt | extend _val=real(null), grid=1, dummy=1;
let keys = materialize(tbl_ex | summarize by _key | extend dummy=1);
gridTimes
| join kind=fullouter keys on dummy
| project-away dummy, dummy1
| union (tbl_ex | extend grid=0)
| where _ts between (stime..etime)
| partition hint.strategy=native by _key (
order by _ts desc, _val nulls last
| scan declare(val1:real=0.0, t1:datetime) with ( // fill backward null values
step s: true => val1=iff(isnull(_val), s.val1, _val), t1=iff(isnull(_val), s.t1, _ts);)
| extend dt1=(t1-_ts)/1m
| order by _ts asc, _val nulls last
| scan declare(val0:real=0.0, t0:datetime) with ( // fill forward null values
step s: true => val0=iff(isnull(_val), s.val0, _val), t0=iff(isnull(_val), s.t0, _ts);)
| extend dt0=(_ts-t0)/1m
| extend _twa_val=iff(dt0+dt1 == 0, _val, ((val0*dt1)+(val1*dt0))/(dt0+dt1))
| scan with ( // fill forward null twa values
step s: true => _twa_val=iff(isnull(_twa_val), s._twa_val, _twa_val);)
| where grid == 0 or (grid == 1 and _ts != prev(_ts))
)
| project _ts, _key, _twa_val, orig_val=iff(grid == 1, 0, 1)
| order by _key asc, _ts asc
};
// Write your query to use the function here.
示例
以下示例使用 invoke 运算符运行函数。
若要使用查询定义的函数,请在嵌入的函数定义后调用它。
let time_weighted_val_fl=(tbl:(*), t_col:string, y_col:string, key_col:string, stime:datetime, etime:datetime, dt:timespan)
{
let tbl_ex = tbl | extend _ts = column_ifexists(t_col, datetime(null)), _val = column_ifexists(y_col, 0.0), _key = column_ifexists(key_col, '');
let gridTimes = range _ts from stime to etime step dt | extend _val=real(null), grid=1, dummy=1;
let keys = materialize(tbl_ex | summarize by _key | extend dummy=1);
gridTimes
| join kind=fullouter keys on dummy
| project-away dummy, dummy1
| union (tbl_ex | extend grid=0)
| where _ts between (stime..etime)
| partition hint.strategy=native by _key (
order by _ts desc, _val nulls last
| scan declare(val1:real=0.0, t1:datetime) with ( // fill backward null values
step s: true => val1=iff(isnull(_val), s.val1, _val), t1=iff(isnull(_val), s.t1, _ts);)
| extend dt1=(t1-_ts)/1m
| order by _ts asc, _val nulls last
| scan declare(val0:real=0.0, t0:datetime) with ( // fill forward null values
step s: true => val0=iff(isnull(_val), s.val0, _val), t0=iff(isnull(_val), s.t0, _ts);)
| extend dt0=(_ts-t0)/1m
| extend _twa_val=iff(dt0+dt1 == 0, _val, ((val0*dt1)+(val1*dt0))/(dt0+dt1))
| scan with ( // fill forward null twa values
step s: true => _twa_val=iff(isnull(_twa_val), s._twa_val, _twa_val);)
| where grid == 0 or (grid == 1 and _ts != prev(_ts))
)
| project _ts, _key, _twa_val, orig_val=iff(grid == 1, 0, 1)
| order by _key asc, _ts asc
};
let tbl = datatable(ts:datetime, val:real, key:string) [
datetime(2021-04-26 00:00), 100, 'Device1',
datetime(2021-04-26 00:45), 300, 'Device1',
datetime(2021-04-26 01:15), 200, 'Device1',
datetime(2021-04-26 00:00), 600, 'Device2',
datetime(2021-04-26 00:30), 400, 'Device2',
datetime(2021-04-26 01:30), 500, 'Device2',
datetime(2021-04-26 01:45), 300, 'Device2'
];
let minmax=materialize(tbl | summarize mint=min(ts), maxt=max(ts));
let stime=toscalar(minmax | project mint);
let etime=toscalar(minmax | project maxt);
let dt = 1h;
tbl
| invoke time_weighted_val_fl('ts', 'val', 'key', stime, etime, dt)
| project-rename val = _twa_val
| order by _key asc, _ts asc
输出
_ts | _key | val | orig_val |
---|---|---|---|
2021-04-26 00:00:00.0000000 | Device1 | 100 | 1 |
2021-04-26 00:45:00.0000000 | Device1 | 300 | 1 |
2021-04-26 01:00:00.0000000 | Device1 | 250 | 0 |
2021-04-26 01:15:00.0000000 | Device1 | 200 | 1 |
2021-04-26 00:00:00.0000000 | Device2 | 600 | 1 |
2021-04-26 00:30:00.0000000 | Device2 | 400 | 1 |
2021-04-26 01:00:00.0000000 | Device2 | 450 | 0 |
2021-04-26 01:30:00.0000000 | Device2 | 500 | 1 |
2021-04-26 01:45:00.0000000 | Device2 | 300 | 1 |