Обсуждение: regr_slope function with auto creation of X column
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?
Jason Aleksi wrote > --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? You shoud be able to use a subquery to first generate the relevant row numbers and then in the outer query apply the regr_slope function. You could also try (theory here - the documentation should be improved in this area) two applications of the OVER clause. regr_expr( row_number() over (...), sales ) over (...) You should probably define the window in the main body and refer to it by name if you attempt this. I honestly have no idea if it will work but the syntax you used before is defined as invalid because nothing can come between the function and the OVER part; which negates the possibility of using a single OVER to cover two functions. I would suggest you not intermix window and group by until you get the window working. Then put that into a cte/with and run the group by separately - you might need to put the group by in the cte and the window in the main query. That said I haven't fully contemplated what it is you are attempting to calculate. Typically moving averages are not going to require a group by clause...you just need to add a where clause that can filter out the first N records where the number of input rows is less than N. David J. -- View this message in context: http://postgresql.nabble.com/regr-slope-function-with-auto-creation-of-X-column-tp5828673p5828676.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com.