0

I have a scenario where i need to split rows in a table and assign certain values to a column for each group. The table name is customers(contains around 300K rows) and i need to split the customers in this table into 9 groups and update each group with a specific value in the "Value" column.

Anyone have any idea on how to do this using PostgreSQL?

This is the table structure(only a part)

Create table customers
(Customer_id  integer,
Customer_email TEXT,
updated_time TIME STAMP WITHOUT TIME ZONE,
Value TEXT
)

Insert into customers Values(1,'sss@gmail.com',now(),'')
Insert into customers Values(2,'abc@gmail.com',now(),'')
Insert into customers Values(3,'retro@gmail.com',now(),'')
Insert into customers Values(4,'als@gmail.com',now(),'')
Insert into customers Values(5,'s123@neferce.com',now(),'')
Insert into customers Values(6,'tt@gmail.com',now(),'')
Insert into customers Values(7,'a123@gmail.com',now(),'')
Insert into customers Values(8,'sabc@gmail.com',now(),'')
Insert into customers Values(9,'67890@gmail.com',now(),'')
Insert into customers Values(10,'qwert@gmail.com',now(),'')
Insert into customers Values(11,'dfg@gmail.com',now(),'')
Insert into customers Values(12,'fvc@gmail.com',now(),'')

The above is the sample table structure and values and now i want the customers in this table to be split into 4 groups(in real i have to split into more groups say like 9 or 10) and update the Value column with a specific text for the split groups.

so the first 4 rows will be assigned with a value 'dos-1-1'
second set of 4 rows with a value 'dos-2-2'
Third set of 4 rows with a value 'dos-3-3'
Fourth set of 4 rows with a value 'dos-4-4'

Below is the desired result,

ID        Email              Date         Value
1   "sss@gmail.com"     "2019-06-14"    "dos-1-1"
2   "abc@gmail.com"     "2019-06-14"    "dos-1-1"
3   "retro@gmail.com"   "2019-06-14"    "dos-1-1"
4   "als@gmail.com"     "2019-06-14"    "dos-2-2"
5   "s123@neferce.com"  "2019-06-14"    "dos-2-2"
6   "tt@gmail.com"      "2019-06-14"    "dos-2-2"
7   "a123@gmail.com"    "2019-06-14"    "dos-3-3"
8   "sabc@gmail.com"    "2019-06-14"    "dos-3-3"
9   "67890@gmail.com"   "2019-06-14"    "dos-3-3"
10  "qwert@gmail.com"   "2019-06-14"    "dos-4-4"
11  "dfg@gmail.com"     "2019-06-14"    "dos-4-4"
12  "fvc@gmail.com"     "2019-06-14"    "dos-4-4"

Above is the situation and in real the table consists of around 300k rows and need to split it into 9 groups and update each group with a specific text.

1
WITH cte AS ( SELECT Customer_id, NTILE(4) OVER ()::TEXT "Value" FROM customers )
UPDATE customers
SET "Value" = 'dos-' ||  cte."Value" || '-' ||  cte."Value"
FROM cte
WHERE customers.Customer_id = cte.Customer_id;

fiddle

PS. It's recommended to use some ORDER BY in OVER clause (for example by Customer_id) - without it the result is not deterministic...

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.