cube数据立方体模型

 多维立方体建表语句

-- 多维分析数据立方体，建表
DROP TABLE IF EXISTS dws.mall_app_tfc_cube;
CREATE TABLE dws.mall_app_tfc_cube
(
    appid              string,
    appversion         string,
    carrier            string,  -- 运营商
    devicetype         string,  -- 手机型号
    nettype            string,
    osname             string,
    osversion          string,
    releasechannel     string,
    resolution         string,
    province           string,   -- 省
    city               string,   -- 市
    region           string,   -- 区
    ses_enter_page_id  string, -- 入口页面id
    ses_exit_page_id   string, -- 退出页面id
    ses_is_jump        int   ,  -- 1:是跳出会话  2:不是跳出会话

    pv_cnt             bigint,  -- 访问页面数
    uv_nct             bigint,  -- 去重访客总数
    ses_cnt            bigint   -- 访问的总时长
)
    PARTITIONED BY (dt string)
    STORED AS ORC
    TBLPROPERTIES ('orc.compress'='snappy')
;

数据计算方式

-- 计算
INSERT INTO TABLE dws.mall_app_tfc_cube PARTITION (dt='2022-02-12')
SELECT
    appid
     ,appversion
     ,carrier
     ,devicetype
     ,nettype
     ,osname
     ,osversion
     ,releasechannel
     ,resolution
     ,province
     ,city
     ,region
     ,ses_enter_page_id
     ,ses_exit_page_id
     ,ses_is_jump
     ,count(if(eventid='pageView',1,null)) as pv_cnt
     ,count(distinct guid) as uv_cnt
     ,count(distinct sessionid)  as ses_cnt
FROM dws.mall_app_tfc_wt
WHERE dt='2022-02-12'
GROUP BY
    appid
       ,appversion
       ,carrier
       ,devicetype
       ,nettype
       ,osname
       ,osversion
       ,releasechannel
       ,resolution
       ,province
       ,city
       ,region
       ,ses_enter_page_id
       ,ses_exit_page_id
       ,ses_is_jump
    GROUPING SETS (
       (),
       (province,city,region),
       (province,city),
       (province),
       (devicetype),
       (osname),
       (ses_enter_page_id),
       (ses_exit_page_id),
       (appid),
       (appid,appversion),
       (carrier),
       (carrier,nettype),
       (nettype)
    );

使用方式 coalesce升级版的nvl

-- cube表的取数示例
-- 各退出页面下的pv数，uv数，会话数
select
    ses_exit_page_id
     ,pv_cnt
     ,uv_nct
     ,ses_cnt
from dws.mall_app_tfc_cube
where dt='2022-02-12'
  and ses_exit_page_id is not null
  and coalesce(
        appid
    ,appversion
    ,carrier
    ,devicetype
    ,nettype
    ,osname
    ,osversion
    ,releasechannel
    ,resolution
    ,province
    ,city
    ,region
    ,ses_enter_page_id
    ,ses_is_jump
    )  is null
;


-- 各手机型号下的pv数，uv数，会话数
select
    devicetype
     ,pv_cnt
     ,uv_nct
     ,ses_cnt
from dws.mall_app_tfc_cube
where dt='2022-02-12'
  and devicetype is not null
  and coalesce(
        appid
    ,appversion
    ,carrier
    ,nettype
    ,osname
    ,osversion
    ,releasechannel
    ,resolution
    ,province
    ,city
    ,region
    ,ses_enter_page_id
    ,ses_exit_page_id
    ,ses_is_jump
    )  is null
;