数据库中有某段用户上报日志，记录着用户的一些操作行为。现要将其清洗后，并筛选出我们感兴趣的用户后，并整理为算法要求的格式。要求过程中只能用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)都可以一目了然。也方便后续进行其他整合。
解决方案以及所涉及的知识点
上文分析会给我们一个大致方向，但是过程中可能还会碰到许多问题，以及还需考虑如何写使性能较好。因此根据分析，重新安排我们的解决方案如下：
-
步骤一：解析content内容并筛选用户
-
从content中得到behavior内容
-
筛选感兴趣用户以及获得用户的其他信息
-
将behabior数据分行分列
-
-
步骤二：剔除重复值、异常值、未知值
-
步骤三：数据合并与信息填充
-
给信息按时间顺序进行编号
-
多条数据合并为一条
-
locate信息填充
-
-
步骤四：生成算法格式
这些解决步骤中所涉及知识点如下：
-
将json数据分行分列【1】
-
三值逻辑的坑【2】【更多知识：链接进入搜索“三值逻辑”】
-
重复值剔除【2】【更多知识：链接进入搜索“重复值”】
-
如何使用分区函数获得数据的行号【3.1】
-
如何使用做连接将两行数据并为一行【3.2.1】
-
如何解决on后不能跟“<>”和“or”【3.2.2】
-
如何找到排序后每行小于它的某字段的最大的最小值【3.2.2】
-
如何获得数据清洗思路【通读全文并体会】
-
隐藏：如何设计数仓架构【通读全文找彩蛋】
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个问题：
-
同一数据多次上报；
-
behavior的数据因为业务原因，变成了locate数据；例如{behavior:a1; timestamp:12370;}；
-
数据上报延迟，导致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 -
behavior不仅有NULL还有空值
那么我们分别解决：
- distinct
- 根据业务情况，若长度大于10则为locate, behavior=other
- 这个预测与后面的loacte填充操作差不多，一起做会节约资源，因此放到后面操作
- 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个问题：
-
behavior=start和behavior=end两条数据对应的x和y相同，这时我们将其归类为一个新的behavior=click；
-
由于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小于它的最大的最小值。
实现步骤
- 获得其小于该行的locate的row_num；
- 求出得到row_num的最大值；
- 根据x和y的变化情况，丰富behavior的形式；
- 与合并，得到完整的用户行为数据。
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 | ... |
步骤：
- 首先将behavior,x_beg,x,end,y_beg,y_end,timestamp用“,”隔开做成一条字符串，这样一个用户就会有多条包含行为信息的字符串str1=”behavior,x_beg,x,end,y_beg,y_end,timestamp“；
- 将该用户的这些字符串用“;”拼接成新的字符串str2="str1;str1;..."；
- 将用户名和str2用":"连接起来，变格式为“用户名:str2”的字符串str3；
- 最后将不同用户之间的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 完成，撒花！
数据清洗是一个探索的过程，先有一个大的方向，然后走一步看一步，每次可能会发现新的问题需要处理。
但是这次比较顺利，没有进行任何返工。
不过暂时还没做性能优化，后续要研究并完成。
网友评论