客户画像项目中可能会遇到此类问题,因为客户画像要求的是对单个用户信息的查询。 但倘若以日周期为准,今日梳理的数据宽表中某个字段为null,但此类字段不能以null显示,要以前一天有数据的值为今日的值,就需要做case when处理。 同时还有个问题,日表拥有某些客户信息(以日更新),但并不是用户全量表,而月表为用户全量表(拥有历史月的所有用户数),日表可能会增加也可能会减少数据。这样 不管你left join谁 都无法得到全量的用户数据。 使用以下方法便可实现:
方法1:
FULL OUTER JOIN 关键字返回左表(Customers)和右表(Orders)中所有的行。如果 "table1" 表中的行没有匹配或者 "table2" 表中的行在 "table1" 表中没有匹配,也会列出这些行。
1 StringBuffer createTmpTable = new StringBuffer(); 2 StringBuffer tmpUnionSelect = new StringBuffer(); 3 StringBuffer tmpTableFrom = new StringBuffer(); 4 for(int i=0;i<ColumnNames.size();i++){ 5 SiCustomerLabelInfoModel Column = ColumnNames.get(i); 6 List<CiMdaSysTableModel> ciMdaSysTable = ciCustomerJDao.getMdaSysTableName(Column.getColumnName()); 7 String alias = "t_" + ciMdaSysTable.get(0).getTableId(); 8 String aliasColumn = alias + "." + Column.getColumnName(); 9 if(ciMdaSysTable.get(0).getUpdateCycle() == 1){ 10 aliasTable = ciMdaSysTable.get(0).getTableName() +"_"+ dayDate + " " + alias; 11 }else{ 12 aliasTable = ciMdaSysTable.get(0).getTableName() + "_" + monthDate + " "+ alias; 13 } 14 if(ciMdaSysTable.get(0).getUpdateCycle() == 2){ 15 mainTable = aliasTable; 16 } 17 ColumnNameList.add(aliasColumn); 18 tableNameList.add(aliasTable); 19 } 20 tmpUnionSelect.append("select ").append(mainColumn).append(" from (").append(" select ").append(mainColumn).append(" from ").append(mainTable); 21 selectResult.append("select ").append("tmpTable.").append(mainColumn); 22 fromTableName.append(" from tmpTable "); 23 Iterator<String> table = tableNameList.iterator(); 24 while(table.hasNext()){ 25 String tableName = table.next(); 26 String[] tableAlias = tableName.split(" "); 27 String alias = tableAlias[1]; 28 29 if(!mainTable.equals(tableName)){ 30 tmpTableFrom.append("union all select ").append(mainColumn).append(" from ").append(tableName).append(") a") 31 .append(" group by ").append(mainColumn); 32 } 33 fromTableName.append(" left join ").append(tableName).append(" on ").append("tmpTable").append(".").append(mainColumn) 34 .append(" = ").append(alias).append(".").append(mainColumn).append(" "); 35 } 36 Iterator<String> column = ColumnNameList.iterator(); 37 while(column.hasNext()){ 38 String columnName = column.next(); 39 selectResult.append(",").append(columnName); 40 } 41 fromTableName.append(" ) a "); 42 selectResult.append(fromTableName); 43 44 createTmpTable.append("create table").append(" tmpTable ").append("as ") 45 .append(tmpUnionSelect).append(" ").append(tmpTableFrom); 46 ciCustomerJDao.executeInBackDataBase(createTmpTable.toString()); 47 48 Createtable.append("create table ").append(newTableName).append(" as SELECT ROW_NUMBER() OVER() as id,* from").append(" (").append(selectResult); 49 ciCustomerJDao.executeInBackDataBase(Createtable.toString());
随后再用此表与前一个日周期生成的表进行case when就OK了。。。有人会想,效率会不会很低呢~ 其实我也在想,但后台为spark集群,应该不会有问题~有问题再调整参数好了~
这里还牵扯到一个问题,就是如果日表的字段更新了,那么最后一步跟前一个日周期的表进行case when不是会报错吗,会报没有那个字段的。 是呀~ 后续要把前一天的字段信息存入mongodb,每次case when时先查出来这些字段信息(前一天的) 然后与今日的的字段进行对比 筛选出共同的,用来拼case when 其他的在生成表的时候直接创建就好。
反正好麻烦啊~ 好痛苦啊~好虐好爽啊~有更好的方法还有待学习呀~