time weighted median / avg in a hop / tumble window

[chenziliang]

Use case

https://www.timescale.com/blog/what-time-weighted-averages-are-and-why-you-should-care/

Describe the solution you'd like

Describe alternatives you've considered

Additional context

[chenziliang]

User can write a JavaScript UDA to achieve the goal for now, but a native C++ aggr function shall have the best perf. The use cases are very wide.

[chenziliang]

Python example

import numpy as np
import pandas as pd

# Sample data: Timestamps in seconds and random values
timestamps = [0, 10, 20, 40, 50]  # Seconds
values = [2, 4, 6, 8, 10]         # Random numerical values

# Calculate time intervals
time_intervals = np.diff(timestamps)
# Append 0 for the last interval (optional for time-weighted analysis)
time_intervals = np.append(time_intervals, 0)

# Time-Weighted Average (TWA)
def time_weighted_average(values, time_intervals):
    weights = time_intervals[:-1]  # Ignore last interval
    weighted_sum = np.sum(np.array(values[:-1]) * weights)
    total_weight = np.sum(weights)
    return weighted_sum / total_weight if total_weight != 0 else np.nan

# Time-Weighted Median (TWM)
def time_weighted_median(values, time_intervals):
    # Replicate values according to time intervals
    weighted_values = np.repeat(values[:-1], time_intervals[:-1])
    return np.median(weighted_values)

# Compute TWA and TWM
twa = time_weighted_average(values, time_intervals)
twm = time_weighted_median(values, time_intervals)

# Display results
print("Sample Data:")
print("Timestamps:", timestamps)
print("Values:", values)
print("Time Intervals:", time_intervals[:-1])

print("\nTime-Weighted Average (TWA):", twa)
print("Time-Weighted Median (TWM):", twm)