hitchnsmile hitchnsmile - 2 years ago 212
SQL Question

How to select in Postgresql difference between arrays in rows?

I have a table

CREATE TABLE people
(
id bigserial NOT NULL,
name text
hobbies text[]
)


Data here gets inserted each day.

Now I have few rows. And I want to get the difference in hobbies arrays between rows.

E.g.

id |name | hobbies
1 Tom [fishing, hunting, football]
2 James [fishing, jogging]
3 Dave [football, computer games]


I would compare 1st row with 2nd and 2nd row with 3rd (what's inside 1st row that's not in 2nd). So the result would be:

hobbies
[hunting, football, fishing, jogging]


I want to retrieve the array of differences inside the array.

Answer Source

If you want to have a row per row diff, you can use a combination of EXCEPT and LEAD():

SELECT id, ARRAY_AGG(diff_hobbies)
FROM
(
  SELECT id, unnest(hobbies)
  FROM people
  EXCEPT
  SELECT id, unnest(LEAD(hobbies) OVER (ORDER BY id))
  FROM people
) t(id, diff_hobbies)
GROUP BY id
;
┌────┬─────────────────────────────┐
│ id │          array_agg          │
├────┼─────────────────────────────┤
│  1 │ {football,hunting}          │
│  2 │ {jogging,fishing}           │
│  3 │ {football,"computer games"} │ 
└────┴─────────────────────────────┘
(3 rows)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download