Creation date Minimum version
2017-9-22 1.2.2

Windowing

The Windowing extension is useful for determining how data changes from one row to another. It uses the SQL Lead and Lag functions. You can run the Windowing extension on multiple columns.

Example: You have a table about read access to a data storage system. The table contains a Timestamp column and a Data_read column. You are interested in knowing how the amount of data read changes over time. The Windowing extension can sort the rows by Timestamp and creates an additional column showing the amount of data read in another row for comparison. You can then perform other operations on this newly created column.

Follow these steps to use the Windowing extension:

  1. In the My Extensions panel, select the Windowing extension.
  2. The following table describes each field in the modal window:

    Field Description
    Window On Names of the selected columns on which you run the SQL Lead or Lag function. Each column contains values that change from row to row, and you are interested in analyzing the changes. Data type of the column must be integer, float, or string.
    Sort On Column on which you sort the table. Sorting is necessary before you can compare data from different rows. Data type of the column must be integer, float, or string.
    Sort order Ascending or descending.
    Lag If you want to compare data to a previous row after the sort, fill out this field. For example, type 1 to compare the current row to the one immediately before it, type 2 to compare the current row to the one that is 2 rows before, and so on. If you do not use this function, type 0 in this field.
    Lead If you want to compare data to the next row after the sort, fill out this field. For example, type 1 if you want to compare the current row to the one immediately after it, type 2 to compare the current row to the one that is 2 rows after, and so on. If you do not use this function, type 0 in this field.
  3. Click APPLY. A new table containing the columns specified in the panel is created. It also contains an additional column with the data in another row, depending on how you specify the Lag or Lead value.

Example of the Windowing extension

In the table named Disk, the columns Timestamp and Data_read show the time of each read access to a storage system and the amount of data read, respectively. The following is the Disk table:

Timestamp Data_read
1451628003 17747
1451638804 17840
1451635204 17810
1451631604 17777

Suppose you want to know by how much the data read changes from one timestamp to the next, fill out the fields in the Windowing modal window as follows:

Field Value
Window On $Data_read
Sort On $Timestamp
Sort Order ascending
Lag 0
Lead 1

The Windowing extension creates the following table:

orig_Timestamp orig_Data_read lead_1_Data_read
1451628003 17747 17777
1451631604 17777 17810
1451635204 17810 17840

In this new table, the Timestamp and Data_read columns are renamed with orig_ prepended. The table is sorted by timestamp. The new column, lead_1_Data_read, contains the orig_Data_read value in the following row. This new column enables you to analyze the changes of data read over time. For example, to find out how much the data amount changes between two timestamps, invoke the subtraction function in the Map operation by entering the following in the function bar:

map(sub(lead_1_Data_read, orig_Data_read))

Go to top