记一次——用sql进行数据清洗实例

数据库中有某段用户上报日志，记录着用户的一些操作行为。现要将其清洗后，并筛选出我们感兴趣的用户后，并整理为算法要求的格式。要求过程中只能用sql。

0 引言

源数据表介绍和分析

数据规模：每天2亿+条数据；
含义：每人每天登陆一次app则产生一条记录；
原始数据表字段如下所示：
Original_table

utdid	user_id	content	ds
zxcvb		{userid:;behavior:...}	20200101
asdfg	user2	{userid:user2;behavior:...}	20200101
asdfg	user2	{userid:user2;behavior:...}	20200101

• utdid(string):设备id

  • 全不为空，后期可以用它来join其他表提取感兴趣用户。

• user_id(string):用户id

  • 经过观察，发现有些为空，可能是一些未注册用户，但是我们有utdid已经足够了。

• content(string):是一个字符串，记录着用户的行为，看上去比较像json。经探查，发现有重复数据，因此后续需要去除。

  • 最外层：{userid:user3;behavior:...} 。可以看到这里是一个map，仅有两个键user_id和behavior；我们对于userid不感兴趣，因为已经被提取为列user_id了。因此我们只对behavior感兴趣，里面记录着用户的操作行为。

  • behavior内层：

    "[{'locate':b1;'behavior':go;'timestamp':123451},

    {'locate':a1;'behavior':leave;'timestamp':123451},

    {'x':10;'y':8;'behavior':start;'timestamp':123456},

    {'x':11;'y':4;'behavior':end;'timestamp':123457},

    {'x':15;'y':12;'behavior':tap;'timestamp':123458},

    {'behavior':begin;'timestamp':123460}...]"

    • 可以发现内层记录用户按时间排序后的行为；
    • 看上去像是一个array，每个元素（一条数据）为一个行为，用map表示的，map里面则有行为的参数。
    • 但是很奇怪的是有很多双引号单引号，检测出来的数据类型也是字符串不是数组。
    • 总共有3种类型的数据：
      • 第一种类型为第1~2行带locate的，是位置记录数据，他们的时间戳相同。表示在某个地点，go表示到达某处，leave表达离开某处；可以发现他们的timestamp的值是一样的，因此表示用户在离开某处则立刻到达某处，在这个时间戳同时上报两条数据，于是我们就得到了用户在该时间从哪里来到哪里去的信息。
      • 第二种类型是3～5行带x和y的，是行为记录数据，他们的时间戳不同，但是在相邻两行。x和y为坐标。start表示动作开始，end表示动作结束；tap表示动作开始即结束。这些数据都是在第1行的locate=a1的位置进行的，也就是说在下一次遇到第一种类型的数据前，这些行为数据都在a1处发生。
      • 第三种类型是behavior为未知，只有时间戳的第6行数据；这可以视为用户在这个时间点没有任何行为，后台仅仅在这个时间点上报一条信息而已。因此后续需要清除。

• ds(string):日期，格式为yyyymmdd

问题分析和结果预想

经过以上分析，我们发现本身content并不易读，因此我们希望将其变为易读模式。

那么我们大致可以有以下分析思路：（1）首先应将content数据解析出来，让其分行、分列；（2）有了最粗糙的原始数据后，那么应该进行一些简单的空值、异常值、重复值的清除；（3）得到清洗后的数据之后，我们发现用户的行为会上报两条数据，那么可能需要对其进行一个合并，让数据的展现形式更为简洁；（4）没有locate的操作信息是无用的，我们无法对其进行任何判断，因此还需要将其的locate信息补充上去；（5）除此之外，我们的用户还需要筛选，并且可能还需要加入其他的维度信息。

因此，根据以上分析，预想结果如下：

utdid	该用户其他维度信息（省略）	go_locate	leave_locate	x_beg	x_end	y_beg	y_end	behavior	timestamp	Ds
asdfg	...	b1	a1					go	123451	20200101
asdfg	...	b1	a1	10	8	11	4	move	123456	20200101
asdfg	...	b1	z1	15	12	15	12	tap	123468	20200101

其中第一条代表了第1～2行数据，表达去到了新位置，behavior=go；第二条代表了第3～4行数据，表达在某位置进行了移动，behavior=move；第三条代表了第5行数据，表示在某位置原地行动，behavior=tap；第6行数据不要。

这样就可以清晰的看到用户在某个时间点(when)在哪里(where)做了什么动作(what)都可以一目了然。也方便后续进行其他整合。

解决方案以及所涉及的知识点

上文分析会给我们一个大致方向，但是过程中可能还会碰到许多问题，以及还需考虑如何写使性能较好。因此根据分析，重新安排我们的解决方案如下：

1. 步骤一：解析content内容并筛选用户

   1. 从content中得到behavior内容

   2. 筛选感兴趣用户以及获得用户的其他信息

   3. 将behabior数据分行分列

2. 步骤二：剔除重复值、异常值、未知值

3. 步骤三：数据合并与信息填充

   1. 给信息按时间顺序进行编号

   2. 多条数据合并为一条

   3. locate信息填充

4. 步骤四：生成算法格式

这些解决步骤中所涉及知识点如下：

1. 将json数据分行分列【1】

2. 三值逻辑的坑【2】【更多知识：链接进入搜索“三值逻辑”】

3. 重复值剔除【2】【更多知识：链接进入搜索“重复值”】

4. 如何使用分区函数获得数据的行号【3.1】

5. 如何使用做连接将两行数据并为一行【3.2.1】

6. 如何解决on后不能跟“<>”和“or”【3.2.2】

7. 如何找到排序后每行小于它的某字段的最大的最小值【3.2.2】

8. 如何获得数据清洗思路【通读全文并体会】

9. 隐藏：如何设计数仓架构【通读全文找彩蛋】

1 步骤一：解析content内容并筛选用户

注：这里，每张临时表都应是一个dag节点，且用ds分区。

如上文分析，content应是一个map{array[map1{}, map2{}....]}的格式，直接解析即可。但是之前说了，这条语句别识别是字符串，且有乱七八糟的引号，不能直接解析。这时候应去找对应的表开发人员。与其自己分析引号有什么规律，还不如直接问表开发人员数据是怎么合成的，解铃还须系铃人。这样一定可以找到准确的可逆操作，少采很多坑。很好的事，表开发人员直接给我了对应数据解析udf函数（说明他们开发时就已经想到解析的问题），可以直接将content分行。

自己想将其变成正常