SparkSQL电商用户画像（八）之用户画像开发（客户购买类

　　用户画像客户购买类目表createdatabaseifnotexistsgdm；createexternaltableifnotexistsgdm。itcastgdmuserbuycategory（useridbigint，客户IDfirstcatbigint，一级分类IDfirstcatnamestring，一级分类名称secondcatbigint，二分类IDsecondcatnamestring，二级分类名称thirdcatbigint，三级分类IDthirdcatnamestring，三级分类名称month1catcntbigint，近30天购物类目次数month1catamtstring，近30天购物类目金额month3catcntbigint，近90天购物类目次数month3catamtstring，近90天购物类目金额month6catcntbigint，近180天购物类目次数month6catamtstring，近180天购物类目金额totalcatcntbigint，累计购物类目次数totalcatamtstring，累计购物类目次数month1cartcatcntbigint，近30天购物车类目次数month3cartcatcntbigint，近90天购物车类目次数month6cartcatcntbigint，近180天购物车类目次数totalcartcatcntbigint，累计购物车类目次数lastcattimetimestamp，最后一次购买类目时间lastcatagobigint，最后一次购买类目距今天数dwdatetimestamp）partitionedby（dtstring）；计算订单中客户购买情况droptableifexistsgdm。itcastgdmuserbuycategorytemp；CREATETABLEgdm。itcastgdmuserbuycategorytempASSELECTa。userid，firstcategoryid，firstcategoryname，secondcategoryid，secondcateryname，b。thirdcartthirdcategoryid，b。thirdcatnamethirdcategoryname，SUM（CASEWHENa。dat301THENb。goodsamountEND）month1categorycnt，SUM（CASEWHENa。dat301THENCOALESCE（b。currprice，0）COALESCE（b。goodsamount，0）END）month1categoryamt，SUM（CASEWHENa。dat901THENb。goodsamountEND）month3categorycnt，SUM（CASEWHENa。dat901THENCOALESCE（b。currprice，0）COALESCE（b。goodsamount，0）END）month3categoryamt，SUM（CASEWHENa。dat1801THENb。goodsamountEND）month6categorycnt，SUM（CASEWHENa。dat1801THENCOALESCE（b。currprice，0）COALESCE（b。goodsamount，0）END）month6categoryamt，SUM（b。goodsamount）totalcategorycnt，SUM（COALESCE（b。currprice，0）COALESCE（b。goodsamount，0））totalcategoryamt，MAX（a。orderdate）lastcategorytime，DATEDIFF（MIN（a。orderdate），20170101）lastcategoryago，FROMUNIXTIME（UNIXTIMESTAMP（））dwdateFROM（SELECTa。，（CASEWHENorderdateDATESUB（20170101，29）ANDorderdate20170101THEN1END）dat30，（CASEWHENorderdateDATESUB（20170101，89）ANDorderdate20170101THEN1END）dat90，（CASEWHENorderdateDATESUB（20170101，179）ANDorderdate20170101THEN1END）dat180FROMfdm。itcastfdmorderaWHEREdt20170101）aJOIN（SELECTFROMfdm。itcastfdmordergoodsWHEREdt20170101）bON（a。useridb。userid）GROUPBYa。userid，b。thirdcart，b。thirdcatname；购物车中类目情况购物车中类目情况droptableifexistsgdm。itcastgdmusercartcategorytemp；createtablegdm。itcastgdmusercartcategorytempasselecta。userid，b。thirdcart，sum（casewhentodate（addtime）datesub（20170101，29）andtodate（addtime）20170101then1end）month1categorycnt，sum（casewhentodate（addtime）datesub（20170101，59）andtodate（addtime）20170101then1end）month3categorycnt，sum（casewhentodate（addtime）datesub（20170101，179）andtodate（addtime）20170101then1end）month6categorycnt，count（1）totalcategorycntfrom（selectfromfdm。itcastfdmordercartwheredt20170101andtodate（addtime）datesub（20170101，179）andtodate（addtime）20170101）aleftjoin（selectgoodsid，thirdcartfromfdm。itcastfdmordergoodswheredt20170101groupbygoodsid，thirdcart）bon（a。goodsidb。goodsid）groupbyuserid，b。thirdcart；整合droptableifexistsgdm。itcastgdmusercategorytotal；createtablegdm。itcastgdmusercategorytotalasselecta。userid，b。firstcategoryid，b。firstcategoryname，b。secondcategoryid，b。secondcateryname，a。thirdcategoryid，b。thirdcategorynamefrom（selectuserid，thirdcategoryidfromgdm。itcastgdmuserbuycategorytempunionallselectuserid，thirdcartfromgdm。itcastgdmusercartcategorytemp）aleftjoingdm。gdmcategorycodebon（a。thirdcategoryidb。thirdcategoryid）groupbya。userid，b。firstcategoryid，b。firstcategoryname，b。secondcategoryid，b。secondcateryname，a。thirdcategoryid，b。thirdcategoryname；生成最终客户购买类目表INSERToverwriteTABLEgdm。itcastgdmuserbuycategoryPARTITION（dt20170101）SELECTt。userid，t。firstcategoryid，t。firstcategoryname，t。secondcategoryid，t。secondcateryname，t。thirdcategoryid，t。thirdcategoryname，t1。month1categorycnt，t1。month1categoryamt，t1。month3categorycnt，t1。month3categoryamt，t1。month6categorycnt，t1。month6categoryamt，t1。totalcategorycnt，t1。totalcategoryamt，t2。month1categorycnt，t2。month3categorycnt，t2。month6categorycnt，t2。totalcategorycnt，t1。lastcategorytime，t1。lastcategoryago，FROMUNIXTIME（UNIXTIMESTAMP（））dwdateFROMgdm。itcastgdmusercategorytotaltLEFTJOINgdm。itcastgdmuserbuycategorytempt1ON（t。useridt1。useridANDt。thirdcategoryidt1。thirdcategoryid）LEFTJOINgdm。itcastgdmusercartcategorytempt2ON（t。useridt2。useridANDt。thirdcategoryidt2。thirdcart）；