Introduction and Usage of Time Series UDF

Time Series UDF Function List

Type	Function	Parameters	Output Data Type	Description
Build Query Table Fields (ETL Use)	date_range_build_v2	(struct_array)	dateRangStruct	Uncompressed time series values. The struct_array parameter needs to be aggregated using functions like collect_list(struct(date, value)).
	date_text_range_build_v2	(struct_array)	dateTextRangStruct
	date_range_zipper	(dateRangStruct)	dateRangStruct	Adjacent equal value compressed time series values. Adds a record with null value for missing dates and compresses (if there are missing dates and the previous date value is not null, adds a record with adjacent date to the previous date and null value, current date compression is compared with the newly added record; simultaneously removes duplicate adjacent equal value records, keeping only the first one).
	date_text_range_zipper	(dateTextRangStruct)	dateTextRangStruct
	date_range_merge	(dateRangStruct_1, dateRangStruct_2)	dateRangStruct	Merge time series, no compression of adjacent equal values after merging.
	date_text_range_merge	(dateTextRangStruct_1, dateTextRangStruct_2)	dateTextRangStruct
	date_range_period_to_date	(dateRangStruct, period:string)	dateRangStruct	period: 'week','month','year' 1. Original dates are preserved, values are accumulated by specified period; 2. Fill beginning dates for missing periods, values are filled with zeros; simultaneously compress adjacent equal values for filled periods, keeping only the first one. Suitable for calculating and finding cumulative values within periods, such as weekly/monthly/yearly cumulative sales.
Lookup Data (Card Use)	date_range_lookup	(dateRangStruct, lookup_date)	Number value	Rolling upward lookup, i.e., if no corresponding date data, look for the nearest previous date data, suitable for inventory data or member status lookup.
	date_text_range_lookup	(dateTextRangStruct, lookup_date)	String value
	date_range_get	(dateRangStruct, lookup_date)	Number value	Exact lookup, suitable for sales data lookup; returns null if not found.
	date_text_range_get	(dateTextRangStruct, lookup_date)	String value

Case Study: Using Time Series UDF to Query Inventory

Use the date_range_build_v2 function in ETL to build inventory query fields, and use date_range_lookup for rolling upward lookup of inventory quantities in cards.

Implementation Steps

1. Process inventory query and MTD inventory query data in ETL

1. Inventory query:

date_range_build_v2(collect_list(struct(`Date`,`Inventory`))) as `Inventory Query`

2. MTD inventory query

date_range_period_to_date([Inventory Query],'month')

ETL preview effect:

2. Create inventory, exact lookup, and MTD inventory query data in the card

1. Inventory

date_range_lookup([Inventory Query],to_date([DYNAMIC_PARAMS.Query Date]))

2. Inventory Query - Exact Lookup

date_range_get([Inventory Query],to_date([DYNAMIC_PARAMS.Query Date]))

3. MTD Inventory Query

date_range_lookup([MTD Inventory Query],to_date([DYNAMIC_PARAMS.Query Date]))

Card Effect: