UseCases/Fourier_Transforms/Fourier_Transforms_SQL.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "fbc53937-0b0a-40a3-8fde-8899f008cb7c",
   "metadata": {},
   "source": [
    "<header>\n",
    "   <p  style='font-size:36px;font-family:Arial; color:#F0F0F0; background-color: #00233c; padding-left: 20pt; padding-top: 20pt;padding-bottom: 10pt; padding-right: 20pt;'>\n",
    "       Applying Fourier Transform on signal series.<br>Discrete Fast Fourier Transform and Convolve functions\n",
    "  <br>\n",
    "       <img id=\"teradata-logo\" src=\"https://storage.googleapis.com/clearscape_analytics_demo_data/DEMO_Logo/teradata.svg\" alt=\"Teradata\" style=\"width: 125px; height: auto; margin-top: 20pt;\">\n",
    "    </p>\n",
    "</header>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "149c35df-f960-4ae1-8573-6e4bdc7d02a3",
   "metadata": {},
   "source": [
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>Introduction</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The <b>Fourier transform</b> is one of the most important algorithms in all of \n",
    "science, mathematics, and communications technology.\n",
    "<br>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'> The Fourier Theorem states that any signal can be perfectly represented as a sum of sine waves, each sine wave having its own phase, frequency, and amplitude. This provides an efficient way to <b>transform a signal (e.g. a radio wave) from the time domain to the frequency domain</b>. The time domain is what we are used to seeing (e.g. a radio wave is a fluctuation of signal amplitude over time). The frequency domain shows how much energy is in the signal at each frequency (sine wave speed). This allows us to view the individual components of a signal, and therefore provides a better understanding of complicated signals.</p>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "88a10dd7-4e57-4744-ac42-65d085efa572",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>There are four parts to a wave:\n",
    "wavelength(λ), period(T), frequency(f), and amplitude (A)</p>\n",
    "<img id=\"signal wave\" src=\"images/signal.png\" alt=\"signal wave\" width=\"400\" />\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The Angular Frequency is ω = 2π × f</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4f71e8f0-693e-4ea4-997e-89a0c6a02c10",
   "metadata": {},
   "source": [
    "### Applications for FFT are\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "<ul>\n",
    "            <li  style = 'font-size:16px;font-family:Arial;color:#00233C'>Digital Signal Processing</li>\n",
    "            <li style = 'font-size:16px;font-family:Arial;color:#00233C'>Noise reduction in signals</li>\n",
    "            <li  style = 'font-size:16px;font-family:Arial;color:#00233C'>Seasonality detection in timeseries</li>\n",
    "            <li  style = 'font-size:16px;font-family:Arial;color:#00233C'>Image Processing</li>\n",
    "        </ul>    \n",
    "</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b9f24f31-41a6-4aa3-9e90-659df5d635b0",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    In this demonstration we will use new <b>UAF </b>(Unbounded Array Framework) Vantage capabilities. With UAF functionality, Vantage can process one-dimensional series (series indexed by time or space) and two-dimensional arrays (matrix indexed by time, space or time and space). The series can reside in any Teradata supported or Teradata accessible table or in an analytic result table (ART). We will use the below functions in this demonstration:\n",
    "<ul>    \n",
    "    <li style = 'font-size:16px;font-family:Arial;color:#00233C'> <b>TD_GENSERIES4FORMULA</b>-provides the ability for you to define and apply a formula to generate a time series </li>\n",
    "    <li style = 'font-size:16px;font-family:Arial;color:#00233C'> <b>TD_DFFT & TD_IDFFT</b> - TD_DFFT Applies the Discrete Fast Fourier Transform to a one-dimensional series and returns a result series containing the computed Fourier Coefficients. The coefficients can be in rectangular (real, imaginary) or polar (amplitude, phase). TD_IDFFT reverses the effects of a Fourier transform on a one-dimensional series. </li>\n",
    "    <li style = 'font-size:16px;font-family:Arial;color:#00233C'> <b>TD_CONVOLVE</b> - Applies digital filter to one-dimensional series by convolving time series with kernel filter </li>\n",
    "    <li style = 'font-size:16px;font-family:Arial;color:#00233C'> <b>TD_PLOT</b> - provides the ability to generate charts. The generated charts can be in the JPG, PNG, or SVG formats </li>\n",
    "</ul>    \n",
    "</p>  \n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3698abb0-31db-4de0-9b5f-527629bb4f77",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C;'>Let's do a simple example on sample data on how the signals with random noise looks like and how do we recognize noise using FFT.</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Steps</b></p>\n",
    "<ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li>Connect to Vantage</li>\n",
    "    <li>Create data points for the series(genData)</li>\n",
    "    <li>Create signal series (SINUSOIDS4) with 4 frequencies using TD_GENSERIES4FORMULA</li>\n",
    "    <li>Create signal series (SINUSOIDS4_noise) with 4 frequencies and random noise added</li>\n",
    "    <li>Apply TD_DFFT Fourier Transform function on both the series so find the significant frequencies</li>\n",
    "    <li>Apply TD_IDFFT function to see that we can regenerate the original series</li>\n",
    "    <li>Preparing a signal frequency of 100Hz to use as a filter</li>\n",
    "    <li>Apply the TD_CONVOLVE function to the series (SINUSOIDS4_noise) to cancel all the frequencies except of 100Hz</li>\n",
    "    <li>Clean up</li>\n",
    "</ul>    \n",
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fb1a6e3c-982e-4c56-a570-f079367ce2dc",
   "metadata": {},
   "source": [
    "<b style = 'font-size:20px;font-family:Arial;color:#00233C'>1. Connect to Vantage</b>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>You will be prompted to provide the password. Enter your password, press Enter, then use down arrow to go to next cell.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3bbd1776-c8ce-46b1-b0bb-4aaed2b2ec42",
   "metadata": {},
   "outputs": [],
   "source": [
    "%connect local, hidewarnings=true"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5c5d2d31-2b9f-4aa4-90df-32d89032c3c4",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Setup for execution of notebook. Begin running steps with Shift + Enter keys.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8d61896d-84a3-4c58-bf3f-d6c9f8c85c10",
   "metadata": {},
   "outputs": [],
   "source": [
    "Set query_band='DEMO=FourierTransform.ipynb;' update for session;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "395a087c-efc1-4d5a-9bfc-070145b4ef90",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Optional step – if you want to see status of databases/tables created and space used.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0613322e-0cc9-4248-88e8-623eb5a1860d",
   "metadata": {},
   "outputs": [],
   "source": [
    "call space_report();  -- optional, takes about 10 seconds"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bc58cae5-7061-4768-84b7-32bf8c30ddd3",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0e839447-207f-4148-919a-70a775221292",
   "metadata": {},
   "source": [
    "<b style = 'font-size:20px;font-family:Arial;color:#00233C'>2. Create the dataset</b>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We will create two signals with 400 datapoints. One pure signal which will have 4 sinusoidal frequencies of 2, 25, 50 & 100 Hz and other which will have random noise added as well. </p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "804c87de-198c-4b7f-af8d-483a3719cad6",
   "metadata": {},
   "outputs": [],
   "source": [
    "/* This table will be create 400 data points */\n",
    "CREATE TABLE genData(ID integer, row_i integer, MAGNITUDE float);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a72398e6-ca16-4690-95c4-cdd96b7d7a41",
   "metadata": {},
   "outputs": [],
   "source": [
    "INSERT INTO genData values(1, 1, 1.0);\n",
    "INSERT INTO genData SELECT ID, row_i+1, MAGNITUDE+1.0 FROM genData;\n",
    "INSERT INTO genData SELECT ID, row_i+2, MAGNITUDE+2.0 FROM genData;\n",
    "INSERT INTO genData SELECT ID, row_i+4, MAGNITUDE+4.0 FROM genData;\n",
    "INSERT INTO genData SELECT ID, row_i+8, MAGNITUDE+8.0 FROM genData;\n",
    "INSERT INTO genData SELECT ID, row_i+16, MAGNITUDE+16.0 FROM genData;\n",
    "INSERT INTO genData SELECT ID, row_i+32, MAGNITUDE+32.0 FROM genData;\n",
    "INSERT INTO genData SELECT ID, row_i+64, MAGNITUDE+64.0 FROM genData;\n",
    "INSERT INTO genData SELECT ID, row_i+128, MAGNITUDE+128.0 FROM genData;\n",
    "INSERT INTO genData SELECT ID, row_i+256, MAGNITUDE+256.0 FROM genData;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6632846b-e666-4dbc-832d-499e1fa56915",
   "metadata": {},
   "outputs": [],
   "source": [
    "--To get only 400 data points\n",
    "DELETE FROM GenData WHERE ROW_I>400.0;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "84e44e3f-b497-4891-bd87-b257e84b1a09",
   "metadata": {},
   "outputs": [],
   "source": [
    "/*************************************************************************/\n",
    "/*  Generate 400 Samplepoint Series which is the sum of 4 Sinusoids      */\n",
    "/*  Sinusoids have frequencies (k) of: 2, 25, 50, 100                    */\n",
    "/*************************************************************************/\n",
    "EXECUTE FUNCTION INTO ART(SINUSOIDS4)\n",
    "TD_GENSERIES4FORMULA(\n",
    "  SERIES_SPEC(TABLE_NAME(GenData), SERIES_ID(ID), ROW_AXIS(SEQUENCE(ROW_I)),\n",
    "  PAYLOAD( FIELDS(MAGNITUDE), CONTENT(REAL))\n",
    "  ),\n",
    "  FUNC_PARAMS(Formula('Y = 4.0*sin(6.283185307179586476*X1/200.0) +\n",
    "                           4.0*sin(6.283185307179586476*X1/16.0 + 3.14159265358979323/4.0) + \n",
    "                           4.0*sin(6.283185307179586476*X1/8.0 + 3.14159265358979323/2.0) +\n",
    "                           4.0*sin(6.283185307179586476*X1/4.0 + 3.14159265358979323/8.0)'))\n",
    ");\n",
    "\n",
    "/* 4.0*sin(6.283185307179586476*X1/200.0) - 2Hz frequnecy\n",
    "   4.0*sin(6.283185307179586476*X1/16.0 + 3.14159265358979323/4.0) - 25Hz with phase added    \n",
    "   4.0*sin(6.283185307179586476*X1/8.0 + 3.14159265358979323/2.0) - 50Hz with phase added \n",
    "   4.0*sin(6.283185307179586476*X1/4.0 + 3.14159265358979323/8.0) - 100Hz with phase added \n",
    "*/"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2bd80b71-da8d-4d4c-a8b3-2229af068dd3",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Let's see what data is generated for the series."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d3bd946e-e3b1-4f1f-8f7b-1386857ff891",
   "metadata": {},
   "outputs": [],
   "source": [
    "select top 5 * from SINUSOIDS4;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7f266b00-401e-4eae-bf79-4ffcbae2dfe9",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Table SINUSOIDS4 is the table containing signal data of 4 sinusidal waves.</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We can see how the signal looks like using TD_PLOT.</p>\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5da0e87e-3e69-4398-8194-8615cffc4b55",
   "metadata": {},
   "outputs": [],
   "source": [
    "/**********************************************************************************/\n",
    "/*                              PLOT generated sinusoidal signal                  */\n",
    "/**********************************************************************************/\n",
    "\n",
    "EXECUTE FUNCTION\n",
    "TD_Plot\n",
    "(\n",
    "    SERIES_SPEC(TABLE_NAME(SINUSOIDS4), ROW_AXIS(SEQUENCE(ROW_I)), \n",
    "                SERIES_ID(ID),\n",
    "                PAYLOAD(FIELDS(Magnitude), CONTENT(REAL)) ),\n",
    "    FUNC_PARAMS( PLOTS[( TYPE('line'))], IMAGE('png')));"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5f8b32af-74b8-451a-afb7-e755943f36a5",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><i>* Please <b> right click on the IMAGE column </b> from the output and choose view image to see the plot generated. </i></p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "0df36eb5-fd03-4ed8-aeee-710a30a6ce67",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>If you followed the instructions above to View Image,  you should have seen image as below</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4655dd9f-f397-4078-9f2e-f3982b2d9f56",
   "metadata": {},
   "source": [
    "<img id=\"sine wave\" src=\"images/sin4wave.png\" alt=\"Sine wave with no noise added\" width=\"400\" />\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Sine wave with no noise added.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dacc990d-6ffd-4335-8e23-2bdc217e155e",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Now, let us add some random <b>noise </b>in the signal. We have added a noise component 3* random(1,10)/random(1,3) in same sine frequencies as above signal.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fe4225fb-fabb-46f9-a783-c6bae2be8919",
   "metadata": {},
   "outputs": [],
   "source": [
    "EXECUTE FUNCTION INTO ART(SINUSOIDS4_noise)\n",
    "TD_GENSERIES4FORMULA(\n",
    "  SERIES_SPEC(TABLE_NAME(GenData), SERIES_ID(ID), ROW_AXIS(SEQUENCE(ROW_I)),\n",
    "  PAYLOAD( FIELDS(MAGNITUDE), CONTENT(REAL))\n",
    "  ),\n",
    "  FUNC_PARAMS(Formula('Y = 4.0*sin(6.283185307179586476*X1/200.0) + \n",
    "                           4.0*sin(6.283185307179586476*X1/16.0 + 3.14159265358979323/4.0) + \n",
    "                           4.0*sin(6.283185307179586476*X1/8.0 + 3.14159265358979323/2.0) + \n",
    "                           4.0*sin(6.283185307179586476*X1/4.0 + 3.14159265358979323/8.0) + \n",
    "                           (3* random(1,10)/random(1,3))'))\n",
    ");"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b0c9a522-fb61-431c-a0af-9c5fb0d55a19",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Let's see how the signal looks like now when it has noise added.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "69d09247-4819-4c14-85a5-119b2f7d569d",
   "metadata": {},
   "outputs": [],
   "source": [
    "/**********************************************************************************/\n",
    "/*             PLOT generated sinusoidal signal with noise added                  */\n",
    "/**********************************************************************************/\n",
    "\n",
    "EXECUTE FUNCTION\n",
    "TD_Plot\n",
    "(\n",
    "    SERIES_SPEC(TABLE_NAME(SINUSOIDS4_noise), ROW_AXIS(SEQUENCE(ROW_I)), \n",
    "                SERIES_ID(ID),\n",
    "                PAYLOAD(FIELDS(Magnitude), CONTENT(REAL)) ),\n",
    "    FUNC_PARAMS( PLOTS[( TYPE('line'))], IMAGE('png')));\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c1f4a252-cc7d-45fa-a5b6-db4d4c8b59ae",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>3. TD_DFFT and TD_IDFFT</b></p>\n",
    "\n",
    "<p style = font-size:16px;font-family:Arial;color:#00233C>Discrete Fourier Transform (DFFT) & Inverse DFFT are the functions available in Vantage Clearscape Analytics which can convert any timeseries in frequency domain and vice versa using the Vantage's UAF (Unbounded Array Framework)</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Let us plot Fourier Transform to see if we can distinguish the main signals (sinusoids)\n",
    "</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3d0b5e4b-2eed-427e-8611-0f6a2cea3716",
   "metadata": {},
   "outputs": [],
   "source": [
    "/**********************************************************************************/\n",
    "/*          Applying TD_DFFT function with signal with no noise                   */\n",
    "/**********************************************************************************/\n",
    "EXECUTE FUNCTION INTO VOLATILE ART(DfftRaw_INTEGRAL) \n",
    "TD_DFFT(\n",
    "  SERIES_SPEC( TABLE_NAME(SINUSOIDS4), SERIES_ID(ID), ROW_AXIS(SEQUENCE(ROW_I)),\n",
    "               PAYLOAD( FIELDS(MAGNITUDE), CONTENT(REAL)) ),\n",
    "  FUNC_PARAMS( FREQ_STYLE(\"K_INTEGRAL\"), HUMAN_READABLE(1) ),\n",
    "  OUTPUT_FMT( CONTENT(AMPL_PHASE_RADIANS) ) );"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "edade436-a24e-49b8-b962-07663c0316cf",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The output of the TD_DFFT function creates a series with identifiers (seriesID & RowID) corresponding to the original series and Fourier Amplitude & Phase coefficient corresponding to ROW_I index (or Real & Imaginary depending on the parameters provided in the function).</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "45ff93e3-5fe9-4613-a5dc-e8bb4b734f88",
   "metadata": {},
   "outputs": [],
   "source": [
    "/* Return Fourier Coeff rows having a significant magnitude */\n",
    "SELECT * FROM DfftRaw_INTEGRAL WHERE Amplitude_Magnitude > 1.0;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9450edb8-3c52-486d-a00c-18c9e5fad460",
   "metadata": {},
   "outputs": [],
   "source": [
    "/**********************************************************************************/\n",
    "/*          Applying TD_DFFT function with signal with noise                      */\n",
    "/**********************************************************************************/\n",
    "EXECUTE FUNCTION INTO VOLATILE ART(DfftRaw_noise) \n",
    "TD_DFFT(\n",
    "  SERIES_SPEC( TABLE_NAME(SINUSOIDS4_noise), SERIES_ID(ID), ROW_AXIS(SEQUENCE(ROW_I)),\n",
    "               PAYLOAD( FIELDS(MAGNITUDE), CONTENT(REAL)) ),\n",
    "  FUNC_PARAMS( FREQ_STYLE(\"K_INTEGRAL\"), HUMAN_READABLE(1) ),\n",
    "  OUTPUT_FMT( CONTENT(AMPL_PHASE_RADIANS) ) );"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9701d68f-7cbb-4588-a982-d4cddd05edc8",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Let's plot these transformed series to see if we can identify the significant frequencies in the signals.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7f025dd0-5ec5-4362-a781-dfc3c9b1e374",
   "metadata": {},
   "outputs": [],
   "source": [
    "/******************************************************************************************/\n",
    "/*                           Plot the results of the DFFT of the Signal                  */\n",
    "/******************************************************************************************/\n",
    "\n",
    "EXECUTE FUNCTION\n",
    "TD_Plot\n",
    "(   SERIES_SPEC(TABLE_NAME(DfftRaw_INTEGRAL), ROW_AXIS(SEQUENCE(ROW_I)),SERIES_ID(ID),\n",
    "                PAYLOAD(FIELDS(Amplitude_Magnitude),CONTENT(REAL)) ),\n",
    "    FUNC_PARAMS( PLOTS[( TYPE('line') )], IMAGE('png'))\n",
    ");\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "88535783-a9f5-4b80-9bf6-2d83ebdbdc8e",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In the image plot produced we can clearly see that the significant frequencies(2, 20, 50 & 100 Hz) in the signal which corresponds to the ones we used to create our signal.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d7738269-3403-4186-8b91-fa85bf94e441",
   "metadata": {},
   "outputs": [],
   "source": [
    "/******************************************************************************************/\n",
    "/*                           Plot the results of the DFFT of the Signal                  */\n",
    "/******************************************************************************************/\n",
    "EXECUTE FUNCTION\n",
    "TD_Plot\n",
    "(   SERIES_SPEC(TABLE_NAME(DfftRaw_noise), ROW_AXIS(SEQUENCE(ROW_I)),SERIES_ID(ID),\n",
    "                PAYLOAD(FIELDS(Amplitude_Magnitude),CONTENT(REAL)) ),\n",
    "    FUNC_PARAMS( PLOTS[( TYPE('line') )], IMAGE('png'))\n",
    ");"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f2dff83b-7503-4665-b02a-9ae83bb5c9a5",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Now we can clearly see that both the series have the same significant frequencies but one has random noises which has created fluctuations in the DFFT plot above.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "34329df1-fdaf-492a-950f-132e105f1e7d",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>We can recreate our original signals(time domain) from the dfft (frequency domain)</b></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "77b438bf-c023-4eb9-906e-25b39b7f60c3",
   "metadata": {},
   "outputs": [],
   "source": [
    "/**********************************************************************************/\n",
    "/*               TAKE INVERSE DFFT of PREVIOUS DFFT OUTPUT                        */\n",
    "/**********************************************************************************/\n",
    "EXECUTE FUNCTION INTO VOLATILE ART(ReturnOriginal) \n",
    "        TD_IDFFT(ART_SPEC(TABLE_NAME(DfftRaw_INTEGRAL),\n",
    "                          PAYLOAD( FIELDS(AMPLITUDE_MAGNITUDE,PHASE_MAGNITUDE), \n",
    "                                   CONTENT(AMPL_PHASE_RADIANS))) );"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "362588b9-62dd-4d0c-a9bb-14c06bc06842",
   "metadata": {},
   "outputs": [],
   "source": [
    "/******************************************************************************************/\n",
    "/*                           Plot the results of the IDFFT of the Signal                  */\n",
    "/******************************************************************************************/\n",
    "EXECUTE FUNCTION\n",
    "TD_Plot\n",
    "(\n",
    "    SERIES_SPEC(TABLE_NAME(ReturnOriginal), ROW_AXIS(SEQUENCE(ROW_I)), SERIES_ID(ID),\n",
    "                PAYLOAD(FIELDS(Real_Amplitude_Magnitude), CONTENT(REAL))),\n",
    "    FUNC_PARAMS(PLOTS[(TYPE('line'))], IMAGE('png')));"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d8b6ab4d-8e92-4ab6-aef5-51e73e4f1ded",
   "metadata": {},
   "outputs": [],
   "source": [
    "EXECUTE FUNCTION INTO VOLATILE ART(ReturnOriginal_noise) \n",
    "        TD_IDFFT(ART_SPEC(TABLE_NAME(DfftRaw_noise),\n",
    "                          PAYLOAD( FIELDS(AMPLITUDE_MAGNITUDE,PHASE_MAGNITUDE), \n",
    "                                   CONTENT(AMPL_PHASE_RADIANS))) );"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a6bb9b68-8846-4a9a-ab63-a631a2af10f5",
   "metadata": {},
   "outputs": [],
   "source": [
    "/******************************************************************************************/\n",
    "/*                           Plot the results of the IDFFT of the Signal                  */\n",
    "/******************************************************************************************/\n",
    "EXECUTE FUNCTION\n",
    "TD_Plot\n",
    "(\n",
    "    SERIES_SPEC(TABLE_NAME(ReturnOriginal_noise), ROW_AXIS(SEQUENCE(ROW_I)), SERIES_ID(ID),\n",
    "                PAYLOAD(FIELDS(Real_Amplitude_Magnitude), CONTENT(REAL))),\n",
    "    FUNC_PARAMS(PLOTS[(TYPE('line'))], IMAGE('png')));"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1621c781-8661-4146-9e10-ccb41b816328",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>4. Signal Filtering</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We can use signal filtering to filter out the pure or significant frequencies from our signals. This can be used in noise filtering also. From our random noise signal, lets filter all the frequencies except 100hz.<br><br>\n",
    "In Vantage Clearscape Analytics we have <b>TD_CONVOLVE</b> function which can filter one signal from another. We also have <b>TD_BINARYSERIESOP</b> function which can perform a point-wise mathematical operation (addition, subtraction, multiplication, or division) on two time series of equal size.<br> First we create a sin wave of 100hz.\n",
    "</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "de975611-f82b-4bc4-8a82-dc4b5929c08f",
   "metadata": {},
   "outputs": [],
   "source": [
    " /* creating filter series with 400 data points*/\n",
    " EXECUTE FUNCTION INTO ART(SINUSOIDS4_fil)\n",
    " TD_GENSERIES4FORMULA(\n",
    "  SERIES_SPEC(TABLE_NAME(GenData), SERIES_ID(ID), ROW_AXIS(SEQUENCE(ROW_I)),\n",
    "  PAYLOAD( FIELDS(MAGNITUDE), CONTENT(REAL))\n",
    "  ),\n",
    "  FUNC_PARAMS(Formula('Y = 4.0*sin(6.283185307179586476*X1/4.0)'))\n",
    ");"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "19f9ac61-a014-4504-a5cb-9c5e59771030",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Let's see how the filter signal looks like.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b7e27be6-6f05-4235-90fd-5a4cfc0727cb",
   "metadata": {},
   "outputs": [],
   "source": [
    "/**********************************************************************************/\n",
    "/*                              PLOT generated sinusoidal signal                  */\n",
    "/**********************************************************************************/\n",
    "\n",
    "EXECUTE FUNCTION\n",
    "TD_Plot\n",
    "(\n",
    "    SERIES_SPEC(TABLE_NAME(SINUSOIDS4_fil), ROW_AXIS(SEQUENCE(ROW_I)), \n",
    "                SERIES_ID(ID),\n",
    "                PAYLOAD(FIELDS(Magnitude), CONTENT(REAL)) ),\n",
    "    FUNC_PARAMS( PLOTS[( TYPE('line'))], IMAGE('png')));"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "cac02ce6-a2ea-4ff3-ac82-367d29f8d502",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Now, let's apply this series as the filter</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "37325c82-bbcc-446c-b54f-1f3ef7f737fd",
   "metadata": {},
   "outputs": [],
   "source": [
    "/*************************************************************************/\n",
    "/*      Convolve Filter Kernel with the Original Signal                 */\n",
    "/* Original Signal contained sinusoids at: 2, 25,50, 100HZ and noise     */\n",
    "/*        Filter should erase everything except 100HZ                  */\n",
    "/*************************************************************************/\n",
    "\n",
    "EXECUTE FUNCTION INTO VOLATILE ART(ApplyLowPass) \n",
    "TD_CONVOLVE(SERIES_SPEC(TABLE_NAME(SINUSOIDS4_noise), SERIES_ID(ID), ROW_AXIS(SEQUENCE(ROW_I)),\n",
    "                        PAYLOAD( FIELDS(MAGNITUDE), CONTENT(REAL))),\n",
    "            SERIES_SPEC(TABLE_NAME(SINUSOIDS4_fil), SERIES_ID(ID), ROW_AXIS(SEQUENCE(ROW_I)),\n",
    "                        PAYLOAD( FIELDS(MAGNITUDE), CONTENT(REAL))),\n",
    "            INPUT_FMT(INPUT_MODE(MATCH)));"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3e749656-a19f-48e9-83ac-065da2ed69d0",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The TD_CONVOLVE function takes one series representing the source series to be filtered and the other series being the\n",
    "filter kernel itself. The resultant output is a source series with the kernel filter applied.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "67f3fcfa-6e0b-4397-830f-cf28178da9ec",
   "metadata": {},
   "outputs": [],
   "source": [
    "SHOW TABLE ApplyLowPass;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "723556ac-9758-4687-a415-7d9fe8e2c86b",
   "metadata": {},
   "outputs": [],
   "source": [
    "/*************************************************************************/\n",
    "/* Verify that the filtered Signal has the desired freq characteristics  */\n",
    "/*                             using the DFFT                            */\n",
    "/*************************************************************************/\n",
    "--DROP TABLE DfftFilteredSins4;\n",
    "EXECUTE FUNCTION INTO VOLATILE ART(DfftFilteredSins4) \n",
    "TD_DFFT(SERIES_SPEC(TABLE_NAME(ApplyLowPass), SERIES_ID(ID), ROW_AXIS(SEQUENCE(ROW_I)),\n",
    "                    PAYLOAD( FIELDS(REAL_MAGNITUDE), CONTENT(REAL)) ),\n",
    "        FUNC_PARAMS( FREQ_STYLE(\"K_INTEGRAL\"), HUMAN_READABLE(1) ),\n",
    "  OUTPUT_FMT( CONTENT(AMPL_PHASE_RADIANS) ) );\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0ba8e30a-7c72-4d34-9e44-e5bc1eeb1868",
   "metadata": {},
   "outputs": [],
   "source": [
    "EXECUTE FUNCTION\n",
    "TD_Plot\n",
    "(   SERIES_SPEC(TABLE_NAME(DfftFilteredSins4), ROW_AXIS(SEQUENCE(ROW_I)),SERIES_ID(ID),\n",
    "                PAYLOAD(FIELDS(Amplitude_real_Magnitude),CONTENT(REAL)) ),\n",
    "    FUNC_PARAMS( PLOTS[( TYPE('line') )], IMAGE('png'))\n",
    ");"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8041b262-4308-43cd-9b67-b059dedda4ff",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>5. Cleanup</b></p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "195f2d21-4abe-4376-9693-b3a79f2350bb",
   "metadata": {},
   "outputs": [],
   "source": [
    "Drop TABLE genData;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4e23b0a5-5110-487f-b10c-faf88f9cbf55",
   "metadata": {},
   "outputs": [],
   "source": [
    "DROP TABLE SINUSOIDS4;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6676bc73-7a9e-429f-8131-b641c3a1a6ff",
   "metadata": {},
   "outputs": [],
   "source": [
    "DROP TABLE SINUSOIDS4_noise;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c04e5a03-c78d-454e-95c4-4014ac783aa9",
   "metadata": {},
   "outputs": [],
   "source": [
    "Drop TABLE SINUSOIDS4_fil;"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "eab13083-5a41-4506-b238-1e1e33a518c9",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In this demo we have seen how we can transform timeseries to frequency domain to get significant frequencies and vice-versa and how we can filter out the frequencies from our signal series.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ac1a9f76-2ba1-4118-9d7e-4d64bf207ec7",
   "metadata": {},
   "source": [
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>Reference Links:</b></p>\n",
    "<ul style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "    <li> Unbounded Array Framework: <a href = 'https://docs.teradata.com/r/Teradata-VantageTM-Unbounded-Array-Framework-Time-Series-Reference/Unbounded-Array-Framework'>https://docs.teradata.com/r/Teradata-VantageTM-Unbounded-Array-Framework-Time-Series-Reference/Unbounded-Array-Framework</a></li>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c1c9db8e-d80d-4b9e-9254-54c4eaa3ddbc",
   "metadata": {},
   "source": [
    "<footer style=\"padding-bottom:35px; background:#f9f9f9; border-bottom:3px solid #00233C\">\n",
    "    <div style=\"float:left;margin-top:14px\">ClearScape Analytics™</div>\n",
    "    <div style=\"float:right;\">\n",
    "        <div style=\"float:left; margin-top:14px\">\n",
    "            Copyright © Teradata Corporation - 2023. All Rights Reserved\n",
    "        </div>\n",
    "    </div>\n",
    "</footer>"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Teradata SQL",
   "language": "Teradata SQL",
   "name": "teradatasql"
  },
  "language_info": {
   "codemirror_mode": "Teradata SQL",
   "file_extension": ".tdrs",
   "mimetype": "application/vnd.teradata.resultset",
   "name": "Teradata SQL",
   "nbconvert_exporter": "",
   "pygments_lexer": "",
   "version": "16.20"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}