прак№2незак

drop table std2_27.table1;

create table std2_27.table1
(
    id integer,
    not_id integer
)
distributed by (id);

insert into std2_27.table1
select row_number() over() as id, not_id from (
    select generate_series(1,(random() * 50)::integer) as not_id
    from generate_series(1, 10000) as id
) a;

select gp_segment_id, count(*)
from std2_27.table1
group by 1

select (gp_toolkit.gp_skew_coefficients('std2_27.table1'::regclass)).skccoeff;

select not_id, count(*)
from std2_27.table1
group by 1

alter table std2_27.table1
set with (reorganize=true)
distributed by (not_id);

select gp_segment_id, count(*)
from std2_27.table1
group by 1

select(gp_toolkit.gp_skew_coefficients('std2_27.table1'::regclass)).skcoeff;



create table std2_27.table2
(
    id integer,
    not_id integer
)
with(appendoptimized=true, orientation=column, compresstype=zstd, compresslevel=1)
distributed by (id);

create table std2_27.table3
(
    field1 int,
    field2 text
)
distributed replicated;

insert into std2_27.table3
select a, md5(a::text)
from generate_series(1, 2000) a;

select gp_segment_id, count(1) from std2_27.table3 group by 1;



create table std2_27.table4
(
    field1 int,
    field2 text
)
distributed randomly;

insert into std2_27.table4
select a, md5(a::text)
from generate_series(1, 10000) a;

select gp_segment_id, count(1)
from std2_27.table4
group by 1;

truncate table std2_27.table4;

insert into std2_27.table4
select 1, md5(1::text);

select gp_segment_id, count(1)
from std2_27.table4
group by 1;



create table std2_27.table5
(
    field1 int,
    field2 text not null
)
distributed by(field1);

insert into std2_27.table5
select a, null
from generate_series(1, 2000) a;



create table std2_27.table6
(
    field1 int,
    field2 text default 'hello'
)
distributed by(field1);

insert into std0.table6
select a
from generate_series(1, 2000) a;

select * from std2_27.table6;



create table std2_27.table7
(
    field1 int primary key,
    field2 text
)
distributed by(field1);

insert into std2_27.table1 table7
(field1, field2)
values(1, 'PRIMARY KEY test')

insert into std2_27.table7
field1, field2)
values(1, 'PRIMARY KEY test')

insert into std2_27.table7
field1, field2)
values(1, 'PRIMARY KEY test')



create table std2_27.table8
(
    field1 int check (field1 > 10),
    field2 text
)
distributed by(field1);

insert into std2_27.table8
(field1, field2)
values(1, 'check test');


create table std2_27.table1 sales
(
    id int,
    dt date,
    amt decimal(10, 2)
)
distributed by (id)
partition by range (dt)
(
    start (date '2016-01-01') inclusive
    end (date '2017-01-01') exclusive
    every (interval '1 month')
);

insert into std2_27.sales
values(1, '2016-01-02'::date, 145);

insert into std2_27.sales
values(1, '2016-10-02'::date, 145);


select * from std2_27.sales;

select * from std2_27.sales_1_prt_1;

select * from std2_27.sales_1_prt_10;

insert into std2_27.sales
values(1, '2018-01-02'::date, 200);


drop table std2_27.sales;

create table std2_27.sales
(
    id int,
    dt date,
    amt decimal(10,2)
)
distributed by (id)
partition by range(dt)
(
    start (date '2016-01-01') inclusive
    end (date '2017-01-01') exclusive
    every (interval '1 month'),
    default partition def
);

insert into std2_27.sales
values(1, '2018-01-02'::date, 200);

select * from std2_27.sales_1_prt_def;



alter table std2_27.sales
split default partition start ('2018-01-01') end ('2018-02-01') exclusive;

select
    partitiontablename,
    partitionrangestart,
    partitionrangeend
from pg_partitions
where
    tablename='sales'
    and schemaname = 'std2_27'
order by partitionrangestart;

select * from std2_27.sales_1_prt_r790101298;


drop table if exists std2_27.sales_2016

create table std2_27.sales_2016
(
    id int,
    dt date,
    amt decimal(10,2)
)
distributed by (id);

truncate table std2_27.sales;

insert into std2_27.sales_2016
values(1, '2016-01-02'::date, 145);

alter table std2_27.sales
exchange partition for (date '2016-10-01')
with table std2_27.sales_2016
with validation;

select * from std2_27.sales;

drop table std2_27.rank

create table std2_27.rank
(
    id int,
    rank int,
    year int,
    gender char(1),
    count int
)
distributed by (id)
partition by range (year)
(
    start (2006) end (2016) every (1),
    default partition extra
);

select
    partitiontablename,
    partitionrangestart,
    partitionrangeend
from pg_partitions
where
    tablename='rank'
    and schemaname = 'std2_27'
order by partitionrangestart;


-- партицирование по списку значений, если они заранее известны
create table std2_27."list"
(
    id int,
    rank int,
    year int,
    gender char(1),
    count int
)
distributed by (id)
partition by list (gender)
(
    partition girls values ('f'),
    partition boys values ('m'),
    default partition other
);

select
    partitiontablename,
    partitionname,
    partitionlistvalues
from pg_partitions
where
    tablename='list'
    and schemaname = 'std2_27'
order by partitionrangestart;



-- построчное хранение в append-optimized таблице
-- эффективно когда при выборке выбираются все или почти все столбцы таблицы
create table std2_27.lineitem_row (
    "1_shipdate" date null,
    "1_orderkey" int4 null,
    "1_discount" numeric(19, 4) null,
    "1_suppkey" int4 null,
    "1_quantity" int4 null,
    "1_returnflag" varchar(1) null,
    "1_partkey" int4 null,
    "1_linestatus" varchar(1) null,
    "1_tax" numeric(19, 4) null,
    "1_commitdate" date null,
    "1_receipdate" date null,
    "1_shipmode" varchar(10) null,
    "1_shipinstruct" varchar(25) null,
    "1_comment" varchar(44) null
)
with (
    appendonly=true,
    orientation=row,
    compresstype=zstd,
    compresslevel=1
)
distributed by ("1_orderkey");


insert into std2_27.lineitem_row
select * from std2_27.lineitem_ext;