当下,不管是业务升级迭代项目,还是体验优化项目,对于数据的需求都越来越大。数据需求主要集中在以下几个方面:
项目数据看板搭建:特别是一些AB实验的看板,能直观呈现项目的核心数据变化
数据分析:项目启动前的探索挖掘以及项目后的效果分析
但是,眼下存在的一个普遍矛盾是:日益增长的数据需求和落后的数据生产力之前的矛盾
俗话说,求人不如求己,掌握基础的数据技能对于技术同学(尤其是开发岗位的同学)并不是一件难事,只是缺少一个合适的入门指南。本文旨在让想学习数据处理的同学能快速入门。
-- 临时表命名建议以“tmp_”开头,odps会知道该表是临时表
-- 临时表的生命周期建议按需设置,不要设置太长,避免资源浪费
CREATE TABLE tmp_ut_cart_clk LIFECYCLE 7 AS
SELECT user_id
FROM <用户浏览数据表>
创建正式表:
-- analytics_dw是odps的空间名,后面的是表名
-- 空间名.表名 才能确定唯一的数据表
-- 以下是一个实际案例
CREATE TABLE IF NOT EXISTS analytics_dw.ads_tb_biz_request_opt_1d
(
bucket_id STRING COMMENT '分桶'
,os STRING COMMENT '系统'
,uv BIGINT COMMENT '分桶用户数'
,pv BIGINT COMMENT '页面访问pv'
,page_stay_time BIGINT COMMRNT '页面停留时间(ms)'
...
)
PARTITIONED BY -- 分区
(
ds STRING COMMENT '日期'
)
LIFECYCLE 30
;
基础查询是数据处理的基础,这一步的主要工作包括数据的清洗和过滤,字段的加工拓展,为后续的数据处理打好坚实基础
SET odps.sql.mapper.split.size=2048; -- 默认是256(单位M)
SELECT user_id
,page
,time_stamp
...
FROM <App用户使用明细表>
WHERE ds = '${bizdate}'
AND product = '<App名称>'
AND event_type = '<事件类型:浏览\点击>'
AND page = '<页面标识>'
;
-- 单个条件
SELECT IF(page = 'Page_XXX', 'y', 'n') AS is_page_xxx
-- 多个条件
,CASE WHEN hh <= 12 THEN '上午'
WHEN hh > 12 AND hh <= 18 THEN '下午'
ELSE '晚上'
END AS 时间段
-- 超级有用:提取args中的kv
,KEYVALUE(args, ',', '=', 'itemid') AS item_id
-- 分割字符串(value为“a_b_c”这种有规律的字符串可以使用)
,SPLIT(value, '_') AS value_list -- 这个是数组,可以使用索引 value_list[0]
-- 去除空值(使用a,b,c中第一个不为NULL的值,否则用最后的空字符串)
,COALESCE(a, b, c, '') AS xxx
-- 版本比较,超级实用
,IF(bi_udf:bi_yt_compare_version(app_version, '10.24.10') >= 0, 'y', 'n') AS is_target_version
-- 解析JSON,提取目标信息
,GET_JSON_OBJECT(json_str, '$.section.item.name') AS item_name
-- 类型转换
,CAST(user_id AS BIGINT) AS user_id
-- 大小写转换
,TOUPPER(os) , TOLOWER(os)
-- 日期格式描述
yyyy 年,4位
MM 月,2位
dd 日,2位
hh/HH 12小时制/24小时制,2位
mi 分钟,2位
ss 秒,2位
SSS 毫秒,3位
-- 通过上面这些格式就能在转化具体的日期时描述日期的格式:
20230807 yyyyMMdd
2023-08-07 yyyy-MM-dd
20230806 13:22:00 yyyyMMdd HH:mi:si
-- 单纯查询某个日期之前或者之后的数据
ds >= '20230807'
TO_DATE('20230807', 'yyyyMMdd') -- 将日期字符串转为 datetime 实例,日期处理的基础
TO_CHAR(datetime, 'yyyyMMdd') -- 将日期函数处理得到各种datetime转换为字符串
FROM_UNIXTIME(123456789) -- 将unix时间戳转换成datetime对象
-- 日期加减,自动处理进位关系
DATEADD(TO_DATE('20230807', 'yyyyMMdd'), 7, 'dd') -- 20230814
DATEADD(TO_DATE('20230807', 'yyyyMMdd'), -7, 'dd') -- 20230731
-- 2个日期间隔(第一个日期-第二个日期,结果可为负)
DATEDIFF(TO_DATE('20230807', 'yyyyMMdd'), TO_DATE('20230806', 'yyyyMMdd'), 'dd') -- 1
-- 提取指定时间
-- 在希望分小时段统计的场景下很实用
DATEPART(TO_DATE('2023-08-07 12:13:22', 'yyyy-MM-dd hh:mi:ss'), 'hh') -- 12
很多时候单一表的数据无法满足我们的需求,需要通过其他表来补充一些信息,这时就需要关联数据。在sql上表现为有Join操作。
-- 基本的join语法如下
SELECT a.user_id
,a.arg1
,a.args
,b.bucket_id
FROM (
SELECT user_id
,arg1
,args
FROM <用户手淘行为表>
) a
LEFT JOIN (
SELECT user_d
,bucket_id
FROM <AB实验分流表>
) b
ON a.user_id = b.user_id
;
-- left join、right join、inner join差别
left join:会保留左表的所有数据(在上面这个例子中左表就是 a,join左边的表),右表中没有匹配的数据将会丢失
right join:和left join相反会保留右表(b)的所有数据,左表中没有匹配的数据会丢失
inner join:最终只有两个表的交集部分会被保留下来
SELECT /* + mapjoin(J2) */
J1.*
,J2.industry
FROM <订单表> J1
LEFT JOIN
(
SELECT cate_level1_id
,industry
FROM <行业维表>
WHERE ds = '${bizdate}'
) J2
ON J1.cate_level1_id = J2.cate_level1_id
;
聚合就是针对数据中的某些维度(系统、版本等)执行一系列计算返回单一值。一般在sql上体现为有Group By操作。一般我们数据处理(指标计算)的最后几步都离不开聚合操作。
-- 常见聚合函数
AVG(age) AS avg_age ) -- 平均值
SUM(cnt) AS total_cnt -- 求和
MIN(age) AS min_age -- 最小值
MAX(age) AS max_age -- 最大值
COUNT(*) / COUNT(item_id) -- 计数 count(*)不会忽略null,count(xx)会忽略null
COUNT(DISTINCT utdid) -- 去重计数
COLLECT_SET(item_id) -- 将去重后的item_id存在一个数组中
COLLECT_ARRAY(item_id) -- 将item_id存在一个数组中(不去重)
PERCENTILE(duration, 0.95) -- 求分位数
SELECT province
,SUM(amount) AS gmv
FROM <每日成交表>
GROUP BY province
UNION ALL
SELECT '整体' AS province
,SUM(amount) AS gmv
FROM <每日成交表>
SELECT IF(GROUPING(province) == 0, province, 'all') AS province
,IF(GROUPING(city) == 0, city, 'all') AS city
,SUM(amount) AS gmv
FROM <每日成交表>
GROUP BY GROUPING SETS((), (province), (province, city))
-- 下面是CUBE的示例
SELECT IF(GROUPING(province) == 0, province, 'all') AS province
,IF(GROUPING(city) == 0, city, 'all') AS city
,SUM(amount) AS gmv
FROM <每日成交表>
GROUP BY CUBE(province, city)
-- 说明:
-- GROUPING SETS:按照制定维度组合来做聚合
-- CUBE:按照相关维度的全排列来做聚合
-- 步骤1 甚至可以写注释方面以后理解
-- 建议将关心的原始数据先清洗处理保存为临时表,方便后面做各种分析使用,提升效率
DROP TABLE IF EXISTS tmp_step1_${bizdate};
CREATE TABLE tmp_step1_${bizdate} LIFECYCLE 3 AS -- 临时表生命周期不要设置太久,避免无意义的资源浪费
SELECT a
,b
,c
FROM <数据表1>
WHERE <筛选条件>
;
-- 步骤2
DROP TABLE IF EXISTS tmp_step2_${bizdate};
CREATE TABLE tmp_step2_${bizdate} LIFECYCLE 3 AS
SELECT a
,b
,c
FROM tmp_step1_${bizdate}
;
-- ....
-- 关注的结果
SELECT *
FROM tmp_stepN_${bizdate}
GROUP BY xxx
;
说明:
@step1 :=
SELECT XX
FROM XXXX;
@step2 :=
SELECT YY
FROM @step1;
....
SELECT *
FROM @stepN;
WITH
step1 AS
(
SELECT XX
FROM XXXX
),
step2 AS
(
SELECT YY
FROM step1
),
....
stepN AS
(
SELECT ...
) -- 最后的这括号后面不要加 ,
INSERT OVERWRITE TABLE <存储表名> PARTITION (ds = '${bizdate}') -- 一个WITH只支持一个INSERT
SELECT *
FROM stepN
写在最后