|
|
buxianggan 发表于 2012-12-4 15:02 ![]()
求解密
PROCEDURE telco_load (CUST_COUNT BINARY_INTEGER DEFAULT 10000) IS
TYPE TARIFF_TYPE IS VARRAY(4) OF VARCHAR2(30);
TARIFF CONSTANT TARIFF_TYPE := TARIFF_TYPE('PEAK','OFFP','WEEK','INT');
TYPE PM_TYPE IS VARRAY(4) OF NUMBER;
CHARGE_PER_MIN CONSTANT PM_TYPE := PM_TYPE(0.19,0.09,0.05,0.35);
DROP_PER_MIN CONSTANT PM_TYPE := PM_TYPE(0.001,0.0001,0.0002,0.004);
TYPE PM_TYPE_GROUP IS VARRAY(4) OF PM_TYPE;
MINS_MEAN_PTT CONSTANT PM_TYPE_GROUP :=
PM_TYPE_GROUP(PM_TYPE(600,150,50,20),
PM_TYPE(50,80,20,250),
PM_TYPE(50,200,200,150),
PM_TYPE(450,100,300,50));
MINS_LIM_PTT CONSTANT PM_TYPE_GROUP :=
PM_TYPE_GROUP(PM_TYPE(800,150,100,0),
PM_TYPE(100,100,100,450),
PM_TYPE(100,300,300,200),
PM_TYPE(600,150,450,90));
TYPE REV_TYPE IS VARRAY(5) OF NUMBER;
REVS REV_TYPE := REV_TYPE(0,0,0,0,0);
CUST_TYPE BINARY_INTEGER;
AGE BINARY_INTEGER;
LOS BINARY_INTEGER;
GENDER CHAR(1);
MINS_MEAN NUMBER;
MINS_STDDEV NUMBER;
MINS BINARY_INTEGER;
CDRID BINARY_INTEGER := 1;
REVID BINARY_INTEGER := 1;
HANDSET VARCHAR2(30);
RVALD NUMBER;
TAR_PLAN VARCHAR2(30);
TAR_TYPE VARCHAR2(30);
DC NUMBER;
CHURN CHAR(1);
CHURNP NUMBER;
TOTREV NUMBER;
EM NUMBER;
REVSPEAK NUMBER;
BEGIN
DBMS_RANDOM.SEED(0);
FOR I IN 1..CUST_COUNT LOOP
IF (DBMS_RANDOM.VALUE <= 0.5) THEN
GENDER := 'M';
ELSE
GENDER := 'F';
END IF;
AGE := -1;
WHILE ((AGE < 15) OR (AGE > 90)) LOOP
AGE := ROUND(DBMS_RANDOM.NORMAL*25+36);
END LOOP;
RVALD := DBMS_RANDOM.VALUE;
IF (RVALD < 0.4) THEN
CUST_TYPE := 1;
ELSIF (RVALD < 0.7) THEN
CUST_TYPE := 2;
ELSIF (RVALD < 0.9) THEN
CUST_TYPE := 3;
ELSE
CUST_TYPE := 4;
END IF;
INSERT INTO CUSTOMERS VALUES(I,AGE,GENDER);
FOR K IN 1..5 LOOP
REVS(K) := 20;
END LOOP;
DC := 0;
TOTREV := 0;
REVSPEAK := 0;
FOR J IN 1..4 LOOP
MINS_MEAN := DBMS_RANDOM.NORMAL*MINS_MEAN_PTT(CUST_TYPE)(J)*0.4+MINS_MEAN_PTT(CUST_TYPE)(J);
MINS_STDDEV := MINS_MEAN * 0.4;
MINS := ROUND(DBMS_RANDOM.NORMAL*MINS_STDDEV+MINS_MEAN);
FOR K IN 1..5 LOOP
MINS := (DBMS_RANDOM.NORMAL*0.2+1)*MINS;
IF (MINS > 0) THEN
INSERT INTO CDR_T VALUES (I,TARIFF(J),K,CDRID,MINS);
CDRID := CDRID + 1;
IF (MINS > MINS_LIM_PTT(CUST_TYPE)(J)) THEN
EM := ROUND(CHARGE_PER_MIN(J)*(MINS-MINS_LIM_PTT(CUST_TYPE)(J)),2);
REVS(K) := REVS(K) + EM;
IF (J = 1) THEN
REVSPEAK := REVSPEAK + EM;
END IF;
END IF;
DC := DC + ROUND((MINS*DROP_PER_MIN(J))*(DBMS_RANDOM.VALUE+0.5));
END IF;
END LOOP;
END LOOP;
FOR K IN 1..5 LOOP
INSERT INTO REVENUES VALUES(REVID,I,K,REVS(K));
REVID := REVID + 1;
TOTREV := TOTREV + REVS(K);
END LOOP;
RVALD := DBMS_RANDOM.VALUE;
IF (RVALD < 0.4) THEN
HANDSET := 'Droid X';
ELSIF (RVALD < 0.7) THEN
HANDSET := 'iPhone 4';
ELSIF (RVALD < 0.8) THEN
HANDSET := 'iPhone 3gs';
ELSE
HANDSET := 'Evo 4g';
END IF;
LOS := 121;
IF (DBMS_RANDOM.VALUE < 0.4) THEN
WHILE (LOS > 120) LOOP
LOS := ROUND(EXP(DBMS_RANDOM.NORMAL*(3/2))*12);
END LOOP;
ELSE
WHILE (LOS > 120) LOOP
LOS := ROUND(EXP(DBMS_RANDOM.NORMAL*(1/4))*72);
END LOOP;
END IF;
RVALD := DBMS_RANDOM.VALUE;
IF (RVALD < 0.6) THEN
TAR_PLAN := 'CAT';
TAR_TYPE := 1;
ELSIF (RVALD < 0.95) THEN
TAR_PLAN := 'PLAY';
TAR_TYPE := 2;
ELSE
TAR_PLAN := 'PLUS';
TAR_TYPE := 3;
END IF;
CHURNP := 0.15;
IF (DBMS_RANDOM.VALUE < 0.8) THEN
CHURNP := CHURNP * (DC+4)/7;
CHURNP := CHURNP * POWER(((200-AGE)/150),3);
CHURNP := CHURNP * POWER(((240-LOS)/180),3);
IF ((REVS(5) > (((REVS(1)+REVS(2)+REVS(3)+REVS(4))/4)*1.1)) OR
(REVS(5) > REVS(4)*1.2)) THEN
CHURNP := CHURNP * 1.5;
ELSE
CHURNP := CHURNP / 1.25;
END IF;
END IF;
IF (REVSPEAK > 20) THEN
CHURNP := CHURNP * 2;
END IF;
IF (DBMS_RANDOM.VALUE < CHURNP) THEN
CHURN := 'Y';
ELSE
CHURN := 'N';
END IF;
INSERT INTO SERVICES VALUES(I,HANDSET,LOS,DC,TAR_TYPE,TAR_PLAN,CHURN);
END LOOP;
COMMIT;
END; |
|
楼主: 〇〇
IP卡
狗仔卡
发表于 2014-12-1 16:15
显身卡
