1.0 - 8.00 - Burst Example 4: Time_Table File - Teradata Vantage

Teradata® Vantage Machine Learning Engine Analytic Function Reference

Product
Teradata Vantage
Release Number
1.0
8.00
Release Date
May 2019
Content Type
Programming Reference
Publication ID
B700-4003-098K
Language
English (United States)

Input

input_table: finance_data
id start_time_column end_time_column expenditure income investment
1 1967-06-30 2007-07-10 415 451 180
2 1967-06-30 2007-07-10 421 465 179
3 1967-06-30 2007-07-10 434 485 185
4 1967-06-30 2007-07-10 448 493 192
5 1967-06-30 2007-07-10 459 509 211
time_table: time_table2
id burst_start burst_end
1 1967-06-30 1967-07-05
1 1967-07-05 1967-07-10
2 1967-06-30 1967-07-05
2 1967-07-05 1967-07-10
3 1967-06-30 1967-07-10
4 1967-06-30 1967-07-04
4 1967-07-04 1967-07-07
4 1967-07-07 1967-07-10
5 1967-06-30 1967-07-02
5 1967-07-02 1967-07-04
5 1967-07-04 1967-07-06
5 1967-07-06 1967-07-08
5 1967-07-08 1967-07-10

SQL Call

The call specifies the ValuesBeforeFirst and ValuesAfterLast arguments. SplitCriteria defaults to 'nosplit'.

SELECT * FROM Burst (
  ON finance_data AS input_table PARTITION BY id ORDER BY id
  ON time_table2 AS time_table PARTITION BY id ORDER BY burst_start
  USING
  TimeColumn ('start_time_column', 'end_time_column')
  TargetColumns ('expenditure', 'income', 'investment')
  StartTime ('06/30/1967')
  EndTime ('07/10/1967')
  ValuesBeforeFirst ('NULL','NULL','NULL')
  ValuesAfterLast ('NULL','NULL','NULL')
  Accumulate ('id')
) AS dt ORDER BY id;

Output

id expenditure income investment burst_start burst_end burst_duration
1 415 451 180 1967-06-30 1967-07-05 43200
1 415 451 180 1967-07-05 1967-07-10 43200
2 421 465 179 1967-06-30 1967-07-05 43200
2 421 465 179 1967-07-05 1967-07-10 43200
3 434 485 185 1967-06-30 1967-07-10 86400
4 448 493 192 1967-06-30 1967-07-04 345600
4 448 493 192 1967-07-04 1967-07-07 259200
4 448 493 192 1967-07-07 1967-07-10 259200
5 459 509 211 1967-06-30 1967-07-02 172800
5 459 509 211 1967-07-02 1967-07-04 172800
5 459 509 211 1967-07-04 1967-07-06 172800
5 459 509 211 1967-07-06 1967-07-08 172800
5 459 509 211 1967-07-08 1967-07-10 172800