I am trying to calculate the slope of sales data over the past X days. The code below computes 30 day average sales, but this will be repeated for 7, 14, 30, 60 and 90 days. The next step is to calculate the regression slope. However, I am having trouble generating the X column. I want the X column to be a Row Counter (1, 2, 3, 4, 5...) based on the OVER PARTITION selection. However, I have been unsuccessful with functions such as row_number(), etc.
Visually, I see the data as being:
ROW, SALESinDollarsK
1,540.00
2,422.00
3,454.00
4,627.00
5,289.00
...
--Historical Data looks like this
row_id, department_id, date, salesCount, salesDollarK, salesDollarKAverage, salesDollarKMean, salesDollarKMedium, salesDollarKMin, salesDollarKMax
--Storing 30 day averages into table (THIS WORKS)
INSERT INTO historical_data_avg (department_id, date, avg30sales) (
SELECT historical_data.department_id, historical_data.date,
avg(historical_data.salesDollarK) OVER (PARTITION BY historical_data.department_id ORDER BY historical_data.date DESC ROWS BETWEEN 1 PRECEDING AND 29 FOLLOWING) AS avg30sales
FROM historical_data
GROUP BY historical_data.department_id, historical_data.date, historical_data.salesDollarK
ORDER BY historical_data.department_id, historical_data.date DESC
)
--Storing 30 day regression slopes into table (DOES NOT WORK)
INSERT INTO historical_data_regr_slope (department_id, date, regr_slope30sales) (
SELECT historical_data.department_id, historical_data.date,
regr_slope(row_number(), historical_data.salesDollarK) OVER (PARTITION BY historical_data.department_id ORDER BY historical_data.date DESC ROWS BETWEEN 1 PRECEDING AND 29 FOLLOWING) AS regr_slope30sales
FROM historical_data
GROUP BY historical_data.department_id, historical_data.date, historical_data.salesDollarK
ORDER BY historical_data.department_id, historical_data.date DESC
)
Any suggestions on how to auto-create the regr_slope X column?
