已知postgresql数据库中有数据表ratings,其定义如下:
postgres=# \d ratings;
Table "public.ratings"
Column | Type | Modifiers
---------+---------+-----------
userid | integer | not null
movieid | integer | not null
rating | real | not null
Indexes:
"ratings_pkey" PRIMARY KEY, btree (userid, movieid)
Check constraints:
"ratings_rating_check" CHECK (rating >= 0::double precision AND rating <= 5::double precision)
现要求使用轮询方法将表ratings分区,数据均匀存储到另外3个分区子表中。
以下为实现分区的sql语句,其基于postgresql编写并能执行成功......
1.同时将隔行查询的数据存入另一个新表query9,如下:
CREATE TABLE query9 AS
select userid,movieid
from (select row_number() over(order by movieid,userid) as id,
movieid,
userid
from ratings) as tbl
where tbl.id%3=1;
2.也可写为: