size qoq
DESCRIPTION
qoqTRANSCRIPT
---FINISHED--------------LARGE-------L = LOAD '/user/karupaiyac/CI/ExpItin/Moving_Logic/Q-o-Q/CompanySize/LARGE/overlapping/2012_2011_1' USING PigStorage() as( YEAR:CHARARRAY,QUARTER:CHARARRAY,count_Companies:DOUBLE,count_users:DOUBLE,total_amount_user1:DOUBLE,total_amount_user2:DOUBLE,total_txn_user1:DOUBLE,total_txn_user2:DOUBLE,total_amount1:DOUBLE,total_amount2:DOUBLE);L1 = LOAD '/user/karupaiyac/CI/ExpItin/Moving_Logic/Q-o-Q/CompanySize/LARGE/overlapping/2014_2013_1' USING PigStorage() as( YEAR:CHARARRAY,QUARTER:CHARARRAY,count_Companies:DOUBLE,count_users:DOUBLE,total_amount_user1:DOUBLE,total_amount_user2:DOUBLE,total_txn_user1:DOUBLE,total_txn_user2:DOUBLE,total_amount1:DOUBLE,total_amount2:DOUBLE);UL = UNION ONSCHEMA L,L1;OL = ORDER UL by YEAR, QUARTER ASC;LARGE = FOREACH OL GENERATECONCAT(YEAR,QUARTER) AS PERIOD_1,CONCAT((CHARARRAY)((LONG)YEAR - 1),QUARTER) AS PERIOD_2,total_amount_user1 AS Total_Spend_1_L,((total_amount_user1) - (total_amount_user2))/(total_amount_user2) AS PERCENTAGE_CHANGE_L,(total_amount_user1/total_txn_user1) AS avg_spend_per_txn1_L,(total_amount_user1/count_Companies) AS avg_spend_per_company_L;--------------MEDIUM-----------M = LOAD '/user/karupaiyac/CI/ExpItin/Moving_Logic/Q-o-Q/CompanySize/MEDIUM/overlapping/2012_2011_1' USING PigStorage() as( YEAR:CHARARRAY,QUARTER:CHARARRAY,count_Companies:DOUBLE,count_users:DOUBLE,total_amount_user1:DOUBLE,total_amount_user2:DOUBLE,total_txn_user1:DOUBLE,total_txn_user2:DOUBLE,total_amount1:DOUBLE,total_amount2:DOUBLE);M1 = LOAD '/user/karupaiyac/CI/ExpItin/Moving_Logic/Q-o-Q/CompanySize/MEDIUM/overlapping/2014_2013_1' USING PigStorage() as( YEAR:CHARARRAY,QUARTER:CHARARRAY,count_Companies:DOUBLE,count_users:DOUBLE,total_amount_user1:DOUBLE,total_amount_user2:DOUBLE,total_txn_user1:DOUBLE,total_txn_user2:DOUBLE,total_amount1:DOUBLE,total_amount2:DOUBLE);UM = UNION ONSCHEMA M,M1;OM = ORDER UM by YEAR, QUARTER ASC;MEDIUM = FOREACH OM GENERATECONCAT(YEAR,QUARTER) AS PERIOD_1,CONCAT((CHARARRAY)((LONG)YEAR - 1),QUARTER) AS PERIOD_2,total_amount_user1 AS Total_Spend_1_M,((total_amount_user1) - (total_amount_user2))/(total_amount_user2) AS PERCENTAGE_CHANGE_M,(total_amount_user1/total_txn_user1) AS avg_spend_per_txn1_M,(total_amount_user1/count_Companies) AS avg_spend_per_company_M;------------SMALL-----------S = LOAD '/user/karupaiyac/CI/ExpItin/Moving_Logic/Q-o-Q/CompanySize/SMALL/overlapping/2012_2011_1' USING PigStorage() as( YEAR:CHARARRAY,QUARTER:CHARARRAY,count_Companies:DOUBLE,count_users:DOUBLE,total_amount_user1:DOUBLE,total_amount_user2:DOUBLE,total_txn_user1:DOUBLE,total_txn_user2:DOUBLE,total_amount1:DOUBLE,total_amount2:DOUBLE);S1 = LOAD '/user/karupaiyac/CI/ExpItin/Moving_Logic/Q-o-Q/CompanySize/SMALL/overlapping/2014_2013_1' USING PigStorage() as( YEAR:CHARARRAY,QUARTER:CHARARRAY,count_Companies:DOUBLE,count_users:DOUBLE,total_amount_user1:DOUBLE,total_amount_user2:DOUBLE,total_txn_user1:DOUBLE,total_txn_user2:DOUBLE,total_amount1:DOUBLE,total_amount2:DOUBLE);US= UNION ONSCHEMA S,S1;OS = ORDER US by YEAR, QUARTER ASC;SMALL = FOREACH OS GENERATECONCAT(YEAR,QUARTER) AS PERIOD_1,CONCAT((CHARARRAY)((LONG)YEAR - 1),QUARTER) AS PERIOD_2,total_amount_user1 AS Total_Spend_1_S,((total_amount_user1) - (total_amount_user2))/(total_amount_user2) AS PERCENTAGE_CHANGE_S,(total_amount_user1/total_txn_user1) AS avg_spend_per_txn1_S,(total_amount_user1/count_Companies) AS avg_spend_per_company_S;SIZE_QOQ = FOREACH (JOIN LARGE BY (PERIOD_1,PERIOD_2), MEDIUM BY (PERIOD_1,PERIOD_2),SMALL BY (PERIOD_1,PERIOD_2)) GENERATELARGE::PERIOD_1 as PERIOD_1,LARGE::PERIOD_2 as PERIOD_2,LARGE::Total_Spend_1_L AS TOTAL_SPEND_1_L,LARGE::PERCENTAGE_CHANGE_L as EXPENSE_PERCENTAGE_CHANGE_L,LARGE::avg_spend_per_txn1_L AS AVG_TXN_PER_TXN_1_L, LARGE::avg_spend_per_company_L AS AVG_SPEND_PER_COMP_L,MEDIUM::Total_Spend_1_M AS TOTAL_SPEND_1_M,MEDIUM::PERCENTAGE_CHANGE_M as EXPENSE_PERCENTAGE_CHANGE_M,MEDIUM::avg_spend_per_txn1_M AS AVG_TXN_PER_TXN_1_M, MEDIUM::avg_spend_per_company_M AS AVG_SPEND_PER_COMP_M,SMALL::Total_Spend_1_S AS TOTAL_SPEND_1_S,SMALL::PERCENTAGE_CHANGE_S as EXPENSE_PERCENTAGE_CHANGE_S,SMALL::avg_spend_per_txn1_S AS AVG_TXN_PER_TXN_1_S, SMALL::avg_spend_per_company_S AS AVG_SPEND_PER_COMP_S,'11-12-2014' as RUN_DATE,'Q' AS Q_Y ;--dump SIZE_QOQ;SIZE_YOY = LOAD '/user/karupaiyac/Results/SIZE_YOY' USING PigStorage() as( PERIOD_1:CHARARRAY,PERIOD_2:CHARARRAY,TOTAL_SPEND_1_L:DOUBLE,EXPENSE_PERCENTAGE_CHANGE_L:DOUBLE,AVG_TXN_PER_TXN_1_L:DOUBLE,AVG_SPEND_PER_COMP_L:DOUBLE,TOTAL_SPEND_1_M:DOUBLE,EXPENSE_PERCENTAGE_CHANGE_M:DOUBLE,AVG_TXN_PER_TXN_1_M:DOUBLE,AVG_SPEND_PER_COMP_M:DOUBLE,TOTAL_SPEND_1_S:DOUBLE,EXPENSE_PERCENTAGE_CHANGE_S:DOUBLE,AVG_TXN_PER_TXN_1_S:DOUBLE,AVG_SPEND_PER_COMP_S:DOUBLE, RUN_DATE:CHARARRAY,Q_Y:CHARARRAY);COMP_SIZE = UNION ONSCHEMA SIZE_YOY,SIZE_QOQ;STORE COMP_SIZE into '/user/karupaiyac/Results/COMP_SIZE' using PigStorage();