我有一个场景,我需要拆分表中的行并将某些值分配给每个组的列。表名是客户(包含大约 300K 行),我需要将此表中的客户分成 9 个组,并在“值”列中使用特定值更新每个组。
任何人都知道如何使用 PostgreSQL 做到这一点?
这是表结构(只有一部分)
Create table customers
(Customer_id integer,
Customer_email TEXT,
updated_time TIME STAMP WITHOUT TIME ZONE,
Value TEXT
)
Insert into customers Values(1,'[email protected]',now(),'')
Insert into customers Values(2,'[email protected]',now(),'')
Insert into customers Values(3,'[email protected]',now(),'')
Insert into customers Values(4,'[email protected]',now(),'')
Insert into customers Values(5,'[email protected]',now(),'')
Insert into customers Values(6,'[email protected]',now(),'')
Insert into customers Values(7,'[email protected]',now(),'')
Insert into customers Values(8,'[email protected]',now(),'')
Insert into customers Values(9,'[email protected]',now(),'')
Insert into customers Values(10,'[email protected]',now(),'')
Insert into customers Values(11,'[email protected]',now(),'')
Insert into customers Values(12,'[email protected]',now(),'')
以上是示例表结构和值,现在我希望将此表中的客户分成 4 个组(实际上我必须分成更多组,比如 9 或 10)并使用特定文本更新 Value 列拆分组。
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'
下面是想要的结果,
ID Email Date Value
1 "[email protected]" "2019-06-14" "dos-1-1"
2 "[email protected]" "2019-06-14" "dos-1-1"
3 "[email protected]" "2019-06-14" "dos-1-1"
4 "[email protected]" "2019-06-14" "dos-2-2"
5 "[email protected]" "2019-06-14" "dos-2-2"
6 "[email protected]" "2019-06-14" "dos-2-2"
7 "[email protected]" "2019-06-14" "dos-3-3"
8 "[email protected]" "2019-06-14" "dos-3-3"
9 "[email protected]" "2019-06-14" "dos-3-3"
10 "[email protected]" "2019-06-14" "dos-4-4"
11 "[email protected]" "2019-06-14" "dos-4-4"
12 "[email protected]" "2019-06-14" "dos-4-4"
以上是这种情况,实际上该表由大约 300k 行组成,需要将其分成 9 个组并使用特定文本更新每个组。