Basically, I have 1000 cells for the same datetime listed in order and corresponding values of KPI1. I want to find the difference in the value of KPI1 for the same cell for 2 consecutive days which means I have to put an offset of 1000
The problem is that this number '1000' is dynamic and the cells increase/decrease dynamically so I'm looking for a way to make the LAG (or any other function for that matter) to remember the cell when it computes the difference.
Aren't there many values of a cell over the course of a day? So wouldn't you want to take the average (or max, min, whatever) of each cell and compare to the average for each cell for the previous day.
WITH base AS (
SELECT t.cell, trunc(t.datetime) AS dt, AVG(kpi1) kpi
FROM schema.table t
GROUP BY t.cell, trunc(t.datetime)
WHERE t.datetime > trunc(SYSDATE) - 7 -- limit to last week
SELECT d.cell, d.dt, (d.kpi - prev_d.kpi) / prev_d.kpi pct_diff
FROM base d
JOIN base prev_d ON d.cell = prev_d.cell AND d.dt = prev_d + 1