Data Condensation

-- 45 years of daily prices
-- https://www.macrotrends.net/2535/coffee-prices-historical-chart-data  (KC)
-- https://www.macrotrends.net/2531/soybean-prices-historical-chart-data (ZS)

drop table if exists #timeseries;

create table #timeseries (
    Classification char(2),
    Timepoint date,
    Measure money,
    primary key (
        Classification,
        Timepoint desc
    )
);

insert into #timeseries (
    Classification,
    Timepoint,
    Measure
)
select 'KC',[date], [price] from Price_KC
union all
select 'ZS',[date], [price] from Price_ZS

declare @windowSize int = 3;

-- select * from #timeseries;

drop table if exists #timeseries_with_mm;

select distinct
    series.Classification,
    series.Timepoint,
    series.Measure,
    percentile_cont(0.5) within group (
        order by windowed_measures.Measure
    ) over (
        partition by series.Classification, series.Timepoint
    ) as MovingMedian
into
    #timeseries_with_mm
from
    #timeseries series
cross apply (
    select
        Measure
    from
        #timeseries window
    where
        window.Classification = series.Classification
    and
        window.Timepoint <= series.Timepoint
    order by
        Classification, Timepoint desc
    offset 0 rows
    fetch next @windowSize rows only
) windowed_measures;

alter table #timeseries_with_mm
add primary key (
    Classification,
    Timepoint desc
);

-- select * from #timeseries_with_mm where Classification = 'KC' order by Timepoint asc;

declare @trendPoints int = 3;

drop table if exists #timeseries_with_mm_ma_md;

select
    series.Classification,
    series.Timepoint,
    series.Measure,
    series.MovingMedian,
    avg(windowed_measures.MovingMedian) as MovingAverageMovingMedian,
    stdevp(windowed_measures.MovingMedian) as MovingDeviationMovingMedian
into
    #timeseries_with_mm_ma_md
from
    #timeseries_with_mm series
outer apply (
    select
        MovingMedian
    from
        #timeseries_with_mm window
    where
        window.Classification = series.Classification
    and
        window.Timepoint <= series.Timepoint
    order by
        Classification, Timepoint desc
    offset 1 rows -- 3 moving medians offset by 1 requires 6 measures
    fetch next @trendPoints rows only
) windowed_measures
group by
    series.Classification,
    series.Timepoint,
    series.Measure,
    series.MovingMedian;

alter table #timeseries_with_mm_ma_md
add primary key (
    Classification,
    Timepoint desc
);

-- accept fluctuations within 3% of the average value
declare @averageComponent float = 0.03;
-- accept fluctuations up to three standard deviations
declare @deviationComponent float = 3.0;

drop table if exists Measure_Analysis;

select
    Classification,
    Timepoint,
    Measure,
    Trend,
    case
        when outlier.Trend is not null
        then (Measure - MovingMedian) / (Measure + MovingMedian)
    end as Significance,
    margin.Tolerance,
    MovingMedian
into
    Measure_Analysis
from
    #timeseries_with_mm_ma_md
cross apply (
    values (
        @averageComponent * MovingAverageMovingMedian + @deviationComponent * MovingDeviationMovingMedian
    )
) margin (Tolerance)
cross apply (
    values (
        case
            when Measure < MovingMedian - margin.Tolerance then '-'
            when Measure > MovingMedian + margin.Tolerance then '+'
        end
    )
) outlier (Trend)
order by
    Classification,
    Timepoint desc;

alter table Measure_Analysis
add primary key (
    Classification,
    Timepoint desc
);

drop table if exists Measure_Condensed;

select
    Classification,
    Timepoint,
    Measure,
    Trend,
    Significance
into
    Measure_Condensed
from (
    select
        trending_and_following_rows.Classification,
        trending_and_following_rows.Timepoint,
        trending_and_following_rows.Measure,
        trending_and_following_rows.Trend,
        trending_and_following_rows.Significance
    from
        Measure_Analysis analysis
    cross apply (
        select
            Classification,
            Timepoint,
            Measure,
            Trend,
            Significance
        from
            Measure_Analysis window
        where
            window.Classification = analysis.Classification
        and
            window.Timepoint >= analysis.Timepoint
        order by
            Classification,
            Timepoint asc
        offset 0 rows
        fetch next 2 rows only
    ) trending_and_following_rows
    where
        analysis.Trend is not null
    union
    select
        trending_and_preceding_rows.Classification,
        trending_and_preceding_rows.Timepoint,
        trending_and_preceding_rows.Measure,
        trending_and_preceding_rows.Trend,
        trending_and_preceding_rows.Significance
    from
        Measure_Analysis analysis
    cross apply (
        select
            Classification,
            Timepoint,
            Measure,
            Trend,
            Significance
        from
            Measure_Analysis window
        where
            window.Classification = analysis.Classification
        and
            window.Timepoint <= analysis.Timepoint
        order by
            Classification,
            Timepoint desc
        offset 0 rows
        fetch next 2 rows only
    ) trending_and_preceding_rows
    where
        analysis.Trend is not null
    union
    select
        analysis.Classification,
        analysis.Timepoint,
        analysis.Measure,
        analysis.Trend,
        analysis.Significance
    from (
        select
            Classification,
            min(Timepoint) as FirstTimepoint,
            max(Timepoint) as LastTimepoint
        from
            Measure_Analysis
        group by
            Classification
    ) first_and_last
    join
        Measure_Analysis analysis
    on
        analysis.Classification = first_and_last.Classification
    and
        analysis.Timepoint in (first_and_last.FirstTimepoint, first_and_last.LastTimepoint)
) condensed;