30m-rolling-average.sql

use timeseriesdb

declare @now datetime2 = sysutcdatetime()

select avg(count) from (select count(*) from (select COLUMN_NAME
                    from (select *
                            from INFORMATION_SCHEMA.COLUMNS
                            where TABLE_SCHEMA = 'default' and TABLE_NAME = 'timeseriestable'
                    )
                    where COLUMN_NAME = '%pattern')
    where time_primary_key <= @now
      and time_primary_key >= dateadd(minute , -30, @now)
    order by time_primary_key) as count;