Home NewsX Time Weighted Average and Value in Azure Data Explorer

Time Weighted Average and Value in Azure Data Explorer

by info.odysseyx@gmail.com
0 comment 3 views


Azure Data Explorer (ADX) supports large-scale time series aggregation through either: summary operator Maintain aggregated data in tabular format, or series operator Convert this to a set of dynamic arrays. Among them are several aggregate functions. average() One of the most popular. ADX calculates this by grouping samples into fixed time bins and applying a simple average over all samples within each time bin, regardless of their specific location within the bin. This is the standard time bin aggregation performed in SQL and other databases. However, there are scenarios where a simple average does not accurately represent time interval values. For example, IoT devices that transmit data typically emit metric values ​​asynchronously and only when they change to conserve bandwidth. In this case, we need to calculate a time-weighted average (TWA), taking into account the exact timestamp and duration of each value in the time store. ADX doesn’t have a built-in aggregate function to calculate a time-weighted average, but we’ve added a few. user-defined functionportion function libraryWe support this:

Below is a query that compares the original and interpolated values, the standard average by the summary operator, twa with forward padding, and twa with linear interpolation.

let tbl = datatable(ts:datetime,  val:real, key:string) [
    datetime(2021-04-26 00:00), 100, 'D1',
    datetime(2021-04-26 00:45), 300, 'D1',
    datetime(2021-04-26 01:15), 200, 'D1',
];
let stime=datetime(2021-04-26 00:00);
let etime=datetime(2021-04-26 01:15);
let dt = 1h;
//
tbl
| where ts between (stime..etime)
| summarize val=avg(val) by bin(ts, dt), key
| project-rename _ts=ts, _key=key
| extend orig_val=0
| extend _key = strcat(_key, '-SUMMARIZE'), orig_val=0
| union (tbl
| invoke time_weighted_val_fl('ts', 'val', 'key', stime, etime, dt)
| project-rename val = _twa_val
| extend _key = strcat(_key, '-SAMPLES'))
| union (tbl
| invoke time_weighted_avg_fl('ts', 'val', 'key', stime, etime, dt)
| project-rename val = tw_avg
| extend _key = strcat(_key, '-TWA-FF'), orig_val=0)
| union (tbl
| invoke time_weighted_avg2_fl('ts', 'val', 'key', stime, etime, dt)
| project-rename val = tw_avg
| extend _key = strcat(_key, '-TWA-LI'), orig_val=0)
| order by _key asc, _ts asc
// use anomalychart just to show original data points as bold dots
| render anomalychart with (anomalycolumns=orig_val, title="Time Wighted Average, Fill Forward & Linear interpolation")

adieldar_0-1727624993177.png

Result Description:

2021-04-26 00:00

2021-04-26 00:00

interpolated value

100

(300+200)/2=250

Average by Summary

(100+300)/2=200

200

Fill TWA forward

(45m*100 + 15m*300)/60m = 150

(15m*300 + 45m*200)/60m = 225

Linear interpolation TWA

45m*(100+300)/2 + 15m*(300+250)/2)/60m = 218.75

15m*(250+200)/2 + 45m*200)/60m = 206.25

All functions operate on multiple time series partitioned by the provided key.

Feel free to try these features and share your feedback!





Source link

You may also like

Leave a Comment

Our Company

Welcome to OdysseyX, your one-stop destination for the latest news and opportunities across various domains.

Newsletter

Subscribe my Newsletter for new blog posts, tips & new photos. Let's stay updated!

Laest News

@2024 – All Right Reserved. Designed and Developed by OdysseyX