Postgres identify pattern in string across multi rows in a group by

Question

I have a query that groups rows together by two specific ids, and orders them by a action date. Along with this, a string of if the action date was added or removed is included.

I'd like to identify result sets that follow a pattern other than 'added' -> 'removed' -> ect. such as 'added' -> 'removed' -> 'added' -> 'added'.

If this was a set of data grouped together, I'd like to identify that as a pattern.

For example, I can use this query

select id_a , id_b, string_agg(action_key, ', ') as agg_field from test_table
group by id_a, id_b, link_type 
order by agg_field desc

to get the column agg_field column as

'added, removed, added, added, removed'

but I'm not sure how to identify that result set in the group by as compared to

'added, removed, added, removed'

Gordon Linoff · Accepted Answer · 2021-07-07 20:51:40Z

1

Keep only rows where there is a change:

select id_a , id_b, string_agg(action_key, ', ') as agg_field
from (select t.*,
             lag(action_key) over (partition by id_a, id_b order by action_date) as prev_action_key
      from test_table t
     ) t
where prev_action_key is distinct form action_key
group by id_a, id_b, link_type 
order by agg_field desc;

I would recommend adding order by action_date to the string_agg().

answered Jul 7, 2021 at 20:51

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

Khakis7 Over a year ago

Didn't know lag() was a thing, very cool solution and worked great. Thanks!

Collectives™ on Stack Overflow

Postgres identify pattern in string across multi rows in a group by

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related