import java.io.IOException import java.sql.Timestamp import java.text.SimpleDateFormat import java.util.Calendar import DAO.{ScalaConn, ScalaHbase} import org.apache.commons.lang3.time.DateFormatUtils import org.apache.hadoop.conf.Configuration import org.apache.hadoop.hbase.client.{Result, Connection, Table} import org.apache.hadoop.hbase.io.ImmutableBytesWritable import org.apache.hadoop.hbase.mapreduce.TableInputFormat import org.apache.hadoop.hbase.util.Bytes import org.apache.hadoop.hbase.{MasterNotRunningException, TableName, ZooKeeperConnectionException} import org.apache.spark.sql.DataFrame import org.slf4j.LoggerFactory /* * 平台月度总结数据等。 * author：谭志坚 * */ @SerialVersionUID(-1) object dm_zj_m { def LOG = LoggerFactory.getLogger(getClass) @throws(classOf[Exception]) def dm_zj_m_run(conf: Configuration, hconn: Connection) { val familyColumn: Array[String] = Array[String]("USERS") ScalaHbase.createTable(conf, hconn,"DM_ZJ_M", familyColumn) ScalaHbase.createTable(conf, hconn,"DM_ZJ_M_TZNH", familyColumn) ScalaHbase.createTable(conf, hconn,"DM_ZJ_M_FL", familyColumn) val spark = ScalaConn.spark val DM_ZJ_M: Table = hconn.getTable(TableName.valueOf("DM_ZJ_M")) val DM_ZJ_M_TZNH: Table = hconn.getTable(TableName.valueOf("DM_ZJ_M_TZNH")) val DM_ZJ_M_FL: Table = hconn.getTable(TableName.valueOf("DM_ZJ_M_FL")) var DM_ZJ_M_maxdate: String = ScalaConn.getMaxDate(conf, hconn, DM_ZJ_M, "USERS", "YM") var DM_ZJ_M_TZNH_maxdate: String = ScalaConn.getMaxDate(conf, hconn, DM_ZJ_M_TZNH, "USERS", "YM") var DM_ZJ_M_FL_maxdate: String = ScalaConn.getMaxDate(conf, hconn, DM_ZJ_M_FL, "USERS", "YM") // print("maxdate = "+maxdate) // val date: Date = new Date(); // var maxdt: Timestamp = new Timestamp(date.getTime) ; if (DM_ZJ_M_maxdate.length > 8) { DM_ZJ_M_maxdate = "201701" } if (DM_ZJ_M_TZNH_maxdate.length > 8) { DM_ZJ_M_TZNH_maxdate = "201701" } if (DM_ZJ_M_FL_maxdate.length > 8) { DM_ZJ_M_FL_maxdate = "201701" } // DM_ZJ_M_maxdate = "201701" // DM_ZJ_M_TZNH_maxdate = "201701" // DM_ZJ_M_FL_maxdate = "201701" // maxdate = "20160101" // DM_YY_TJ_maxdate = "2016-01-01" // zcwcmaxdate = "2016-01-01" // if (DM_YY_TJ_maxdate.length < 11) { // DM_YY_TJ_maxdate = DM_YY_TJ_maxdate.substring(0, 10) + " 00:00:00" // } // if (zcwcmaxdate.length < 11) { // zcwcmaxdate = zcwcmaxdate.substring(0, 10) + " 00:00:00" // } val tableNew: String = "t_users" val tableInvests: String = "t_planing_invests" val tableBids: String = "t_planing" val tInvests: String = "t_invests" val tBids: String = "t_bids" val thqb: String = "t_huoqibao_user_invest" val tfxg: String = "t_fxgo_invests" val tue: String = "t_user_events" val tud: String = "t_user_details" val dam: String = "dm_accountmassage" val tal: String = "t_activity_log" val tubm: String = "t_user_bribery_money" val tfrd: String = "t_fund_recharge_detail" val tirr: String = "t_interest_rate_roll" val mti: String = "memberintegralinfo" val tpbi: String = "t_planing_bill_invests" val tfbi: String = "t_fxgo_bill_invests" val tbi: String = "t_bill_invests" val thutr: String = "t_huoqibao_user_trade_record" val tubmu: String = "t_user_bribery_money_used" val tfwmu: String = "t_fund_withdraw_money_used" val tirrb: String = "t_interest_rate_roll_bill" val tpo: String = "t_planing_orders" val so: String = "saleorder" val tcb: String = "t_cashback" val tbii: String = "t_bid_increase_interest" val DFNew: DataFrame = ScalaConn.getP2PDataFrame(tableNew) val DFInvests: DataFrame = ScalaConn.getP2PDataFrame(tableInvests) val DFBids: DataFrame = ScalaConn.getP2PDataFrame(tableBids) val DFtInvests: DataFrame = ScalaConn.getP2PDataFrame(tInvests) val DtBids: DataFrame = ScalaConn.getP2PDataFrame(tBids) val DFthqb: DataFrame = ScalaConn.getP2PDataFrame(thqb) val DFtfxg: DataFrame = ScalaConn.getP2PDataFrame(tfxg) val DFtue: DataFrame = ScalaConn.getP2PDataFrame(tue) val DFtud: DataFrame = ScalaConn.getP2PDataFrame(tud) val DFdam: DataFrame = ScalaConn.getHadoopDataFrame(dam) val DFtal: DataFrame = ScalaConn.getP2PDataFrame(tal) val DFtubm: DataFrame = ScalaConn.getP2PDataFrame(tubm) val DFtfrd: DataFrame = ScalaConn.getP2PDataFrame(tfrd) val DFtirr: DataFrame = ScalaConn.getP2PDataFrame(tirr) val DFmti: DataFrame = ScalaConn.getP2PDataFrame(mti) val DFtpbi: DataFrame = ScalaConn.getP2PDataFrame(tpbi) val DFtfbi: DataFrame = ScalaConn.getP2PDataFrame(tfbi) val DFtbi: DataFrame = ScalaConn.getP2PDataFrame(tbi) val DFthutr: DataFrame = ScalaConn.getP2PDataFrame(thutr) val DFtubmur: DataFrame = ScalaConn.getP2PDataFrame(tubmu) val DFtfwmu: DataFrame = ScalaConn.getP2PDataFrame(tfwmu) val DFtirrb: DataFrame = ScalaConn.getP2PDataFrame(tirrb) val DFtpo: DataFrame = ScalaConn.getP2PDataFrame(tpo) val DFso: DataFrame = ScalaConn.getP2PDataFrame(so) val DFtcb: DataFrame = ScalaConn.getP2PDataFrame(tcb) val DFtbii: DataFrame = ScalaConn.getP2PDataFrame(tbii) DFNew.createOrReplaceTempView("t_users") DFInvests.createOrReplaceTempView("t_planing_invests") DFBids.createOrReplaceTempView("t_planing") DFtInvests.createOrReplaceTempView("t_invests") DtBids.createOrReplaceTempView("t_bids") DFthqb.createOrReplaceTempView("t_huoqibao_user_invest") DFtfxg.createOrReplaceTempView("t_fxgo_invests") DFtue.createOrReplaceTempView("t_user_events") DFtud.createOrReplaceTempView("t_user_details") DFdam.createOrReplaceTempView("dm_accountmassage") DFtal.createOrReplaceTempView("t_activity_log") DFtubm.createOrReplaceTempView("t_user_bribery_money") DFtfrd.createOrReplaceTempView("t_fund_recharge_detail") DFtirr.createOrReplaceTempView("t_interest_rate_roll") DFmti.createOrReplaceTempView("memberintegralinfo") DFtpbi.createOrReplaceTempView("t_planing_bill_invests") DFtfbi.createOrReplaceTempView("t_fxgo_bill_invests") DFtbi.createOrReplaceTempView("t_bill_invests") DFthutr.createOrReplaceTempView("t_huoqibao_user_trade_record") DFtubmur.createOrReplaceTempView("t_user_bribery_money_used") DFtfwmu.createOrReplaceTempView("t_fund_withdraw_money_used") DFtirrb.createOrReplaceTempView("t_interest_rate_roll_bill") DFtpo.createOrReplaceTempView("t_planing_orders") DFso.createOrReplaceTempView("saleorder") DFtcb.createOrReplaceTempView("t_cashback") DFtbii.createOrReplaceTempView("t_bid_increase_interest") conf.set(TableInputFormat.INPUT_TABLE, "DM_ALL_INVEST") import spark.implicits._ var hbaseRDD = spark.sparkContext.newAPIHadoopRDD(conf, classOf[TableInputFormat], classOf[ImmutableBytesWritable], classOf[Result]) var HbaseDF = hbaseRDD.map(r=>( Bytes.toString(r._2.getRow), Bytes.toString(r._2.getValue(Bytes.toBytes("USERS"),Bytes.toBytes("USER_ID"))), Bytes.toString(r._2.getValue(Bytes.toBytes("USERS"),Bytes.toBytes("REG_TIME"))), Bytes.toString(r._2.getValue(Bytes.toBytes("USERS"),Bytes.toBytes("DT"))), Bytes.toString(r._2.getValue(Bytes.toBytes("INFO"),Bytes.toBytes("RECHARGE"))), Bytes.toString(r._2.getValue(Bytes.toBytes("INFO"),Bytes.toBytes("A"))), Bytes.toString(r._2.getValue(Bytes.toBytes("INFO"),Bytes.toBytes("B"))), Bytes.toString(r._2.getValue(Bytes.toBytes("INFO"),Bytes.toBytes("C"))), Bytes.toString(r._2.getValue(Bytes.toBytes("INFO"),Bytes.toBytes("V"))), Bytes.toString(r._2.getValue(Bytes.toBytes("INFO"),Bytes.toBytes("D"))), Bytes.toString(r._2.getValue(Bytes.toBytes("INFO"),Bytes.toBytes("XXB"))), Bytes.toString(r._2.getValue(Bytes.toBytes("INFO"),Bytes.toBytes("XDT"))), Bytes.toString(r._2.getValue(Bytes.toBytes("INFO"),Bytes.toBytes("TTZ"))), Bytes.toString(r._2.getValue(Bytes.toBytes("INFO"),Bytes.toBytes("FXG"))) )).toDF("ROWKEY","USER_ID","REG_TIME","DT","RECHARGE","A","B","C","V","D","XXB","XDT","TTZ","FXG") HbaseDF.createOrReplaceTempView("DM_ALL_INVEST") val DM_ZJ_M_sql = "SELECT\n\tb.ym,\n\tsum(b.reg_cnt) reg_cnt,\n\tsum(b.hf_cnt) hf_cnt,\n round(sum(b.recharge),2) recharge,\n round(sum(b.hkamt),2) hkamt, \n sum(b.human_cnt) human_cnt,\n sum(b.invt_cnt) invt_cnt,\n round(sum(b.invt_amt),2) invt_amt,\n\tround(sum(b.tznh),2) tznh\t\nFROM\n\t(\n\t\tSELECT\n\t\t\tDATE_FORMAT(time, 'yyyyMM') ym,\n\t\t\tsum(1) reg_cnt,\n\t\t\tsum(\n\t\t\t\tIF (\n\t\t\t\t\tIFNULL(ips_acct_no, '') != '',\n\t\t\t\t\t1,\n\t\t\t\t\t0\n\t\t\t\t)\n\t\t\t) hf_cnt,\n '0' recharge,\n '0' hkamt,\t\t\t\n\t\t\t'0' human_cnt,\t\n '0' invt_cnt,\t\t\n\t\t\t'0' invt_amt,\n '0' tznh\t\t\t\n\t\tFROM\n\t\t\tt_users\n\t\tWHERE\n\t\t\tDATE_FORMAT(time, 'yyyyMM') >= '" + DM_ZJ_M_maxdate + "'\n\t\tGROUP BY\n\t\t\tDATE_FORMAT(time, 'yyyyMM')\n\t\tUNION ALL\n\t\t\tSELECT\n\t\t\t\ta.ym,\n\t\t\t\t'0' reg_cnt,\n\t\t\t\t'0' hf_cnt,\n '0' recharge,\n '0' hkamt,\t\t\n COUNT(DISTINCT a.uid) human_cnt,\n\t\t\t\tCOUNT(a.uid) invt_cnt,\n\t\t\t\tsum(a.amt) invt_amt,\n\t\t\t\tsum(a.tznh) tznh\t\t\t\t\n\t\t\tFROM\n\t\t\t\t(\n\t\t\t\t\tSELECT\n\t\t\t\t\t\tDATE_FORMAT(tpi.time, 'yyyyMM') ym,\n\t\t\t\t\t\ttpi.user_id uid,\n\t\t\t\t\t\tsum(tpi.amount) amt,\n sum(tpi.amount * tp.cycle_days /360) tznh\n\t\t\t\t\tFROM\n\t\t\t\t\t\tt_planing_invests tpi\n\t\t\t\t\tJOIN t_planing tp ON tpi.planing_id = tp.id\n\t\t\t\t\tWHERE\n\t\t\t\t\t\ttpi.transfer_id = 0\t\t\t\t\n\t\t\t\t\tAND DATE_FORMAT(tpi.time, 'yyyyMM') >= '" + DM_ZJ_M_maxdate + "'\n\t\t\t\t\tGROUP BY\n\t\t\t\t\t\tDATE_FORMAT(tpi.time, 'yyyyMM'),\n\t\t\t\t\t\ttpi.user_id\n\t\t\t\t\tUNION ALL\n\t\t\t\t\t\tSELECT\n\t\t\t\t\t\t\tDATE_FORMAT(ts.time, 'yyyyMM') ym,\n\t\t\t\t\t\t\tts.user_id uid,\n\t\t\t\t\t\t\tsum(ts.amount) amt,\n sum(ts.amount * CASE\n\t\t\t\t\t\t\tWHEN tb.period_unit = '-1' THEN\n\t\t\t\t\t\t\t\t\t\ttb.period * 12 * 30\n\t\t\t\t\t\t\tWHEN tb.period_unit = '0' THEN\n\t\t\t\t\t\t\t\t\t\ttb.period * 30\n\t\t\t\t\t\t\tWHEN tb.period_unit = '1' THEN\n\t\t\t\t\t\t\t\t\t\ttb.period \n\t\t\t\t\t\t\tEND /360) tznh\n\t\t\t\t\t\tFROM\n\t\t\t\t\t\t\tt_invests ts\n\t\t\t\t\t\tJOIN t_bids tb ON ts.bid_id = tb.id\n\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\ttb.is_fund <> 1\n\t\t\t\t\t\tAND ts.transfers_id = 0\t\t\t\t\t\n\t\t\t\t\t\tAND DATE_FORMAT(ts.time, 'yyyyMM') >= '" + DM_ZJ_M_maxdate + "'\n\t\t\t\t\t\tGROUP BY\n\t\t\t\t\t\t\tDATE_FORMAT(ts.time, 'yyyyMM'),\n\t\t\t\t\t\t\tts.user_id\n\t\t\t\t\t\tUNION ALL\n\t\t\t\t\t\t\tSELECT\n\t\t\t\t\t\t\t\tDATE_FORMAT(apply_date, 'yyyyMM') ym,\n\t\t\t\t\t\t\t\tuser_id uid,\n\t\t\t\t\t\t\t\tsum(apply_amount) amt,\n sum(apply_amount * 60 /360) tznh\n\t\t\t\t\t\t\tFROM\n\t\t\t\t\t\t\t\tt_huoqibao_user_invest\n\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\tSTATUS = 1\t\t\t\t\t\t\n\t\t\t\t\t\t\tAND DATE_FORMAT(apply_date, 'yyyyMM') >= '" + DM_ZJ_M_maxdate + "'\n\t\t\t\t\t\t\tGROUP BY\n\t\t\t\t\t\t\t\tDATE_FORMAT(apply_date, 'yyyyMM'),\n\t\t\t\t\t\t\t\tuser_id\n\t\t\t\t\t\t\tUNION ALL\n\t\t\t\t\t\t\t\tSELECT\n\t\t\t\t\t\t\t\t\tDATE_FORMAT(ts.time, 'yyyyMM') ym,\n\t\t\t\t\t\t\t\t\tuser_id uid,\n\t\t\t\t\t\t\t\t\tsum(ts.amount) amt,\n sum(ts.amount * ts.periods * 30 /360) tznh\n\t\t\t\t\t\t\t\tFROM\n\t\t\t\t\t\t\t\t\tt_fxgo_invests ts\n\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\tSTATUS = 1\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\tAND DATE_FORMAT(ts.time, 'yyyyMM') >= '" + DM_ZJ_M_maxdate + "'\n\t\t\t\t\t\t\t\tGROUP BY\n\t\t\t\t\t\t\t\t\tDATE_FORMAT(ts.time, 'yyyyMM'),\n\t\t\t\t\t\t\t\t\tuser_id\n\t\t\t\t) a\n\t\t\tGROUP BY\n\t\t\t\ta.ym\t\n\t\t\t\tUNION ALL\t\t\t\t\t\n\t\t\t\t\t\t\tSELECT\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\tDATE_FORMAT(tud.time, 'yyyyMM')\tym,\n '0' reg_cnt,\n\t\t\t\t\t\t '0' hf_cnt,\n sum(tud.amount) recharge,\n '0' hkamt,\n\t\t\t\t\t \t'0' human_cnt,\n\t\t\t\t\t\t '0' invt_cnt,\t\t\n '0' invt_amt,\t\t\t\n\t\t\t\t\t \t'0' tznh\t\n\t\t\t\t\t\t\tFROM\n\t\t\t\t\t\t\t\tt_user_details tud\n\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\ttud.operation IN (1, 350)\tAND DATE_FORMAT(tud.time, 'yyyyMM') >= '" + DM_ZJ_M_maxdate + "'\n\t\t\t\t\t\t\tGROUP BY\tDATE_FORMAT(tud.time, 'yyyyMM')\n UNION ALL\n SELECT yhk.dt,\n '0' reg_cnt,\n '0' hf_cnt,\n '0' recharge,\n sum(yhk.hk) hkamt, \n '0' human_cnt,\n '0' invt_cnt,\n '0' invt_amt,\n '0' tznh\n from (\n\t\t\t\t\t\t\t\tSELECT\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\tDATE_FORMAT(\n\t\t\t\t\t\t\t\t\t\treal_receive_time,\n\t\t\t\t\t\t\t\t\t\t'yyyyMM'\n\t\t\t\t\t\t\t\t\t) dt,\n\t\t\t\t\t\t\t\t\tsum(real_receive_corpus)+\n\t\t\t\t\t\t\t\t\tsum(real_receive_interest) + sum(\n\t\t\t\t\t\t\t\t\t\treal_platform_receive_interest\n\t\t\t\t\t\t\t\t\t) + sum(real_roll_receive_interest) hk\n\t\t\t\t\t\t\t\tFROM\n\t\t\t\t\t\t\t\t\tt_planing_bill_invests\n\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\tDATE_FORMAT(real_receive_time,'yyyyMM') >= '" + DM_ZJ_M_maxdate + "'\n\t\t\t\t\t\t\t\tGROUP BY\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\tDATE_FORMAT(real_receive_time,'yyyyMM')\t\t\t\t\t\t\n\t\t\t\t\t \tUNION ALL\n SELECT\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\tDATE_FORMAT(real_receive_time,'yyyyMM') dt,\n\t\t\t\t\t\t\t\t\t\tsum(real_receive_corpus)+sum(real_receive_interest) + sum(\n\t\t\t\t\t\t\t\t\t\t\treal_platform_receive_interest) + sum(real_roll_receive_interest) hk\n\t\t\t\t\t\t\t\t\tFROM\n\t\t\t\t\t\t\t\t\t\tt_fxgo_bill_invests\n\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\treal_receive_time >= '" + DM_ZJ_M_maxdate + "'\n\t\t\t\t\t\t\t\t\tGROUP BY\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\tDATE_FORMAT(real_receive_time,'yyyyMM')\t\t\t\t\t\t\n\t\t\t\t\t\t \tUNION ALL\n SELECT\t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\tDATE_FORMAT(real_receive_time,'yyyyMM') dt,\n\t\t\t\t\t\t\t\t\t\t\tsum(real_receive_corpus)+\tsum(real_receive_interest) hk\n\t\t\t\t\t\t\t\t\t\tFROM\n\t\t\t\t\t\t\t\t\t\t\tt_bill_invests\n\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\tDATE_FORMAT(real_receive_time,'yyyyMM') >= '" + DM_ZJ_M_maxdate + "'\n\t\t\t\t\t\t\t\t\t\tGROUP BY\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\t\tDATE_FORMAT(real_receive_time,'yyyyMM')\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\tUNION ALL\n\t\t\t\t\t\t\t\t\tSELECT\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\tDATE_FORMAT(redeem_date, 'yyyyMM') dt,\n\t\t\t\t\t\t\t\t\t\tsum(IFNULL(amount, 0)) hk\n\t\t\t\t\t\t\t\t\tFROM\n\t\t\t\t\t\t\t\t\t\tt_huoqibao_user_trade_record\n\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\tSTATUS = 1\n\t\t\t\t\t\t\t\t\tAND DATE_FORMAT(redeem_date, 'yyyyMM') >= '" + DM_ZJ_M_maxdate + "'\n\t\t\t\t\t\t\t\t\tGROUP BY\t\t\t\t\t\t\t\t\t\t\n\t\t\t\t\t\t\t\t\t\tDATE_FORMAT(redeem_date, 'yyyyMM')\n\t\t\t\t\t) yhk\t\t\n\t\t\t\tGROUP BY\n\t\t\t\t\tyhk.dt\n\t) b\nGROUP BY\n\tb.ym" //1、当月注册；2；当月有充值的用户充值金额 val dyzc_dycz_sql = "SELECT\n\tSUBSTR(ROWKEY,1,6),\n\tcount(DISTINCT(USER_ID)) USERCOUNT1,\n\tsum(RECHARGE) RECHARGE1\nFROM\n\tDM_ALL_INVEST \nWHERE\n\tSUBSTR(ROWKEY,1,6) >= '"+DM_ZJ_M_maxdate+"'\nAND SUBSTR(ROWKEY,1,6) = regexp_replace(substr(reg_time, 1, 7), '-', '')\nAND RECHARGE > 0\nGROUP BY\n\tSUBSTR(ROWKEY,1,6) " //1、非当月注册；2、之前未投资；3、当月有充值的用户充值金额 val fdyzc_wtz_dycz_sql = "SELECT\n\tSUBSTR(t.ROWKEY,1,6),\n\tcount(DISTINCT(t.USER_ID)) USERCOUNT2,\n\tsum(t.RECHARGE) RECHARGE2\nFROM\n\tDM_ALL_INVEST t\nWHERE\n\tSUBSTR(t.ROWKEY , 1 , 6) >= '"+DM_ZJ_M_maxdate+"'\nAND SUBSTR(t.ROWKEY , 1 , 6) <> regexp_replace(substr(t.reg_time, 1, 7), '-', '')\nAND t.RECHARGE > 0 AND not exists(select 1 from DM_ALL_INVEST i where i.USER_ID=t.USER_ID and SUBSTR(i.ROWKEY , 1 , 6)<= SUBSTR(t.ROWKEY , 1 , 6) \nand i.A+i.B+t.C+i.D+t.V+i.XXB+i.XDT+i.TTZ+i.FXG>0)\nGROUP BY\n\tSUBSTR(t.ROWKEY,1,6) " //1、非当月注册；2、有过投资；3、当月有充值的用户充值金额 val fdyzc_ytz_dycz_sql = "SELECT\n\tSUBSTR(t.ROWKEY,1,6),\n\tcount(DISTINCT(t.USER_ID)) USERCOUNT2,\n\tsum(t.RECHARGE) RECHARGE2\nFROM\n\tDM_ALL_INVEST t\nWHERE\n\tSUBSTR(t.ROWKEY , 1 , 6) >= '"+DM_ZJ_M_maxdate+"'\nAND SUBSTR(t.ROWKEY, 1, 6) <> regexp_replace(substr(t.reg_time, 1, 7), '-', '')\nAND t.RECHARGE > 0 AND exists(select 1 from DM_ALL_INVEST i where i.USER_ID=t.USER_ID and SUBSTR(i.ROWKEY , 1 , 6)<= SUBSTR(t.ROWKEY , 1 , 6) \nand i.A+i.B+t.C+i.D+t.V+i.XXB+i.XDT+i.TTZ+i.FXG>0)\nGROUP BY\n\tSUBSTR(ROWKEY,1,6) " //1、当月注册；2；当月有投资的用户投资金额 val dyzc_ytz_sql = "SELECT\n\tSUBSTR(t.ROWKEY,1,6),\n\tcount(DISTINCT(t.USER_ID)) USERCOUNT2,\n\tsum(t.A+t.B+t.C+t.D+t.V+t.XXB+t.XDT+t.TTZ+t.FXG) INVEST_AMT\nFROM\n\tDM_ALL_INVEST t\nWHERE\n\tSUBSTR(t.ROWKEY, 1, 6) >= '"+DM_ZJ_M_maxdate+"'\nAND SUBSTR(t.ROWKEY,1,6) = regexp_replace(substr(t.reg_time, 1, 7), '-', '')\nAND t.A+t.B+t.C+t.D+t.V+t.XXB+t.XDT+t.TTZ+t.FXG>0\nGROUP BY\n\tSUBSTR(ROWKEY,1,6) " //1、非当月注册；2、之前未投资；3、当月有投资的用户投资金额 val fdyzc_wtz_sql= "SELECT\n\tSUBSTR(t.ROWKEY,1,6),\n\tcount(DISTINCT(t.USER_ID)) USERCOUNT2,\n\tsum(t.A+t.B+t.C+t.D+t.V+t.XXB+t.XDT+t.TTZ+t.FXG) INVEST_AMT\nFROM\n\tDM_ALL_INVEST t\nWHERE\n\tSUBSTR(t.ROWKEY , 1, 6) >= '"+DM_ZJ_M_maxdate+"'\nAND SUBSTR(t.ROWKEY,1,6) <> regexp_replace(substr(t.reg_time, 1, 7), '-', '')\nAND t.A+t.B+t.C+t.D+t.V+t.XXB+t.XDT+t.TTZ+t.FXG>0\nand not exists(select 1 from DM_ALL_INVEST i \nwhere i.USER_ID=t.USER_ID \nand SUBSTR(i.ROWKEY , 1 , 6) < SUBSTR(t.ROWKEY , 1 , 6) \n and i.A+i.B+i.C+i.D+i.V+i.XXB+i.XDT+i.TTZ+i.FXG>0)\nGROUP BY\n\tSUBSTR(ROWKEY,1,6) " //1、非当月注册；2、有过投资；3、当月有投资的用户投资金额 val fdyzc_ytz_sql= "SELECT\n\tSUBSTR(t.ROWKEY,1,6),\n\tcount(DISTINCT(t.USER_ID)) USERCOUNT2,\n\tsum(t.A+t.B+t.C+t.D+t.V+t.XXB+t.XDT+t.TTZ+t.FXG) INVEST_AMT\nFROM\n\tDM_ALL_INVEST t\nWHERE\n\tSUBSTR(t.ROWKEY,1,6) >= '"+DM_ZJ_M_maxdate+"'\nAND SUBSTR(t.ROWKEY, 1 , 6) <> regexp_replace(substr(t.reg_time, 1, 7), '-', '')\nAND t.A+t.B+t.C+t.D+t.V+t.XXB+t.XDT+t.TTZ+t.FXG>0\nand exists(select 1 from DM_ALL_INVEST i \nwhere i.USER_ID=t.USER_ID \nand SUBSTR(i.ROWKEY , 1 , 6) < SUBSTR(t.ROWKEY , 1 , 6) \n and i.A+i.B+i.C+i.D+i.V+i.XXB+i.XDT+i.TTZ+i.FXG>0)\nGROUP BY\n\tSUBSTR(ROWKEY,1,6) " //福利数据-蜂狂合伙人返现金额 val DM_ZJ_M_FL_sql = "SELECT sf.ym,sum(sf.red_get_amount),sum(sf.red_use_amount),sum(sf.fund_get_amt),sum(sf.fund_profit),sum(sf.fund_use_amt),sum(sf.tirr_get_cnt),sum(sf.tirr3_amt),\nsum(sf.tirr_amt),sum(sf.tirr_cnt),sum(sf.fb),sum(sf.fbdh),sum(sf.yqfx) from (\nSELECT\n\tDATE_FORMAT(receive_time, 'yyyyMM') ym,\n\tsum(amount) red_get_amount,\n\t'0' red_use_amount,\n\t'0' fund_get_amt,\n\t'0' fund_profit,\n\t'0' fund_use_amt,\n\t'0' tirr_get_cnt,\n\t'0' tirr3_amt,\n\t'0' tirr_amt,\n\t'0' tirr_cnt,\n\t'0' fb,\n\t'0' fbdh,\n\t'0' yqfx\nFROM\n\tt_user_bribery_money\nWHERE\n\tDATE_FORMAT(receive_time, 'yyyyMM') >= '"+DM_ZJ_M_FL_maxdate+"'\nGROUP BY\n\tDATE_FORMAT(receive_time, 'yyyyMM')\nUNION ALL\n\tSELECT\n\t\tDATE_FORMAT(used_time, 'yyyyMM') ym,\n\t\t'0' red_get_amount,\n\t\tsum(current_use_amount) red_use_amount,\n\t\t'0' fund_get_amt,\n\t\t'0' fund_profit,\n\t\t'0' fund_use_amt,\n\t\t'0' tirr_get_cnt,\n\t\t'0' tirr3_amt,\n\t\t'0' tirr_amt,\n\t\t'0' tirr_cnt,\n\t\t'0' fb,\n\t\t'0' fbdh,\n\t\t'0' yqfx\n\tFROM\n\t\tt_user_bribery_money_used\n\tWHERE\n\t\tDATE_FORMAT(used_time, 'yyyyMM') >= '"+DM_ZJ_M_FL_maxdate+"'\n\tGROUP BY\n\t\tDATE_FORMAT(used_time, 'yyyyMM')\n\tUNION ALL\n\t\tSELECT\n\t\t\tDATE_FORMAT(recharge_time, 'yyyyMM') ym,\n\t\t\t'0' red_get_amount,\n\t\t\t'0' red_use_amount,\n\t\t\tsum(amount) fund_get_amt,\n\t\t\tsum(profit) fund_profit,\n\t\t\t'0' fund_use_amt,\n\t\t\t'0' tirr_get_cnt,\n\t\t\t'0' tirr3_amt,\n\t\t\t'0' tirr_amt,\n\t\t\t'0' tirr_cnt,\n\t\t\t'0' fb,\n\t\t\t'0' fbdh,\n\t\t\t'0' yqfx\n\t\tFROM\n\t\t\tt_fund_recharge_detail\n\t\tWHERE\n\t\t\tDATE_FORMAT(recharge_time, 'yyyyMM') >= '"+DM_ZJ_M_FL_maxdate+"'\n\t\tGROUP BY\n\t\t\tDATE_FORMAT(recharge_time, 'yyyyMM')\n\t\tUNION ALL\n\t\t\tSELECT\n\t\t\t\tDATE_FORMAT(used_time, 'yyyyMM') ym,\n\t\t\t\t'0' red_get_amount,\n\t\t\t\t'0' red_use_amount,\n\t\t\t\t'0' fund_get_amt,\n\t\t\t\t'0' fund_profit,\n\t\t\t\tsum(current_use_amount) fund_use_amt,\n\t\t\t\t'0' tirr_get_cnt,\n\t\t\t\t'0' tirr3_amt,\n\t\t\t\t'0' tirr_amt,\n\t\t\t\t'0' tirr_cnt,\n\t\t\t\t'0' fb,\n\t\t\t\t'0' fbdh,\n\t\t\t\t'0' yqfx\n\t\t\tFROM\n\t\t\t\tt_fund_withdraw_money_used\n\t\t\tWHERE\n\t\t\t\tDATE_FORMAT(used_time, 'yyyyMM') >= '"+DM_ZJ_M_FL_maxdate+"'\n\t\t\tGROUP BY\n\t\t\t\tDATE_FORMAT(used_time, 'yyyyMM')\n\t\t\tUNION ALL\n\t\t\t\tSELECT\n\t\t\t\t\tDATE_FORMAT(tirr.time, 'yyyyMM') ym,\n\t\t\t\t\t'0' red_get_amount,\n\t\t\t\t\t'0' red_use_amount,\n\t\t\t\t\t'0' fund_get_amt,\n\t\t\t\t\t'0' fund_profit,\n\t\t\t\t\t'0' fund_use_amt,\n\t\t\t\t\tcount(tirr.id) tirr_get_cnt,\n\t\t\t\t\t'0' tirr3_amt,\n\t\t\t\t\t'0' tirr_amt,\n\t\t\t\t\t'0' tirr_cnt,\n\t\t\t\t\t'0' fb,\n\t\t\t\t\t'0' fbdh,\n\t\t\t\t\t'0' yqfx\n\t\t\t\tFROM\n\t\t\t\t\tt_interest_rate_roll tirr\n\t\t\t\tWHERE\n\t\t\t\t\tDATE_FORMAT(tirr.time, 'yyyyMM') >= '"+DM_ZJ_M_FL_maxdate+"'\n\t\t\t\tGROUP BY\n\t\t\t\t\tDATE_FORMAT(tirr.time, 'yyyyMM')\n\t\t\t\tUNION ALL\n\t\t\t\t\tSELECT\n\t\t\t\t\t\tt.ym,\n\t\t\t\t\t\t'0' red_get_amount,\n\t\t\t\t\t\t'0' red_use_amount,\n\t\t\t\t\t\t'0' fund_get_amt,\n\t\t\t\t\t\t'0' fund_profit,\n\t\t\t\t\t\t'0' fund_use_amt,\n\t\t\t\t\t\t'0' tirr_get_cnt,\n\t\t\t\t\t\tsum(t.tirr3_amt) tirr3_amt,\n\t\t\t\t\t\tsum(t.tirr_amt) tirr_amt,\n\t\t\t\t\t\tsum(t.tirr_cnt) tirr_cnt,\n\t\t\t\t\t\t'0' fb,\n\t\t\t\t\t\t'0' fbdh,\n\t\t\t\t\t\t'0' yqfx\n\t\t\t\t\tFROM\n\t\t\t\t\t\t(\n\t\t\t\t\t\t\tSELECT\n\t\t\t\t\t\t\t\tDATE_FORMAT(ti.time, 'yyyyMM') ym,\n\t\t\t\t\t\t\t\tsum(\n\n\t\t\t\t\t\t\t\t\tIF (\n\t\t\t\t\t\t\t\t\t\ttir.interest_rate = 3,\n\t\t\t\t\t\t\t\t\t\tti.amount * (\n\t\t\t\t\t\t\t\t\t\t\tCASE\n\t\t\t\t\t\t\t\t\t\t\tWHEN tb.period_unit = 0 THEN\n\t\t\t\t\t\t\t\t\t\t\t\ttb.period * 30\n\t\t\t\t\t\t\t\t\t\t\tWHEN tb.period_unit = 1 THEN\n\t\t\t\t\t\t\t\t\t\t\t\ttb.period\n\t\t\t\t\t\t\t\t\t\t\tEND\n\t\t\t\t\t\t\t\t\t\t) * tir.interest_rate / 360 * 0.01,\n\t\t\t\t\t\t\t\t\t\t0\n\t\t\t\t\t\t\t\t\t)\n\t\t\t\t\t\t\t\t) tirr3_amt,\n\t\t\t\t\t\t\t\tsum(\n\n\t\t\t\t\t\t\t\t\tIF (\n\t\t\t\t\t\t\t\t\t\ttir.interest_rate <> 3,\n\t\t\t\t\t\t\t\t\t\tti.amount * (\n\t\t\t\t\t\t\t\t\t\t\tCASE\n\t\t\t\t\t\t\t\t\t\t\tWHEN tb.period_unit = 0 THEN\n\t\t\t\t\t\t\t\t\t\t\t\ttb.period * 30\n\t\t\t\t\t\t\t\t\t\t\tWHEN tb.period_unit = 1 THEN\n\t\t\t\t\t\t\t\t\t\t\t\ttb.period\n\t\t\t\t\t\t\t\t\t\t\tEND\n\t\t\t\t\t\t\t\t\t\t) * tir.interest_rate / 360 * 0.01,\n\t\t\t\t\t\t\t\t\t\t0\n\t\t\t\t\t\t\t\t\t)\n\t\t\t\t\t\t\t\t) tirr_amt,\n\t\t\t\t\t\t\t\tCOUNT(ti.id) tirr_cnt\n\t\t\t\t\t\t\tFROM\n\t\t\t\t\t\t\t\tt_invests ti\n\t\t\t\t\t\t\tJOIN (\n\t\t\t\t\t\t\t\tSELECT DISTINCT\n\t\t\t\t\t\t\t\t\ttrrb.interest_rate,\n\t\t\t\t\t\t\t\t\ttrrb.invest_id\n\t\t\t\t\t\t\t\tFROM\n\t\t\t\t\t\t\t\t\tt_interest_rate_roll_bill trrb\n\t\t\t\t\t\t\t\tJOIN t_interest_rate_roll tirr ON trrb.interest_id = tirr.id\n\t\t\t\t\t\t\t) tir ON tir.invest_id = ti.id\n\t\t\t\t\t\t\tJOIN t_bids tb ON ti.bid_id = tb.id\n\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\tti.amount > 0\n\t\t\t\t\t\t\tAND DATE_FORMAT(ti.time, 'yyyyMM') >= '"+DM_ZJ_M_FL_maxdate+"'\n\t\t\t\t\t\t\tGROUP BY\n\t\t\t\t\t\t\t\tDATE_FORMAT(ti.time, 'yyyyMM')\n\t\t\t\t\t\t\tUNION ALL\n\t\t\t\t\t\t\t\tSELECT\n\t\t\t\t\t\t\t\t\tDATE_FORMAT(tpo.time, 'yyyyMM') ym,\n\t\t\t\t\t\t\t\t\t'0' tirr3_amt,\n\t\t\t\t\t\t\t\t\tsum(\n\t\t\t\t\t\t\t\t\t\ttpo.amount * tp.cycle_days * tpo.interest_rate_roll_apr * 0.01 / 360\n\t\t\t\t\t\t\t\t\t) tirr_amt,\n\t\t\t\t\t\t\t\t\tcount(tpo.id) tirr_cnt\n\t\t\t\t\t\t\t\tFROM\n\t\t\t\t\t\t\t\t\tt_planing_orders tpo\n\t\t\t\t\t\t\t\tJOIN t_planing tp ON tpo.planing_id = tp.id\n\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\ttpo.interest_rate_roll_apr > 0\n\t\t\t\t\t\t\t\tAND tpo. STATUS = 1\n\t\t\t\t\t\t\t\tAND tpo.amount > 0\n\t\t\t\t\t\t\t\tAND DATE_FORMAT(tpo.time, 'yyyyMM') >= '"+DM_ZJ_M_FL_maxdate+"'\n\t\t\t\t\t\t\t\tGROUP BY\n\t\t\t\t\t\t\t\t\tDATE_FORMAT(tpo.time, 'yyyyMM')\n\t\t\t\t\t\t) t\n\t\t\t\t\tGROUP BY\n\t\t\t\t\t\tt.ym\n\t\t\t\t\tUNION ALL\n\t\t\t\t\t\tSELECT\n\t\t\t\t\t\t\tDATE_FORMAT(m.createTime, 'yyyyMM') ym,\n\t\t\t\t\t\t\t'0' red_get_amount,\n\t\t\t\t\t\t\t'0' red_use_amount,\n\t\t\t\t\t\t\t'0' fund_get_amt,\n\t\t\t\t\t\t\t'0' fund_profit,\n\t\t\t\t\t\t\t'0' fund_use_amt,\n\t\t\t\t\t\t\t'0' tirr_get_cnt,\n\t\t\t\t\t\t\t'0' tirr3_amt,\n\t\t\t\t\t\t\t'0' tirr_amt,\n\t\t\t\t\t\t\t'0' tirr_cnt,\n\t\t\t\t\t\t\tsum(m.quantity) fb,\n\t\t\t\t\t\t\t'0' fbdh,\n\t\t\t\t\t\t\t'0' yqfx\n\t\t\t\t\t\tFROM\n\t\t\t\t\t\t\tmemberintegralinfo m\n\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\tDATE_FORMAT(m.createTime, 'yyyyMM') >= '"+DM_ZJ_M_FL_maxdate+"'\n\t\t\t\t\t\tAND m.total > 0\n\t\t\t\t\t\tAND m.businessType NOT IN (3, 5)\n\t\t\t\t\t\tGROUP BY\n\t\t\t\t\t\t\tDATE_FORMAT(m.createTime, 'yyyyMM')\n\t\t\t\t\t\tUNION ALL\n\t\t\t\t\t\t\tSELECT\n\t\t\t\t\t\t\t\tso.ym,\n\t\t\t\t\t\t\t\t'0' red_get_amount,\n\t\t\t\t\t\t\t\t'0' red_use_amount,\n\t\t\t\t\t\t\t\t'0' fund_get_amt,\n\t\t\t\t\t\t\t\t'0' fund_profit,\n\t\t\t\t\t\t\t\t'0' fund_use_amt,\n\t\t\t\t\t\t\t\t'0' tirr_get_cnt,\n\t\t\t\t\t\t\t\t'0' tirr3_amt,\n\t\t\t\t\t\t\t\t'0' tirr_amt,\n\t\t\t\t\t\t\t\t'0' tirr_cnt,\n\t\t\t\t\t\t\t\t'0' fb,\n\t\t\t\t\t\t\t\tSUM(so.fbdh) fbdh,\n\t\t\t\t\t\t\t\t'0' yqfx\n\t\t\t\t\t\t\tFROM\n\t\t\t\t\t\t\t\t(\n\t\t\t\t\t\t\t\t\tSELECT\n\t\t\t\t\t\t\t\t\t\tDATE_FORMAT(createTime, 'yyyyMM') ym,\n\t\t\t\t\t\t\t\t\t\tsum(giftChangeAmount) fbdh\n\t\t\t\t\t\t\t\t\tFROM\n\t\t\t\t\t\t\t\t\t\tsaleorder\n\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\tpayTypeID = 'pay.giftpay'\n\t\t\t\t\t\t\t\t\tAND DATE_FORMAT(createTime, 'yyyyMM') >= '"+DM_ZJ_M_FL_maxdate+"'\n\t\t\t\t\t\t\t\t\tGROUP BY\n\t\t\t\t\t\t\t\t\t\tDATE_FORMAT(createTime, 'yyyyMM')\n\t\t\t\t\t\t\t\t) so\n\t\t\t\t\t\t\tGROUP BY\n\t\t\t\t\t\t\t\tso.ym\n\t\t\t\t\t\t\tUNION ALL\n\t\t\t\t\t\t\t\tSELECT\n\t\t\t\t\t\t\t\t\tfx.ym,\n\t\t\t\t\t\t\t\t\t'0' red_get_amount,\n\t\t\t\t\t\t\t\t\t'0' red_use_amount,\n\t\t\t\t\t\t\t\t\t'0' fund_get_amt,\n\t\t\t\t\t\t\t\t\t'0' fund_profit,\n\t\t\t\t\t\t\t\t\t'0' fund_use_amt,\n\t\t\t\t\t\t\t\t\t'0' tirr_get_cnt,\n\t\t\t\t\t\t\t\t\t'0' tirr3_amt,\n\t\t\t\t\t\t\t\t\t'0' tirr_amt,\n\t\t\t\t\t\t\t\t\t'0' tirr_cnt,\n\t\t\t\t\t\t\t\t\t'0' fb,\n\t\t\t\t\t\t\t\t\t'0' fbdh,\n\t\t\t\t\t\t\t\t\tsum(fx.yqfx) yqfx\n\t\t\t\t\t\t\t\tFROM\n\t\t\t\t\t\t\t\t\t(\n\t\t\t\t\t\t\t\t\t\tSELECT\n\t\t\t\t\t\t\t\t\t\t\tDATE_FORMAT(ts.time, 'yyyyMM') ym,\n\t\t\t\t\t\t\t\t\t\t\tsum(cash.cashmoney) yqfx\n\t\t\t\t\t\t\t\t\t\tFROM\n\t\t\t\t\t\t\t\t\t\t\tt_invests ts\n\t\t\t\t\t\t\t\t\t\tJOIN (\n\t\t\t\t\t\t\t\t\t\t\tSELECT\n\t\t\t\t\t\t\t\t\t\t\t\tcashmoney,\n\t\t\t\t\t\t\t\t\t\t\t\tinvest_id\n\t\t\t\t\t\t\t\t\t\t\tFROM\n\t\t\t\t\t\t\t\t\t\t\t\tt_cashback\n\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\tbilltype = '1'\n\t\t\t\t\t\t\t\t\t\t) cash ON cash.invest_id = ts.id\n\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\tts.transfers_id = 0\n\t\t\t\t\t\t\t\t\t\tAND DATE_FORMAT(ts.time, 'yyyyMM') >= '"+DM_ZJ_M_FL_maxdate+"'\n\t\t\t\t\t\t\t\t\t\tGROUP BY\n\t\t\t\t\t\t\t\t\t\t\tDATE_FORMAT(ts.time, 'yyyyMM')\n\t\t\t\t\t\t\t\t\t\tUNION ALL\n\t\t\t\t\t\t\t\t\t\t\tSELECT\n\t\t\t\t\t\t\t\t\t\t\t\tDATE_FORMAT(tf.time, 'yyyyMM') ym,\n\t\t\t\t\t\t\t\t\t\t\t\tsum(cash3.cashmoney) yqfx\n\t\t\t\t\t\t\t\t\t\t\tFROM\n\t\t\t\t\t\t\t\t\t\t\t\tt_fxgo_invests tf\n\t\t\t\t\t\t\t\t\t\t\tJOIN (\n\t\t\t\t\t\t\t\t\t\t\t\tSELECT\n\t\t\t\t\t\t\t\t\t\t\t\t\tcashmoney,\n\t\t\t\t\t\t\t\t\t\t\t\t\tinvest_id\n\t\t\t\t\t\t\t\t\t\t\t\tFROM\n\t\t\t\t\t\t\t\t\t\t\t\t\tt_cashback\n\t\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t\tbilltype = '3'\n\t\t\t\t\t\t\t\t\t\t\t) cash3 ON cash3.invest_id = tf.id\n\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\ttf. STATUS = 1\n\t\t\t\t\t\t\t\t\t\t\tAND DATE_FORMAT(tf.time, 'yyyyMM') >= '"+DM_ZJ_M_FL_maxdate+"'\n\t\t\t\t\t\t\t\t\t\t\tGROUP BY\n\t\t\t\t\t\t\t\t\t\t\t\tDATE_FORMAT(tf.time, 'yyyyMM')\n\t\t\t\t\t\t\t\t\t\t\tUNION ALL\n\t\t\t\t\t\t\t\t\t\t\t\tSELECT\n\t\t\t\t\t\t\t\t\t\t\t\t\tDATE_FORMAT(ts2.time, 'yyyyMM') ym,\n\t\t\t\t\t\t\t\t\t\t\t\t\tsum(cash2.cashmoney) yqfx\n\t\t\t\t\t\t\t\t\t\t\t\tFROM\n\t\t\t\t\t\t\t\t\t\t\t\t\tt_planing_orders ts2\n\t\t\t\t\t\t\t\t\t\t\t\tJOIN (\n\t\t\t\t\t\t\t\t\t\t\t\t\tSELECT\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tcashmoney,\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tinvest_id\n\t\t\t\t\t\t\t\t\t\t\t\t\tFROM\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tt_cashback\n\t\t\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tbilltype = '2'\n\t\t\t\t\t\t\t\t\t\t\t\t) cash2 ON cash2.invest_id = ts2.id\n\t\t\t\t\t\t\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\t\t\t\t\t\t\tts2. STATUS > 0\n\t\t\t\t\t\t\t\t\t\t\t\tAND DATE_FORMAT(ts2.time, 'yyyyMM') >= '"+DM_ZJ_M_FL_maxdate+"'\n\t\t\t\t\t\t\t\t\t\t\t\tGROUP BY\n\t\t\t\t\t\t\t\t\t\t\t\t\tDATE_FORMAT(ts2.time, 'yyyyMM')\n\t\t\t\t\t\t\t\t\t) fx\n\t\t\t\t\t\t\t\tGROUP BY\n\t\t\t\t\t\t\t\t\tfx.ym\n)sf group by sf.ym" //val userLogonRows: List[Row] = filteredScoreDF.groupBy(filteredScoreDF.col("dt"), filteredScoreDF.col("periods")).count.alias("cnt").collectAsList //投资年化 val DM_ZJ_M_TZNH_sql = "select tz.dt,tz.periods,sum(tz.fjh_amt) fjh_amt,sum(tz.fjh_tznh) fjh_tznh,sum(tz.fxg) fxg,sum(tz.fxg_tznh) fxg_tznh,sum(tz.new) new,sum(tz.new_tznh) new_tznh, sum(tz.xdt) xdt,sum(tz.xdt_tznh) xdt_tznh,sum(tz.ttz) ttz,sum(tz.ttz_tznh) ttz_tznh from (\t\t\t\nSELECT\t\n\t\t\t\tDATE_FORMAT(tpi.time,'yyyyMM') dt, tp.cycle_days periods,\t\t\t\t\t\t\t\t\n\t\t\t\tSUM(tpi.amount) fjh_amt,\n sum(tpi.amount * tp.cycle_days /360) fjh_tznh,\n\t\t\t\t'0' new,\n '0' new_tznh,\n\t\t\t\t'0' xdt,\n '0' xdt_tznh,\n\t\t\t\t'0' ttz,\n '0' ttz_tznh,\n\t\t\t\t'0' fxg,\n '0' fxg_tznh\n\t\t\tFROM\n\t\t\t\tt_planing_invests tpi\n\t\t\tJOIN t_planing tp ON tpi.planing_id = tp.id\n\t\t\tWHERE\n\t\t\t\ttpi.transfer_id = 0\n\t\t\tAND DATE_FORMAT(tpi.time,'yyyyMM') >= '" + DM_ZJ_M_TZNH_maxdate +"'\n\t\t\tGROUP BY\t\t\t\n\t\t\t\tDATE_FORMAT(tpi.time,'yyyyMM'),tp.cycle_days\t\t\t\t\n\t\t\tUNION ALL\n\t\t\t\tSELECT\t\t\t\t\t\n\t\t\t\t\tDATE_FORMAT(ts.time,'yyyyMM') dt,\n CASE\n\t\t\t\t\t\t\tWHEN tb.period_unit = '-1' THEN\n\t\t\t\t\t\t\t\t\t\ttb.period * 12 * 30\n\t\t\t\t\t\t\tWHEN tb.period_unit = '0' THEN\n\t\t\t\t\t\t\t\t\t\ttb.period * 30\n\t\t\t\t\t\t\tWHEN tb.period_unit = '1' THEN\n\t\t\t\t\t\t\t\t\t\ttb.period \n\t\t\t\t\t\t\tEND as periods, \t\t\t\t\n\t\t\t\t\t'0' fjh_amt,\n '0' fjh_tznh, \n\t\t\t\t\tsum(\n\t\t\t\t\t\tIF (tnew.bid_id > 0, ts.amount, 0)\n\t\t\t\t\t) new,\n sum(IF (tnew.bid_id > 0,ts.amount * CASE\n\t\t\t\t\t\t\tWHEN tb.period_unit = '-1' THEN\n\t\t\t\t\t\t\t\t\t\ttb.period * 12 * 30\n\t\t\t\t\t\t\tWHEN tb.period_unit = '0' THEN\n\t\t\t\t\t\t\t\t\t\ttb.period * 30\n\t\t\t\t\t\t\tWHEN tb.period_unit = '1' THEN\n\t\t\t\t\t\t\t\t\t\ttb.period \n\t\t\t\t\t\t\tEND /360,0)) new_tznh, \n\t\t\t\t\tsum(\n\t\t\t\t\t\tIF (tnew.bid_id > 0,0, ts.amount)\n\t\t\t\t\t) xdt,\n sum(IF (tnew.bid_id > 0,0,ts.amount * CASE\n\t\t\t\t\t\t\tWHEN tb.period_unit = '-1' THEN\n\t\t\t\t\t\t\t\t\t\ttb.period * 12 * 30\n\t\t\t\t\t\t\tWHEN tb.period_unit = '0' THEN\n\t\t\t\t\t\t\t\t\t\ttb.period * 30\n\t\t\t\t\t\t\tWHEN tb.period_unit = '1' THEN\n\t\t\t\t\t\t\t\t\t\ttb.period \n\t\t\t\t\t\t\tEND /360)) xdt_tznh, \n\t\t\t\t\t'0' ttz,\n '0' ttz_tznh,\n\t\t\t\t\t'0' fxg,\n '0' fxg_tznh\n\t\t\t\tFROM\n\t\t\t\t\tt_invests ts\n\t\t\t\tJOIN t_bids tb ON ts.bid_id = tb.id\n\t\t\t\tLEFT JOIN (\n\t\t\t\t\tSELECT\n\t\t\t\t\t\tbid_id\n\t\t\t\t\tFROM\n\t\t\t\t\t\tt_bid_increase_interest\n\t\t\t\t\tWHERE\n\t\t\t\t\t\tis_novice = 1\n\t\t\t\t) tnew ON ts.bid_id = tnew.bid_id\n\t\t\t\tWHERE\n\t\t\t\t\ttb.is_fund <> 1\n\t\t\t\tAND ts.transfers_id = 0\n\t\t\t\tAND DATE_FORMAT(ts.time,'yyyyMM') >= '" + DM_ZJ_M_TZNH_maxdate + "'\n\t\t\t\tGROUP BY\t\t\t\t\n\t\t\t\t\tDATE_FORMAT(ts.time,'yyyyMM'),\n\t\t\t\t\t\t\t tb.period_unit,\n\t\t\t\t\t\t\ttb.period \t\t\t\t\t\t\t\t\t\t\t\n\t\t\t\tUNION ALL\n\t\t\t\t\tSELECT\t\t\t\t\t\t\n\t\t\t\t\t\tDATE_FORMAT(apply_date,'yyyyMM') dt,\t\n '60' periods,\t\t\t\t\t\n\t\t\t\t\t\t\t'0' fjh_amt,\n '0' fjh_tznh,\t\t\t\t\t\n\t\t\t\t\t '0' new,\n '0' new_tznh,\n\t\t\t\t '0' xdt,\n '0' xdt_tznh,\n\t\t\t\t\t\tsum(apply_amount) ttz, \n\t\t\t \tsum(apply_amount * 60 /360) ttz_tznh,\n '0' fxg,\n '0' fxg_tznh\n\t\t\t\t\tFROM\n\t\t\t\t\t\tt_huoqibao_user_invest\n\t\t\t\t\tWHERE\n\t\t\t\t\t\tSTATUS = 1\n\t\t\t\t\tAND DATE_FORMAT(apply_date,'yyyyMM') >= '" + DM_ZJ_M_TZNH_maxdate + "' \n\t\t\t\t\tGROUP BY\t\t\t\t\t\n\t\t\t\t\t\tDATE_FORMAT(\n\t\t\t\t\t\t\tapply_date,\n\t\t\t\t\t\t\t'yyyyMM'\n\t\t\t\t\t\t)\n\t\t\t\t\tUNION ALL\n\t\t\t\t\t\tSELECT\t\n\t\t\t\t\t\t\tDATE_FORMAT(ts.time,'yyyyMM') dt, ts.periods * 30 periods,\n\t\t\t\t\t\t\t'0' fjh_amt,\n '0' fjh_tznh,\t\t\t\t\t\n\t\t\t\t\t '0' new,\n '0' new_tznh,\n\t\t\t\t '0' xdt,\n '0' xdt_tznh,\n\t\t\t\t\t\t\t'0' ttz,\n '0' ttz_tznh,\n\t\t\t\t\t\t\tsum(ts.amount) fxg,\n sum(ts.amount * ts.periods * 30 /360) fxg_tznh\t\t\n\t\t\t\t\t\tFROM\n\t\t\t\t\t\t\tt_fxgo_invests ts\n\t\t\t\t\t\tWHERE\n\t\t\t\t\t\t\tts. STATUS = 1\n\t\t\t\t\t\tAND DATE_FORMAT(ts.time,'yyyyMM') >= '" + DM_ZJ_M_TZNH_maxdate +"'\n\t\t\t\t\t\tGROUP BY DATE_FORMAT(ts.time,'yyyyMM'), ts.periods\t\t\t\t\n) tz group by tz.dt,tz.periods" var DM_ZJ_M_DF: DataFrame = spark.sql(DM_ZJ_M_sql) var dyzc_dycz_DF: DataFrame = spark.sql(dyzc_dycz_sql) var fdyzc_wtz_dycz_DF: DataFrame = spark.sql(fdyzc_wtz_dycz_sql) var fdyzc_ytz_dycz_DF: DataFrame = spark.sql(fdyzc_ytz_dycz_sql) var dyzc_ytz_DF: DataFrame = spark.sql(dyzc_ytz_sql) var fdyzc_wtz_DF: DataFrame = spark.sql(fdyzc_wtz_sql) var fdyzc_ytz_DF: DataFrame = spark.sql(fdyzc_ytz_sql) var fl_fk_DF: DataFrame = spark.sql(DM_ZJ_M_FL_sql) var tznh_DF: DataFrame = spark.sql(DM_ZJ_M_TZNH_sql) try { DM_ZJ_M_DF.collect().foreach { userRow => { val ym: String = String.valueOf(userRow.getString(0)) val reg_cnt: String = String.valueOf(userRow.getDouble(1)) val hf_cnt: String = String.valueOf(userRow.getDouble(2)) val recharge: String = String.valueOf(userRow.getDouble(3)) val hkamt: String = String.valueOf(userRow.getDouble(4)) val human_cnt: String = String.valueOf(userRow.getDouble(5)) val invt_cnt: String = String.valueOf(userRow.getDouble(6)) val invt_amt: String = String.valueOf(userRow.getDouble(7)) val tznh : String = String.valueOf(userRow.getDecimal(8)) val rowKey: String = ym ScalaHbase.addRow(DM_ZJ_M, rowKey, "USERS", "YM", ym) ScalaHbase.addRow(DM_ZJ_M, rowKey, "USERS", "REG_CNT", reg_cnt) ScalaHbase.addRow(DM_ZJ_M, rowKey, "USERS", "HF_CNT", hf_cnt) ScalaHbase.addRow(DM_ZJ_M, rowKey, "USERS", "INVT_CNT", invt_cnt) ScalaHbase.addRow(DM_ZJ_M, rowKey, "USERS", "INVT_AMT", invt_amt) ScalaHbase.addRow(DM_ZJ_M, rowKey, "USERS", "HKAMT", hkamt) ScalaHbase.addRow(DM_ZJ_M, rowKey, "USERS", "RECHARGE", recharge) ScalaHbase.addRow(DM_ZJ_M, rowKey, "USERS", "HUMAN_CNT", human_cnt) ScalaHbase.addRow(DM_ZJ_M, rowKey, "USERS", "TZNH", tznh) }} } catch { case e: Exception => { if (e.getClass == classOf[MasterNotRunningException]) { LOG.error("MasterNotRunningException:"+e.toString) } if (e.getClass == classOf[ZooKeeperConnectionException]) { LOG.error("ZooKeeperConnectionException:"+e.toString) } if (e.getClass == classOf[IOException]) { LOG.error("IOException:"+e.toString) } e.printStackTrace } } finally { DM_ZJ_M_DF =null if (null != DM_ZJ_M) { DM_ZJ_M.close } } try { dyzc_dycz_DF.collect().foreach { userRow => { val ym: String = String.valueOf(userRow.getString(0)) val USERCOUNT1: String = String.valueOf(userRow.getLong(1)) val RECHARGE1: String = String.valueOf(userRow.getDouble(2)) val rowKey: String = ym.replaceAll("-","") ScalaHbase.addRow(DM_ZJ_M, rowKey, "USERS", "YM", ym) ScalaHbase.addRow(DM_ZJ_M, rowKey, "USERS", "DYZC_DYCZ_CNT", USERCOUNT1) ScalaHbase.addRow(DM_ZJ_M, rowKey, "USERS", "DYZC_DYCZ_AMT", RECHARGE1) }} fdyzc_wtz_dycz_DF.collect().foreach { userRow => { val ym: String = String.valueOf(userRow.getString(0)) val USERCOUNT2: String = String.valueOf(userRow.getLong(1)) val RECHARGE2: String = String.valueOf(userRow.getDouble(2)) val rowKey: String = ym.replaceAll("-","") ScalaHbase.addRow(DM_ZJ_M, rowKey, "USERS", "YM", ym) ScalaHbase.addRow(DM_ZJ_M, rowKey, "USERS", "FDYZC_WTZ_DYCZ_CNT", USERCOUNT2) ScalaHbase.addRow(DM_ZJ_M, rowKey, "USERS", "FDYZC_WTZ_DYCZ_AMT", RECHARGE2) }} fdyzc_ytz_dycz_DF.collect().foreach { userRow => { val ym: String = String.valueOf(userRow.getString(0)) val USERCOUNT3: String = String.valueOf(userRow.getLong(1)) val RECHARGE3: String = String.valueOf(userRow.getDouble(2)) val rowKey: String = ym.replaceAll("-","") ScalaHbase.addRow(DM_ZJ_M, rowKey, "USERS", "YM", ym) ScalaHbase.addRow(DM_ZJ_M, rowKey, "USERS", "FDYZC_YTZ_DYCZ_CNT", USERCOUNT3) ScalaHbase.addRow(DM_ZJ_M, rowKey, "USERS", "FDYZC_YTZ_DYCZ_AMT", RECHARGE3) }} dyzc_ytz_DF.collect().foreach { userRow => { val ym: String = String.valueOf(userRow.getString(0)) val USERCOUNT4: String = String.valueOf(userRow.getLong(1)) val RECHARGE4: String = String.valueOf(userRow.getDouble(2)) val rowKey: String = ym.replaceAll("-","") ScalaHbase.addRow(DM_ZJ_M, rowKey, "USERS", "YM", ym) ScalaHbase.addRow(DM_ZJ_M, rowKey, "USERS", "DYZC_YTZ_CNT", USERCOUNT4) ScalaHbase.addRow(DM_ZJ_M, rowKey, "USERS", "DYZC_YTZ_AMT", RECHARGE4) }} fdyzc_wtz_DF.collect().foreach { userRow => { val ym: String = String.valueOf(userRow.getString(0)) val USERCOUNT5: String = String.valueOf(userRow.getLong(1)) val INVEST_AMT: String = String.valueOf(userRow.getDouble(2)) val rowKey: String = ym.replaceAll("-","") ScalaHbase.addRow(DM_ZJ_M, rowKey, "USERS", "YM", ym) ScalaHbase.addRow(DM_ZJ_M, rowKey, "USERS", "FDYTZ_WTZ_CNT", USERCOUNT5) ScalaHbase.addRow(DM_ZJ_M, rowKey, "USERS", "FDYTZ_WTZ_AMT", INVEST_AMT) }} fdyzc_ytz_DF.collect().foreach { userRow => { val ym: String = String.valueOf(userRow.getString(0)) val USERCOUNT6: String = String.valueOf(userRow.getLong(1)) val INVEST_AMT: String = String.valueOf(userRow.getDouble(2)) val rowKey: String = ym.replaceAll("-","") ScalaHbase.addRow(DM_ZJ_M, rowKey, "USERS", "YM", ym) ScalaHbase.addRow(DM_ZJ_M, rowKey, "USERS", "FDYZC_YTZ_CNT", USERCOUNT6) ScalaHbase.addRow(DM_ZJ_M, rowKey, "USERS", "FDYZC_YTZ_AMT", INVEST_AMT) }} } catch { case e: Exception => { if (e.getClass == classOf[MasterNotRunningException]) { LOG.error("MasterNotRunningException:"+e.toString) } if (e.getClass == classOf[ZooKeeperConnectionException]) { LOG.error("ZooKeeperConnectionException:"+e.toString) } if (e.getClass == classOf[IOException]) { LOG.error("IOException:"+e.toString) } e.printStackTrace } } finally { dyzc_dycz_DF =null if (null != DM_ZJ_M) { DM_ZJ_M.close } } try { fl_fk_DF.collect().foreach { userRow => { val ym: String = String.valueOf(userRow.getString(0)) val red_get_amount: String = String.valueOf(userRow.getDouble(1)) val red_use_amount: String = String.valueOf(userRow.getDouble(2)) val fund_get_amt: String = String.valueOf(userRow.getDouble(3)) val fund_profit: String = String.valueOf(userRow.getDouble(4)) val fund_use_amt: String = String.valueOf(userRow.getDouble(5)) val tirr_get_cnt: String = String.valueOf(userRow.getDouble(6)) val tirr3_amt: String = String.valueOf(userRow.getDouble(7)) val tirr_amt: String = String.valueOf(userRow.getDouble(8)) val tirr_cnt: String = String.valueOf(userRow.getDouble(9)) val fb: String = String.valueOf(userRow.getDouble(10)) val fbdh: String = String.valueOf(userRow.getDouble(11)) val yqfx: String = String.valueOf(userRow.getDouble(12)) // val rowKey: String = new StringBuffer(DateFormatUtils.format(ts, "yyyyMMdd")).toString + DateFormatUtils.format(ts, "HHmmss") + user_id // val id:String = String.format("%04d",Integer.valueOf(cycle_days)) val rowKey: String = ym.replaceAll("-","") ScalaHbase.addRow(DM_ZJ_M_FL, rowKey, "USERS", "YM", ym) ScalaHbase.addRow(DM_ZJ_M_FL, rowKey, "USERS", "RED_GET_AMOUNT", red_get_amount) ScalaHbase.addRow(DM_ZJ_M_FL, rowKey, "USERS", "RED_USE_AMOUNT", red_use_amount) ScalaHbase.addRow(DM_ZJ_M_FL, rowKey, "USERS", "FUND_GET_AMT", fund_get_amt) ScalaHbase.addRow(DM_ZJ_M_FL, rowKey, "USERS", "FUND_PROFIT", fund_profit) ScalaHbase.addRow(DM_ZJ_M_FL, rowKey, "USERS", "FUND_USE_AMT", fund_use_amt) ScalaHbase.addRow(DM_ZJ_M_FL, rowKey, "USERS", "TIRR_GET_CNT", tirr_get_cnt) ScalaHbase.addRow(DM_ZJ_M_FL, rowKey, "USERS", "TIRR3_AMT", tirr3_amt) ScalaHbase.addRow(DM_ZJ_M_FL, rowKey, "USERS", "TIRR_AMT", tirr_amt) ScalaHbase.addRow(DM_ZJ_M_FL, rowKey, "USERS", "TIRR_CNT", tirr_cnt) ScalaHbase.addRow(DM_ZJ_M_FL, rowKey, "USERS", "FB", fb) ScalaHbase.addRow(DM_ZJ_M_FL, rowKey, "USERS", "FBDH", fbdh) ScalaHbase.addRow(DM_ZJ_M_FL, rowKey, "USERS", "YQFX", yqfx) }} } catch { case e: Exception => { if (e.getClass == classOf[MasterNotRunningException]) { LOG.error("MasterNotRunningException:"+e.toString) } if (e.getClass == classOf[ZooKeeperConnectionException]) { LOG.error("ZooKeeperConnectionException:"+e.toString) } if (e.getClass == classOf[IOException]) { LOG.error("IOException:"+e.toString) } e.printStackTrace } } finally { fl_fk_DF =null if (null != DM_ZJ_M_FL) { DM_ZJ_M_FL.close } } try { tznh_DF.collect().foreach { userRow => { val ym: String = String.valueOf(userRow.getString(0)) val periods: String = String.valueOf(userRow.getString(1)) val fjh_amt: String = String.valueOf(userRow.getDouble(2)) val fjh_tznh: String = String.valueOf(userRow.getDouble(3)) val fxg_amt: String = String.valueOf(userRow.getDouble(4)) val fxg_tznh: String = String.valueOf(userRow.getDouble(5)) val new_amt: String = String.valueOf(userRow.getDouble(6)) val new_tznh: String = String.valueOf(userRow.getDouble(7)) val xdt_amt: String = String.valueOf(userRow.getDouble(8)) val xdt_tznh: String = String.valueOf(userRow.getDouble(9)) val ttz_amt: String = String.valueOf(userRow.getDouble(10)) val ttz_tznh: String = String.valueOf(userRow.getDouble(11)) // val ts: Timestamp = ScalaConn.GetStampByTime(ymd) // val rowKey: String = new StringBuffer(DateFormatUtils.format(ts, "yyyyMMdd")).toString + DateFormatUtils.format(ts, "HHmmss") val id:String = String.format("%04d",Integer.valueOf(periods)) val rowKey = ym + id // val rowKey: String = ScalaConn.GetStampByTime1(ymd).toString ScalaHbase.addRow(DM_ZJ_M_TZNH, rowKey, "USERS", "YM", ym) ScalaHbase.addRow(DM_ZJ_M_TZNH, rowKey, "USERS", "PERIODS", periods) ScalaHbase.addRow(DM_ZJ_M_TZNH, rowKey, "USERS", "FJH_AMT", fjh_amt) ScalaHbase.addRow(DM_ZJ_M_TZNH, rowKey, "USERS", "FJH_TZNH", fjh_tznh) ScalaHbase.addRow(DM_ZJ_M_TZNH, rowKey, "USERS", "FXG_AMT", fxg_amt) ScalaHbase.addRow(DM_ZJ_M_TZNH, rowKey, "USERS", "FXG_TZNH", fxg_tznh) ScalaHbase.addRow(DM_ZJ_M_TZNH, rowKey, "USERS", "NEW_AMT", new_amt) ScalaHbase.addRow(DM_ZJ_M_TZNH, rowKey, "USERS", "NEW_TZNH", new_tznh) ScalaHbase.addRow(DM_ZJ_M_TZNH, rowKey, "USERS", "XDT_AMT", xdt_amt) ScalaHbase.addRow(DM_ZJ_M_TZNH, rowKey, "USERS", "XDT_TZNH", xdt_tznh) ScalaHbase.addRow(DM_ZJ_M_TZNH, rowKey, "USERS", "TTZ_AMT", ttz_amt) ScalaHbase.addRow(DM_ZJ_M_TZNH, rowKey, "USERS", "TTZ_TZNH", ttz_tznh) } } } catch { case e: Exception => { if (e.getClass == classOf[MasterNotRunningException]) { LOG.error("MasterNotRunningException:" + e.toString) } if (e.getClass == classOf[ZooKeeperConnectionException]) { LOG.error("ZooKeeperConnectionException:" + e.toString) } if (e.getClass == classOf[IOException]) { LOG.error("IOException:" + e.toString) } e.printStackTrace } } finally { tznh_DF = null if (null != DM_ZJ_M_TZNH) { DM_ZJ_M_TZNH.close } } } }
扫一扫
1565
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
