Как получить среднее значение столбца по дате за последние 3 месяца по сгруппированным столбцам в SQL Server?

ProgramBox

Как получить среднее значение столбца по дате за последние 3 месяца по сгруппированным столбцам в SQL Server?

Post author:admin
Запись опубликована:15 января, 2022
Post category:Вопросы по программированию

#sql #sql-server #join #average

Вопрос:

У меня есть эти 3 таблицы:

Таблица 1

счетчик_ID	Идентификатор дома
123456	567890
123457	567810
123463	567811
123478	567812

Таблица 2

счетчик_ID	Идентификатор владельца	Идентификатор квитанции
123456	0901987	678954
123457	0901987	423567
123463	0901987	543211
123478	0901987	345332

Таблица 3

сумма	Идентификатор владельца	Идентификатор квитанции	Дата
575	0901987	678954	2020-02-20
300	0901987	678954	2020-04-21
450	0901987	678954	2020-05-22
125	0901987	678954	2020-06-21
180	0901987	423567	2020-02-20
350	0901987	423567	2020-03-21
1200	0901987	543211	2020-02-20
600	0901987	543211	2020-03-20
700	0901987	543211	2020-04-20
380	0901987	345332	2020-02-20
475	0901987	345332	2020-04-20
950	0901987	345332	2020-07-20
110	0901987	345332	2020-08-20

Результат, который я хочу создать:

Идентификатор владельца	счетчик_ID	Идентификатор квитанции	Идентификатор дома	avg_Amount
0901987	123456	678954	567890	362
0901987	123457	423567	567810	265
0901987	123463	543211	567811	833
0901987	123478	345332	567812	478

Как создать этот вывод из таблицы 1, Таблицы 2, таблицы 3?

Я хочу рассчитать по следующей формуле в результате, столбец avg_Amount:

Средняя сумма счета-фактуры за последние 3 месяца, но если дни выставления счета одного и того же идентификатора владельца и идентификатора получения меньше 3 (количество(записей) в таблице 3, возьмите среднее значение этих доступных дней.

1. Вы предпринимали какие-либо попытки…? Кем они были? Почему они не сработали? Вы говорите о receipt_ID том , чтобы быть меньше, чем 3 , но ни одна из ценностей для receipt_ID этого < 3 не существует .

2. в таблице 3 есть записи, которые учитываются(receipt_ID)

3. take the average of that available days. что это значит, если это просто среднее значение, чем оно отличается от того, если есть более 3 строк

4. @Charlieface Требуется в среднем за последние 3 месяца. Другими словами, при наличии записей за 4 месяца запрашивается среднее значение за первые 3 месяца. Но если срок регистрации составляет менее 3 месяцев, будет взята средняя арифметическая.

Ответ №1:

Я не совсем понимаю, что вы имеете в виду, говоря, что хотите всего 3 месяца.

Кажется, возможно, вы хотите взять среднее значение всех строк, которые находятся в пределах трех месяцев от самой ранней строки (при разделении на receipt_ID ).

Для этого мы можем использовать функцию окна

 SELECT
  t2.owner_ID,
  t2.counter_ID,
  t2.receipt_ID,
  t1.house_ID,
  avg_Amount = AVG(t3.amount)
FROM Table1 t1
JOIN Table2 t2 ON t2.counter_ID = t1.counter_ID
JOIN (
    SELECT *,
        MaxDate = MAX(t3.[Date]) OVER (PARTITION BY t3.receipt_ID)
    FROM Table3 t3
) t3 ON t3.receipt_ID = t2.receipt_ID
    AND t3.[Date] > DATEADD(month, -12, t3.MaxDate)
GROUP BY
  t2.owner_ID,
  t2.counter_ID,
  t2.receipt_ID,
  t1.house_ID;

1. Привет, Чарли Фейс, спасибо за твой ответ. Извините за задержку. Вакцина против Covid-19 подействовала на меня очень сильно. Поэтому я не мог ответить тебе раньше. Я хочу взять среднее значение суммы, соответствующей данным за последние 12 месяцев, которые сгруппированы по t2.owner_ID, t2.counter_ID, t2.receipt_ID, t1.house_ID. Если сгруппированные данные включают записи менее чем за 12 месяцев, я хочу получить среднее значение по всем записям. Однако; если у него более 12 записей за 12 месяцев, я хочу получить только среднее значение записи за последние 12 месяцев.

2. Что означает «данные за последние 12 месяцев»: последние 12 месяцев, начиная с сегодняшнего дня? Или последние 12 месяцев, доступные для этой группы owner_ID, counter_ID, receipt_ID, house_ID ?

3. Это «последние 12 месяцев, доступных для этой группы owner_ID, counter_ID, receipt_ID, house_ID». В моей таблице примеров я хотел описать ее в течение 3 месяцев.

4. Ах, так тебе нужна MAX дата, а не то MIN . Исправили

5. Большое вам спасибо @Charlieface.

Метки: Как получить среднее значение столбца в MySQL