數(shù)據庫中有某段用戶上報日志洋侨，記錄著用戶的一些操作行為。現(xiàn)要將其清洗后坦敌，并篩選出我們感興趣的用戶后侣诵，并整理為算法要求的格式。要求過程中只能用sql狱窘。

0 引言

源數(shù)據表介紹和分析

數(shù)據規(guī)模：每天2億+條數(shù)據杜顺；
含義：每人每天登陸一次app則產生一條記錄；
原始數(shù)據表字段如下所示：
Original_table

• utdid(string):設備id

  • 全不為空蘸炸，后期可以用它來join其他表提取感興趣用戶躬络。

• user_id(string):用戶id

  • 經過觀察，發(fā)現(xiàn)有些為空搭儒，可能是一些未注冊用戶穷当，但是我們有utdid已經足夠了。

• content(string):是一個字符串淹禾，記錄著用戶的行為馁菜，看上去比較像json。經探查铃岔，發(fā)現(xiàn)有重復數(shù)據火邓，因此后續(xù)需要去除。

  • 最外層：{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}...]"

    • 可以發(fā)現(xiàn)內層記錄用戶按時間排序后的行為；
    • 看上去像是一個array恐仑，每個元素（一條數(shù)據）為一個行為泉坐，用map表示的，map里面則有行為的參數(shù)裳仆。
    • 但是很奇怪的是有很多雙引號單引號腕让，檢測出來的數(shù)據類型也是字符串不是數(shù)組。
    • 總共有3種類型的數(shù)據：
      • 第一種類型為第1~2行帶locate的，是位置記錄數(shù)據纯丸，他們的時間戳相同偏形。表示在某個地點，go表示到達某處觉鼻，leave表達離開某處俊扭；可以發(fā)現(xiàn)他們的timestamp的值是一樣的，因此表示用戶在離開某處則立刻到達某處坠陈，在這個時間戳同時上報兩條數(shù)據萨惑，于是我們就得到了用戶在該時間從哪里來到哪里去的信息。
      • 第二種類型是3～5行帶x和y的仇矾，是行為記錄數(shù)據庸蔼，他們的時間戳不同，但是在相鄰兩行若未。x和y為坐標朱嘴。start表示動作開始倾鲫，end表示動作結束粗合；tap表示動作開始即結束。這些數(shù)據都是在第1行的locate=a1的位置進行的乌昔，也就是說在下一次遇到第一種類型的數(shù)據前隙疚，這些行為數(shù)據都在a1處發(fā)生。
      • 第三種類型是behavior為未知磕道，只有時間戳的第6行數(shù)據供屉；這可以視為用戶在這個時間點沒有任何行為，后臺僅僅在這個時間點上報一條信息而已溺蕉。因此后續(xù)需要清除伶丐。

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

問題分析和結果預想

經過以上分析疯特，我們發(fā)現(xiàn)本身content并不易讀哗魂，因此我們希望將其變?yōu)橐鬃x模式。

那么我們大致可以有以下分析思路：（1）首先應將content數(shù)據解析出來漓雅，讓其分行录别、分列；（2）有了最粗糙的原始數(shù)據后邻吞，那么應該進行一些簡單的空值组题、異常值、重復值的清除抱冷；（3）得到清洗后的數(shù)據之后崔列，我們發(fā)現(xiàn)用戶的行為會上報兩條數(shù)據，那么可能需要對其進行一個合并旺遮，讓數(shù)據的展現(xiàn)形式更為簡潔峻呕；（4）沒有l(wèi)ocate的操作信息是無用的利职，我們無法對其進行任何判斷，因此還需要將其的locate信息補充上去瘦癌；（5）除此之外猪贪，我們的用戶還需要篩選，并且可能還需要加入其他的維度信息讯私。

因此热押，根據以上分析，預想結果如下：

其中第一條代表了第1～2行數(shù)據斤寇，表達去到了新位置桶癣，behavior=go；第二條代表了第3～4行數(shù)據娘锁，表達在某位置進行了移動牙寞，behavior=move；第三條代表了第5行數(shù)據莫秆，表示在某位置原地行動间雀，behavior=tap；第6行數(shù)據不要镊屎。

這樣就可以清晰的看到用戶在某個時間點(when)在哪里(where)做了什么動作(what)都可以一目了然惹挟。也方便后續(xù)進行其他整合。

解決方案以及所涉及的知識點

上文分析會給我們一個大致方向缝驳，但是過程中可能還會碰到許多問題连锯，以及還需考慮如何寫使性能較好。因此根據分析用狱，重新安排我們的解決方案如下：

1. 步驟一：解析content內容并篩選用戶

   1. 從content中得到behavior內容

   2. 篩選感興趣用戶以及獲得用戶的其他信息

   3. 將behabior數(shù)據分行分列

2. 步驟二：剔除重復值运怖、異常值、未知值

3. 步驟三：數(shù)據合并與信息填充

   1. 給信息按時間順序進行編號

   2. 多條數(shù)據合并為一條

   3. locate信息填充

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

這些解決步驟中所涉及知識點如下：

1. 將json數(shù)據分行分列【1】

2. 三值邏輯的坑【2】【更多知識：鏈接進入搜索“三值邏輯”】

3. 重復值剔除【2】【更多知識：鏈接進入搜索“重復值”】

4. 如何使用分區(qū)函數(shù)獲得數(shù)據的行號【3.1】

5. 如何使用做連接將兩行數(shù)據并為一行【3.2.1】

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

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

8. 如何獲得數(shù)據清洗思路【通讀全文并體會】

9. 隱藏：如何設計數(shù)倉架構【通讀全文找彩蛋】

1 步驟一：解析content內容并篩選用戶

注：這里夏伊，每張臨時表都應是一個dag節(jié)點摇展，且用ds分區(qū)。

如上文分析署海，content應是一個map{array[map1{}, map2{}....]}的格式吗购，直接解析即可。但是之前說了砸狞，這條語句別識別是字符串沙庐，且有亂七八糟的引號秒梅，不能直接解析疑故。這時候應去找對應的表開發(fā)人員隅要。與其自己分析引號有什么規(guī)律，還不如直接問表開發(fā)人員數(shù)據是怎么合成的，解鈴還須系鈴人埠偿。這樣一定可以找到準確的可逆操作透罢，少采很多坑。很好的事冠蒋，表開發(fā)人員直接給我了對應數(shù)據解析udf函數(shù)（說明他們開發(fā)時就已經想到解析的問題）羽圃，可以直接將content分行。

自己想將其變成正常格式的數(shù)據可以參考：

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
                -- 自行開發(fā)的函數(shù)朽寞，用于得到content內容
                , get_json_object_object(a.content, '$.behavior') as (content)
                , ds
from    orginal_table
WHERE   ds = '${date}'

• 第一步：使用distinct清除上報重復的數(shù)據
• 第二步：用udf函數(shù)獲得正確格式的content內容

1.2 篩選感興趣用戶以及獲得用戶的其他信息

要知道，后面將content分行后斩郎，數(shù)據量會變大脑融，同一個用戶會從幾條數(shù)據變?yōu)楹芏嗪芏鄺l數(shù)據。因此這時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 --使用設備信息做關聯(lián)

1.3 將behabior數(shù)據分行分列

在“源數(shù)據表的介紹和分析中”锻煌，我們提到數(shù)據分為3種類型妓布，我們可知用戶上報的行為信息總共只有l(wèi)ocate, x, y, behavior, timestamp五種類型。因此便將其化為五列炼幔，數(shù)據中沒有的列則為NULL秋茫。例如對于第一類數(shù)據史简，有l(wèi)ocate, behavior, timestamp乃秀，則x和y則為NULL圆兵；同理，第二類數(shù)據locate為NULL刀脏。

這樣一方面可以解決不同類型上報數(shù)據內容不統(tǒng)一的情況，另一方面我們確實也想知道第二類數(shù)據的locate是什么超凳，為后續(xù)填補留個位置轮傍。

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
        -- 分區(qū)信息
        , 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 --使用設備信息做關聯(lián)
    )
    -- json_array_to_str為自定義udf函數(shù)
    LATERAL VIEW EXPLODE(json_array_to_str(content)) t AS t1
) 
;

1.4 結果

這時暂雹，應該得到的表格式為：

2 步驟二：剔除重復值创夜、異常值、未知值

經過數(shù)據探查后涧尿，發(fā)現(xiàn)3個問題：

1. 同一數(shù)據多次上報姑廉；

2. behavior的數(shù)據因為業(yè)務原因，變成了locate數(shù)據瞬内；例如{behavior:a1; timestamp:12370;}虫蝶；

3. 數(shù)據上報延遲能真，導致start和end行為直接夾雜了其他數(shù)據扰柠；例如

   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. 根據業(yè)務情況卤档，若長度大于10則為locate, behavior=other
3. 這個預測與后面的loacte填充操作差不多劝枣，一起做會節(jié)約資源舔腾，因此放到后面操作
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 步驟三：數(shù)據合并與信息填充

這里我們需要將一個操作中的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

得到：

3.2 多條數(shù)據合并為一條

我們希望將如下數(shù)據格式：

變?yōu)椋?/p>

這樣做一方面可以使數(shù)據表示更加簡潔，一條數(shù)據包含了更多信息肢娘；另一方面方便數(shù)據查找與分類匯總橱健；

3.2.1 第一類數(shù)據二條合一條

首先我們處理第一類數(shù)據拘荡，原因有二：一方面珊皿，后續(xù)的信息填充需要使用到第一類數(shù)據蟋定，而先將其進行合并，則可以在后續(xù)處理中省去很多功夫扼仲；另一方面屠凶，由于時間戳相同矗愧，處理起來相對比第二類數(shù)據較簡單唉韭。

對于第一類數(shù)據而言纽哥，同一時間上報兩條數(shù)據栖秕，這里我們把其合成一條簇捍；

這里為了后續(xù)方便處理暑塑，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 --現(xiàn)地點
        , 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
;

結果如下：

3.2.2 第二類數(shù)據二條合一條

對于第二類數(shù)據伺绽，時間戳不同憔恳，因此需要用到row_num钥组；除此之外程梦，存在數(shù)據上報延遲的情況屿附；

在本步驟挺份，我們暫時不將其填充locate信息匀泊，但是為了保持格式相同，因此讓go=locate, leave=locate揣非，其實為NULL早敬；

同理搞监，我們也由易到難介紹腺逛。

1. 對于位置不變的數(shù)據

這類數(shù)據最簡單棍矛，在同一時間戳的位置不變够委，因此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. 對于位置改變的數(shù)據

在這一步我們發(fā)現(xiàn)了2個問題：

1. behavior=start和behavior=end兩條數(shù)據對應的x和y相同潘拨，這時我們將其歸類為一個新的behavior=click铁追；

2. 由于sql在表與表連接中不可以使用不等式和或琅束，只能用“=”涩禀；否則我們可以使用如下語句做來解決數(shù)據延遲上報的問題艾船。就是因為下列語句不可行丽声，因此我們要對其進行特殊的清洗雁社。

   -- 邏輯如下霉撵，但是這條語句是不可運行的
   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行間隔
           )
   

因此喇完，我們先對相鄰兩行數(shù)據進行合并锦溪。這里刻诊，我們以（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'的數(shù)據）易迹，原因為當數(shù)據延遲時（accb）睹欲，做left join后窘疮，由于a找不到符合條件的b闸衫，則x_end蔚出，y_end，local_timestamp悦冀，row_num均為NULL盒蟆，后續(xù)用WHERE語句排除這條數(shù)據历等，以免又產生無效數(shù)據募闲。

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. 數(shù)據上報延遲解決方案

如第2章，可知數(shù)據延遲的情況如下要出；經過統(tǒng)計后患蹂，其一般中間間隔2行传于。

這里又出現(xiàn)一個問題沼溜，如何排除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)) --行數(shù)相差3

4. 代碼合并和結果

最終將1帕涌、2蚓曼、3的代碼使用union合并起來

CREATE TABLE ods_gesture_point_behavior_test AS
t1
union
t2
union
t3

結果：

3.3 locate信息填充

對于每個操作，我們想知道時間小于它（row_num小于它）且離它最近的loacte（小于它的row_num的最大值）其弊。即對每行數(shù)據找尋在row_num小于它的最大的最小值梭伐。

實現(xiàn)步驟

1. 獲得其小于該行的locate的row_num；
2. 求出得到row_num的最大值摔蓝；
3. 根據x和y的變化情況拌滋，豐富behavior的形式鸠真；
4. 與合并吠卷，得到完整的用戶行為數(shù)據祭隔。

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 結果

這樣就生成了需求的結果：

4 步驟四：生成算法格式

最后搞坝，根據算法同學的要求桩撮，再生成他需求的格式。這一步可根據不同需求進行改變芜果。

要求：每天，分locate舀射，將數(shù)據聚合起來并寫成一條字符串后控。字符串包括utdid浩淘，坐標张抄，位置洼怔，時間戳署惯；

分析：他所需的數(shù)據格式大概如下

步驟：

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 完成，撒花域那！

數(shù)據清洗是一個探索的過程咙边，先有一個大的方向，然后走一步看一步，每次可能會發(fā)現(xiàn)新的問題需要處理败许。

但是這次比較順利王带，沒有進行任何返工。

不過暫時還沒做性能優(yōu)化市殷，后續(xù)要研究并完成愕撰。