记一次——用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分行。

自己想将其变成正常格式的数据可以参考：

class trace_json_parser(object):
    def evaluate(self, content): 
              // 让字符串变成可以解析的字符串（就是引号替换掉，这里注意转义字符的双引号问题）
        raw_data = content.replace('"[','[').replace(']"',']').replace('\\"','"').replace('\\\"','"')
          
        // 解析
        try:
            js = json.loads(raw_data)  
        except:
            return 'error' 
        
        behavior_track = js['behavior_track'] 
        return str(len(behavior_track))

1.1 从content中得到behavior的内容

select  DISTINCT utdid
                -- 自行开发的函数，用于得到content内容
                , get_json_object_object(a.content, '$.behavior') as (content)
                , ds
from    orginal_table
WHERE   ds = '${date}'
  • 第一步：使用distinct清除上报重复的数据
  • 第二步：用udf函数获得正确格式的content内容

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

要知道，后面将content分行后，数据量会变大，同一个用户会从几条数据变为很多很多条数据。因此这时join其他的表最划算。这里表user_info中有我们感兴趣用户的基本信息，此时用Inner join得到两张表用户的交集。

SELECT      a.ds
          , b.*
          , get_json_object_object(a.content, '$.behavior') as (content)
FROM 
(
    select  DISTINCT utdid, content, ds
    from    orginal_table
    WHERE   ds = '${date}'
) a
inner JOIN 
(
    SELECT  first_utdid as utdid
            , user_id
            , login_nick
            , os
            , os_version
            , app_version
            , device_model
            , resolution
    FROM    user_info --用户信息表  
    WHERE   ds = '${date}'
) b
on a.utdid = b.utdid --使用设备信息做关联

1.3 将behabior数据分行分列

在“源数据表的介绍和分析中”，我们提到数据分为3种类型，我们可知用户上报的行为信息总共只有locate, x, y, behavior, timestamp五种类型。因此便将其化为五列，数据中没有的列则为NULL。例如对于第一类数据，有locate, behavior, timestamp，则x和y则为NULL；同理，第二类数据locate为NULL。

这样一方面可以解决不同类型上报数据内容不统一的情况，另一方面我们确实也想知道第二类数据的locate是什么，为后续填补留个位置。

CREATE TABLE ods_gesture_point_test AS 
SELECT  utdid
        -- 一些用户信息
        , user_id
        , login_nick
        , os
        , os_version
        , app_version
        , device_model
        , resolution
        -- 按解析出来的内容分列
        , get_json_object_object(t1, '$.locate') as locate
        , get_json_object_object(t1, '$.x') as x
        , get_json_object_object(t1, '$.y') as y
        , get_json_object_object(t1, '$.behavior') as behavior
        , get_json_object_object(t1, '$.timestamp') as local_timestamp
        -- 分区信息
        , ds
FROM 
(
    SELECT  ds
            , user_id
            , login_nick
            , os
            , os_version
            , app_version
            , utdid
            , device_model
            , resolution
            , t1
    FROM    
    (
        SELECT  
                a.ds
                , b.*
                , get_json_object_object(a.content, '$.behavior') as (content)
        FROM 
        (
            select  DISTINCT utdid, content, ds
            from    orginal_table
            WHERE   ds = '${date}'
        ) a
        inner JOIN 
        (
            SELECT  utdid
                    , user_id
                    , login_nick
                    , os
                    , os_version
                    , app_version
                    , device_model
                    , resolution
            FROM    user_info --用户信息表  
            WHERE   ds = '${date}'
        ) b
        on a.utdid = b.utdid --使用设备信息做关联
    )
    -- json_array_to_str为自定义udf函数
    LATERAL VIEW EXPLODE(json_array_to_str(content)) t AS t1
) 
;

1.4 结果

这时，应该得到的表格式为：

utdid 该用户其他维度信息（省略） locate x y behavior local_timestamp ds
asdfg ... b1 go 123451 20200101
asdfg ... a1 leave 123451 20200101
asdfg ... 10 8 start 123456 20200101
asdfg ... 11 4 end 123457 20200101
asdfg ... 15 12 tap 123458 20200101
asdfg ... 123460 20200101

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

经过数据探查后，发现3个问题：

  1. 同一数据多次上报；

  2. behavior的数据因为业务原因，变成了locate数据；例如{behavior:a1; timestamp:12370;}；

  3. 数据上报延迟，导致start和end行为直接夹杂了其他数据；例如

    utdid 该用户其他维度信息（省略） locate x y behavior local_timestamp ds
    asdfg ... 10 8 start 123471 20200101
    asdfg ... b1 go 123472 20200101
    asdfg ... a1 leave 123472 20200101
    asdfg ... 11 4 end 123473 20200101

  4. behavior不仅有NULL还有空值

那么我们分别解决：

  1. distinct
  2. 根据业务情况，若长度大于10则为locate, behavior=other
  3. 这个预测与后面的loacte填充操作差不多，一起做会节约资源，因此放到后面操作
  4. where筛选
CREATE TABLE ods_gesture_point_clean AS 
select  DISTINCT  -- 解决问题1
        login_nick
        , user_id
        , os
        , os_version
        , app_version
        , utdid
        , revolution
        , device_model
        , IF(locate is NULL AND LENGTH(behavior) > 10 and x is NULL AND y is NULL, behavior, page) as page --解决问题2
        , x
        , y
        , IF(locate is NULL AND LENGTH(behavior) > 10 and x is NULL AND y is NULL, 'other', behavior) as behavior --解决问题2
        , local_timestamp
        , ds
FROM    ods_gesture_point_test
WHERE   ds = '${date}'
                -- 解决问题4
        and behavior IS NOT NULL 
        and behavior <> ''
;

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

这里我们需要将一个操作中的2行变成1行，并将locate信息填充到操作中去。

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

因为编号这个操作较耗时，单独建立一张表。

CREATE TABLE dwd_gesture_point_order_test AS 
SELECT  *
        , ROW_NUMBER() OVER(PARTITION BY login_nick ORDER BY local_timestamp) AS row_num
FROM    ods_gesture_point_clean_test
order   by login_nick, local_timestamp

得到：

utdid 该用户其他维度信息（省略） locate x y behavior local_timestamp row_num ds
asdfg ... b1 go 123451 1 20200101
asdfg ... a1 leave 123451 2 20200101
asdfg ... 10 8 start 123456 3 20200101
asdfg ... 11 4 end 123457 4 20200101
asdfg ... 15 12 tap 123458 5 20200101
asdfg ... 123460 6 20200101

3.2 多条数据合并为一条

我们希望将如下数据格式：

utdid 该用户其他维度信息（省略） locate x y behavior local_timestamp ds

变为：

utdid 该用户其他维度信息（省略） go leave x_beg x_end y_beg y_end behavior timestamp Ds

这样做一方面可以使数据表示更加简洁，一条数据包含了更多信息；另一方面方便数据查找与分类汇总；

3.2.1 第一类数据二条合一条

首先我们处理第一类数据，原因有二：一方面，后续的信息填充需要使用到第一类数据，而先将其进行合并，则可以在后续处理中省去很多功夫；另一方面，由于时间戳相同，处理起来相对比第二类数据较简单。

对于第一类数据而言，同一时间上报两条数据，这里我们把其合成一条；

utdid 该用户其他维度信息（省略） locate x y behavior local_timestamp ds
asdfg ... b1 go 123451 20200101
asdfg ... a1 leave 123451 20200101

这里为了后续方便处理，x和y拓展成了x_beg，x_end，y_beg，y_end，当然，这里的值都是null

SELECT  a.login_nick
        , a.user_id
        , a.os
        , a.os_version
        , a.app_version
        , a.utdid
        , a.resolution
        , a.device_model
        , a.locate as go_locate --现地点
        , b.locate as leave_locate --上一个地点
        , a.x as x_beg
        , a.x as x_end
        , a.y as y_beg
        , a.y as y_end
        , a.behavior
        , a.local_timestamp
        , a.row_num
        , a.ds
FROM 
(
    SELECT  *
    FROM    ods_gesture_point_order_test
    WHERE   page is not null AND behavior IN ('go', 'other' )
)a
LEFT JOIN 
(
    SELECT  *
    FROM    ods_gesture_point_order_test
    WHERE   page is not null AND behavior = 'leave'
)b
on a.ds = b.ds and a.utdid = b.utdid AND a.local_timestamp = b.local_timestamp
;

结果如下：

utdid 该用户其他维度信息（省略） go_locate leave_locate x_beg x_end y_beg y_end behavior timestamp Ds
asdfg ... b1 a1 go 123451 20200101

3.2.2 第二类数据二条合一条

对于第二类数据，时间戳不同，因此需要用到row_num；除此之外，存在数据上报延迟的情况；

在本步骤，我们暂时不将其填充locate信息，但是为了保持格式相同，因此让go=locate, leave=locate，其实为NULL；

同理，我们也由易到难介绍。

1. 对于位置不变的数据

这类数据最简单，在同一时间戳的位置不变，因此x_beg=x, x_end=x , y_beg=y, y_end=y；

create table t1 as
select  login_nick
        , user_id
        , os
        , os_version
        , app_version
        , utdid
        , resolution
        , device_model
        -- locate为NULL
        , locate as go_locate
        , locate as leave_locate
        -- 位置不变
        , x as x_beg
        , x as x_end
        , y as y_beg
        , y as y_end
        , behavior
        , local_timestamp
        , row_num
        , ds
FROM    ods_gesture_point_order_test
WHERE   page is null AND behavior = 'tap'
2. 对于位置改变的数据

在这一步我们发现了2个问题：

  1. behavior=start和behavior=end两条数据对应的x和y相同，这时我们将其归类为一个新的behavior=click；

  2. 由于sql在表与表连接中不可以使用不等式和或，只能用“=”；否则我们可以使用如下语句做来解决数据延迟上报的问题。就是因为下列语句不可行，因此我们要对其进行特殊的清洗。

    -- 逻辑如下，但是这条语句是不可运行的
select   ...
from     xxx as a
left join xxx as b
on           (a.row_num = (b.row_num - 1)) -- 相邻两行
             OR 
             (a.row_num = (b.row_num - 3) --要么中间有2行间隔
         and 
         a.row_num <> (b.row_num - 1) -- 排除start,end,start,end的情况，这时也会有2行间隔
        )

因此，我们先对相邻两行数据进行合并。这里，我们以（1）behavior=start的x和y分别作为x_beg和y_beg，behavior=end的x和y分别作为x_end和y_end；（2）按照行号做left join。除此之外，（3）local_timestamp和row_num取b表（即behavior = 'end'的数据），原因为当数据延迟时（accb），做left join后，由于a找不到符合条件的b，则x_end，y_end，local_timestamp，row_num均为NULL，后续用WHERE语句排除这条数据，以免又产生无效数据。

create table t2 as
SELECT  a.login_nick
        , a.user_id
        , a.os
        , a.os_version
        , a.app_version
        , a.utdid
        , a.resolution
        , a.device_model
        , a.locate as go_locate
        , a.locate as leave_locate
        , a.x as x_beg
        , b.x as x_end
        , a.y as y_beg
        , b.y as y_end
        , if(a.x = b.x AND a.y = b.y, 'click', 'move') as behavior
        , b.local_timestamp
        , b.row_num
        , a.ds
FROM 
(
    SELECT  *
    FROM    ods_gesture_point_order_test_1
    WHERE   locate is null AND behavior = 'start'
)a
LEFT JOIN 
(
    SELECT  *
    FROM    ods_gesture_point_order_test_1
    WHERE   locate is null AND behavior = 'end'
)b
on a.ds = b.ds AND a.utdid = b.utdid AND (a.row_num = (b.row_num - 1)) --相邻两行
WHERE b.local_timestamp is NOT NULL
3. 数据上报延迟解决方案

如第2章，可知数据延迟的情况如下；经过统计后，其一般中间间隔2行。

utdid 该用户其他维度信息（省略） locate x y behavior local_timestamp ds
asdfg ... 10 8 start 123471 20200101
asdfg ... b1 go 123472 20200101
asdfg ... a1 leave 123472 20200101
asdfg ... 11 4 end 123473 20200101

这里又出现一个问题，如何排除start,end,start,end的情况？他们的第1个start和第2个end也相差两行，同时on后也不能写不等式。对于这个问题，只好使用where后嵌套子查询办法了。

create table t3 as
SELECT  a.login_nick
        , a.user_id
        , a.os
        , a.os_version
        , a.app_version
        , a.utdid
        , a.resolution
        , a.device_model
        , a.locate as go_locate
        , a.locate as leave_locate
        , a.x as x_beg
        , b.x as x_end
        , a.y as y_beg
        , b.y as y_end
        , if(a.x = b.x AND a.y = b.y, 'click', 'swipe') as behavior
        , b.local_timestamp
        , b.row_num
        , a.ds
FROM 
(
    SELECT  *
    FROM    ods_gesture_point_order_test
    WHERE   locate is null AND behavior = 'begin'
                    -- 排除start,end,start,end的情况
            and (row_num + 1) NOT in (SELECT row_num FROM ods_gesture_point_order_test WHERE  locate is null AND behavior = 'end')
)a
LEFT JOIN 
(
    SELECT  *
    FROM    ods_gesture_point_order_test
    WHERE   locate is null AND behavior = 'end'
)b
on a.ds = b.ds AND a.utdid = b.utdid AND (a.row_num = (b.row_num - 3)) --行数相差3
4. 代码合并和结果

最终将1、2、3的代码使用union合并起来

CREATE TABLE ods_gesture_point_behavior_test AS
t1
union
t2
union
t3

结果：

utdid 该用户其他维度信息（省略） go_locate leave_locate x_beg x_end y_beg y_end behavior timestamp ds
asdfg ... 1 2 1 2 click 123449 20200101
asdfg ... 10 8 11 4 move 123456 20200101
asdfg ... 15 12 15 12 tap 123468 20200101

3.3 locate信息填充

对于每个操作，我们想知道时间小于它（row_num小于它）且离它最近的loacte（小于它的row_num的最大值）。即对每行数据找寻在row_num小于它的最大的最小值。

实现步骤

  1. 获得其小于该行的locate的row_num；
  2. 求出得到row_num的最大值；
  3. 根据x和y的变化情况，丰富behavior的形式；
  4. 与合并，得到完整的用户行为数据。
CREATE TABLE dwd_gesture_point_ans_test AS 
SELECT * FROM ods_gesture_point_page_test

UNION  --步骤4

SELECT login_nick
        , user_id
        , os
        , os_version
        , app_version
        , utdid
        , resolution
        , device_model
        , go_locate
        , leave_locate
        , x_beg
        , x_end
        , y_beg
        , y_end
        -- 步骤3
        -- 后期可用behavior like '%move%'来筛选
        , CASE WHEN (((ABS(x_beg - x_end) > ABS(y_beg - y_end))) and ((x_beg - x_end) > 0)) THEN 'leftMove'
               WHEN (((ABS(x_beg - x_end) > ABS(y_beg - y_end))) and ((x_beg - x_end) < 0)) THEN 'rightMove'
               WHEN (((ABS(x_beg - x_end) < ABS(y_beg - y_end))) and ((y_beg - y_end) > 0)) THEN 'upMove'
               WHEN (((ABS(x_beg - x_end) < ABS(y_beg - y_end))) and ((y_beg - y_end) < 0)) THEN 'downMove'
            END AS behavior
        , local_timestamp
        , row_num
        , ds      
from
(
    SELECT  login_nick
                , user_id
            , os
            , os_version
            , app_version
            , utdid
            , resolution
            , device_model
            , go_locate
            , leave_locate
            , x_beg
            , x_end
            , y_beg
            , y_end
            , behavior
            , local_timestamp
            , row_num
            , locate_num
            , ds 
            , MAX(locate_num) OVER (PARTITION BY utdid, x_beg, x_end, y_beg, y_end, behavior, local_timestamp, row_num) as max_locate_num --步骤2
    FROM 
    (
        SELECT  a.go_locate
                , a.leave_locate
                , a.row_num as locate_num
                , b.login_nick
                , b.user_id
                , b.os
                , b.os_version
                , b.app_version
                , b.utdid
                , b.resolution
                , b.device_model
                , b.x_beg
                , b.x_end
                , b.y_beg
                , b.y_end
                , b.behavior
                , b.local_timestamp
                , b.row_num
                , b.ds 
        FROM  
        (
            SELECT  *
            FROM    ods_gesture_point_behavior_test
        ) b
        LEFT JOIN 
        (
            SELECT  utdid
                    , go_locate
                    , leave_locate
                    , row_num
                    , ds
            FROM    ods_gesture_point_page_test
        ) a
        on  a.ds = b.ds and a.utdid = b.utdid
        WHERE a.row_num < b.row_num --步骤1
    )
    having locate_num = max_locate_num --步骤2
)
;

3.4 结果

这样就生成了需求的结果：

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

4 步骤四：生成算法格式

最后，根据算法同学的要求，再生成他需求的格式。这一步可根据不同需求进行改变。

要求：每天，分locate，将数据聚合起来并写成一条字符串。字符串包括utdid，坐标，位置，时间戳；

分析：他所需的数据格式大概如下

ds locate info
20200101 a1 user1:behavior,x_beg,x,end,y_beg,y_end,timestamp;behavior,x_beg,x,end,y_beg,y_end,timestamp;user2:...
20200101 a2 ...
20200202 a1 ...

步骤：

  1. 首先将behavior,x_beg,x,end,y_beg,y_end,timestamp用“,”隔开做成一条字符串，这样一个用户就会有多条包含行为信息的字符串str1=”behavior,x_beg,x,end,y_beg,y_end,timestamp“；
  2. 将该用户的这些字符串用“;”拼接成新的字符串str2="str1;str1;..."；
  3. 将用户名和str2用":"连接起来，变格式为“用户名:str2”的字符串str3；
  4. 最后将不同用户之间的str3使用";;"拼接为info="str3;;str3;;....."
CREATE TABLE dws_gesture_point_sf_test AS
SELECT page, wm_concat(';;',info) as info -- 步骤4
FROM 
(
    SELECT ds, locate, CONCAT_WS(':', utdid, info) OVER (PARTITION BY ds, locate, ORDER BY row_num) as info -- 步骤3
    FROM 
    (
        SELECT locate, utdid, wm_concat(';',info) as info -- 步骤2
        FROM 
        (
            SELECT ds, locate, utdid, CONCAT_WS(',',behavior,x_beg,x_beg,y_beg,y_end,loca_timestamp,row_num) as info -- 步骤1
            FROM 
            (
                SELECT  ds, locate, utdid, behavior, x_beg, x_beg, y_beg, y_end, loca_timestamp, row_num
                FROM    dwd_gesture_point_ans_test
                WHERE   behavior <> 'locate' and behavior <> 'other'
            )
        )
        GROUP BY ds, page, utdid
    )
)
GROUP BY page
;

5 完成，撒花！

数据清洗是一个探索的过程，先有一个大的方向，然后走一步看一步，每次可能会发现新的问题需要处理。

但是这次比较顺利，没有进行任何返工。

不过暂时还没做性能优化，后续要研究并完成。

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 202,802评论 5赞 476
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 85,109评论 2赞 379
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 149,683评论 0赞 335
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 54,458评论 1赞 273
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 63,452评论 5赞 364
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 48,505评论 1赞 281
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 37,901评论 3赞 395
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 36,550评论 0赞 256
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 40,763评论 1赞 296
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 35,556评论 2赞 319
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 37,629评论 1赞 329
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 33,330评论 4赞 318
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 38,898评论 3赞 307
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,897评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 31,140评论 1赞 259
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 42,807评论 2赞 349
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 42,339评论 2赞 342

推荐阅读更多精彩内容