CREATE DATABASE test;
USE test;
CREATE TABLE refund (
carrier_cd varchar(5), -- 运营商代码
biz_line int, -- 漫游方向，1来访，2出访
bill_month int, -- 退费账期，YYYYMM
sttl_month int, -- 结算账期，YYYYMM
accu_type int, -- 业务类型，1 MOC_L，2 MOC_H，3 MOC_I，4 MTC，5 SMS_MO，6 GPRS
actual_traffic bigint, -- 实际业务量，语音秒，短信条，数据Byte
rounded_traffic bigint, -- 计费业务量，语音秒，短信条，数据Byte
chg_sdr_tap bigint -- Tap金额，单位厘，转换为SDR需除以100万
);
INSERT INTO refund VALUES ('JPNJP', 2, 201810, null, 5, 282538, 282538, 8476140000);
INSERT INTO refund VALUES ('JPNJP', 2, 201810, null, 2, 25075617, 515457, 51631848000);
INSERT INTO refund VALUES ('JPNJP', 2, 201810, null, 3, 1568708, 30860, 12374717000);
INSERT INTO refund VALUES ('TWNPC', 2, 201810, null, 3, 1196791, 23552, 8385517900);
INSERT INTO refund VALUES ('JPNJP', 2, 201810, null, 4, 33725021, 809524, 17593694000);
INSERT INTO refund VALUES ('TWNPC', 2, 201810, null, 2, 17649660, 356366, 39643868650);
INSERT INTO refund VALUES ('JPNJP', 2, 201810, null, 6, 157567000000000, 153877000000, 11660100000000);
INSERT INTO refund VALUES ('JPNJP', 2, 201810, null, 1, 5244735, 113861, 7317109820);
INSERT INTO refund VALUES ('TWNPC', 2, 201810, null, 1, 3322119, 76858, 5831213150);
INSERT INTO refund VALUES ('TWNPC', 2, 201810, null, 5, 120914, 120914, 1348191100);
INSERT INTO refund VALUES ('TWNPC', 2, 201810, null, 4, 22292563, 520581, 5803693590);
INSERT INTO refund VALUES ('TWNPC', 2, 201810, null, 6, 43101500000000, 42093520825, 306000000000);
INSERT INTO refund VALUES ('TWNPC', 2, 201811, null, 4, 1018629, 23667, 264419910);
INSERT INTO refund VALUES ('JPNJP', 2, 201811, null, 1, 124080, 2758, 179118880);
INSERT INTO refund VALUES ('JPNJP', 2, 201811, null, 5, 9709, 9709, 291270000);
INSERT INTO refund VALUES ('JPNJP', 2, 201811, null, 2, 987947, 19900, 2015319000);
INSERT INTO refund VALUES ('TWNPC', 2, 201811, null, 1, 111954, 2565, 178000460);
INSERT INTO refund VALUES ('TWNPC', 2, 201811, null, 3, 53774, 1034, 368292090);
INSERT INTO refund VALUES ('JPNJP', 2, 201811, null, 3, 85396, 1627, 659153000);
INSERT INTO refund VALUES ('JPNJP', 2, 201811, null, 4, 1340152, 31767, 697168000);
INSERT INTO refund VALUES ('TWNPC', 2, 201811, null, 2, 728316, 14665, 1631993070);
INSERT INTO refund VALUES ('TWNPC', 2, 201811, null, 5, 4261, 4261, 47510150);
INSERT INTO refund VALUES ('JPNJP', 2, 201811, null, 6, 6930930000000, 6768618999, 518245000000);
INSERT INTO refund VALUES ('TWNPC', 2, 201811, null, 6, 1365990000000, 1334054610, 9701743920);
INSERT INTO refund VALUES ('JPNJP', 2, 201811, null, 5, 277162, 277162, 8314860000);
INSERT INTO refund VALUES ('JPNJP', 2, 201811, null, 2, 26225512, 536194, 54299095000);
INSERT INTO refund VALUES ('JPNJP', 2, 201811, null, 1, 3452592, 76379, 4969044760);
INSERT INTO refund VALUES ('TWNPC', 2, 201811, null, 4, 22515985, 534449, 5960720200);
INSERT INTO refund VALUES ('TWNPC', 2, 201811, null, 1, 2781059, 64812, 4746647650);
INSERT INTO refund VALUES ('TWNPC', 2, 201811, null, 3, 1043016, 20360, 7252605310);
INSERT INTO refund VALUES ('TWNPC', 2, 201811, null, 2, 17837722, 359395, 39995884240);
INSERT INTO refund VALUES ('TWNPC', 2, 201811, null, 5, 112292, 112292, 1252055800);
INSERT INTO refund VALUES ('TWNPC', 2, 201811, null, 6, 42186000000000, 41199381840, 299617000000);
INSERT INTO refund VALUES ('JPNJP', 2, 201811, null, 6, 165560000000000, 161683000000, 12379400000000);
INSERT INTO refund VALUES ('JPNJP', 2, 201811, null, 4, 35483165, 860061, 18878426000);
INSERT INTO refund VALUES ('JPNJP', 2, 201811, null, 3, 1504593, 29291, 11866584000);
INSERT INTO refund VALUES ('JPNJP', 1, 201810, null, 3, 6385517, 123784, 45818069696);
INSERT INTO refund VALUES ('JPNJP', 1, 201810, null, 1, 1859885, 38794, 2420608366);
INSERT INTO refund VALUES ('TWNPC', 1, 201810, null, 1, 940614, 21643, 1235127399);
INSERT INTO refund VALUES ('TWNPC', 1, 201810, null, 4, 3807148, 92385, 961145182);
INSERT INTO refund VALUES ('TWNPC', 1, 201810, null, 5, 19444, 19444, 202295376);
INSERT INTO refund VALUES ('TWNPC', 1, 201810, null, 3, 209180, 4080, 1205922602);
INSERT INTO refund VALUES ('JPNJP', 1, 201810, null, 6, 9099950000000, 8888115823, 722166000000);
INSERT INTO refund VALUES ('TWNPC', 1, 201810, null, 2, 3029474, 64536, 6107137130);
INSERT INTO refund VALUES ('JPNJP', 1, 201810, null, 5, 57910, 57910, 1807429010);
INSERT INTO refund VALUES ('JPNJP', 1, 201810, null, 2, 12847255, 249619, 24608498052);
INSERT INTO refund VALUES ('JPNJP', 1, 201810, null, 4, 9480324, 187655, 3904520881);
INSERT INTO refund VALUES ('TWNPC', 1, 201810, null, 6, 76047500000000, 74274616341, 525449000000);
INSERT INTO refund VALUES ('TWNPC', 1, 201811, null, 4, 3503577, 85354, 884861026);
INSERT INTO refund VALUES ('JPNJP', 1, 201811, null, 1, 1764433, 37445, 2328989118);
INSERT INTO refund VALUES ('JPNJP', 1, 201811, null, 2, 13597312, 263831, 25929860312);
INSERT INTO refund VALUES ('JPNJP', 1, 201811, null, 5, 47819, 47819, 1487218719);
INSERT INTO refund VALUES ('JPNJP', 1, 201811, null, 4, 9784080, 192982, 4001238962);
INSERT INTO refund VALUES ('TWNPC', 1, 201811, null, 3, 207195, 4006, 1187735299);
INSERT INTO refund VALUES ('JPNJP', 1, 201811, null, 3, 1859425, 36283, 13324926141);
INSERT INTO refund VALUES ('TWNPC', 1, 201811, null, 1, 851477, 19501, 1108765299);
INSERT INTO refund VALUES ('TWNPC', 1, 201811, null, 2, 2891480, 61434, 5798441871);
INSERT INTO refund VALUES ('TWNPC', 1, 201811, null, 5, 17896, 17896, 185527832);
INSERT INTO refund VALUES ('TWNPC', 1, 201811, null, 6, 83618200000000, 81668277927, 575724000000);
INSERT INTO refund VALUES ('JPNJP', 1, 201811, null, 6, 9298640000000, 9082217993, 735340000000);
INSERT INTO refund VALUES ('TWNPC', 1, 202207, null, 1, 3301.1, 4172, 255.368241);
INSERT INTO refund VALUES ('TWNPC', 1, 202207, null, 2, 13179.9166666666, 15588, 1630.755682);
INSERT INTO refund VALUES ('TWNPC', 1, 202207, null, 3, 306.733333333333, 373, 115.164729);
INSERT INTO refund VALUES ('TWNPC', 1, 202207, null, 4, 14498.0833333333, 22714, 253.942561);
INSERT INTO refund VALUES ('TWNPC', 1, 202207, null, 5, 5013, 5013, 56.04534);
INSERT INTO refund VALUES ('TWNPC', 1, 202207, null, 6, 105478453590.537, 105485167536, 47179.569478);
INSERT INTO refund VALUES ('TWNPC', 2, 202207, null, 1, 9398.58333333333, 12555, 1000.55136);
INSERT INTO refund VALUES ('TWNPC', 2, 202207, null, 2, 49224.6833333333, 56911, 6868.54867);
INSERT INTO refund VALUES ('TWNPC', 2, 202207, null, 3, 3831.1, 4291, 1657.4268);
INSERT INTO refund VALUES ('TWNPC', 2, 202207, null, 4, 64578.6166666666, 94884, 1147.38946);
INSERT INTO refund VALUES ('TWNPC', 2, 202207, null, 5, 23719, 23719, 286.76271);
INSERT INTO refund VALUES ('TWNPC', 2, 202207, null, 6, 10124967588.4902, 10125186900, 4336.74578);
INSERT INTO refund VALUES ('TWNPC', 1, 202208, 202209, 1, 22.4666666666666, 30, 1.853131);
INSERT INTO refund VALUES ('TWNPC', 1, 202208, 202209, 2, 112.25, 121, 13.190372);
INSERT INTO refund VALUES ('TWNPC', 1, 202208, 202209, 4, 59.4833333333333, 87, 0.977108);
INSERT INTO refund VALUES ('TWNPC', 1, 202208, 202209, 5, 41, 41, 0.460471);
INSERT INTO refund VALUES ('TWNPC', 1, 202208, 202209, 6, 1044417203.48632, 1044479229, 469.264504);
INSERT INTO refund VALUES ('TWNPC', 2, 202208, 202209, 1, 284, 370, 30.19904);
INSERT INTO refund VALUES ('TWNPC', 2, 202208, 202209, 2, 1970.1, 2270, 276.23097);
INSERT INTO refund VALUES ('TWNPC', 2, 202208, 202209, 3, 93.1666666666666, 107, 41.67107);
INSERT INTO refund VALUES ('TWNPC', 2, 202208, 202209, 4, 2893.8, 4078, 49.72255);
INSERT INTO refund VALUES ('TWNPC', 2, 202208, 202209, 5, 805, 805, 9.81295);
INSERT INTO refund VALUES ('TWNPC', 2, 202208, 202209, 6, 374936880.228515, 374946976, 161.92013);
INSERT INTO refund VALUES ('TWNPC', 1, 202208, null, 1, 4026.21666666666, 5093, 312.932237);
INSERT INTO refund VALUES ('TWNPC', 1, 202208, null, 2, 14931.6333333333, 17873, 1870.003671);
INSERT INTO refund VALUES ('TWNPC', 1, 202208, null, 3, 522.883333333333, 579, 192.702666);
INSERT INTO refund VALUES ('TWNPC', 1, 202208, null, 4, 15380.8, 24066, 270.288838);
INSERT INTO refund VALUES ('TWNPC', 1, 202208, null, 5, 5636, 5636, 63.297916);
INSERT INTO refund VALUES ('TWNPC', 1, 202208, null, 6, 115867809456.671, 115875273246, 52060.98915);
INSERT INTO refund VALUES ('TWNPC', 2, 202208, null, 1, 10032.9, 13500, 1048.49318);
INSERT INTO refund VALUES ('TWNPC', 2, 202208, null, 2, 52885.5666666666, 60726, 7389.60713);
INSERT INTO refund VALUES ('TWNPC', 2, 202208, null, 3, 2813.65, 3251, 1266.09929);
INSERT INTO refund VALUES ('TWNPC', 2, 202208, null, 4, 71229.9666666666, 104430, 1273.28199);
INSERT INTO refund VALUES ('TWNPC', 2, 202208, null, 5, 25384, 25384, 309.43096);
INSERT INTO refund VALUES ('TWNPC', 2, 202208, null, 6, 10852107724.166, 10852359398, 4686.59502);
INSERT INTO refund VALUES ('TWNPC', 1, 202209, 202209, 1, 4573.65, 5651, 345.478082);
INSERT INTO refund VALUES ('TWNPC', 1, 202209, 202209, 2, 17821.3666666666, 20872, 2246.646141);
INSERT INTO refund VALUES ('TWNPC', 1, 202209, 202209, 3, 353.083333333333, 396, 130.023744);
INSERT INTO refund VALUES ('TWNPC', 1, 202209, 202209, 4, 14284.45, 22306, 249.714626);
INSERT INTO refund VALUES ('TWNPC', 1, 202209, 202209, 5, 5002, 5002, 55.99739);
INSERT INTO refund VALUES ('TWNPC', 1, 202209, 202209, 6, 86641911269.664, 86647384562, 38803.150972);
INSERT INTO refund VALUES ('TWNPC', 2, 202209, 202209, 1, 10239.75, 13681, 1110.59294);
INSERT INTO refund VALUES ('TWNPC', 2, 202209, 202209, 2, 53728.3666666666, 61855, 7534.71118);
INSERT INTO refund VALUES ('TWNPC', 2, 202209, 202209, 3, 3868.88333333333, 4419, 1722.74042);
INSERT INTO refund VALUES ('TWNPC', 2, 202209, 202209, 4, 73591.7333333333, 107141, 1307.94176);
INSERT INTO refund VALUES ('TWNPC', 2, 202209, 202209, 5, 27049, 27049, 330.26829);
INSERT INTO refund VALUES ('TWNPC', 2, 202209, 202209, 6, 12038623481.4453, 12038858696, 5204.33711);
INSERT INTO refund VALUES ('TWNPC', 1, 202209, 202210, 1, 21.8333333333333, 25, 1.539288);
INSERT INTO refund VALUES ('TWNPC', 1, 202209, 202210, 2, 64.35, 77, 8.020532);
INSERT INTO refund VALUES ('TWNPC', 1, 202209, 202210, 3, 0.333333333333333, 1, 0.180237);
INSERT INTO refund VALUES ('TWNPC', 1, 202209, 202210, 4, 39.25, 51, 0.57094);
INSERT INTO refund VALUES ('TWNPC', 1, 202209, 202210, 5, 53, 53, 0.593335);
INSERT INTO refund VALUES ('TWNPC', 1, 202209, 202210, 6, 563927015.924804, 563961165, 252.55703);
INSERT INTO refund VALUES ('TWNPC', 2, 202209, 202210, 1, 398.8, 544, 57.26778);
INSERT INTO refund VALUES ('TWNPC', 2, 202209, 202210, 2, 2407.55, 2780, 338.63866);
INSERT INTO refund VALUES ('TWNPC', 2, 202209, 202210, 3, 183.733333333333, 218, 84.98691);
INSERT INTO refund VALUES ('TWNPC', 2, 202209, 202210, 4, 2938.45, 4167, 50.86869);
INSERT INTO refund VALUES ('TWNPC', 2, 202209, 202210, 5, 1183, 1183, 14.44443);
INSERT INTO refund VALUES ('TWNPC', 2, 202209, 202210, 6, 462281997.89746, 462289607, 199.84757);
INSERT INTO refund VALUES ('TWNPC', 1, 202210, 202210, 1, 3515.96666666666, 4436, 267.604987);
INSERT INTO refund VALUES ('TWNPC', 1, 202210, 202210, 2, 16538.6166666666, 19229, 1994.654576);
INSERT INTO refund VALUES ('TWNPC', 1, 202210, 202210, 3, 400.6, 459, 145.589845);
INSERT INTO refund VALUES ('TWNPC', 1, 202210, 202210, 4, 14794.05, 22850, 251.193555);
INSERT INTO refund VALUES ('TWNPC', 1, 202210, 202210, 5, 6667, 6667, 73.290331);
INSERT INTO refund VALUES ('TWNPC', 1, 202210, 202210, 6, 46453349426.3212, 46456626650, 20432.346674);
INSERT INTO refund VALUES ('TWNPC', 2, 202210, 202210, 1, 13991.8333333333, 18748, 1443.80739);
INSERT INTO refund VALUES ('TWNPC', 2, 202210, 202210, 2, 57576.15, 66399, 7843.35677);
INSERT INTO refund VALUES ('TWNPC', 2, 202210, 202210, 3, 3975.5, 4533, 1713.70336);
INSERT INTO refund VALUES ('TWNPC', 2, 202210, 202210, 4, 75739.3666666666, 111991, 1325.74461);
INSERT INTO refund VALUES ('TWNPC', 2, 202210, 202210, 5, 27959, 27959, 331.03456);
INSERT INTO refund VALUES ('TWNPC', 2, 202210, 202210, 6, 15484408759.6582, 15484635426, 6491.4095);
INSERT INTO refund VALUES ('TWNPC', 1, 202210, 202211, 1, 33.4833333333333, 38, 2.297558);
INSERT INTO refund VALUES ('TWNPC', 1, 202210, 202211, 2, 27.6333333333333, 34, 3.445789);
INSERT INTO refund VALUES ('TWNPC', 1, 202210, 202211, 4, 71.2666666666666, 106, 1.165272);
INSERT INTO refund VALUES ('TWNPC', 1, 202210, 202211, 5, 32, 32, 0.351776);
INSERT INTO refund VALUES ('TWNPC', 1, 202210, 202211, 6, 389826687.442382, 389849074, 171.458354);
INSERT INTO refund VALUES ('TWNPC', 2, 202210, 202211, 1, 498.616666666666, 700, 53.19239);
INSERT INTO refund VALUES ('TWNPC', 2, 202210, 202211, 2, 2528.71666666666, 2916, 344.45148);
INSERT INTO refund VALUES ('TWNPC', 2, 202210, 202211, 3, 173.083333333333, 205, 77.50035);
INSERT INTO refund VALUES ('TWNPC', 2, 202210, 202211, 4, 4067.6, 5828, 68.99107);
INSERT INTO refund VALUES ('TWNPC', 2, 202210, 202211, 5, 1075, 1075, 12.728);
INSERT INTO refund VALUES ('TWNPC', 2, 202210, 202211, 6, 585108984.645507, 585117166, 245.29125);
INSERT INTO refund VALUES ('TWNPC', 1, 202211, 202211, 1, 4255.23333333333, 5237, 311.901656);
INSERT INTO refund VALUES ('TWNPC', 1, 202211, 202211, 2, 14758.8, 17403, 1770.468083);
INSERT INTO refund VALUES ('TWNPC', 1, 202211, 202211, 3, 518.183333333333, 558, 183.033573);
INSERT INTO refund VALUES ('TWNPC', 1, 202211, 202211, 4, 15173.25, 24174, 262.070334);
INSERT INTO refund VALUES ('TWNPC', 1, 202211, 202211, 5, 6205, 6205, 67.268405);
INSERT INTO refund VALUES ('TWNPC', 1, 202211, 202211, 6, 41766376346.0361, 41769263371, 18114.33669);
INSERT INTO refund VALUES ('TWNPC', 2, 202211, 202211, 1, 13899.6, 18851, 1395.73933);
INSERT INTO refund VALUES ('TWNPC', 2, 202211, 202211, 2, 59932.75, 68722, 8034.01491);
INSERT INTO refund VALUES ('TWNPC', 2, 202211, 202211, 3, 4654.73333333333, 5403, 2021.52707);
INSERT INTO refund VALUES ('TWNPC', 2, 202211, 202211, 4, 88831.45, 132498, 1552.49599);
INSERT INTO refund VALUES ('TWNPC', 2, 202211, 202211, 5, 28949, 28949, 339.28228);
INSERT INTO refund VALUES ('TWNPC', 2, 202211, 202211, 6, 15680520256.5585, 15680765197, 6505.76279);
INSERT INTO refund VALUES ('TWNPC', 1, 202211, 202212, 2, 9.4, 12, 1.183842);
INSERT INTO refund VALUES ('TWNPC', 1, 202211, 202212, 3, 5.5, 7, 2.026185);
INSERT INTO refund VALUES ('TWNPC', 1, 202211, 202212, 5, 1, 1, 0.010841);
INSERT INTO refund VALUES ('TWNPC', 1, 202211, 202212, 6, 132548585.243164, 132556255, 57.485712);
INSERT INTO refund VALUES ('TWNPC', 2, 202211, 202212, 1, 370.25, 525, 38.28747);
INSERT INTO refund VALUES ('TWNPC', 2, 202211, 202212, 2, 2610.5, 2997, 350.36737);
INSERT INTO refund VALUES ('TWNPC', 2, 202211, 202212, 3, 181.133333333333, 215, 80.44203);
INSERT INTO refund VALUES ('TWNPC', 2, 202211, 202212, 4, 3632.21666666666, 5424, 63.55361);
INSERT INTO refund VALUES ('TWNPC', 2, 202211, 202212, 5, 1034, 1034, 12.11848);
INSERT INTO refund VALUES ('TWNPC', 2, 202211, 202212, 6, 530594708.595703, 530603216, 220.14055);
INSERT INTO refund VALUES ('TWNPC', 1, 202212, 202212, 1, 3783.45, 4693, 274.848948);
INSERT INTO refund VALUES ('TWNPC', 1, 202212, 202212, 2, 13618.55, 16207, 1622.26308);
INSERT INTO refund VALUES ('TWNPC', 1, 202212, 202212, 3, 327, 381, 116.13574);
INSERT INTO refund VALUES ('TWNPC', 1, 202212, 202212, 4, 14275.35, 22513, 240.596438);
INSERT INTO refund VALUES ('TWNPC', 1, 202212, 202212, 5, 5329, 5329, 56.951023);
INSERT INTO refund VALUES ('TWNPC', 1, 202212, 202212, 6, 36751594848.8232, 36754252246, 15714.973474);
INSERT INTO refund VALUES ('TWNPC', 2, 202212, 202212, 1, 14948.2166666666, 20832, 1540.65328);
INSERT INTO refund VALUES ('TWNPC', 2, 202212, 202212, 2, 68414.1833333333, 78665, 9228.414);
INSERT INTO refund VALUES ('TWNPC', 2, 202212, 202212, 3, 5526.4, 6419, 2445.94907);
INSERT INTO refund VALUES ('TWNPC', 2, 202212, 202212, 4, 84565.6833333333, 128383, 1509.4433);
INSERT INTO refund VALUES ('TWNPC', 2, 202212, 202212, 5, 34923, 34923, 410.69448);
INSERT INTO refund VALUES ('TWNPC', 2, 202212, 202212, 6, 16623451725.2792, 16623705883, 6920.92232);
INSERT INTO refund VALUES ('TWNPC', 1, 202212, 202301, 1, 3.83333333333333, 8, 0.47023);
INSERT INTO refund VALUES ('TWNPC', 1, 202212, 202301, 2, 87.5333333333333, 97, 9.933583);
INSERT INTO refund VALUES ('TWNPC', 1, 202212, 202301, 4, 27.5, 34, 0.363358);
INSERT INTO refund VALUES ('TWNPC', 1, 202212, 202301, 5, 59, 59, 0.630533);
INSERT INTO refund VALUES ('TWNPC', 1, 202212, 202301, 6, 269171530.228515, 269190532, 115.089349);
INSERT INTO refund VALUES ('TWNPC', 2, 202212, 202301, 1, 493.283333333333, 716, 54.77061);
INSERT INTO refund VALUES ('TWNPC', 2, 202212, 202301, 2, 1398.21666666666, 1675, 196.49939);
INSERT INTO refund VALUES ('TWNPC', 2, 202212, 202301, 3, 85.6, 106, 39.79751);
INSERT INTO refund VALUES ('TWNPC', 2, 202212, 202301, 4, 1663.41666666666, 2593, 30.48703);
INSERT INTO refund VALUES ('TWNPC', 2, 202212, 202301, 5, 1021, 1021, 12.00696);
INSERT INTO refund VALUES ('TWNPC', 2, 202212, 202301, 6, 727366564.361328, 727375267, 302.83054);
INSERT INTO refund VALUES ('TWNPC', 1, 202301, 202301, 1, 2236.85, 3092, 182.026035);
INSERT INTO refund VALUES ('TWNPC', 1, 202301, 202301, 2, 10547.5666666666, 12710, 1283.088899);
INSERT INTO refund VALUES ('TWNPC', 1, 202301, 202301, 3, 158.2, 205, 63.564669);
INSERT INTO refund VALUES ('TWNPC', 1, 202301, 202301, 4, 9398.3, 14412, 155.014809);
INSERT INTO refund VALUES ('TWNPC', 1, 202301, 202301, 5, 4127, 4127, 44.390012);
INSERT INTO refund VALUES ('TWNPC', 1, 202301, 202301, 6, 24038934509.0839, 24041176932, 10345.30072);
INSERT INTO refund VALUES ('TWNPC', 2, 202301, 202301, 1, 38336.25, 53792, 4074.41182);
INSERT INTO refund VALUES ('TWNPC', 2, 202301, 202301, 2, 93702.7666666666, 109726, 12850.80733);
INSERT INTO refund VALUES ('TWNPC', 2, 202301, 202301, 3, 6739.23333333333, 7761, 2908.99402);
INSERT INTO refund VALUES ('TWNPC', 2, 202301, 202301, 4, 132331.416666666, 185764, 2180.34889);
INSERT INTO refund VALUES ('TWNPC', 2, 202301, 202301, 5, 58885, 58885, 691.3099);
INSERT INTO refund VALUES ('TWNPC', 2, 202301, 202301, 6, 40601978261.1572, 40602436642, 16876.05219);
INSERT INTO refund VALUES ('TWNPC', 1, 202301, 202302, 1, 6.55, 10, 0.59157);
INSERT INTO refund VALUES ('TWNPC', 1, 202301, 202302, 2, 36.0166666666666, 43, 4.310396);
INSERT INTO refund VALUES ('TWNPC', 1, 202301, 202302, 3, 5.83333333333333, 7, 2.124272);
INSERT INTO refund VALUES ('TWNPC', 1, 202301, 202302, 4, 36.95, 54, 0.580824);
INSERT INTO refund VALUES ('TWNPC', 1, 202301, 202302, 5, 25, 25, 0.2689);
INSERT INTO refund VALUES ('TWNPC', 1, 202301, 202302, 6, 230271844.381835, 230291410, 99.093591);
INSERT INTO refund VALUES ('TWNPC', 2, 202301, 202302, 1, 1764.5, 2341, 187.89362);
INSERT INTO refund VALUES ('TWNPC', 2, 202301, 202302, 2, 4726.33333333333, 5544, 649.29787);
INSERT INTO refund VALUES ('TWNPC', 2, 202301, 202302, 3, 179.25, 217, 81.33639);
INSERT INTO refund VALUES ('TWNPC', 2, 202301, 202302, 4, 9237.28333333333, 12867, 151.02172);
INSERT INTO refund VALUES ('TWNPC', 2, 202301, 202302, 5, 2119, 2119, 24.87706);
INSERT INTO refund VALUES ('TWNPC', 2, 202301, 202302, 6, 1520340033.60156, 1520359125, 631.92321);
INSERT INTO refund VALUES ('TWNPC', 1, 202302, 202302, 1, 4404.1, 5953, 357.138051);
INSERT INTO refund VALUES ('TWNPC', 1, 202302, 202302, 2, 14354.65, 17442, 1779.206772);
INSERT INTO refund VALUES ('TWNPC', 1, 202302, 202302, 3, 313.666666666666, 380, 115.11336);
INSERT INTO refund VALUES ('TWNPC', 1, 202302, 202302, 4, 14706.3333333333, 23071, 252.595777);
INSERT INTO refund VALUES ('TWNPC', 1, 202302, 202302, 5, 5649, 5649, 61.850901);
INSERT INTO refund VALUES ('TWNPC', 1, 202302, 202302, 6, 34834310577.874, 34837418149, 15258.846983);
INSERT INTO refund VALUES ('TWNPC', 2, 202302, 202302, 1, 21606.25, 30169, 2226.16318);
INSERT INTO refund VALUES ('TWNPC', 2, 202302, 202302, 2, 84268.7666666666, 98257, 11478.87391);
INSERT INTO refund VALUES ('TWNPC', 2, 202302, 202302, 3, 5243.48333333333, 6092, 2277.72324);
INSERT INTO refund VALUES ('TWNPC', 2, 202302, 202302, 4, 138418.716666666, 206478, 2417.39948);
INSERT INTO refund VALUES ('TWNPC', 2, 202302, 202302, 5, 41171, 41171, 482.11241);
INSERT INTO refund VALUES ('TWNPC', 2, 202302, 202302, 6, 23350360910.0576, 23350704482, 9681.2087);
INSERT INTO refund VALUES ('TWNPC', 1, 202302, 202303, 1, 15.7666666666666, 26, 1.565607);
INSERT INTO refund VALUES ('TWNPC', 1, 202302, 202303, 2, 25.2666666666666, 44, 3.955072);
INSERT INTO refund VALUES ('TWNPC', 1, 202302, 202303, 4, 34.3, 52, 0.569327);
INSERT INTO refund VALUES ('TWNPC', 1, 202302, 202303, 5, 54, 54, 0.591246);
INSERT INTO refund VALUES ('TWNPC', 1, 202302, 202303, 6, 429993301.14746, 430025726, 188.346538);
INSERT INTO refund VALUES ('TWNPC', 2, 202302, 202303, 1, 387.366666666666, 571, 53.32531);
INSERT INTO refund VALUES ('TWNPC', 2, 202302, 202303, 2, 2404.86666666666, 2801, 327.22688);
INSERT INTO refund VALUES ('TWNPC', 2, 202302, 202303, 3, 167.233333333333, 193, 72.16029);
INSERT INTO refund VALUES ('TWNPC', 2, 202302, 202303, 4, 3812.95, 5971, 69.90793);
INSERT INTO refund VALUES ('TWNPC', 2, 202302, 202303, 5, 1017, 1017, 11.90907);
INSERT INTO refund VALUES ('TWNPC', 2, 202302, 202303, 6, 568821956.679687, 568831569, 235.83425);
INSERT INTO refund VALUES ('TWNPC', 1, 202303, 202303, 1, 5339.63333333333, 7311, 437.328693);
INSERT INTO refund VALUES ('TWNPC', 1, 202303, 202303, 2, 18686.0333333333, 22863, 2313.456115);
INSERT INTO refund VALUES ('TWNPC', 1, 202303, 202303, 3, 700.416666666666, 818, 253.85342);
INSERT INTO refund VALUES ('TWNPC', 1, 202303, 202303, 4, 24100.25, 37197, 405.668842);
INSERT INTO refund VALUES ('TWNPC', 1, 202303, 202303, 5, 7454, 7454, 81.293324);
INSERT INTO refund VALUES ('TWNPC', 1, 202303, 202303, 6, 55355404186.8125, 55360235382, 24154.451691);
INSERT INTO refund VALUES ('TWNPC', 2, 202303, 202303, 1, 17134.6666666666, 23593, 1775.04496);
INSERT INTO refund VALUES ('TWNPC', 2, 202303, 202303, 2, 67102.1666666666, 78565, 9317.74922);
INSERT INTO refund VALUES ('TWNPC', 2, 202303, 202303, 3, 3547.71666666666, 4149, 1574.82147);
INSERT INTO refund VALUES ('TWNPC', 2, 202303, 202303, 4, 105778.216666666, 162067, 1925.79699);
INSERT INTO refund VALUES ('TWNPC', 2, 202303, 202303, 5, 34285, 34285, 407.3058);
INSERT INTO refund VALUES ('TWNPC', 2, 202303, 202303, 6, 18011177069.3916, 18011471127, 7580.96126);
-- 代码1：bill_month <= 202208 的数据不用汇总，但需要把stll_month = 202209的数据过滤掉。
SELECT carrier_cd AS 运营商代码,
biz_line AS 漫游方向,
bill_month AS 退费账期,
accu_type AS 业务类型,
CASE
WHEN accu_type in (1, 2, 3, 4) THEN actual_traffic/60
WHEN accu_type in (6, 7) THEN actual_traffic/1024
ELSE actual_traffic
END AS 实际业务量,
rounded_traffic AS 计费业务量,
chg_sdr_tap/1000000 AS 实际金额
FROM refund
-- WHERE bill_month <= 202208 AND sttl_month != 202209
WHERE bill_month <= 202208 AND sttl_month IS NULL
ORDER BY bill_month, carrier_cd, biz_line, accu_type, rounded_traffic desc;
-- 代码2：汇总bill_month > 202208 的各项值
/*
SELECT carrier_cd AS 运营商代码,
biz_line AS 漫游方向,
bill_month AS 退费账期,
accu_type AS 业务类型,
SUM(CASE
WHEN accu_type in (1, 2, 3, 4) THEN actual_traffic/60
WHEN accu_type in (6, 7) THEN actual_traffic/1024
ELSE actual_traffic
END) AS 实际业务量,
SUM(rounded_traffic) AS 计费业务量,
SUM(chg_sdr_tap/1000000) AS 实际金额
FROM refund
WHERE bill_month > 202208
GROUP BY carrier_cd, biz_line, bill_month, accu_type
ORDER BY bill_month, carrier_cd, biz_line, accu_type, SUM(rounded_traffic) desc;
*/