Optimizing your Vertica SQL query to run current totals

Question

I have table S with time series data:

key day delta 

For this key it is possible, but it is unlikely that the days will be absent.

I would like to build a cumulative column from delta values ​​(positive INTs) to insert this cumulative data into another table. This is what I still have:

 SELECT key, day, SUM(delta) OVER (PARTITION BY key ORDER BY day asc RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), delta FROM S 

In my SQL flavor, the default window suggestion is the RANGE BETWEEN UNLIMITED DIRECT AND CURRENT RANGE, but I left it there to be explicit.

This request is very slow, as an order of magnitude slower than the old broken request, which fills 0s for the total bill. Any suggestions on other cumulative number generation methods?

I reviewed the solutions here: Performing the total number of grouped records in a table

I use RDBM, this is Vertica. Vertica SQL excludes the first sub-task solution, and its query planner predicts that solving the 2nd left outer join is about 100 times more expensive than the analytic form shown above.

Answer 1

Sometimes it’s faster to use a correlated subquery:

 SELECT [key] , [day] , delta , (SELECT SUM(delta) FROM S WHERE [key] < t1.[key]) AS DeltaSum FROM S t1