1.1 - 8.10 - Burst Example: TimeIntervalTable File - Teradata Vantage

Teradata Vantage™ - Machine Learning Engine Analytic Function Reference

Product
Teradata Vantage
Release Number
1.1
8.10
Release Date
October 2019
Content Type
Programming Reference
Publication ID
B700-4003-079K
Language
English (United States)

Input

InputTable: 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
TimeIntervalTable: 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 syntax elements. SplitCriteria defaults to 'nosplit'.

SELECT * FROM Burst (
  ON finance_data AS InputTable PARTITION BY id ORDER BY id
  ON time_table2 AS TimeIntervalTable 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 ;

Output

 id expenditure income investment burst_start burst_end  burst_duration 
 -- ----------- ------ ---------- ----------- ---------- -------------- 
  2       421.0  465.0      179.0 1967-06-30  1967-07-05       432000.0
  3       434.0  485.0      185.0 1967-06-30  1967-07-10       864000.0
  4       448.0  493.0      192.0 1967-06-30  1967-07-04       345600.0
  2       421.0  465.0      179.0 1967-07-05  1967-07-10       432000.0
  1       415.0  451.0      180.0 1967-06-30  1967-07-05       432000.0
  4       448.0  493.0      192.0 1967-07-04  1967-07-07       259200.0
  1       415.0  451.0      180.0 1967-07-05  1967-07-10       432000.0
  4       448.0  493.0      192.0 1967-07-07  1967-07-10       259200.0
  5       459.0  509.0      211.0 1967-06-30  1967-07-02       172800.0
  5       459.0  509.0      211.0 1967-07-02  1967-07-04       172800.0
  5       459.0  509.0      211.0 1967-07-04  1967-07-06       172800.0
  5       459.0  509.0      211.0 1967-07-06  1967-07-08       172800.0
  5       459.0  509.0      211.0 1967-07-08  1967-07-10       172800.0

Download a zip file of all examples and a SQL script file that creates their input tables from the attachment in the left sidebar.