I have a table: Description of attributes:

1. abonent – subscriber's number;
2. region_id – id of the region where the subscriber is located;
3. dttm – the day and time of the call.

I need to determine the last location of the subscriber for each day.

Someone help please, I'm new to SQL(

CodePudding user response：

Generate time series could solve the groupings and a MAX from the group should retrieve the last from the group.

 SELECT sq.lastdate ,sq.allregions,sq.allabonents, c.dttm lastdate, c.abonent lastabonent , c.region_id lastregion FROM 
    (SELECT MAX(dttm) lastdate, array_agg(b.region_id) allregions ,array_agg(b.abonent) allabonents , dateRange
    FROM generate_series(current_date - interval '1 year' , current_date , interval '1 day') daterange
    JOIN table AS b ON b.dttm >= daterange AND b.dttm <= daterange interval'1 day' 
    ) AS sq 
    JOIN table AS c ON c.dttm = sq.lastdate 

CodePudding user response：

with helper as (select abonent, region_id, dttm,
                       row_number() over (partition by abonent, date(dttm) 
                                          order by dttm desc) as row_number
                from abonent_log)
select abonent, region_id, dttm 
from helper
where row_number = 1;

The window function row_number() helps to enumerate abonents inside of a group (abonent, date(dttm)).

Also, date() function is used inside of a partition-clause to group values by some date, not the full date-time value.

See the demo.