Peppy Peppy - 5 months ago 40
SQL Question

SQL Windowing function: MAX(date) over Partition BY Clause

I have a query as:

select
location_id,
max(created) over (partition by location_id) as "created"
from
device
group by
location_id, created
order by
location_id


This results in max date being repeated against each grp.


21 2015-01-26T18:25:00.000Z

22 2015-10-18T13:21:32.000Z

22 2015-10-18T13:21:32.000Z

22 2015-10-18T13:21:32.000Z


Now if I calculate the difference between the dates:

select
location_id,
date(created),
max(date(created)) - min(date(created)) over (partition by location_id) as "created"
from
device
group by
location_id, created
order by
location_id



21 2015-01-26T00:00:00.000Z 0

22 2015-01-26T00:00:00.000Z 0

22 2015-03-12T00:00:00.000Z 45

22 2015-10-18T00:00:00.000Z 265


Why do I get difference between first record and 2nd and then 3rd? Why not just the difference between max-min repeated through the groups?

Hope I am clear with the question.

Answer

Your second query is parsed like this:

select . . .
       (max(date(created)) -
        min(date(created)) over (partition by location_id)
       ) as "created"
. . .

That is, the windowing clause is only applied to the min(). I think you intend:

select . . .
       (max(date(created) over (partition by location_id) -
        min(date(created)) over (partition by location_id)
       ) as "created"
. . .