SparkSQL电商用户画像（七）之用户画像开发（客户消费订

　　用户画像客户消费订单表createdatabaseifnotexistsgdm；createtableifnotexistsgdm。itcastgdmuserconsumeorder（useridstring，客户IDfirstordertimetimestamp，第一次消费时间lastordertimetimestamp，最近一次消费时间firstorderagobigint，首单距今时间lastorderagobigint，尾单距今时间month1hgordercntbigint，近30天购买次数（不含退拒）month1hgorderamtdouble，近30天购买金额（不含退拒）month2hgordercntbigint，近60天购买次数（不含退拒）month2hgorderamtdouble，近60天购买金额（不含退拒）month3hgordercntbigint，近90天购买次数（不含退拒）month3hgorderamtdouble，近90天购买金额（不含退拒）month1ordercntbigint，近30天购买次数（含退拒）month1orderamtdouble，近30天购买金额（含退拒）month2ordercntbigint，近60天购买次数（含退拒）month2orderamtdouble，近60天购买金额（含退拒）month3ordercntbigint，近90天购买次数（含退拒）month3orderamtdouble，近90天购买金额（含退拒）maxorderamtdouble，最大消费金额minorderamtdouble，最小消费金额totalordercntbigint，累计消费次数（不含退拒）totalorderamtdouble，累计消费金额（不含退拒）useravgamtdouble，客单价（含退拒）month3useravgamtdouble，近90天的客单价commonaddressstring，常用收货地址commonpaytypestring，常用支付方式month1cartcntbigint，近30天购物车的次数month1cartgoodscntbigint，近30天购物车商品件数month1cartsubmitcntbigint，近30天购物车提交商品件数month1cartratedouble，近30天购物车成功率month1cartcanclecntdouble，近30天购物车放弃件数returncntbigint，退货商品数量returnamtdouble，退货商品金额rejectcntbigint，拒收商品数量rejectamtdouble，拒收商品金额lastreturntimetimestamp，最近一次退货时间schoolordercntbigint，学校下单总数companyordercntbigint，单位下单总数homeordercntbigint，家里下单总数forenoonordercntbigint，上午下单总数afternoonordercntbigint，下午下单总数nightordercntbigint，晚上下单总数morningordercntbigint，凌晨下单总数dwdatetimestamp）partitionedby（dtstring）；客户消费订单模型表临时表01droptableifexistsgdm。itcastgdmuserconsumeordertemp01；CREATETABLEgdm。itcastgdmuserconsumeordertemp01ASSELECTt。userid，MIN（orderdate）firstordertime，第一次消费时间MAX（orderdate）lastordertime，最近一次消费时间DATEDIFF（MIN（orderdate），20170101）firstorderago，首单距今时间DATEDIFF（MAX（orderdate），20170101）lastorderago，尾单距今时间SUM（CASEWHENt。dat301ANDt。orderflag0THEN1END）month1hgordercnt，近30天购买次数（不含退拒）SUM（CASEWHENt。dat301ANDt。orderflag0THENt。ordermoneyEND）month1hgorderamt，近30天购买金额（不含退拒）SUM（CASEWHENt。dat601ANDt。orderflag0THEN1END）month2hgordercnt，近60天购买次数（不含退拒）SUM（CASEWHENt。dat601ANDt。orderflag0THENt。ordermoneyEND）month2hgorderamt，近60天购买金额（不含退拒）SUM（CASEWHENt。dat901ANDt。orderflag0THEN1END）month3hgordercnt，近90天购买次数（不含退拒）SUM（CASEWHENt。dat901ANDt。orderflag0THENt。ordermoneyEND）month3hgorderamt，近90天购买金额（不含退拒）SUM（dat30）month1ordercnt，近30天购买次数（含退拒）SUM（CASEWHENt。dat301THENt。ordermoneyEND）month1orderamt，近30天购买金额（含退拒）SUM（dat60）month2ordercnt，近60天购买次数（含退拒）SUM（CASEWHENt。dat601THENt。ordermoneyEND）month2orderamt，近60天购买金额（含退拒）SUM（dat90）month3ordercnt，近90天购买次数（含退拒）SUM（CASEWHENt。dat901THENt。ordermoneyEND）month3orderamt，近90天购买金额（含退拒）MAX（t。ordermoney）maxorderamt，最大消费金额MIN（t。ordermoney）minorderamt，最小消费金额SUM（CASEWHENt。orderflag0THEN1END）totalordercnt，累计消费次数（不含退拒）SUM（CASEWHENt。orderflag0THENt。ordermoneyEND）totalorderamt，累计消费金额（不含退拒）SUM（couponmoney）totalcouponamt，累计使用代金券金额SUM（t。ordermoney）COUNT（1）useravgamt，客单价（含退拒）0month3useravgamt，近90天的客单价（含退拒）0commonaddress，常用收获地址0commonpaytype，常用支付方式0month1cartcnt，最近30天购物车次数0month1cartgoodscnt，最近30天购物车商品件数0month1cartsubmitcnt，最近30天购物车提交商品件数0month1orderrate，最近30天购物车成功率0month1cartcanclecnt，最近30天购物车放弃件数SUM（CASEWHENt。orderstatus3THENt1。goodsamountEND）returncnt，退货商品数量SUM（CASEWHENt。orderstatus3THENt。ordermoneyEND）returnamt，退货商品金额SUM（CASEWHENt。orderstatus4THENt1。goodsamountEND）rejectcnt，拒收商品数量SUM（CASEWHENt。orderstatus4THENt。ordermoneyEND）rejectamt，拒收商品金额MAX（CASEWHENt。orderstatus3THENt。orderdateEND）lastreturntime，最近一次退货时间SUM（CASEWHENt2。orderaddr1THEN1END）schoolordercnt，学校下单总数SUM（CASEWHENt2。orderaddr2THEN1END）companyordercnt，单位下单总数SUM（CASEWHENt2。orderaddr3THEN1END）homeordercnt，家里下单总数SUM（CASEWHENt。orderhour8ANDt。orderhour11THEN1END）forenoonordercnt，上午下单总数SUM（CASEWHENt。orderhour12ANDt。orderhour18THEN1END）afternoonordercnt，下午下单总数SUM（CASEWHENt。orderhour19ANDt。orderhour22THEN1END）nightordercnt，晚上下单总数SUM（CASEWHENt。orderhour23ANDt。orderhour7THEN1END）morningordercnt凌晨下单总数FROM（SELECTa。，（CASEWHENorderdateDATESUB（20170101，29）ANDorderdate20170101THEN1END）dat30，（CASEWHENorderdateDATESUB（20170101，59）ANDorderdate20170101THEN1END）dat60，（CASEWHENorderdateDATESUB（20170101，89）ANDorderdate20170101THEN1END）dat90，（CASEWHENa。orderstatusIN（3，4）THEN1ELSE0END）orderflag，退货与拒收标示HOUR（orderdate）orderhourFROMgdm。itcastgdmorderaWHEREdt20170101）tLEFTJOIN（SELECTorderid，goodsamountFROMfdm。itcastfdmordergoods）t1ON（t。orderidt1。orderid）LEFTJOIN（SELECTuserid，orderaddrFROMgdm。itcastuserorderaddrmodel）t2ON（t。useridt2。userid）GROUPBYt。userid；客户消费订单模型表临时表01droptableifexistsgdm。itcastgdmuserconsumeordertemp01；CREATETABLEgdm。itcastgdmuserconsumeordertemp01ASSELECTt。userid，MIN（orderdate）firstordertime，第一次消费时间MAX（orderdate）lastordertime，最近一次消费时间DATEDIFF（MIN（orderdate），20170101）firstorderago，首单距今时间DATEDIFF（MAX（orderdate），20170101）lastorderago，尾单距今时间SUM（CASEWHENt。dat301ANDt。orderflag0THEN1END）month1hgordercnt，近30天购买次数（不含退拒）SUM（CASEWHENt。dat301ANDt。orderflag0THENt。ordermoneyEND）month1hgorderamt，近30天购买金额（不含退拒）SUM（CASEWHENt。dat601ANDt。orderflag0THEN1END）month2hgordercnt，近60天购买次数（不含退拒）SUM（CASEWHENt。dat601ANDt。orderflag0THENt。ordermoneyEND）month2hgorderamt，近60天购买金额（不含退拒）SUM（CASEWHENt。dat901ANDt。orderflag0THEN1END）month3hgordercnt，近90天购买次数（不含退拒）SUM（CASEWHENt。dat901ANDt。orderflag0THENt。ordermoneyEND）month3hgorderamt，近90天购买金额（不含退拒）SUM（dat30）month1ordercnt，近30天购买次数（含退拒）SUM（CASEWHENt。dat301THENt。ordermoneyEND）month1orderamt，近30天购买金额（含退拒）SUM（dat60）month2ordercnt，近60天购买次数（含退拒）SUM（CASEWHENt。dat601THENt。ordermoneyEND）month2orderamt，近60天购买金额（含退拒）SUM（dat90）month3ordercnt，近90天购买次数（含退拒）SUM（CASEWHENt。dat901THENt。ordermoneyEND）month3orderamt，近90天购买金额（含退拒）MAX（t。ordermoney）maxorderamt，最大消费金额MIN（t。ordermoney）minorderamt，最小消费金额SUM（CASEWHENt。orderflag0THEN1END）totalordercnt，累计消费次数（不含退拒）SUM（CASEWHENt。orderflag0THENt。ordermoneyEND）totalorderamt，累计消费金额（不含退拒）SUM（couponmoney）totalcouponamt，累计使用代金券金额SUM（t。ordermoney）COUNT（1）useravgamt，客单价（含退拒）0month3useravgamt，近90天的客单价（含退拒）0commonaddress，常用收获地址0commonpaytype，常用支付方式0month1cartcnt，最近30天购物车次数0month1cartgoodscnt，最近30天购物车商品件数0month1cartsubmitcnt，最近30天购物车提交商品件数0month1orderrate，最近30天购物车成功率0month1cartcanclecnt，最近30天购物车放弃件数SUM（CASEWHENt。orderstatus3THENt1。goodsamountEND）returncnt，退货商品数量SUM（CASEWHENt。orderstatus3THENt。ordermoneyEND）returnamt，退货商品金额SUM（CASEWHENt。orderstatus4THENt1。goodsamountEND）rejectcnt，拒收商品数量SUM（CASEWHENt。orderstatus4THENt。ordermoneyEND）rejectamt，拒收商品金额MAX（CASEWHENt。orderstatus3THENt。orderdateEND）lastreturntime，最近一次退货时间SUM（CASEWHENt2。orderaddr1THEN1END）schoolordercnt，学校下单总数SUM（CASEWHENt2。orderaddr2THEN1END）companyordercnt，单位下单总数SUM（CASEWHENt2。orderaddr3THEN1END）homeordercnt，家里下单总数SUM（CASEWHENt。orderhour8ANDt。orderhour11THEN1END）forenoonordercnt，上午下单总数SUM（CASEWHENt。orderhour12ANDt。orderhour18THEN1END）afternoonordercnt，下午下单总数SUM（CASEWHENt。orderhour19ANDt。orderhour22THEN1END）nightordercnt，晚上下单总数SUM（CASEWHENt。orderhour23ANDt。orderhour7THEN1END）morningordercnt凌晨下单总数FROM（SELECTa。，（CASEWHENorderdateDATESUB（20170101，29）ANDorderdate20170101THEN1END）dat30，（CASEWHENorderdateDATESUB（20170101，59）ANDorderdate20170101THEN1END）dat60，（CASEWHENorderdateDATESUB（20170101，89）ANDorderdate20170101THEN1END）dat90，（CASEWHENa。orderstatusIN（3，4）THEN1ELSE0END）orderflag，退货与拒收标示HOUR（orderdate）orderhourFROMgdm。itcastgdmorderaWHEREdt20170101）tLEFTJOIN（SELECTorderid，goodsamountFROMfdm。itcastfdmordergoods）t1ON（t。orderidt1。orderid）LEFTJOIN（SELECTuserid，orderaddrFROMgdm。itcastuserorderaddrmodel）t2ON（t。useridt2。userid）GROUPBYt。userid；购物车临时模型表临时表02DROPTABLEIFEXISTSgdm。itcastgdmuserconsumeordertemp02；CREATETABLEgdm。itcastgdmuserconsumeordertemp02ASSELECTuserid，COUNT（1）month1cartcnt，最近30天购物车次数SUM（goodsnum）month1cartgoodscnt，最近30天购物车商品件数SUM（CASEWHENsumbittimeTHENgoodsnumELSE0END）month1cartsubmitcnt，最近30天购物车提交商品件数month1cartrate，最近30天购物车成功率SUM（CASEWHENcancletimeTHENgoodsnumELSE0END）month1cartcanclecnt最近30天购物车放弃件数FROMfdm。itcastfdmordercartWHEREdt20170101ANDtodate（addtime）DATESUB（20170101，29）ANDtodate（addtime）20170101GROUPBYuserid；购物车临时模型表常用地址和常用支付方式临时表03droptableifexistsgdm。itcastgdmuserconsumeordertemp03；createtablegdm。gdmuserconsumeordertemp03asselectt。userid，t。con，t。type，t。cntfrom（selectb。userid，b。con，b。type，b。cnt，rownumber（）over（distributebyb。userid，b。typesortbyb。cnt，b。typedesc）rnfrom（selecta。userid，concat（coalesce（areaname，），coalesce（address，））con，addresstype，count（1）cntfromgdm。itcastgdmorderawheredt20170101groupbya。userid，concat（coalesce（areaname，），coalesce（address，））unionallselecta。userid，a。paytypecon，paytypetype，count（1）cntfromgdm。itcastgdmorderawheredt20170101groupbya。userid，a。paytype）b）twheret。rn1；购物车表和订单表整合droptableifexistsgdm。itcastgdmuserconsumeordertemp100；createtablegdm。gdmuserconsumeordertemp100asselecta。useridfrom（selectuseridfromgdm。itcastgdmuserconsumeordertemp01unionallselectuseridfromgdm。itcastgdmuserconsumeordertemp02）agroupbya。userid；