· 6 years ago · Dec 26, 2019, 02:12 PM
1-- ################################################################
2-- ################## AVERAGE QTY per store/article ###############
3-- ################################################################
4
5DROP TABLE IF EXISTS idr_stores;
6CREATE TEMP TABLE IF NOT EXISTS idr_stores
7(
8 but_idr_business_unit INT8
9)
10DISTSTYLE ALL
11SORTKEY (but_idr_business_unit);
12
13INSERT INTO idr_stores
14SELECT
15bu.but_idr_business_unit
16FROM cds.d_business_unit bu
17WHERE bu.but_idr_business_unit IN (33585,33586,33587,33588,33589,33591,33592,33593,33594,33595,33596,33597,33598,33599,33600,33601,33602,33603,33604,33605,33606,33607,33608,33609,33610,33611,33612,33613,33614,33615,33616,33617,33618,33619,33620,33621,33622,33623,33624,33625,33626,33627,33628,33629,33630,33631,33632,33633,33634,33636,33637,33638,33639,33640,33641,33642,33643,33644,33645,33646,33647,33648,33649,33650,33651,33653,33654,33655,33656,33657,33658,33659,33660,33661,33664,33665,33666,33667,33668,33669,33670,33672,33673,33674,33675,33676,33677,33680,33685,33686,33688,33690,33691,33692,33693,33694,33695,33696,33697,33698,33700,33701,33702,33703,33704,33705,33706,33708,33710,33711,33716,33717,33718,33719,33720,33721,33723,33724,33725,33727,33728,33731,33732,33733,33734,33735,33736,33740,33741,33742,33744,33746,33747,33748,33749,33750,33752,33753,33755,33756,33757,33758,33760,33761,33762,33764,33765,33766,33767,33768,33769,33770,33771,33772,33773,33774,33775,33776,33777,33778,33780,33782,33784,33785,33786,33787,33790,33791,33793,33795,33796,33797,33798,33799,33801,33802,33803,33804,33805,33806,33807,33808,33814,33815,33816,33817,33818,33819,33820,33821,33824,33825,33829,33830,33832,33833,33835,33837,33838,33839,33840,33842,33845,33848,33849,33850,33851,33852,33854,33857,33858,33859,33861,33862,33863,33864,33865,33868,33872,33873,33874,33875,33877,33878,33880,33881,33883,33887,33888,33889,33892,33893,33894,33896,33897,33898,33899,33900,33902,33903,33904,33906,33907,33908,33911,33912,33913,33917,33918,33919,33920,33921,33923,33924,33925,33928,33931,33934,33935,33936,33937,33938,33939,33940,33941,33942,33945,33946,33947,33948,33949,33950,33952,33953,33954,33957,33958,33961,33963,33964,33966,33967,33968,33969,33970,33974,33976,33977,33978,33979,33980,33981,33983,33984,33985,33986,33987,33988,33990,33991,33992,33994,33995,33996,34002,34003,34004,34008,34010,34011,34012,34014,34015,34019,34020,34023,34024,34025,34026,34027,34028,34029,34030,34033,34035,34036,34037,34038,34039,34040,34041,34042,34043,34044,34046,34047,34048,34049,34050,34051,34054,34055,34056,34057,34058,34059,34062,34063,34065,34066,34069,34070,34071,34072,34073,34074,34076,34079,34080,34083,34085,34087,34088,34092,34095,34096,34097,34099,34103,34104,34106,34107,34108,34110,34111,34115,34116,34118,34119,34120,34121,34122,34123,34124,34125,34126,34127,34128,34132,34133,34134,34135,34136,34144,34146,34147,34148,34152,34156,34158,34160,34161,34162,34163,34164,34165,34169,34174,34175,34176,34179,34182,34183,34195,34196,34197,34198,34199,34201,34202,34203,34204,34205,34206,34207,34208,34209,34211,34212,34213,34214,34215,34216,34217,34218,34219,34220,34224,34231,34233,34234,34235,34236,34237,34239,34240,34241,34242,34243,34244,34245,34246,34247,34248,34249,34250,34251,34252,34253,34256,34258,34260,34261,34262,34263,34270,34271,34272,34273,34274,34275,34277,34278,34281,34283,34286,34287,34288,34289,34290,34300,34301,34303,34304,34305,34307,34308,34309,34311,34312,34314,34315,34316,34317,34320,34322,34324,34325,34329,34330,34331,34332,34333,34334,34335,34337,34339,34340,34341,34343,34344,34347,34348,34349,34350,34351,34352,34353,34354,34355,34356,34357,34358,34359,34361,34362,34363,34364,34365,34366,34367,34368,34369,34370,34371,34372,34378,34379,34380,34381,34383,34385,34386,34387,34391,34392,34394,34395,34396,34397,34398,34399,34400,34402,34403,34404,34405,34406,34407,34408,34410,34412,34413,34414,34415,34416,34418,34419,34420,34423,34426,34427,34428,34429,34430,34431,34435,34436,34438,34439,34441,34443,34444,34445,34446,34447,34448,34449,34450,34451,34452,34454,34455,34456,34458,34459,34460,34461,34462,34463,34464,34465,34466,34467,34469,34471,34472,34473,34474,34475,34476,34478,34479,34481,34484,34487,34488,34490,34491,34492,34493,34494,34495,34496,34498,34499,34500,34501,34502,34503,34505,34506,34507,34509,34510,34511,34512,34513,34515,34519,34520,34521,34523,34524,34525,34526,34527,34528,34532,34533,34534,34535,34536,34537,34538,34539,34543,34546,34547,34548,34549,34550,34551,34552,34553,34554,34555,34556,34559,34560,34561,34562,34563,34564,34565,34567,34570,34571,34572,34573,34574,34575,34576,34577,34578,34579,34580,34581,34582,34583,34584,34586,34587,34589,34590,34591,34594,34596,34597,34600,34610,34612,34614,34615,34616,34617,34618,34619,34620,34622,34623,34627,34628,34629,34630,34631,34633,34634,34635,34636,34637,34638,34639,34640,34641,34642,34643,34644,34645,34646,34647,34648,34649,34650,34651,34652,34653,34654,34656,34657,34658,34659,34666,34667,34668,34669,34670,34671,34673,34675,34676,34677,34678,34679,34680,34681,34682,34687,34688,34690,34691,34693,34696,34700,34703,34704,34706,34707,34708,34709,34710,34711,34713,34719,34720,34721,34722,34723,34724,34726,34727,34728,34729,34730,34732,34733,34734,34737,34738,34739,34740,34741,34743,34744,34745,34746,34747,34748,34749,34750,34751,34752,34754,34755,34756,34757,34758,34759,34760,34761,34762,34763,34764,34765,34766,34767,34768,34769,34770,34771,34772,34773,34774,34775,34776,34777,34778,34779,34780,34781,34782,34783,34784,34785,34786,34787,34788,34791,34792,34793,34794,34795,34796,34797,34798,34802,34803,34804,34805,34806,34807,34808,34809,34810,34811,34812,34813,34816,34817,34818,34820,34822,34823,34824,34825,34835,34836,34837,34838,34839,34840,34841,34843,34844,34845,34846,34852,34853,34854,34855,34856,34857,34858,34862,34864,34865,34866,34869,34870,34871,34872,34875,34876,34877,34878,34879,34880,34881,34882,34883,34885,34886,34887,34888,34890,34891,34892,34901,34902,34909,34911,34912,34914,34915,34916,34917,34918,34919,34920,34921,34939,34940,34941,34942,34943,34945,34947,34949,34953,261400,263002,263003,263249,263384,263621,263622,263893,264621,305479,305480,305481,305483,307429,307789,307929,307930,308211,308503,308798,309089,309090,309091,309650,309651,309652,310365,310529,310850,311138,311139,311811,312098,312099,312433,312993,314068,314101,314105,314180,314182,314259,314263,315781,315827,316081,316467,316631,316931,317781,318035,318277,318489,318631,319047,319048,319129,319210,319681,320120,320248,320279,320580,320729,320810,321139,321249,321795,321861,321862,321863,322098,322099,322177,323010,323089,323091,323428,323665,324700,324727,324728,324729,324769,324850,325285,326463,326767,326769,326818,326909,327083,327122,327217,327513,327778,340404,340416,340613,340653,340660,340706,340711,340712,340883,340888,340929,340940,341030,341082,341129,341142,341196,341222,341357,341468,341631,344493,344659,344787,344975,345125,345372,345503,345733,345820,345822,345854,345873,346141,346516,346517,347455,347578,347766,348154,348470,348729,348747,348926,595612,596093,596248,596363,596542,596598,596839,596909,597150,597153,597191,597506,597561,597590,598314,657045,657455,657617,658583,659005,659064,659120,659339,659410,660533,660858,662052,662110,663447,663618,663978,664061,664148,664384,664455,664517,664536,664553,665160,666104,667213,668171,668631,668768,669558,669711,670558,670629,670796,670852,671184,671699,671834,671874,672345,672468,672648,673188,673243,673931,673985,673997,675090,675572,675737,676016,676257,676349,676409,676655,676881,677069,677871,677874,678619,679421,680480,684897,685652,685730,686369,686786,686820,687497,687599,689483,705462,705637,706746,707120,707756,708308,708366,708751,709025,710230,712325,712428,712683,713079,714041,714991,715640,718446,719080,719294,720070,720425,720896,720942,721585,721653,721693,721869,722280,722339,723115,723163,723200,723583,723703,723804,723959,725121,726955,728905,731626,732456,910211,949753,954167,954951,957722,957730,957845,958201,958775,959326,959658,960074,961655,962002,962009,962407,962429,962486,963364,963553,963580,963948,964912,965015,966813,968376,968392,970826,972710,974020,974795,974832,978068,978669,992104,992406,992834,997044,998610,998961,999836,1000085,1006247,1007252,1008131,1010325,1010427,1010444,1010670,1011258,1011533,1011722,1012012,1013359,1024168,1027001,1027588,1032261,1032610,1037709,1038766)
18AND bu.but_num_typ_but = 7;
19--2sec
20
21-- ################################################################
22-- ################## AVERAGE QTY per store/article ###############
23-- ################################################################
24--3m22s
25DROP TABLE IF EXISTS mean_qty_article;
26CREATE TEMP TABLE IF NOT EXISTS mean_qty_article
27(
28 cnt_idr_country SMALLINT,
29 but_idr_business_unit INT8,
30 sku_idr_sku INT8,
31 transac_count INTEGER,
32 mean_qty NUMERIC(22,2),
33 stddev_qty NUMERIC(22,2)
34)
35DISTKEY (sku_idr_sku)
36SORTKEY (sku_idr_sku, but_idr_business_unit);
37
38TRUNCATE mean_qty_article;
39
40INSERT INTO mean_qty_article
41SELECT td.cnt_idr_country,
42 td.but_idr_business_unit,
43 td.sku_idr_sku,
44 COUNT(td.the_transaction_id) AS transac_count,
45 AVG(td.f_qty_item*1.00) AS mean_qty,
46 STDDEV_POP(td.f_qty_item*1.00) AS stddev_qty
47FROM cds.f_transaction_detail_last_two_years td
48INNER JOIN idr_stores st
49 ON st.but_idr_business_unit = td.but_idr_business_unit
50WHERE td.tdt_date_to_ordered >= DATEADD (year,-2,CURRENT_DATE)
51AND td.sku_idr_sku <> 0
52AND td.tdt_type_detail = 'sale'
53AND td.f_qty_item > 0
54GROUP BY td.cnt_idr_country,
55 td.but_idr_business_unit,
56 td.sku_idr_sku;
57
58ANALYZE mean_qty_article;
59
60-- ################################################################
61-- ################## AVERAGE QTY per country/article #############
62-- ################################################################
63DROP TABLE IF EXISTS mean_qty_cnt_article;
64CREATE TEMP TABLE IF NOT EXISTS mean_qty_cnt_article
65(
66 cnt_idr_country SMALLINT,
67 sku_idr_sku INT8,
68 transac_count INTEGER,
69 mean_cnt_qty NUMERIC(22,2),
70 stddev_cnt_qty NUMERIC(22,2)
71)
72DISTKEY (sku_idr_sku)
73SORTKEY (sku_idr_sku);
74
75TRUNCATE mean_qty_cnt_article;
76
77-- cds.f_transaction_detail (the_transaction_id),
78
79INSERT INTO mean_qty_cnt_article
80SELECT td.cnt_idr_country,
81 td.sku_idr_sku,
82 COUNT(td.the_transaction_id) AS transac_count,
83 AVG(td.f_qty_item*1.00) AS mean_qty,
84 STDDEV_POP(td.f_qty_item*1.00) AS stddev_qty
85FROM cds.f_transaction_detail_last_two_years td
86INNER JOIN idr_stores st
87 ON st.but_idr_business_unit = td.but_idr_business_unit
88WHERE td.tdt_date_to_ordered >= DATEADD (year,-2,CURRENT_DATE)
89AND td.sku_idr_sku <> 0
90AND td.tdt_type_detail = 'sale'
91AND td.f_qty_item > 0
92GROUP BY td.cnt_idr_country,
93 td.sku_idr_sku;
94
95ANALYZE mean_qty_cnt_article;
96
97-- ################################################################
98-- ################## Copy ftd for optimization ###################
99-- ################################################################
100DROP TABLE IF EXISTS raw_transactions;
101CREATE TEMP TABLE IF NOT EXISTS raw_transactions
102(
103 cnt_idr_country SMALLINT,
104 but_idr_business_unit NUMERIC(9),
105 sku_idr_sku INT8,
106 the_transaction_id VARCHAR(60),
107 tdt_date_to_ordered DATE,
108 f_qty_item INTEGER
109)
110
111DISTKEY (but_idr_business_unit)
112SORTKEY (but_idr_business_unit,sku_idr_sku);
113
114INSERT INTO raw_transactions
115SELECT td.cnt_idr_country
116, td.but_idr_business_unit
117, td.sku_idr_sku
118, td.the_transaction_id
119, td.tdt_date_to_ordered
120, td.f_qty_item
121FROM cds.f_transaction_detail_current td
122WHERE td.tdt_date_to_ordered >= DATEADD (day,-3,CURRENT_DATE) -- TO SUPPRESS
123AND td.sku_idr_sku <> 0
124AND td.tdt_type_detail = 'sale'
125AND td.f_qty_item > 0
126
127-- ################################################################
128-- ################## Averages per article/store ##################
129-- ################################################################
130DROP TABLE IF EXISTS bulk_transactions;
131CREATE TEMP TABLE IF NOT EXISTS bulk_transactions
132(
133 cnt_idr_country SMALLINT,
134 but_idr_business_unit NUMERIC(9),
135 but_num_business_unit NUMERIC(9),
136 but_name_business_unit VARCHAR(36),
137 the_transaction_id VARCHAR(60),
138 tdt_date_to_ordered DATE,
139 mdl_num_model_r3 BIGINT,
140 mdl_label VARCHAR(150),
141 num_article BIGINT,
142 quantity INTEGER,
143 transac_count INTEGER,
144 store_mean_qty NUMERIC(22,2),
145 store_stddev_qty NUMERIC(22,2),
146 cnt_mean_qty NUMERIC(22,2),
147 cnt_stddev_qty NUMERIC(22,2),
148 corrected_bulk NUMERIC(22,2),
149 is_bulk BOOLEAN
150)
151DISTKEY (but_idr_business_unit)
152SORTKEY (but_num_business_unit,mdl_num_model_r3,num_article);
153
154TRUNCATE bulk_transactions;
155
156INSERT INTO bulk_transactions
157SELECT rt.cnt_idr_country,
158 rt.but_idr_business_unit,
159 bu.but_num_business_unit,
160 bu.but_name_business_unit,
161 rt.the_transaction_id,
162 rt.tdt_date_to_ordered,
163 sk.mdl_num_model_r3,
164 sk.mdl_label,
165 sk.sku_num_sku_r3,
166 rt.f_qty_item,
167 mq.transac_count AS transac_count,
168 mq.mean_qty AS store_mean_qty,
169 mq.stddev_qty AS store_stddev_qty,
170 mcq.mean_cnt_qty AS cnt_mean_qty,
171 mcq.stddev_cnt_qty AS cnt_stddev_qty,
172 CASE
173 WHEN (mq.transac_count < 30 OR store_stddev_qty > 2 * cnt_stddev_qty) THEN
174 CASE WHEN rt.f_qty_item > 5 * cnt_mean_qty
175 THEN cnt_mean_qty
176 ELSE rt.f_qty_item
177 END
178 ELSE
179 CASE WHEN rt.f_qty_item > 5 * store_mean_qty
180 THEN store_mean_qty
181 ELSE rt.f_qty_item
182 END
183 END AS corrected_bulk,
184 CASE WHEN (mq.transac_count < 30 OR store_stddev_qty > 2 * cnt_stddev_qty) THEN
185 CASE WHEN rt.f_qty_item > 5 * cnt_mean_qty
186 THEN TRUE
187 ELSE FALSE
188 END
189 ELSE
190 CASE WHEN rt.f_qty_item > 5 * store_mean_qty
191 THEN TRUE
192 ELSE FALSE
193 END
194 END AS is_bulk
195FROM raw_transactions rt
196INNER JOIN cds.d_business_unit bu
197 ON bu.but_idr_business_unit = rt.but_idr_business_unit
198INNER JOIN cds.d_sku sk
199 ON sk.sku_idr_sku = rt.sku_idr_sku
200INNER JOIN mean_qty_article mq
201 ON rt.cnt_idr_country = mq.cnt_idr_country
202 AND mq.but_idr_business_unit = mq.but_idr_business_unit
203 AND rt.sku_idr_sku = mq.sku_idr_sku
204INNER JOIN mean_qty_cnt_article mcq
205 ON rt.cnt_idr_country = mcq.cnt_idr_country
206 AND rt.sku_idr_sku = mq.sku_idr_sku
207--{{com_daily}}WHERE td.tdt_date_to_ordered >= DATEADD (day,-3,CURRENT_DATE)
208--{{com_daily_inv}}WHERE td.tdt_date_to_ordered >= DATEADD (year,-2,CURRENT_DATE)
209;
210
211ANALYZE bulk_transactions;