SQL Server: средние продажи с критериями диапазона дат

Я новичок в SQL Server (около 10 минут). У меня есть тестовая база данных, которая содержит несколько столбцов (Units, Date, Master ID, AVE Monthly Sales). Среднемесячные продажи пусты.

Справочная информация: я импортирую данные из файлов Excel, файлы создаются из других файлов, и среднее время расчета продаж в Excel сводит меня с ума, поэтому этот пост.

Что я хотел бы сделать, так это заполнить столбец "Средние продажи" средними единицами за последние 6 месяцев на основе даты в соответствующей строке. т.е. если дата 2016-06-31, я должен получить среднюю единицу продаж для этого конкретного Master ID (Master ID не мой уникальный ключ - к вашему сведению) с 2016-01-01 до 2016-06-31 включительно,

Как уже упоминалось, я новичок в SQL, я использую SQL Server Express (работает локально), таблица данных (SALES) имеет около 8 м рядов. Я не уверен, что такое вычисление должно быть выполнено на SQL, но я хотел бы попробовать, чтобы я мог проверить, если он в любом случае быстрее, чем мой текущий подход (Excel).

Было бы здорово, если бы кто-то смог объяснить это на примере.

Спасибо. Отметка.

Источник

2 ответа

Я предполагаю, что вы хотите, чтобы это было динамическим, а не фиксированным значением (чтобы можно было изменять данные и показывать правильное значение). В приведенном ниже примере показано, как я могу сделать это в запросе SELECT (без изменения базовых данных);

Тестовые данные (# = временная таблица)

CREATE TABLE #TestTable (Units int, Date_Field datetime, Master_ID int)
INSERT INTO #TestTable (Units, Date_Field, Master_ID)
VALUES
 (10,'2016-07-06',1)
,(20,'2016-08-06',1)
,(30,'2016-09-06',1)
,(40,'2016-10-06',1)
,(50,'2016-11-06',1)
,(60,'2016-12-06',1)
,(70,'2016-10-06',2)
,(80,'2016-11-06',2)
,(90,'2016-12-06',2)

запрос

SELECT
     tt.Master_ID
     ,tt.Date_Field
     ,tt.Units
     ,SUM(tt2.Units) Sum_Units
FROM #TestTable tt
LEFT JOIN #TestTable tt2
    ON tt2.Date_Field BETWEEN DATEADD(m,-3,tt.Date_Field) AND tt.Date_Field
    AND tt.Master_ID = tt2.Master_ID
GROUP BY tt.Master_ID, tt.Date_Field, tt.Units
ORDER BY tt.Master_ID, tt.Date_Field

Выход

Master_ID   Date_Field                  Units   Sum_Units
1           2016-07-06 00:00:00.000     10      10
1           2016-08-06 00:00:00.000     20      30
1           2016-09-06 00:00:00.000     30      60
1           2016-10-06 00:00:00.000     40      90
1           2016-11-06 00:00:00.000     50      120
1           2016-12-06 00:00:00.000     60      150
2           2016-10-06 00:00:00.000     70      70
2           2016-11-06 00:00:00.000     80      150
2           2016-12-06 00:00:00.000     90      240

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

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

Источник

Замените someuniquefield вашим столбцом первичного ключа, а также протестируйте его, выбрав сначала только select.

UPDATE t SET [AVE Monthly Sales] = x.avgsum FROM table t JOIN
(SELECT someuniquefield ,
  AVG(CASE WHEN date > DATEADD(m, -6, date - DATEPART(d, date) + 1) THEN Units END) OVER (ORDER BY Date DESC) avgsum 
    FROM table  ) as x
ON x.someunqiuefield.=t.someuniquefield
Источник
Другие вопросы по тегам