Windowed Functions and Query Optimizer

Question

I have a table with the following structure.

|anId|     aDate|aNumber|
-------------------------
|   1|2018-01-20|    100|
|   1|2019-01-01|   -100|
|   1|2019-02-01|     10|
|   2|2019-01-02|     40|

I have a query to return, on a specific date, whether or not previous (inclusive) aNumbers sum is > 0 for each .

select
  anId,
  aDate,
  1 as aStatus
from (
  select
    anId,
    aDate,
    sum(aNumber) OVER (
      PARTITION BY anId
      ORDER BY aDate
      ROWS BETWEEN UNBOUNDED PRECEDING AND 0 PRECEDING
    ) as aSum
  from
    myTable
)
where
  aSum > 0
;

So this query would return

|anId|     aDate|aStatus|
-------------------------
|   1|2018-01-20|      1|
|   2|2019-01-02|      1|
|   1|2019-02-01|      1|

Now I've turned the query into a view myView. I'd like to query this view for date ranges. I may query the table daily/monthly/yearly whatever, but I want to be able to export the query results from one date range, and then export/append the results for the next date range.

select
  anId,
  aDate,
  aStatus
from
  myView
where
  aDate between (2018-01-01) and (2018-12-31)
;

Would return

|anId|     aDate|aStatus|
-------------------------
|   1|2018-01-20|      1|

And the next year

select
  anId,
  aDate,
  aStatus
from
  myView
where
  aDate between (2019-01-01) and (2019-12-31)
;

Should return

|anId|     aDate|aStatus|
-------------------------
|   2|2019-01-02|      1|
|   1|2019-02-01|      1|

Allowing me to stitch together the results to get the original, unfiltered, view records.

Ok, now that the stage is set, my concern with this approach is that when I filter the date from the view, it will impact the windowed function.

When I filter on 2019, will the windowed sum still include the 2018 aNumbers? Will my date range filter be applied in the inner select, prior to the sum?

From your results, I guess you could have deducted that the sum will include all previous rows. Otherwise, you wouldn't have any rows for Id 1 as it would be negative. A further test, could be seen if you add the Sum column to your view and see the results with and without filter. — Luis Cazares, Feb 27 '19 at 17:33
The result set in the question was what it `Should return`, if working the way I want it to. Not an actual result. After building out the question I recognized that I could simply create the example data and test it. I've done that, will answer soon. — Jamie, Feb 27 '19 at 17:37

Jamie · Accepted Answer · 2019-07-19T16:30:38.070

After creating this question, I realized it should be simple enough to test it.

CREATE TABLE [dbo].[myTable](
    [anId] [char](36) NOT NULL,
    [aDate] [datetime2](7) NULL,
    [aNumber] [int] NULL
) ON [PRIMARY]
GO

insert into myTable(anId,aDate,aNumber) values ('1','2018-01-20',100);
insert into myTable(anId,aDate,aNumber) values ('1','2019-01-01',-100);
insert into myTable(anId,aDate,aNumber) values ('1','2019-02-01',10);
insert into myTable(anId,aDate,aNumber) values ('2','2019-01-20',40);

Using a sub-select instead of creating the actual view

select
    *
from (
    select
      anId,
      aDate,
      1 as aStatus
    from (
      select
        anId,
        aDate,
        sum(aNumber) OVER (
          PARTITION BY anId
          ORDER BY aDate
          ROWS BETWEEN UNBOUNDED PRECEDING AND 0 PRECEDING
        ) as aSum
      from
        myTable
    ) a
    where
      a.aSum > 0
) b
where
    b.aDate < '2019-01-01'
;

Returns:

|anId|     aDate|aStatus|
-------------------------
|   1|2018-01-20|      1|

And

select
    *
from (
    select
      anId,
      aDate,
      1 as aStatus
    from (
      select
        anId,
        aDate,
        sum(aNumber) OVER (
          PARTITION BY anId
          ORDER BY aDate
          ROWS BETWEEN UNBOUNDED PRECEDING AND 0 PRECEDING
        ) as aSum
      from
        myTable
    ) a
    where
      a.aSum > 0
) b
where
    b.aDate >= '2019-01-01'
;

Returns:

|anId|     aDate|aStatus|
-------------------------
|   2|2019-01-02|      1|
|   1|2019-02-01|      1|

This confirms that the date filter doesn't impact the sums. However it leaves me with some concern that the subquery is sub-optimal, as it may be running sums across significantly more data than necessary. ie. when I want 2018 data, is it still calculating sums for 2019 data?

Windowed Functions and Query Optimizer

1 Answers1