This would be a function commonly used for calculating the running performance of a stock price, however I imagine there are other business cases where this formula could be useful and / or used to hack some type of running / window function for business data.
I would say it’d be more of join function query wise though name wise I’d probably call it a runningChange.
measure # required
, [sort_order_field ASC/DESC, ...] # required
, start_index # required - args: date or integer
, end_index # optional to compare over different indexes / dates - args: date or integer
,[ partition_field, ... ] # optional
This would a rowwise calculation. Say for example we have a dataset with the fields Date, Value, and Partition. When a user adds this as a calculated field to their dataset it is preprocessed as the min start_index. The min start_index is then compared against all other indexes going forward. If a user enters a end_index then the calculation is compared against all values going forward up until the end_index.
# no argument
This might be super compute intensive as it’s a pretty dynamic function as a user would be able to compare it against any dates within their dataset. However speaking just for users that might use this for stock market data. You could easily accomplish the needs for most people by offering a few precomputed time periods. Such as 5days, 1month, 3months, YTD, 1year, 3year, 5year, 10year. These precomputed periods can be found on most stock market web apps. Generally this is their way around having a function like this be dynamic and limiting users to precomputed periods like the ones above.
Possible SQL Code:
with tbl as (
where date in (select min(date) from dummy_data group by partition)
tbl_rows.value - tbl_start_index.value as Diff,
(tbl_rows.value - tbl_start_index.value) / tbl_start_index.value as percentDiff
from tbl tbl_start_index
left join dummy_data tbl_rows
on tbl_start_index.partition = tbl_rows.partition
Then could optimize with a couple preprocessed selections like I mentioned above… not really a window function, but kinda meets the argument schema of it. Also in the above example the partition is required.