Untitled
plain_text
a month ago
1.3 kB
2
Indexable
Never
def preprae_moving_aggregate_query( start_dt: str, end_dt: str, agg_size_in_days: int, table_info: TableInfo, ) -> str: return f""" WITH date_range AS ( SELECT (DATE_ADD(DATE('{start_dt}'), INTERVAL x DAY)) as interval_end_dt FROM UNNEST(GENERATE_ARRAY(0, DATE_DIFF(DATE('{end_dt}'), DATE('{start_dt}'), DAY))) as x ) SELECT t.cust_id, t.token_idx, SUM(t.value_sum) AS value_sum, {agg_size_in_days} as agg_days, '{table_info.source_name}' as source_name, d.interval_end_dt as end_observation FROM date_range d JOIN {table_info.input_table_name} t ON t.end_observation BETWEEN DATE_SUB(d.interval_end_dt, INTERVAL {agg_size_in_days - 1} DAY) AND d.interval_end_dt WHERE t.end_observation > '2022-01-01' -- to satisfy need to use where clause on paritioned col AND t.end_observation BETWEEN DATE_SUB('{start_dt}', INTERVAL {agg_size_in_days} DAY) AND '{end_dt}' GROUP BY d.interval_end_dt, t.cust_id, t.token_idx ORDER BY d.interval_end_dt, t.cust_id, t.token_idx """