Question 90039 in PostgreSQL Question posted in
The official documentation can be found here.

How can I determine the last location of the subscriber for each day? – Postgresql

I have a table:

abonent region_id dttm
7072110988 32722 2021-08-18 13:15
7072110988 32722 2021-08-18 14:00
7072110988 21534 2021-08-18 14:15
7072110988 32722 2021-08-19 09:00
7071107101 12533 2021-08-19 09:15
7071107101 32722 2021-08-19 09:27

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(

Tags:

2

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0 
    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search