demo.sinefit.sql

demo.sinefit.sql

-- table - __data1 - insert
DROP TABLE IF EXISTS __data1;
CREATE TABLE __data1 AS
    SELECT
        tt,
        SIN(tt) AS yy
    FROM (
        SELECT
            value * 1.0 / (2 * 3.14) AS tt
        FROM GENERATE_SERIES(0, 256)
    );


-- table - __result1 - insert
DROP TABLE IF EXISTS __result1;
CREATE TABLE __result1 AS
    SELECT
        tt,
        yy,
        ROUND(SINEFIT_EXTRACT(__fit_result, 0, 'syy', 1), 4) AS yy_fitted,
        ROUND(SINEFIT_EXTRACT(__fit_result, 0, 'saa', 1), 4) AS amplitude,
        ROUND(SINEFIT_EXTRACT(__fit_result, 0, 'spp', 1), 4) AS phase,
        ROUND(SINEFIT_EXTRACT(__fit_result, 0, 'sww', 1), 4) AS angular_frequency
    FROM (
        SELECT
            tt,
            yy,
            WIN_SINEFIT2(
                1, NULL,
                tt, yy
            ) OVER (
                ORDER BY tt ASC
                ROWS BETWEEN 64 - 1 PRECEDING AND 0 FOLLOWING
            ) AS __fit_result
        FROM __data1
    );


-- chart - chart._03_tradebot_historical_1_month - create
DROP TABLE IF EXISTS chart.__plot1;
CREATE TABLE chart.__plot1 (
    datatype TEXT NOT NULL,
    series_index INTEGER,
    xx REAL,
    yy REAL,
    series_label REAL,
    xx_label TEXT,
    options TEXT
);
INSERT INTO chart.__plot1 (datatype, options)
    SELECT
        'options' AS datatype,
        '{"title":"sinefit","xaxisTitle":"time","yaxisTitle":"amplitude"}' AS options;
INSERT INTO chart.__plot1 (datatype, options, series_index, series_label)
    SELECT
        'series_label' AS datatype,
        JSON_OBJECT(
            'isDummy', 0,
            'isHidden', 0
        ) AS options,
        series_index,
        series_label
    FROM (
        SELECT 1 AS series_index, 'data-actual' AS series_label
        --
        UNION ALL
        --
        SELECT 2 AS series_index, 'data-fitted' AS series_label
    );
INSERT INTO chart.__plot1 (datatype, series_index, xx, yy)
    SELECT
        'yy_value' AS datatype,
        series_index,
        xx,
        yy
    FROM (
        SELECT 1 AS series_index, tt AS xx, yy AS yy FROM __result1
        --
        UNION ALL
        --
        SELECT 2 AS series_index, tt AS xx, yy_fitted AS yy FROM __result1
    );