Datespine Groups

Generates a date spine across custom groupings. Each grouping will be returned with a continuous set of dates. Spines can be calculated globally using the table min/max or locally using each group min/max.
All intervals are considered to be start-inclusive and end-exclusive, or [start, end]. The join with the date spine will be an outer join such that all intervals are present and all data that does not fall into one of those intervals is excluded.
Parameters
Name
Type
Description
Is Optional
group_by
column_list
The column(s) used to partition you data into groups. A datespine will be calculated for each group.
​
date_col
column
The column used to create intervals. This must be a datetime column.
​
start_timestamp
timestamp
The timestamp to start calculating from. If not provided, the min of the date_col will be used
True
end_timestamp
timestamp
The timestamp to calculate to If not provided, the max of the date_col will be used
True
interval_type
date_part
A valid SQL datepart to slice the date_col. For interval types, see this Snowflake doc.​
​
group_bounds
value
Pass 'global' to construct each datespine using the same start & end date (i.e. the min/max of the entire table) Pass 'local' to constuct each datespine using the min/max of the group as the start & end dates Pass 'mixed' to use a local start, while retaining a global end date 'global' is default
True
Example
ds = rasgo.get.dataset(74)
​
# Usage 1: Local datespines - will return a datespine per group with 
# min/max values within the group
ds2 = ds.datespine_groups(
    group_by=['PRODUCTKEY'],
    date_col='ORDERDATE',
    group_bounds='local',
    interval_type='day',
    start_timestamp='2010-12-29',
    end_timestamp='2014-01-28' 
)
ds2.preview()
​
# Usage 2: Global datespines - will return a datespine per group with 
# min/max values set to the global min/max
ds2 = ds.datespine_groups(
    group_by=['PRODUCTKEY'],
    date_col='ORDERDATE',
    group_bounds='global',
    interval_type='day',
    start_timestamp='2010-12-29',
    end_timestamp='2014-01-28' 
)
ds2.preview()
​
Source Code
RasgoTransforms/datespine_groups.sql at main · rasgointelligence/RasgoTransforms
GitHub

Name	Type	Description	Is Optional
group_by	column_list	The column(s) used to partition you data into groups. A datespine will be calculated for each group.
date_col	column	The column used to create intervals. This must be a datetime column.
start_timestamp	timestamp	The timestamp to start calculating from. If not provided, the min of the date_col will be used	True
end_timestamp	timestamp	The timestamp to calculate to If not provided, the max of the date_col will be used	True
interval_type	date_part	A valid SQL datepart to slice the date_col. For interval types, see this Snowflake doc.
group_bounds	value	Pass 'global' to construct each datespine using the same start & end date (i.e. the min/max of the entire table) Pass 'local' to constuct each datespine using the min/max of the group as the start & end dates Pass 'mixed' to use a local start, while retaining a global end date 'global' is default	True

Datepart

Datespine

Last modified 7mo ago