Finding data patterns in sequential Postgresql rows

Question

I'd like to ask Postgres how often two occurrences of an event, one occurrence per row, are seen. For example, if I have user events like:

User 1: Clicked button 1, redirected to page 2
User 1: Clicked button 2, redirected to page 3
User 1: Clicked button 18, redirected to page 100
User 1: Clicked button 1, redirected to page 2
User 1: Clicked button 2, redirected to page 3

then I would see the pattern ((button 1, page 2) => (button 2, page 3)) counted as two occurances.

Is this possible, and if so, how?

score 0 · Answer 1 · answered Jan 08 '14 at 07:02

It's a very good question and has a fairly simple solution. Use GROUP BY and HAVING to find out which user shows what sort of repeated behavior.

Please see the fiddle example here which discusses the DDL and the query I have used to get the desired result.

From your description I recommend you create a table for storing user events as follows:

CREATE TABLE t_clickevent (
   clickevent_id        INTEGER,
   user_id              INTEGER,
   clicked_button_id   INTEGER,
   redirected_url_id   INTEGER);

Add any more columns as and when you require. This is just a minimal structure.

Use the query as follows:

SELECT user_id, clicked_button_id,
       redirected_url_id
  FROM t_clickevent
GROUP BY user_id, clicked_button_id,
       redirected_url_id
HAVING count(*) > 1;

Output:

USER_ID     CLICKED_BUTTON_ID     REDIRECTED_URL_ID
----------- --------------------- -----------------
1           1                     2
1           2                     3

Cheers!

Finding data patterns in sequential Postgresql rows

1 Answers1