前一天数据偏移至当日数据

客户画像项目中可能会遇到此类问题，因为客户画像要求的是对单个用户信息的查询。 但倘若以日周期为准，今日梳理的数据宽表中某个字段为null,但此类字段不能以null显示，要以前一天有数据的值为今日的值，就需要做case when处理。 同时还有个问题，日表拥有某些客户信息（以日更新)，但并不是用户全量表，而月表为用户全量表（拥有历史月的所有用户数），日表可能会增加也可能会减少数据。这样 不管你left join谁 都无法得到全量的用户数据。 使用以下方法便可实现:

方法1：

FULL OUTER JOIN 关键字返回左表（Customers）和右表（Orders）中所有的行。如果 "table1" 表中的行没有匹配或者 "table2" 表中的行在 "table1" 表中没有匹配，也会列出这些行。

 select case when a.product_no is null then b.product_no else a.product_no end product_no,a.l2_01_01_04,b.l2_01_01_04_05 from table1 a full outer join table2 b on a.product_no = b.product_no;

使用full outer join 并对客户的信息字段进行case when 为空判断，如果为空则取前一天的表中数据.

 

方法2：

日表和月表中都有同一个product_no字段，那么可以首先union一下product_no,然后创建只有product_no的中间表（有重复可以distinct或者group by一下)

create table table3 as select product_no from (select product_no from table1 union all select product_no from table2) a group by product_no;

 

随后用此中间表left join 日表 left join 月表 便可得到全量的用户数据了~

select tmpTable.product_no,t_3000.l2_01_01_04,t_3842.L1_01_02_01 from tmpTable  left join mongodb_201406 t_3000 on tmpTable.product_no = t_3000.product_no  left join DW_COCLBL_M01_201406 t_3842 on tmpTable.product_no = t_3842.product_no  ) a

 

随后呢~ 再为此表创建带自增列的最终表，方便后续份页读取数据并进行数据转换~代码如下:(用代码拼sql很痛苦，因为牵扯到别名问题呀~)

StringBuffer createTmpTable = new StringBuffer();
                StringBuffer tmpUnionSelect = new StringBuffer();
                StringBuffer tmpTableFrom = new StringBuffer();
                for(int i=0;i<ColumnNames.size();i++){
                    SiCustomerLabelInfoModel Column = ColumnNames.get(i);
                    List<CiMdaSysTableModel> ciMdaSysTable = ciCustomerJDao.getMdaSysTableName(Column.getColumnName());
                    String alias = "t_" + ciMdaSysTable.get(0).getTableId();
                    String aliasColumn = alias + "." + Column.getColumnName();
                    if(ciMdaSysTable.get(0).getUpdateCycle() == 1){
                        aliasTable = ciMdaSysTable.get(0).getTableName() +"_"+ dayDate + " " + alias;
                    }else{
                        aliasTable = ciMdaSysTable.get(0).getTableName() + "_" + monthDate + " "+ alias;
                    }
                    if(ciMdaSysTable.get(0).getUpdateCycle() == 2){
                        mainTable = aliasTable;
                    }
                    ColumnNameList.add(aliasColumn);
                    tableNameList.add(aliasTable);
                }
                tmpUnionSelect.append("select ").append(mainColumn).append(" from (").append(" select ").append(mainColumn).append(" from ").append(mainTable);
                selectResult.append("select ").append("tmpTable.").append(mainColumn);
                fromTableName.append(" from tmpTable ");
                Iterator<String> table = tableNameList.iterator();
                while(table.hasNext()){
                    String tableName = table.next();
                    String[] tableAlias = tableName.split(" ");
                    String alias = tableAlias[1];
                    
                    if(!mainTable.equals(tableName)){
                        tmpTableFrom.append("union all select ").append(mainColumn).append(" from ").append(tableName).append(") a")
                        .append(" group by ").append(mainColumn);
                    }
                    fromTableName.append(" left join ").append(tableName).append(" on ").append("tmpTable").append(".").append(mainColumn)
                    .append(" = ").append(alias).append(".").append(mainColumn).append(" ");
                }
                Iterator<String> column = ColumnNameList.iterator();
                while(column.hasNext()){
                    String columnName = column.next();
                    selectResult.append(",").append(columnName);
                }
                fromTableName.append(" ) a ");
                selectResult.append(fromTableName);

                createTmpTable.append("create table").append(" tmpTable ").append("as ")
                .append(tmpUnionSelect).append(" ").append(tmpTableFrom);
                ciCustomerJDao.executeInBackDataBase(createTmpTable.toString());
                
                Createtable.append("create table ").append(newTableName).append(" as SELECT ROW_NUMBER() OVER() as id,* from").append(" (").append(selectResult);
                ciCustomerJDao.executeInBackDataBase(Createtable.toString());

随后再用此表与前一个日周期生成的表进行case when就OK了。。。有人会想，效率会不会很低呢~ 其实我也在想，但后台为spark集群，应该不会有问题~有问题再调整参数好了~

这里还牵扯到一个问题，就是如果日表的字段更新了，那么最后一步跟前一个日周期的表进行case when不是会报错吗，会报没有那个字段的。 是呀~ 后续要把前一天的字段信息存入mongodb,每次case when时先查出来这些字段信息（前一天的） 然后与今日的的字段进行对比 筛选出共同的，用来拼case when 其他的在生成表的时候直接创建就好。 

反正好麻烦啊~ 好痛苦啊~好虐好爽啊~有更好的方法还有待学习呀～