Блог

Главная - Вопросы по программированию - За последние 7 дней текущие суммы усредняются по мере выполнения на каждую дату

За последние 7 дней текущие суммы усредняются по мере выполнения на каждую дату

#sql #oracle

Вопрос:

Итак, запрос прост, но я сталкиваюсь с проблемами при реализации логики Sql. Вот запрос, предположим, у меня есть такие записи, как

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

Ожидаемый результат-прогресс за последние 7 дней в среднем за каждую дату (текущая дата за 6 дней до этого).

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

Я пытался

   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;

Но я получаю неправильные средние значения. Кто-нибудь может, пожалуйста, помочь?

Примечание: Я тоже пробовал без задержек, это тоже приводит к неправильным средним значениям

Комментарии:

1. либо это mysql, либо oracle, но не то и другое вместе.

2. Помечайте только ту базу данных, которую вы используете.

3. Добавлена база данных

Ответ №1:

Вы можете использовать self join для группировки дат

 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

Если вы хотите, чтобы ваш correlated subquery подход работал, вам на самом деле это не нужно 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;

Если у вас нет нескольких строк на дату, это становится еще проще

 select dt, 
       amt, 
      avg(amt) over (order by dt rows between 6 preceding and current row) as avg_lg
 from t;

Кроме того, условие DATE>=t.date-7 , которое вы использовали, оставлено открытым с одной стороны, что означает, что оно будет соответствовать множеству дат, которые не должны были быть определены.

ДЕМОНСТРАЦИЯ

Комментарии:

1. Также ценю ваши усилия, я хочу знать, почему это сработало в моем запросе

2. @HimanshuAhuja Надеюсь, мое редактирование поможет

3. Великий человек! Вот что я делал, о чем я думал, когда использовал один и тот же запрос, запутанный в предложении where, я думаю

Ответ №2:

Вы можете использовать аналитическую функцию с предложением windowing для получения результатов:

 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;

Вот DBFiddle, показывающий запрос в действии (ССЫЛКА)

Метки: