Transpose function

The Transpose function converts time series data for processing in a tabular format.

Syntax

Transpose (ts       TimeSeries, 
       begin_stamp datetime year to fraction(5) default NULL, 
       end_stamp   datetime year to fraction(5) default NULL,
       flags    integer default 0) 
returns row;

Transpose (query    lvarchar,
        dummy        row,
        begin_stamp  datetime year to fraction(5) default NULL, 
        end_stamp    datetime year to fraction(5) default NULL,
       col_name   lvarchar default NULL,
       flags      integer default 0) 
returns row with (iterator);

ts: The time series to transpose.
begin_stamp: The begin point of the range. Can be NULL.
end_stamp: The end point of the range. Can be NULL.
flags: Determines how a scan works on the returned set.
query: A string that contains a SELECT statement that can return multiple columns but only one time series column. The non-time-series columns are concatenated with each time series element in the returned rows.
dummy: A row type that must be passed in as NULL and cast to the expected return type of each row that is returned by the query string version of the Transpose function.
col_name: If col_name is not NULL, only the column that is specified with this parameter is used from the time series element, plus the non-time-series columns.

Description

The Transpose function is an iterator function. You can run the Transpose function with the EXECUTE FUNCTION statement or in a table expression.

Normally the transpose function skips NULL elements when returning the rows found in a time series. If the TS_SCAN_NULLS_OK (0x40) bit of the flags parameter is set, the Transpose function returns NULL elements.

If the beginning point is NULL, the scan starts at the first element of the time series, unless the TS_SCAN_EXACT_START value of the flags parameter is set.

If the end point is NULL, the scan ends at the last element of the time series, unless the TS_SCAN_EXACT_END value of the flags parameter is set.

`The flags` argument values

The flags argument determines how a scan works on the returned set. The value of flags is the sum of the wanted flag values from the following table.

Table 1. The flags argument values
Flag	Value	Meaning
TS_SCAN_HIDDEN	512	Return hidden elements marked by HideElem (see HideElem function).
TS_SCAN_EXACT_START	256	Return the element at the beginning timepoint, adding null elements if necessary.
TS_SCAN_EXACT_END	128	Return elements up to the end point (return `NULL` if necessary).
TS_SCAN_NULLS_OK	64	Return null time series elements (by default, time series elements that are `NULL` are not returned).
TS_SCAN_NO_NULLS	32	Instead of returning a null row, return a row with the time stamp set and the other columns set to `NULL`.
TS_SCAN_SKIP_END	16	Skip the element at the end timepoint of the scan range.
TS_SCAN_SKIP_BEGIN	8	Skip the element at the beginning timepoint of the scan range.
TS_SCAN_SKIP_HIDDEN	4	Used by ts_begin_scan() to tell ts_next() not to return hidden elements.

Returns

Multiple rows that contain a time stamp and the other columns of the time series elements.

Example 1: Convert time series data to a table

The following statement converts the data from stock_data for HCLTECH to tabular form:

execute function Transpose((select stock_data 
   from daily_stocks where stock_name = 'HCLTECH'));

Example 2: Transpose clipped data

The following statement converts data for a clipped range into tabular form:

execute function Transpose((select stock_data from daily_stocks
             where stock_name = 'HCLTECH'), 
             datetime(2011-01-05) year to day, 
             NULL::datetime year to fraction(5));

The statement returns the following data in the form of a row data type:

ROW('2011-01-06 00:00:00.00000',99.00000
000000,54.00000000000,66.00000000000,888.0000000000)

Example 3: Convert time series and other data into tabular format

The following example returns the time series columns together with the non-time-series columns in tabular form:

execute function Transpose ('select * from daily_stocks',  NULL::row(stock_id
int, stock_name lvarchar, 
   t datetime year to fraction(5), high real, low real, final real, volume real));

Example 4: Display specific data as multiple fields within a single column

The following statement selects the time and energy readings from a time series:

SELECT mr.t,mr.energy
  FROM TABLE(transpose
              ((SELECT readings FROM smartmeters
                WHERE meter_id = 13243))::smartmeter_row) 
            AS tab(mr);

The statements returns a table named tab that contains one column, named mr. The mr column is an unnamed row type that has the same fields as the TimeSeries subtype named smartmeter_row. The output has a field for time and a field for energy:

t                               energy

2011-01-01 00:00:00.00000          29
2011-01-01 00:15:00.00000          18
2011-01-01 00:30:00.00000          13
2011-01-01 00:45:00.00000          26
2011-01-01 01:00:00.00000          21
2011-01-01 01:15:00.00000          15
2011-01-01 01:30:00.00000          20
2011-01-01 01:45:00.00000          24
2011-01-01 02:00:00.00000          30
2011-01-01 02:15:00.00000          30
2011-01-01 02:30:00.00000          29
2011-01-01 02:45:00.00000          32
2011-01-01 03:00:00.00000          29

Example 5: Display specific data in a table with multiple columns

The following statement uses the statement from the previous example inside a table expression in the FROM clause:

SELECT * FROM (
         SELECT mr.t,mr.energy,mr.temperature
             FROM TABLE(transpose
                 ((SELECT readings FROM smartmeters 
                   WHERE meter_id = 13243))::smartmeter_row)
                     AS tab(mr)
        ) AS sm(t,energy,temp)
  WHERE temp < -10;

The statement returns the following data in the form of a table named sm that contains three columns:

t                              energy          temp

2011-01-01 00:00:00.00000          29  -13.0000000000
2011-01-01 00:30:00.00000          13  -18.0000000000
2011-01-01 01:00:00.00000          21  -13.0000000000
2011-01-01 01:15:00.00000          15  -11.0000000000
2011-01-01 03:15:00.00000          22  -19.0000000000
2011-01-01 03:45:00.00000          28  -14.0000000000
2011-01-01 04:00:00.00000          19  -14.0000000000
2011-01-01 04:30:00.00000          27  -14.0000000000
2011-01-01 04:45:00.00000          27  -15.0000000000
2011-01-01 05:00:00.00000          28  -11.0000000000