Fast Fourier Transform

📌

For smooth practice, the following query should be run to prepare tables and data.

CREATE TAG TABLE IF NOT EXISTS EXAMPLE (
    NAME VARCHAR(20) PRIMARY KEY,
    TIME DATETIME BASETIME,
    VALUE DOUBLE SUMMARIZED
);

Generates sample data

Open a new tql editor on the web ui and copy the code below and run it.

In this example, oscillator() generates a composite wave of 15Hz 1.0 + 24Hz 1.5. And CHART_SCATTER() has dataZoom() option function that provides an slider under the x-Axis.

FAKE( 
  oscillator(
    freq(15, 1.0), freq(24, 1.5),
    range('now', '10s', '1ms')) 
)
CHART_SCATTER( dataZoom('slider', 95, 100))

web-fft-tql-fake

Store data into database

Store the generated data into the database with the tag name ‘signal’.

FAKE( oscillator(
    freq(15, 1.0), freq(24, 1.5),
    range('now', '10s', '1ms')) 
)
INSERT( 'time', 'value', table('example'), tag('signal') )

It will show “10000 rows inserted.” message in the “Result” pane.

For a comment, it took about 270ms in a test machine (Apple mac mini M1), but using APPEND() method in the example below, took 65ms (x4 faster).

FAKE( oscillator(
    freq(15, 1.0), freq(24, 1.5),
    range('now', '10s', '1ms')) 
)
MAPVALUE(-1,'signal')
APPEND( table('example') )

⚠️

The ‘APPEND’ works only when fields of input records exactly match with columns of the table in order and types.

Read data from database

The code below reads the stored data from the ’example’ table.

QUERY('value', from('example', 'signal'), between('last-10s', 'last'))
CHART_LINE( dataZoom('slider', 95, 100))

web-fft-tql-query

Fast Fourier Transform

Add few data manipulation function between QUERY() source and CHART_LINE() sink.

QUERY('value', from('example', 'signal'), between('last-10s', 'last'))

MAPKEY('sample')
GROUPBYKEY()
FFT()

CHART_LINE(
      xAxis(0, 'Hz'),
      yAxis(1, 'Amplitude'),
      dataZoom('slider', 0, 10) 
  )

web-fft-tql-2d

How it works

QUERY()

QUERY(...) yields records from the query result, and tql treats the first field as key and the others are value tuple. {key: rownum, value: (time, value) }

MAPKEY(‘sample’)

MAPKEY('sample') sets the constant string ‘sample’ as a new key for all records. As result all records have same key 'sample' and (time, value) as value. {key: 'sample', value:(time, value)}

GROUPBYKEY()

GROUPBYKEY() merge all records that has the same key. In this example, all query results are combined into a record that has same key ‘sample’ and value is an array of tuples which formed {key: 'sample', value:[ (time1, value1), (time2, value2), ..., (timeN, valueN) ]}.

FFT()

FFT() applies Fast Fourier Transform on the value of the record and transform the value (time-value) into an array of tuples (frequency-amplitude). {key: 'sample', value:[ (Hz1, Ampl1), (Hz2, Ampl2), ... ]}.

Adding time axis

QUERY( 'value', from('example', 'signal'), between('last-10s', 'last'))

MAPKEY( roundTime(value(0), '500ms') )
GROUPBYKEY()
FFT(minHz(0), maxHz(100))
FLATTEN()
PUSHKEY('fft')
CHART_BAR3D(
      xAxis(0, 'time', 'time'),
      yAxis(1, 'Hz'),
      zAxis(2, 'Amp'),
      size('600px', '600px'), visualMap(0, 1.5), theme('westeros')
)

web-fft-tql-3d

How it works

QUERY()

QUERY(...) yields records from the query result, and tql treats the first field as key and the others are value tuple. {key: time, value: (value) }

MAPKEY()

MAPKEY( roundTime(value(0), '500ms')) sets the new key with the result of roundTime value(0) by 500 miliseconds. As result the records are transformed into {key: (time/500ms)*500ms, value:(time, value)}

GROUPBYKEY()

GROUPBYKEY() makes records grouped in every 500ms. {key: time1In500ms, value:[(time1, value1), (time2, value2)...]}

FFT()

FFT() applies Fast Fourier Transform for each record. The optional functions minHz(0) and maxHz(100) limits the scope of the output just for the better visualization. {key:time1In500ms, value:[(Hz1, Ampl1), ...]}, {key:'time2In500ms', value:[(Hz1, Ampl1), ...]}, …

FLATTEN()

FLATTEN() reduces the dimension of the value array by splitting into multiple records. As result it yields.

PUSHKEY()

PUSHKEY('fft') sets the constant string ‘fft’ as new key for all records. and the previous key will be “pushed” into the first place of value array. {key:'fft', value:(time1In500ms, Hz1, Ampl1)}, {key:'fft', value:(time1In500ms, Hz2, Ampl2)}…

Last updated on Sep 27, 2023

TIMEWINDOW