64 lines
3.2 KiB
SQL
64 lines
3.2 KiB
SQL
WITH range_ts AS (
|
|
SELECT date_trunc('minute'::text, ts.ts) AS ts
|
|
FROM generate_series(( SELECT min("glucose-import"."time") AS min
|
|
FROM "glucose-import"), ( SELECT max("glucose-import"."time") AS max
|
|
FROM "glucose-import"), '00:01:00'::interval) ts(ts)
|
|
), coallated AS (
|
|
SELECT rts.ts,
|
|
gi_prev."time" AS stime,
|
|
gi_next."time" AS etime,
|
|
gi_prev.value AS svalue,
|
|
gi_next.value AS evalue,
|
|
(EXTRACT(epoch FROM rts.ts - gi_prev."time") / EXTRACT(epoch FROM gi_next."time" - gi_prev."time"))::double precision * gi_prev.value + (EXTRACT(epoch FROM gi_next."time" - rts.ts) / EXTRACT(epoch FROM gi_next."time" - gi_prev."time"))::double precision * gi_next.value AS samp_value
|
|
FROM range_ts rts
|
|
LEFT JOIN LATERAL ( SELECT "glucose-import".id,
|
|
"glucose-import".serialnum,
|
|
"glucose-import".devicetype,
|
|
"glucose-import".units,
|
|
"glucose-import".value,
|
|
"glucose-import"."time"
|
|
FROM "glucose-import"
|
|
WHERE "glucose-import"."time" <= rts.ts
|
|
ORDER BY "glucose-import"."time" DESC
|
|
LIMIT 1) gi_prev ON true
|
|
LEFT JOIN LATERAL ( SELECT "glucose-import".id,
|
|
"glucose-import".serialnum,
|
|
"glucose-import".devicetype,
|
|
"glucose-import".units,
|
|
"glucose-import".value,
|
|
"glucose-import"."time"
|
|
FROM "glucose-import"
|
|
WHERE "glucose-import"."time" >= rts.ts
|
|
ORDER BY "glucose-import"."time"
|
|
LIMIT 1) gi_next ON true
|
|
WHERE gi_prev."time" IS NOT NULL AND gi_next."time" IS NOT NULL AND gi_next."time" <> gi_prev."time"
|
|
), collected AS (
|
|
SELECT coallated.ts AS "time",
|
|
ema(coallated.samp_value::real, 0.33::real) OVER (ORDER BY coallated.ts) AS smoothval,
|
|
coallated.samp_value AS absvalue
|
|
FROM coallated
|
|
), firstorder AS (
|
|
SELECT collected."time",
|
|
collected.smoothval,
|
|
collected.absvalue,
|
|
collected.smoothval - lag(collected.smoothval, 1) OVER (ORDER BY collected."time") AS delta1st
|
|
FROM collected
|
|
), secondorder AS (
|
|
SELECT firstorder."time",
|
|
firstorder.smoothval,
|
|
firstorder.absvalue,
|
|
firstorder.delta1st,
|
|
firstorder.delta1st - lag(firstorder.delta1st, 1) OVER (ORDER BY firstorder."time") AS delta2nd
|
|
FROM firstorder
|
|
)
|
|
SELECT "time",
|
|
smoothval,
|
|
absvalue,
|
|
delta1st,
|
|
delta2nd,
|
|
ema(delta1st, 0.25::real) OVER (ORDER BY "time") AS smoothdelta1st,
|
|
ema(delta2nd, 0.25::real) OVER (ORDER BY "time") AS smoothdelta2nd,
|
|
stddev(delta1st) OVER (ORDER BY "time" RANGE BETWEEN '02:00:00'::interval PRECEDING AND CURRENT ROW) AS std_dev_delta1st,
|
|
avg(absvalue) OVER (ORDER BY "time" RANGE BETWEEN '3 mons'::interval PRECEDING AND CURRENT ROW) AS "3mo_eabg",
|
|
avg(absvalue) OVER (ORDER BY "time" RANGE BETWEEN '7 days'::interval PRECEDING AND CURRENT ROW) AS week_eabg
|
|
FROM secondorder;
|