🥇Happy Party na andalana roa fahatsiarovana momba ny fahafantarana ny fisarahana ao amin'ny PostgreSQL10

Sasin-teny na ny fomba nipoiran'ny hevitra fizarana

Manomboka eto ny tantara: Tadidinao ve ny nanombohan'izany rehetra izany. Sambany no nisy ny zava-drehetra. Taorian'ny saika reraka ny loharanon-karena rehetra ho an'ny fanatsarana ny fangatahana, tamin'izany fotoana izany, dia nipoitra ny fanontaniana - inona no manaraka? Toy izany no nipoiran'ny hevitra momba ny fisarahana.

Digression tononkira:
Amin'izay fotoana izay indrindra, satria raha ny fantatra dia nisy ny tahirim-pahombiazana tsy mbola nokarohina... Misaotra asmm ary habr!

Noho izany, ahoana no ahafahanao mampifaly ny mpanjifa, ary manatsara ny fahaizanao manokana?

Mba hanatsorana ny zavatra rehetra araka izay azo atao, dia tsy misy afa-tsy fomba roa hanatsarana tanteraka ny zava-bitan'ny angon-drakitra:
1) Lalana midadasika - mampitombo ny loharanon-karena isika, manova ny fanamafisana;
2) Lalana mafonja - fanatsarana ny fangatahana

Satria, averiko indray, tamin'io fotoana io dia tsy hita intsony izay tokony hovaina amin'ny fangatahana hanafaingana, ny lalana dia nofidina - fanovana endrika latabatra.

Noho izany, mipetraka ny fanontaniana lehibe: inona ary ahoana no hiovantsika?

Fepetra voalohany

Voalohany, misy ity ERD ity (aseho amin'ny fomba notsotsotra amin'ny fepetra):

Endri-javatra manan-danja:

fifandraisana maro samy hafa
ny latabatra dia efa manana fanalahidin'ny fisarahana mety

Fangatahana voalohany:

SELECT
            p."PARAMETER_ID" as  parameter_id,
            pc."PC_NAME" AS pc_name,
            pc."CUSTOMER_PARTNUMBER" AS customer_partnumber,
            w."LASERMARK" AS lasermark,
            w."LOTID" AS lotid,
            w."REPORTED_VALUE" AS reported_value,
            w."LOWER_SPEC_LIMIT" AS lower_spec_limit,
            w."UPPER_SPEC_LIMIT" AS upper_spec_limit,
            p."TYPE_CALCUL" AS type_calcul,
            s."SHIPMENT_NAME" AS shipment_name,
            s."SHIPMENT_DATE" AS shipment_date,
            extract(year from s."SHIPMENT_DATE") AS year,
            extract(month from s."SHIPMENT_DATE") as month,
            s."REPORT_NAME" AS report_name,
            p."SPARAM_NAME" AS SPARAM_name,
            p."CUSTOMERPARAM_NAME" AS customerparam_name
        FROM data w INNER JOIN shipment s ON s."SHIPMENT_ID" = w."SHIPMENT_ID"
             INNER JOIN parameters p ON p."PARAMETER_ID" = w."PARAMETER_ID"
             INNER JOIN shipment_pc sp ON s."SHIPMENT_ID" = sp."SHIPMENT_ID"
             INNER JOIN pc pc ON pc."PC_ID" = sp."PC_ID"
             INNER JOIN ( SELECT w2."LASERMARK" , MAX(s2."SHIPMENT_DATE") AS "SHIPMENT_DATE"
                          FROM shipment s2 INNER JOIN data w2 ON s2."SHIPMENT_ID" = w2."SHIPMENT_ID" 
                          GROUP BY w2."LASERMARK"
                         ) md ON md."SHIPMENT_DATE" = s."SHIPMENT_DATE" AND md."LASERMARK" = w."LASERMARK"
        WHERE 
             s."SHIPMENT_DATE" >= '2018-07-01' AND s."SHIPMENT_DATE" <= '2018-09-30' ;

Vokatry ny famonoana amin'ny angon-drakitra fitsapana:
Cost : 502 997.55
Fotoana famonoana: 505 segondra.

Inona no hitantsika? Fangatahana tsy tapaka, mifototra amin'ny fe-potoana.
Andeha isika hanao ny vinavina lojika tsotra indrindra: raha misy santionany amin'ny fizarana fotoana, hanampy antsika ve izany? Marina izany - fisarahana.

Inona no ho fizarana?

Raha vao jerena dia miharihary ny safidy - fizarazarana fanambaràna ny latabatra "fanaterana" amin'ny alàlan'ny famaha "SHIPMENT_DATE" (mitsambikina lavitra loatra - amin'ny farany dia lasa ratsy kely ny famokarana).

Ahoana ny fisarahana?

Tsy sarotra loatra koa io fanontaniana io. Soa ihany fa ao amin'ny PostgreSQL 10 dia misy mekanika fisarahana amin'ny olombelona ankehitriny.
Noho izany:

Tehirizo fanariam-bolan'ny latabatra loharano - pg_dump source_table
Fafao ny latabatra tany am-boalohany - asio table source_table
Mamorona latabatra ray aman-dreny miaraka amin'ny fizarazarana - mamorona latabatra source_table
Mamorona fizarana - mamorona latabatra source_table, mamorona index
Ampidiro ny fanariam-pako noforonina tamin'ny dingana 1 - pg_restore

Scripts ho an'ny fisarahana

Ho an'ny fahatsorana sy ny fanamorana dia natambatra ho script iray ny dingana 2,3,4.

Noho izany:
Tehirizo fanariam-pamokarana ny latabatra loharano

pg_dump postgres --file=/dump/shipment.dmp --format=c --table=shipment --verbose > /dump/shipment.log 2>&1

Fafao ny latabatra loharano + Mamorona latabatra ray aman-dreny miaraka amin'ny fizarazarana + Mamorona fisarahana

--create_partition_shipment.sql
do language plpgsql $$
declare 
rec_shipment_date RECORD ;
partition_name varchar;
index_name varchar;
current_year varchar ;
current_month varchar ;
begin_year varchar ;
begin_month varchar ;
next_year varchar ;
next_month varchar ;
first_flag boolean ;
i integer ;
begin
  RAISE NOTICE 'CREATE TEMPORARY TABLE FOR SHIPMENT_DATE';
  CREATE TEMP TABLE tmp_shipment_date as select distinct "SHIPMENT_DATE" from shipment order by "SHIPMENT_DATE" ;

  RAISE NOTICE 'DROP TABLE shipment';
  drop table shipment cascade ;
  
  CREATE TABLE public.shipment
  (
    "SHIPMENT_ID" integer NOT NULL DEFAULT nextval('shipment_shipment_id_seq'::regclass),
    "SHIPMENT_NAME" character varying(30) COLLATE pg_catalog."default",
    "SHIPMENT_DATE" timestamp without time zone,
    "REPORT_NAME" character varying(40) COLLATE pg_catalog."default"
  )
  PARTITION BY RANGE ("SHIPMENT_DATE")
  WITH (
      OIDS = FALSE
  )
  TABLESPACE pg_default;

  RAISE NOTICE 'CREATE PARTITIONS FOR TABLE shipment';

  current_year:='0';
  current_month:='0';

  begin_year := '0' ;
  begin_month := '0'  ;
  next_year := '0' ;
  next_month := '0'  ;

  FOR rec_shipment_date IN SELECT * FROM tmp_shipment_date LOOP
      
      RAISE NOTICE 'SHIPMENT_DATE=%',rec_shipment_date."SHIPMENT_DATE";
      
      current_year := date_part('year' ,rec_shipment_date."SHIPMENT_DATE");
      current_month := date_part('month' ,rec_shipment_date."SHIPMENT_DATE") ; 

      IF to_number(current_month,'99') < 10 THEN
        current_month := '0'||current_month ; 
      END IF ;

      --Init borders
      IF   begin_year = '0' THEN
       first_flag := true ; --first time flag
       begin_year := current_year ;
       begin_month := current_month ;   
   
        IF current_month = '12' THEN
          next_year := date_part('year' ,rec_shipment_date."SHIPMENT_DATE" + interval '1 year') ;
        ELSE
          next_year := current_year ;
        END IF;
     
       next_month := date_part('month' ,rec_shipment_date."SHIPMENT_DATE" + interval '1 month') ;

      END IF;

      -- Check current date into borders NOT for First time
      IF to_date( current_year||'.'||current_month, 'YYYY.MM') >= to_date( begin_year||'.'||begin_month, 'YYYY.MM') AND 
         to_date( current_year||'.'||current_month, 'YYYY.MM') < to_date( next_year||'.'||next_month, 'YYYY.MM') AND 
         NOT first_flag 
      THEN
         CONTINUE ; 
      ELSE
       --NEW borders only for second and after time 
       begin_year := current_year ;
       begin_month := current_month ;   
   
        IF current_month = '12' THEN
          next_year := date_part('year' ,rec_shipment_date."SHIPMENT_DATE" + interval '1 year') ;
        ELSE
          next_year := current_year ;
        END IF;
     
       next_month := date_part('month' ,rec_shipment_date."SHIPMENT_DATE" + interval '1 month') ;

      END IF;      

      partition_name := 'shipment_shipment_date_'||begin_year||'-'||begin_month||'-01-'|| next_year||'-'||next_month||'-01'  ;
 
     EXECUTE format('CREATE TABLE ' || quote_ident(partition_name) || ' PARTITION OF shipment FOR VALUES FROM ( %L ) TO ( %L )  ' , current_year||'-'||current_month||'-01' , next_year||'-'||next_month||'-01'  ) ; 

      index_name := partition_name||'_shipment_id_idx';
      RAISE NOTICE 'INDEX NAME =%',index_name;
      EXECUTE format('CREATE INDEX ' || quote_ident(index_name) || ' ON '|| quote_ident(partition_name) ||' USING btree ("SHIPMENT_ID") TABLESPACE pg_default ' ) ; 

      --Drop first time flag
      first_flag := false ;
   
  END LOOP;

end
$$;

Manafatra ny fanariam-pako

pg_restore -d postgres --data-only --format=c --table=shipment --verbose  shipment.dmp > /tmp/data_dump/shipment_restore.log 2>&1

Fanamarinana ny vokatry ny fisarahana

Inona no vokany? Ny lahatsoratra feno momba ny drafitra famonoana dia lehibe sy mankaleo, noho izany dia azo atao ny mametra ny tenanao amin'ny isa farany.

dia

Cost: 502 997.55
Fotoana famonoana: 505 segondra.

Lasa

Cost: 77 872.36
Fotoana famonoana: 79 segondra.

Tena vokatra tsara. Ahena ny vidiny sy ny fotoana fanatanterahana. Noho izany, ny fampiasana ny fisarahana dia manome ny vokatra andrasana ary, amin'ny ankapobeny, tsy misy mahagaga.

Ataovy faly ny mpanjifa

Ny valin'ny fitsapana dia natolotra ny mpanjifa mba hojerena. Ary rehefa avy nandinika izany izy ireo, dia nomena didim-pitsarana somary tsy nampoizina izy ireo: “Tsara, zarao ny latabatra “data”.

Eny, saingy nanadihady latabatra "fanaterana" hafa tanteraka izahay; ny tabilao "data" dia tsy manana ny saha "SHIPMENT_DATE".

Tsy misy olana, ampio, ovay. Ny tena zava-dehibe dia ny mpanjifa dia afa-po amin'ny vokatra, ny antsipirian'ny fampiharana dia tsy dia zava-dehibe loatra.

Fizarana ny latabatra lehibe "data"

Amin'ny ankapobeny, tsy nisy olana manokana nipoitra. Na izany aza, ny algorithm partitioning, mazava ho azy, dia niova kely.

Manampy ny tsanganana "SHIPMENT_DATA" amin'ny tabilao "data".

psql -h хост -U база -d юзер
=> ALTER TABLE data ADD COLUMN "SHIPMENT_DATE" timestamp without time zone ;

Fenoy ny soatoavin'ny tsanganana "SHIPMENT_DATA" ao amin'ny tabilao "data" miaraka amin'ny sandan'ny tsanganana mitovy anarana avy amin'ny latabatra "shipment"

-----------------------------
--update_data.sql
--updating for altered table "data" to values of "shipment_data" from the table "shipment"
--version 1.0
do language plpgsql $$
declare 
rec_shipment_data RECORD ;
shipment_date timestamp without time zone ; 
row_count integer ;
total_rows integer ;
begin

  select count(*) into total_rows from shipment ; 
  RAISE NOTICE 'Total %',total_rows;
  row_count:= 0 ;

  FOR rec_shipment_data IN SELECT * FROM shipment LOOP

   update data set "SHIPMENT_DATE" = rec_shipment_data."SHIPMENT_DATE" where "SHIPMENT_ID" = rec_shipment_data."SHIPMENT_ID";
   
   row_count:=  row_count +1 ;
   RAISE NOTICE 'row count = % , from %',row_count,total_rows;
  END LOOP;

end
$$;

Tehirizo fanariam-bolan'ny tabilao "data".

pg_dump postgres --file=/dump/data.dmp --format=c --table=data --verbose > /dump/data.log 2>&1</source

Avereno averina ny tabilao voazara "data"

--create_partition_data.sql
--create partitions for the table "wafer data" by range column "shipment_data" with one month duration
--version 1.0
do language plpgsql $$
declare 
rec_shipment_date RECORD ;
partition_name varchar;
index_name varchar;
current_year varchar ;
current_month varchar ;
begin_year varchar ;
begin_month varchar ;
next_year varchar ;
next_month varchar ;
first_flag boolean ;
i integer ;

begin

  RAISE NOTICE 'CREATE TEMPORARY TABLE FOR SHIPMENT_DATE';
  CREATE TEMP TABLE tmp_shipment_date as select distinct "SHIPMENT_DATE" from shipment order by "SHIPMENT_DATE" ;


  RAISE NOTICE 'DROP TABLE data';
  drop table data cascade ;


  RAISE NOTICE 'CREATE PARTITIONED TABLE data';
  
  CREATE TABLE public.data
  (
    "RUN_ID" integer,
    "LASERMARK" character varying(20) COLLATE pg_catalog."default" NOT NULL,
    "LOTID" character varying(80) COLLATE pg_catalog."default",
    "SHIPMENT_ID" integer NOT NULL,
    "PARAMETER_ID" integer NOT NULL,
    "INTERNAL_VALUE" character varying(75) COLLATE pg_catalog."default",
    "REPORTED_VALUE" character varying(75) COLLATE pg_catalog."default",
    "LOWER_SPEC_LIMIT" numeric,
    "UPPER_SPEC_LIMIT" numeric , 
    "SHIPMENT_DATE" timestamp without time zone
  )
  PARTITION BY RANGE ("SHIPMENT_DATE")
  WITH (
    OIDS = FALSE
  )
  TABLESPACE pg_default ;


  RAISE NOTICE 'CREATE PARTITIONS FOR TABLE data';

  current_year:='0';
  current_month:='0';

  begin_year := '0' ;
  begin_month := '0'  ;
  next_year := '0' ;
  next_month := '0'  ;
  i := 1;

  FOR rec_shipment_date IN SELECT * FROM tmp_shipment_date LOOP
      
      RAISE NOTICE 'SHIPMENT_DATE=%',rec_shipment_date."SHIPMENT_DATE";
      
      current_year := date_part('year' ,rec_shipment_date."SHIPMENT_DATE");
      current_month := date_part('month' ,rec_shipment_date."SHIPMENT_DATE") ; 

      --Init borders
      IF   begin_year = '0' THEN
       RAISE NOTICE '***Init borders';
       first_flag := true ; --first time flag
       begin_year := current_year ;
       begin_month := current_month ;   
   
        IF current_month = '12' THEN
          next_year := date_part('year' ,rec_shipment_date."SHIPMENT_DATE" + interval '1 year') ;
        ELSE
          next_year := current_year ;
        END IF;
     
       next_month := date_part('month' ,rec_shipment_date."SHIPMENT_DATE" + interval '1 month') ;

      END IF;

--      RAISE NOTICE 'current_year=% , current_month=% ',current_year,current_month;
--      RAISE NOTICE 'begin_year=% , begin_month=% ',begin_year,begin_month;
--      RAISE NOTICE 'next_year=% , next_month=% ',next_year,next_month;

      -- Check current date into borders NOT for First time

      RAISE NOTICE 'Current data = %',to_char( to_date( current_year||'.'||current_month, 'YYYY.MM'), 'YYYY.MM');
      RAISE NOTICE 'Begin data = %',to_char( to_date( begin_year||'.'||begin_month, 'YYYY.MM'), 'YYYY.MM');
      RAISE NOTICE 'Next data = %',to_char( to_date( next_year||'.'||next_month, 'YYYY.MM'), 'YYYY.MM');

      IF to_date( current_year||'.'||current_month, 'YYYY.MM') >= to_date( begin_year||'.'||begin_month, 'YYYY.MM') AND 
         to_date( current_year||'.'||current_month, 'YYYY.MM') < to_date( next_year||'.'||next_month, 'YYYY.MM') AND 
         NOT first_flag 
      THEN
         RAISE NOTICE '***CONTINUE';
         CONTINUE ; 
      ELSE
       --NEW borders only for second and after time 
       RAISE NOTICE '***NEW BORDERS';
       begin_year := current_year ;
       begin_month := current_month ;   
   
        IF current_month = '12' THEN
          next_year := date_part('year' ,rec_shipment_date."SHIPMENT_DATE" + interval '1 year') ;
        ELSE
          next_year := current_year ;
        END IF;
     
       next_month := date_part('month' ,rec_shipment_date."SHIPMENT_DATE" + interval '1 month') ;


      END IF;      

      IF to_number(current_month,'99') < 10 THEN
        current_month := '0'||current_month ; 
      END IF ;

      IF to_number(begin_month,'99') < 10 THEN
        begin_month := '0'||begin_month ; 
      END IF ;

      IF to_number(next_month,'99') < 10 THEN
        next_month := '0'||next_month ; 
      END IF ;

      RAISE NOTICE 'current_year=% , current_month=% ',current_year,current_month;
      RAISE NOTICE 'begin_year=% , begin_month=% ',begin_year,begin_month;
      RAISE NOTICE 'next_year=% , next_month=% ',next_year,next_month;

      partition_name := 'data_'||begin_year||begin_month||'01_'||next_year||next_month||'01'  ;

      RAISE NOTICE 'PARTITION NUMBER % , TABLE NAME =%',i , partition_name;
      
      EXECUTE format('CREATE TABLE ' || quote_ident(partition_name) || ' PARTITION OF data FOR VALUES FROM ( %L ) TO ( %L )  ' , begin_year||'-'||begin_month||'-01' , next_year||'-'||next_month||'-01'  ) ; 

      index_name := partition_name||'_shipment_id_parameter_id_idx';
      RAISE NOTICE 'INDEX NAME =%',index_name;
      EXECUTE format('CREATE INDEX ' || quote_ident(index_name) || ' ON '|| quote_ident(partition_name) ||' USING btree ("SHIPMENT_ID", "PARAMETER_ID") TABLESPACE pg_default ' ) ; 

      index_name := partition_name||'_lasermark_idx';
      RAISE NOTICE 'INDEX NAME =%',index_name;
      EXECUTE format('CREATE INDEX ' || quote_ident(index_name) || ' ON '|| quote_ident(partition_name) ||' USING btree ("LASERMARK" COLLATE pg_catalog."default") TABLESPACE pg_default ' ) ; 

      index_name := partition_name||'_shipment_id_idx';
      RAISE NOTICE 'INDEX NAME =%',index_name;
      EXECUTE format('CREATE INDEX ' || quote_ident(index_name) || ' ON '|| quote_ident(partition_name) ||' USING btree ("SHIPMENT_ID") TABLESPACE pg_default ' ) ; 

      index_name := partition_name||'_parameter_id_idx';
      RAISE NOTICE 'INDEX NAME =%',index_name;
      EXECUTE format('CREATE INDEX ' || quote_ident(index_name) || ' ON '|| quote_ident(partition_name) ||' USING btree ("PARAMETER_ID") TABLESPACE pg_default ' ) ; 

      index_name := partition_name||'_shipment_date_idx';
      RAISE NOTICE 'INDEX NAME =%',index_name;
      EXECUTE format('CREATE INDEX ' || quote_ident(index_name) || ' ON '|| quote_ident(partition_name) ||' USING btree ("SHIPMENT_DATE") TABLESPACE pg_default ' ) ; 

      --Drop first time flag
      first_flag := false ;

  END LOOP;
end
$$;

Ampidiro ny fanariam-pako noforonina tamin'ny dingana 3.

pg_restore -h хост -юзер -d база --data-only --format=c --table=data --verbose  data.dmp > data_restore.log 2>&1

Mamorona fizarana mitokana ho an'ny angona taloha

---------------------------------------------------
--create_partition_for_old_dates.sql
--create partitions for keeping old dates 
--version 1.0
do language plpgsql $$
declare 
rec_shipment_date RECORD ;
partition_name varchar;
index_name varchar;

begin

      SELECT min("SHIPMENT_DATE") AS min_date INTO rec_shipment_date from data ;

      RAISE NOTICE 'Old date is %',rec_shipment_date.min_date ;

      partition_name := 'data_old_dates'  ;

      RAISE NOTICE 'PARTITION NAME IS %',partition_name;

      EXECUTE format('CREATE TABLE ' || quote_ident(partition_name) || ' PARTITION OF data FOR VALUES FROM ( %L ) TO ( %L )  ' , '1900-01-01' , 
              to_char( rec_shipment_date.min_date,'YYYY')||'-'||to_char(rec_shipment_date.min_date,'MM')||'-01'  ) ; 

      index_name := partition_name||'_shipment_id_parameter_id_idx';
      EXECUTE format('CREATE INDEX ' || quote_ident(index_name) || ' ON '|| quote_ident(partition_name) ||' USING btree ("SHIPMENT_ID", "PARAMETER_ID") TABLESPACE pg_default ' ) ; 

      index_name := partition_name||'_lasermark_idx';
      EXECUTE format('CREATE INDEX ' || quote_ident(index_name) || ' ON '|| quote_ident(partition_name) ||' USING btree ("LASERMARK" COLLATE pg_catalog."default") TABLESPACE pg_default ' ) ; 

      index_name := partition_name||'_shipment_id_idx';
      EXECUTE format('CREATE INDEX ' || quote_ident(index_name) || ' ON '|| quote_ident(partition_name) ||' USING btree ("SHIPMENT_ID") TABLESPACE pg_default ' ) ; 

      index_name := partition_name||'_parameter_id_idx';
      EXECUTE format('CREATE INDEX ' || quote_ident(index_name) || ' ON '|| quote_ident(partition_name) ||' USING btree ("PARAMETER_ID") TABLESPACE pg_default ' ) ; 

      index_name := partition_name||'_shipment_date_idx';
      EXECUTE format('CREATE INDEX ' || quote_ident(index_name) || ' ON '|| quote_ident(partition_name) ||' USING btree ("SHIPMENT_DATE") TABLESPACE pg_default ' ) ; 

end
$$;

Vokatra farany:

dia
Cost: 502 997.55
Fotoana famonoana: 505 segondra.

Lasa
Cost: 68 533.70
Fotoana famonoana: 69 segondra

Mendrika, tena mendrika. Ary raha jerena fa teny an-dalana dia nahavita nahafehy bebe kokoa na latsaka ny mekanika fisarahana ao amin'ny PostgreSQL 10 - vokatra tsara.

Fihoarana herisetra

Azo atao ve ny manao tsaratsara kokoa - ENY, AFAKA!Mba hanaovana izany dia mila mampiasa MATERIALIZED VIEW ianao.
MAMORONA VIEW MATERIALIZED LASERMARK_VIEW

CREATE MATERIALIZED VIEW LASERMARK_VIEW 
AS
SELECT w."LASERMARK" , MAX(s."SHIPMENT_DATE") AS "SHIPMENT_DATE"
FROM shipment s INNER JOIN data w ON s."SHIPMENT_ID" = w."SHIPMENT_ID" 
GROUP BY w."LASERMARK" ;

CREATE INDEX lasermark_vw_shipment_date_ind on lasermark_view USING btree ("SHIPMENT_DATE") TABLESPACE pg_default;
analyze lasermark_view ;

Averinay indray ny fangatahana:
Fanontaniana mampiasa fomba fijery mivaingana

SELECT
            p."PARAMETER_ID" as  parameter_id,
            pc."PC_NAME" AS pc_name,
            pc."CUSTOMER_PARTNUMBER" AS customer_partnumber,
            w."LASERMARK" AS lasermark,
            w."LOTID" AS lotid,
            w."REPORTED_VALUE" AS reported_value,
            w."LOWER_SPEC_LIMIT" AS lower_spec_limit,
            w."UPPER_SPEC_LIMIT" AS upper_spec_limit,
            p."TYPE_CALCUL" AS type_calcul,
            s."SHIPMENT_NAME" AS shipment_name,
            s."SHIPMENT_DATE" AS shipment_date,
            extract(year from s."SHIPMENT_DATE") AS year,
            extract(month from s."SHIPMENT_DATE") as month,
            s."REPORT_NAME" AS report_name,
            p."STC_NAME" AS STC_name,
            p."CUSTOMERPARAM_NAME" AS customerparam_name
        FROM data w INNER JOIN shipment s ON s."SHIPMENT_ID" = w."SHIPMENT_ID"
             INNER JOIN parameters p ON p."PARAMETER_ID" = w."PARAMETER_ID"
             INNER JOIN shipment_pc sp ON s."SHIPMENT_ID" = sp."SHIPMENT_ID"
             INNER JOIN pc pc ON pc."PC_ID" = sp."PC_ID"
             INNER JOIN LASERMARK_VIEW md ON md."SHIPMENT_DATE" = s."SHIPMENT_DATE" AND md."LASERMARK" = w."LASERMARK"
        WHERE 
              s."SHIPMENT_DATE" >= '2018-07-01' AND s."SHIPMENT_DATE" <= '2018-09-30';

Ary mahazo valiny hafa izahay:
dia
Cost: 502 997.55
Fotoana famonoana: Segondra

Lasa
Cost: 42 481.16
Fotoana famonoana: 43 segondra.

Mazava ho azy fa mamitaka ny vokatra mampanantena toy izany, ka mila havaozina ny hevitra. Noho izany dia tsy hanampy betsaka ny fotoana manontolo handraisana data. Fa toy ny fanandramana dia tena mahaliana.

Raha ny marina, araka ny hita dia misaotra indray asmm ary Habru!- Ny fanontaniana dia azo hatsaraina bebe kokoa.

afterword

Noho izany, afa-po ny mpanjifa. SY mila araraoty ny zava-misy.

Asa vaovao: Inona no azonao atao mba hihalalina sy hanitarana?

Ary avy eo tadidiko - ry zalahy, tsy manana fanaraha-maso ny angon-drakitra PostgreSQL izahay.

Amin'ny tanana, mbola misy ny fanaraha-maso amin'ny endrika Cloud Watch amin'ny AWS. Inona anefa no tombony azo amin'ity fanaraha-maso ny DBA ity? Amin'ny ankapobeny, saika tsy misy.

Raha manana fahafahana hanao zavatra mahasoa sy mahaliana ho an'ny tenanao ianao, dia tsy afaka manararaotra izany fahafahana izany ...
HO AN'NY

Toy izao no tonga amin'ny ampahany mahaliana indrindra:

3 Desambra 2018.
Fanapahan-kevitra hanomboka fikarohana momba ny fahafaha-manara-maso ny fanatanterahana ny fangatahana PostgreSQL.

Saingy tantara hafa tanteraka izany.

Mbola hitohy…

Source: www.habr.com

Happy Party na fahatsiarovana roa momba ny fahafantarana ny fizarazarana ao amin'ny PostgreSQL10