I have an R table that contains 1.3 billion rows with a primary key and a unique clustered index on the S, L, P and Date fields. When I execute a query on R alone with LEAD or LAG window functions or by limiting the partition by ROWS:
OFFSET ([SomeData], 1) OVER (PARTITION BY S, L, P ORDER BY Date)
AVG ([SomeOtherData]OVER (PARTITION BY S, L, ORDER BY Date ROWS N PRECEDING)
the execution plan has an unnecessary sort on S, L, P and RowNumber. It's useless. The date must be unique for all S, L and P. In addition, if I perform a SELECT TOP (N), the 1.3 billion rows are analyzed when N lines only are needed.
Curiously, this problem does not occur at all if I do not have a partition on my LEAD / LAG function nor to use RANGE instead of ROW for framing:
OFFSET ([SomeData], 1) OVER (ORDER BY S, L, P, Date)
AVG ([SomeOtherData]) OVER (PARTITION BY S, L, ORDER BY Date Range PRECEDING UNBOUNDED)
but then I do not have proper frames. What should I do differently on the index or query hints to prevent unnecessary sorting and full analysis of the table?