How to use over – partition by query in SQL in order to get the current, average, and maximum value?

I have this table where it shows a point that was being done by a device at an area and specific location.

working_date    device   points   area   location
19-06-2020        a        1       x       xa   
19-06-2020        a        2       x       xa 
19-06-2020        a        3       x       xa 
19-06-2020        a        4       x       xa
20-06-2020        a        5       x       xa
20-06-2020        a        6       x       xa
20-06-2020        a        7       x       xa
20-06-2020        a        8       x       xa
20-06-2020        a        9       x       xa

I want to get the current, average and also maximum points grouped by area and also location. If I choose any day, the current qty will show the quantity of the latest working date. Meanwhile, average quantity will show the overall average in which the devices were working. Lastly, the maximum qty will show the overall maximum point that being done by the devices.

Based on my table aboves, if I choose 21-06-2020 then the desired results:

working_date  area  location   device   current_qty  avg_qty   max_qty
21-06-2020     x       xa        a         5           4,5        5

the average qty is from total_qty / total_of_date, while the maximum qty is from the maximum quantity from all dates.

The query I have built so far was:

select t1.working_date, t1.device, t1.area, t1.location, t1.points_qty, t1.total_date,
sum(t1.pile_qty) over(partition by t1.working_date) / sum(t1.total_date) over(partition by t1.working_date) as avg_qty,
max(t1.pile_qty) over(partition by t1.working_date) as max_qty
from (
select working_date, device, points, area, location, count(points) as points_qty, count(distinct working_date) as total_date 
from table1 group by device, area, location
group by working_date, device, points, area, location) t1
group by working_date, device, points, area, location, pile_qty, total_date

with the query above, I got:

working_date  area  location   device   current_qty  avg_qty   max_qty
21-06-2020     x       xa        a         5           5          5

How should I write my query in order to get the desired results?

Thanks in advance.

Answer

I think, I have solution for you. However, I am not sure about the answer will provide the correct result in different scenarios. Here is my code below=> Please check the link=>DB-FIDDLE LINK.

WITH CTE AS
    (
      SELECT working_date,area,location,device, 
             COUNT(working_date) GrpCount
      FROM MYTable 
      GROUP BY working_date,area,location,device
    
    ),y AS
    (SELECT area,location,device,GrpCount,
           (SELECT GrpCount FROM CTE WHERE working_date<TO_DATE('21-06-2020','DD-MM-YYYY') ORDER BY working_date DESC LIMIT 1)  current_qty  
    FROM CTE
    )
    SELECT TO_DATE('21-06-2020','DD-MM-YYYY'),area,location,device, 
           MAX(current_qty) current_qty,
           string_agg(GrpCount::text, ',') avg_qty,
           Max(GrpCount) max_qty
    FROM Y
    GROUP BY area,location,device

Note:-Here, you can see, for current_qty I have used your input date 21-06-2020 like (SELECT GrpCount FROM CTE WHERE working_date<TO_DATE('21-06-2020','DD-MM-YYYY') ORDER BY working_date DESC LIMIT 1) current_qty to find current qty. And it gives me your expected result. Please check the code with different range of date range and data.

Advertisement

Answer