Burst Example: TimeIntervalTable File | Teradata Vantage - Burst Example: TimeIntervalTable File

Burst Example: TimeIntervalTable File | Teradata Vantage - Burst Example: TimeIntervalTable File - Teradata Vantage

Machine Learning Engine Analytic Function Reference

Product

Teradata Vantage

Release Number

9.02

9.01

2.0

1.3

Published

February 2022

Language

English (United States)

Last Update

2022-02-10

dita:mapPath

rnn1580259159235.ditamap

dita:ditavalPath

ybt1582220416951.ditaval

dita:id

B700-4003

lifecycle

Product Category

Teradata Vantage™

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.