Window Function caching?
(1) By Akhilesh Airen (airen977) on 2021-12-09 11:35:55 [link] [source]
Hi Team, I would like to understand, does SQLITE evaluates window function for each row separately or will it execute only once as the window is same for each row? select item, location, sales_date, firm_qty, sum(firm_qty) OVER win as sm_ttl from planning_data window win as (RANGE CURRENT ROW) Thanks
(2) By Dan Kennedy (dan) on 2021-12-10 07:05:27 in reply to 1 [source]
Just the once, if I understand the question correctly. And the same value returned for all rows of the query. Window functions do not use nested loops.
In this case the "planning_data" table is scanned once and the rows copied to a temporary table. The sum() is calculated as part of this pass. Then SQLite scans through the temp table, returning rows to the user. This query is really the worst case for window functions - as the database engine needs to see all the rows before it can begin returning data to the user. With a different window definition, SQLite would begin returning rows earlier, and can remove rows from the temp table as they are returned to reduce memory usage.
(3) By Akhilesh Airen (airen977) on 2021-12-13 07:34:06 in reply to 2 [link] [source]
Yes, you got it right.