SQL query to remove consecutive duplicates in table

user_826290 0
Viewed 589

I have a table in the format below:

|name |visited_property|visit_date_time    |
|Marry|Residence_inn   |05-25-2020 15:00:01|
|Marry|Residence_inn   |05-25-2020 15:15:01|
|Marry|Residence_inn   |05-25-2020 15:30:01|
|Marry|Hilton_garden   |05-25-2020 17:10:01|
|Marry|Marriott_hotel  |05-25-2020 18:10:01|

|Harry|Hilton_garden   |05-26-2020 10:10:01|
|Harry|Residence_inn   |05-26-2020 12:10:01|
|Harry|Hilton_garden   |05-26-2020 15:10:01|

I want to write a query to get the list of distinct properties (and order of visit) they visit. If they visit the same property consecutively, I'd like to count that as 1 visit. If they visit the same property non-consecutively, I'd like to count the visits separately.

Ideally I would like to replicate the table and get rid of the consecutive duplicates to have something that looks like this (getting rid of Marry's 2nd and 3rd visit to Residence Inn since it's 3 visits to that property in a row, while keeping Harry's both visits to Hilton garden since they are non-consecutive):

|name |visited_property|visit_date_time    |
|Marry|Residence_inn   |05-25-2020 15:00:01|
|Marry|Hilton_garden   |05-25-2020 17:10:01|
|Marry|Marriott_hotel  |05-25-2020 18:10:01|

|Harry|Hilton_garden   |05-26-2020 10:10:01|
|Harry|Residence_inn   |05-26-2020 12:10:01|
|Harry|Hilton_garden   |05-26-2020 15:10:01|

What SQL statement can I use? Appreciate your help.

amazon-redshift sql
2 Answers

Use lag():

select t.*
from (select t.*,
             lag(visited_property) over (partition by name order by visit_date_time) as prev_visited_property
      from t
     ) t
where prev_visited_property is null or prev_visited_property <> visited_property;

This returns the first occurrence of each property for each name.

user_11440350

How about adding a unique constraint. Something like following:

# remove duplicate rows
-- create a new temporary table
CREATE TABLE tmp_table_name LIKE existing_table_name;
-- add a unique constraint
ALTER TABLE tmp_table_name ADD UNIQUE(column_name);
-- scan over the existing table to insert entries in the temporary table
INSERT IGNORE INTO tmp_table_name SELECT * FROM existing_table_name ORDER BY column_name;
select * from tmp_table_name;
-- rename tables
RENAME TABLE existing_table_name TO existing_table_name_backup, tmp_table_name TO existing_table_name;
-- drop the backup table
drop table existing_table_name_backup;
select * from existing_table_name;
user_41950530
Related