· 4 years ago · Jan 14, 2021, 11:44 AM
1 CREATE TABLE IF NOT EXISTS arch_doze (
2 "UUP_Code" INTEGER,
3 "Code" FLOAT,
4 "Silo" FLOAT,
5 "Mine" VARCHAR(40),
6 "Mark" VARCHAR(8),
7 "Percents" FLOAT,
8 "PercReal" FLOAT,
9 "Product" FLOAT NULL,
10 "TimeBegin" VARCHAR(8) NULL,
11 "TimeEnd" VARCHAR(8) NULL,
12 "Number_layer" FLOAT,
13 "Shift" FLOAT,
14 "Data" TIMESTAMP WITH TIME ZONE,
15 "W" FLOAT,
16 "A" FLOAT,
17 "V" FLOAT,
18 "X" FLOAT,
19 "Y" FLOAT,
20 "P" FLOAT,
21 "S" FLOAT,
22 "NAKLADN" FLOAT,
23 "MSDtimeS" TIMESTAMP WITH TIME zone,
24 "SULFUR" FLOAT,
25 "SECTION" INTEGER NULL,
26 "SHIFTNUMBER" INTEGER ,
27 "R_NULL" FLOAT,
28 "VT" FLOAT,
29 "SUMM_OK" FLOAT,
30 "SIGMA_R" FLOAT,
31 "CONSAMER" INTEGER,
32 "N_PROB" INTEGER,
33 "ADD_DTTM" TIMESTAMP WITH TIME zone DEFAULT now(),
34 "LAST_UPD_DTTM" TIMESTAMP WITH TIME zone NULL,
35 CONSTRAINT arch_doze_pk PRIMARY KEY ("UUP_Code", "Code", "Silo", "Number_layer", "Shift", "Data")
36 );
37
38 option1 = public.get_oprions();
39
40
41 -- ARCH_UUP1 — Архив дозировок УУП1
42 CREATE FOREIGN TABLE IF NOT EXISTS arch_uup1 (
43 "Code" FLOAT,
44 "Silo" FLOAT,
45 "Mine" VARCHAR(40),
46 "Mark" VARCHAR(8),
47 "Percents" FLOAT,
48 "PercReal" FLOAT,
49 "Product" FLOAT,
50 "TimeBegin" VARCHAR(8),
51 "TimeEnd" VARCHAR(8),
52 "Number_layer" FLOAT,
53 "Shift" FLOAT,
54 "Data" TIMESTAMP WITH TIME ZONE,
55 "W" FLOAT,
56 "A" FLOAT,
57 "V" FLOAT,
58 "X" FLOAT,
59 "Y" FLOAT,
60 "P" FLOAT,
61 "S" FLOAT,
62 "NAKLADN" FLOAT,
63 "MSDtimeS" TIMESTAMP WITH TIME ZONE,
64 "SULFUR" FLOAT,
65 "SECTION" INTEGER,
66 "SHIFTNUMBER" INTEGER,
67 "R_NULL" FLOAT,
68 "VT" FLOAT,
69 "SUMM_OK" FLOAT,
70 "SIGMA_R" FLOAT,
71 "CONSAMER" INTEGER,
72 "N_PROB" INTEGER
73 )
74 SERVER mssql_altai
75
76
77 OPTIONS (query 'SELECT * FROM dbo.arch_uup1 WHERE dbo.arch_uup1.Data IS NOT NULL;');
78
79
80
81 WITH v AS (
82 SELECT
83 '1' as "UUP_Code", "Code", "Silo", "Mine", "Mark", "Percents", "PercReal",
84 "Product", "TimeBegin", "TimeEnd", "Number_layer", "Shift", "Data", "W",
85 "A", "V", "X", "Y", "P", "S", "NAKLADN", "MSDtimeS", "SULFUR", "SHIFTNUMBER",
86 "R_NULL", "VT", "SUMM_OK", "SIGMA_R", "CONSAMER", "N_PROB"
87 FROM arch_uup1 where "Data" > interval '5 day' or (SELECT COUNT(*) FROM arch_doze=
88 EXCEPT
89 SELECT
90 "UUP_Code","Code", "Silo", "Mine", "Mark", "Percents", "PercReal",
91 "Product", "TimeBegin", "TimeEnd", "Number_layer", "Shift", "Data", "W",
92 "A", "V", "X", "Y", "P", "S", "NAKLADN", "MSDtimeS", "SULFUR", "SHIFTNUMBER",
93 "R_NULL", "VT", "SUMM_OK", "SIGMA_R", "CONSAMER", "N_PROB"
94 FROM arch_doze
95 )
96 INSERT INTO arch_doze (
97 "UUP_Code","Code", "Silo", "Mine", "Mark", "Percents", "PercReal", "Product",
98 "TimeBegin", "TimeEnd", "Number_layer", "Shift", "Data", "W", "A", "V", "X",
99 "Y", "P", "S", "NAKLADN", "MSDtimeS", "SULFUR", "SHIFTNUMBER", "R_NULL",
100 "VT", "SUMM_OK", "SIGMA_R", "CONSAMER", "N_PROB"
101 )
102 SELECT
103 '1' as "UUP_Code", "Code", "Silo", "Mine", "Mark", "Percents", "PercReal",
104 "Product", "TimeBegin", "TimeEnd", "Number_layer", "Shift", "Data", "W",
105 "A", "V", "X", "Y", "P", "S", "NAKLADN", "MSDtimeS", "SULFUR", "SHIFTNUMBER",
106 "R_NULL", "VT", "SUMM_OK", "SIGMA_R", "CONSAMER", "N_PROB"
107 FROM v ON CONFLICT
108 ("UUP_Code", "Code", "Silo", "Number_layer", "Shift", "Data")
109 DO UPDATE SET
110 "UUP_Code" = EXCLUDED."UUP_Code",
111 "Code" = EXCLUDED."Code",
112 "Silo" = EXCLUDED."Silo",
113 "Mine" = EXCLUDED."Mine",
114 "Mark" = EXCLUDED."Mark",
115 "Percents" = EXCLUDED."Percents",
116 "PercReal" = EXCLUDED."PercReal",
117 "Product" = EXCLUDED."Product",
118 "TimeBegin" = EXCLUDED."TimeBegin",
119 "TimeEnd" = EXCLUDED."TimeEnd",
120 "Number_layer" = EXCLUDED."Number_layer",
121 "Shift" = EXCLUDED."Shift",
122 "Data" = EXCLUDED."Data",
123 "W" = EXCLUDED."W",
124 "A" = EXCLUDED."A",
125 "V" = EXCLUDED."V",
126 "X" = EXCLUDED."X",
127 "Y" = EXCLUDED."Y",
128 "P" = EXCLUDED."P",
129 "S" = EXCLUDED."S",
130 "NAKLADN" = EXCLUDED."NAKLADN",
131 "MSDtimeS" = EXCLUDED."MSDtimeS",
132 "SULFUR" = EXCLUDED."SULFUR",
133 "SECTION" = EXCLUDED."SECTION",
134 "SHIFTNUMBER" = EXCLUDED."SHIFTNUMBER",
135 "R_NULL" = EXCLUDED."R_NULL",
136 "VT" = EXCLUDED."VT",
137 "SUMM_OK" = EXCLUDED."SUMM_OK",
138 "SIGMA_R" = EXCLUDED."SIGMA_R",
139 "CONSAMER" = EXCLUDED."CONSAMER",
140 "N_PROB" = EXCLUDED."N_PROB",
141 "ADD_DTTM" = EXCLUDED."ADD_DTTM",
142 "LAST_UPD_DTTM"= Now();
143