I'm trying to categorize data that seem to similar from my database table.

Here's how the table looks like

I need a select query that will categorize my data as in the table below ignoring the unique values.

I can achieve this in pandas but I would appreciate if I also learn how to achieve the same in SQL

CodePudding user response：

with data as (
  select
    id,
    account as account1,
    last_value(account) over (partition by account::numeric) as account2
  from your_table
)
select id, account1, account2 from data where account1 != account2;

CodePudding user response：

It is not an obvious

• data type;
• what mean account2 field the result table;
• which data must place in id field the result table;

Below is the quick and duty solution.

In subquery count unique data by count(*) agregate function.

SELECT id, account::INTEGER
FROM
(select *, count(account::INTEGER) over (partition by account::INTEGER) as count from
(VALUES
(1, '011'),
(2, '11'),
(3, '023'),
(4, '23'),
(5, '456')
) as "ID"(id, account)) as "ID"(id, account)
where count > 1