大数据基础技能入门指南

-- 临时表命名建议以“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

-- 基本的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：最终只有两个表的交集部分会被保留下来

-- 常见聚合函数
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