Postgresql - Looping through array_agg

Question

I have a table from which I need to calculate the number of times intent_level changes for each id.

Sample Table format :

id | start_time | intent_level
----+------------+--------------
 1  | 2          | status
 1  | 3          | status
 1  | 1          |
 1  | 4          | category
 2  | 5          | status
 2  | 8          |
 2  | 7          | status

I couldn't figure out how to loop through array_agg and compare consecutive elements. Below I tried using array_agg, but then I don't know how to loop through it and compare consecutive elements.

select 
id, 
array_agg (intent_level ORDER BY start_time)
FROM temp.chats
GROUP BY id;

which gives output :

id |          array_agg
----+-----------------------------
 1  | {"",status,status,category}
 2  | {status,status,""}

Desired output is :

id |          array_agg
----+-----------------------------
 1  | 2
 2  | 1

2 (since value changes from "" to status(1 to 2) and status to category(3 to 4))

1 (since value changes from status to ""(2 to 3))

CREATE AND INSERT QUERIES :

create table temp.chats (
    id varchar(5),
    start_time varchar(5),
    intent_level varchar(20)
);

insert into temp.chats values 
('1', '2', 'status'),
('1', '3', 'status'),
('1', '1', ''),
('1', '4', 'category'),
('2', '5', 'status'),
('2', '8', ''),
('2', '7', 'status');

Gordon Linoff · Accepted Answer · 2021-07-27 12:15:44Z

1

Use lag() and aggregate:

select id, count(*)
from (select c.*,
             lag(intent_level) over (partition by id order by start_time) as prev_intent_level
      from temp.chats c
     ) c
where prev_intent_level is distinct from intent_level
group by id;

Here is a db<>fiddle.

Arrays seem quite unnecessary for this.

answered Jul 27, 2021 at 12:15

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Suraj Over a year ago

Thanks for the answer. But the result should be 2 and 1 right? Your query gives 3 and 2.

Suraj Over a year ago

Since LAG() function allows us to access previous row from the given row, I guess the query is comparing the first element with something, which it shouldn't. Also should we use the (partition by id order by start_time) block for intent_level also, so that it's ordered according to the start_time.

Serg Over a year ago

Use lag(intent_level, 1, intent_level) to exclude first row in a group from the changes.

Suraj Over a year ago

Thanks Gordon Linoff and @Serg.

Collectives™ on Stack Overflow

Postgresql - Looping through array_agg

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related