So, the query is simple but i am facing issues in implementing the Sql logic. Heres the query suppose i have records like

  Phoneno Company Date      Amount 
   83838   xyz    20210901  100
   87337   abc    20210902  500
   47473   cde    20210903  600

Output expected is past 7 days progress as running avg of amount for each date (current date n 6 days before)

   Date         amount avg
   20210901     100    100
   20210902     500    300
   20210903     600    400

I tried

  Select date, amount, select 
  avg(lg) from (
  Select case when lag(amount)
  Over (order by NULL) IS NULL
  THEN AMOUNT
  ELSE
  lag(amount)
  Over (order by NULL) END AS LG) 
  From table
  WHERE DATE>=t.date-7) as avg
  From table t;
  

But i am getting wrong avg values. Could anyone please help?

Note: Ive tried without lag too it results the wrong avgs too

CodePudding user response：

You could use a self join to group the dates

select distinct 
       a.dt,
       b.dt as preceding_dt, --just for QA purpose
       a.amt,
       b.amt as preceding_amt,--just for QA purpose
       avg(b.amt) over (partition by a.dt) as avg_amt
from t a
join t b on  a.dt-b.dt between 0 and 6
group by a.dt, b.dt, a.amt, b.amt; --to dedupe the data after the join

If you want to make your correlated subquery approach work, you don't really need the lag.

select dt, 
       amt, 
      (select avg(b.amt) from t b where a.dt-b.dt between 0 and 6) as avg_lg
from t a;

Also the condition DATE>=t.date-7 you used is left open on one side meaning it will qualify a lot of dates that shouldn't have been qualified.

DEMO

CodePudding user response：

You can use analytical function with the windowing clause to get your results:

SELECT DISTINCT BillingDate, 
       AVG(amount) OVER (ORDER BY BillingDate 
                         RANGE BETWEEN TO_DSINTERVAL('7 00:00:00') PRECEDING 
                               AND TO_DSINTERVAL('0 00:00:00') FOLLOWING) AS RUNNING_AVG
FROM accounts
ORDER BY BillingDate;

Here is a DBFiddle showing the query in action (LINK)