由于项目开发使用PostGIS数据库，在项目开发过程中，由于数据量很大，因此使用分区表，由于PostGIS不能直接创建分区表，因此首先创建主表（父表），其次，创建分区，最后每个子分区上添加索引。

1.创建主表（父表）

create table sde.wsqt_gas_data
(id                    bigint,device_number         varchar(128),date                  timestamp(6),time                  varchar(255),gast_c                numeric(20, 8),gast_c_sd             numeric(20, 8),gasp_torr             numeric(20, 8),gasp_torr_sd          numeric(20, 8),co_ppm                numeric(20, 8),co_ppm_sd             numeric(20, 8),cod_ppm               numeric(20, 8),cod_ppm_sd            numeric(20, 8),co2_ppm               numeric(20, 8),co2_ppm_sd            numeric(20, 8),co2d_ppm              numeric(20, 8),co2d_ppm_sd           numeric(20, 8),ch4_ppm               numeric(20, 8),ch4_ppm_sd            numeric(20, 8),ch4d_ppm              numeric(20, 8),ch4d_ppm_sd           numeric(20, 8),h2o_ppm               numeric(20, 8),h2o_ppm_sd            numeric(20, 8),ambt_c                numeric(20, 8),ambt_c_sd             numeric(20, 8),rd0_us                numeric(20, 8),rd0_us_sd             numeric(20, 8),rd1_us                numeric(20, 8),rd1_us_sd             numeric(20, 8),fit_flag              varchar(2),temp_status_ma        numeric(20, 8),temp_status_ma_sd     numeric(20, 8),analyzer_status_ma    numeric(20, 8),analyzer_status_ma_sd numeric(20, 8),mpvposition           numeric(20, 8),miu_desc              varchar(32),miu_valve             numeric(20, 8),del_flag              numeric(1),create_by             varchar(36),create_time           timestamp(6),update_by             varchar(36),update_time           timestamp(6),detected_time         timestamp(6),area_code             varchar(12)
) PARTITION BY RANGE (detected_time);

2.创建分区

CREATE TABLE sde.wsqt_gas_data_2024_01 PARTITION OF sde.wsqt_gas_data FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');
CREATE TABLE sde.wsqt_gas_data_2024_02 PARTITION OF sde.wsqt_gas_data FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');
CREATE TABLE sde.wsqt_gas_data_2024_03 PARTITION OF sde.wsqt_gas_data FOR VALUES FROM ('2024-03-01') TO ('2024-04-01');
CREATE TABLE sde.wsqt_gas_data_2024_04 PARTITION OF sde.wsqt_gas_data FOR VALUES FROM ('2024-04-01') TO ('2024-05-01');
CREATE TABLE sde.wsqt_gas_data_2024_05 PARTITION OF sde.wsqt_gas_data FOR VALUES FROM ('2024-05-01') TO ('2024-06-01');
CREATE TABLE sde.wsqt_gas_data_2024_06 PARTITION OF sde.wsqt_gas_data FOR VALUES FROM ('2024-06-01') TO ('2024-07-01');
CREATE TABLE sde.wsqt_gas_data_2024_07 PARTITION OF sde.wsqt_gas_data FOR VALUES FROM ('2024-07-01') TO ('2024-08-01');
CREATE TABLE sde.wsqt_gas_data_2024_08 PARTITION OF sde.wsqt_gas_data FOR VALUES FROM ('2024-08-01') TO ('2024-09-01');
CREATE TABLE sde.wsqt_gas_data_2024_09 PARTITION OF sde.wsqt_gas_data FOR VALUES FROM ('2024-09-01') TO ('2024-10-01');
CREATE TABLE sde.wsqt_gas_data_2024_10 PARTITION OF sde.wsqt_gas_data FOR VALUES FROM ('2024-10-01') TO ('2024-11-01');
CREATE TABLE sde.wsqt_gas_data_2024_11 PARTITION OF sde.wsqt_gas_data FOR VALUES FROM ('2024-11-01') TO ('2024-12-01');
CREATE TABLE sde.wsqt_gas_data_2024_12 PARTITION OF sde.wsqt_gas_data FOR VALUES FROM ('2024-12-01') TO ('2025-01-01');CREATE TABLE sde.wsqt_gas_data_2023_01 PARTITION OF sde.wsqt_gas_data FOR VALUES FROM ('2023-01-01') TO ('2023-02-01');
CREATE TABLE sde.wsqt_gas_data_2023_02 PARTITION OF sde.wsqt_gas_data FOR VALUES FROM ('2023-02-01') TO ('2023-03-01');
CREATE TABLE sde.wsqt_gas_data_2023_03 PARTITION OF sde.wsqt_gas_data FOR VALUES FROM ('2023-03-01') TO ('2023-04-01');
CREATE TABLE sde.wsqt_gas_data_2023_04 PARTITION OF sde.wsqt_gas_data FOR VALUES FROM ('2023-04-01') TO ('2023-05-01');
CREATE TABLE sde.wsqt_gas_data_2023_05 PARTITION OF sde.wsqt_gas_data FOR VALUES FROM ('2023-05-01') TO ('2023-06-01');
CREATE TABLE sde.wsqt_gas_data_2023_06 PARTITION OF sde.wsqt_gas_data FOR VALUES FROM ('2023-06-01') TO ('2023-07-01');
CREATE TABLE sde.wsqt_gas_data_2023_07 PARTITION OF sde.wsqt_gas_data FOR VALUES FROM ('2023-07-01') TO ('2023-08-01');
CREATE TABLE sde.wsqt_gas_data_2023_08 PARTITION OF sde.wsqt_gas_data FOR VALUES FROM ('2023-08-01') TO ('2023-09-01');
CREATE TABLE sde.wsqt_gas_data_2023_09 PARTITION OF sde.wsqt_gas_data FOR VALUES FROM ('2023-09-01') TO ('2023-10-01');
CREATE TABLE sde.wsqt_gas_data_2023_10 PARTITION OF sde.wsqt_gas_data FOR VALUES FROM ('2023-10-01') TO ('2023-11-01');
CREATE TABLE sde.wsqt_gas_data_2023_11 PARTITION OF sde.wsqt_gas_data FOR VALUES FROM ('2023-11-01') TO ('2023-12-01');
CREATE TABLE sde.wsqt_gas_data_2023_12 PARTITION OF sde.wsqt_gas_data FOR VALUES FROM ('2023-12-01') TO ('2024-01-01');

3.子分区添加索引

CREATE INDEX wsqt_gas_data_2023_01_idx ON sde.wsqt_gas_data_2023_01 (detected_time);
CREATE INDEX wsqt_gas_data_2023_02_idx ON sde.wsqt_gas_data_2023_02 (detected_time);
CREATE INDEX wsqt_gas_data_2023_03_idx ON sde.wsqt_gas_data_2023_03 (detected_time);

4.相关大数据学习demo地址:
https://github.com/carteryh/big-data