数仓常见sql套路

业务术语
1）用户
用户以设备为判断标准，在移动统计中，每个独立设备认为是一个独立用户。Android
系统根据IMEI 号，IOS 系统根据OpenUDID 来标识一个独立用户，每部手机一个用户。
2）新增用户
首次联网使用应用的用户。如果一个用户首次打开某APP，那这个用户定义为新增用
户；卸载再安装的设备，不会被算作一次新增。新增用户包括日新增用户、周新增用户、月
新增用户。
3）活跃用户
打开应用的用户即为活跃用户，不考虑用户的使用情况。每天一台设备打开多次会被计
为一个活跃用户。
4）周（月）活跃用户
某个自然周（月）内启动过应用的用户，该周（月）内的多次启动只记一个活跃用户。
5）月活跃率
月活跃用户与截止到该月累计的用户总和之间的比例。
6）沉默用户
用户仅在安装当天（次日）启动一次，后续时间无再启动行为。该指标可以反映新增用
户质量和用户与APP 的匹配程度。
7）版本分布
不同版本的周内各天新增用户数，活跃用户数和启动次数。利于判断APP 各个版本之
间的优劣和用户行为习惯。
8）本周回流用户
上周未启动过应用，本周启动了应用的用户。
9）连续n 周活跃用户
连续n 周，每周至少启动一次。
10）忠诚用户
连续活跃5 周以上的用户
11）连续活跃用户
连续2 周及以上活跃的用户
12）近期流失用户
连续n（2<= n <= 4）周没有启动应用的用户。（第n+1 周没有启动过）
13）留存用户
某段时间内的新增用户，经过一段时间后，仍然使用应用的被认作是留存用户；这部分
用户占当时新增用户的比例即是留存率。
例如，5 月份新增用户200，这200 人在6 月份启动过应用的有100 人，7 月份启动过
应用的有80 人，8 月份启动过应用的有50 人；则5 月份新增用户一个月后的留存率是50%，
二个月后的留存率是40%，三个月后的留存率是25%。
14）用户新鲜度
每天启动应用的新老用户比例，即新增用户数占活跃用户数的比例。
15）单次使用时长
每次启动使用的时间长度。
16）日使用时长
累计一天内的使用时间长度。
17）启动次数计算标准
IOS 平台应用退到后台就算一次独立的启动；Android 平台我们规定，两次启动之间的
间隔小于30 秒，被计算一次启动。用户在使用过程中，若因收发短信或接电话等退出应用
30 秒又再次返回应用中，那这两次行为应该是延续而非独立的，所以可以被算作一次使用
行为，即一次启动。业内大多使用30 秒这个标准，但用户还是可以自定义此时间间隔。

ods: 原始json数据

dwd：用户启动表数据 get_json_object转换

dwd: dwd_start_log: 
CREATE EXTERNAL TABLE dwd_start_log(
`mid_id` string,
`user_id` string,
`version_code` string,
`version_name` string,
`lang` string,
`source` string,
`os` string,
`area` string,
`model` string,
`brand` string,
`sdk_version` string,
`gmail` string,
`height_width` string,
`app_time` string,
`network` string,
`lng` string,
`lat` string,
`entry` string,
`open_ad_type` string,
`action` string,
`loading_time` string,
`detail` string,
`extend1` string
) PARTITIONED BY (
dt string)
stored as parquet
location '/warehouse/gmall/dwd/dwd_start_log/'
TBLPROPERTIES('parquet.compression'='lzo'); 

dws: 每日设备行为 每日设备行为，主要按照设备id 统计。

create external table dws_uv_detail_daycount
(
`mid_id` string COMMENT '设备唯一标识',
`user_id` string COMMENT '用户标识',
`version_code` string COMMENT '程序版本号',
`version_name` string COMMENT '程序版本名',
`lang` string COMMENT '系统语言',
`source` string COMMENT '渠道号',
`os` string COMMENT '安卓系统版本',
`area` string COMMENT '区域',
`model` string COMMENT '手机型号',
`brand` string COMMENT '手机品牌',
`sdk_version` string COMMENT 'sdkVersion',
`gmail` string COMMENT 'gmail',
`height_width` string COMMENT '屏幕宽高',
`app_time` string COMMENT '客户端日志产生时的时间',
`network` string COMMENT '网络模式',
`lng` string COMMENT '经度',
`lat` string COMMENT '纬度',
`login_count` bigint COMMENT '活跃次数'
) partitioned by(dt string)
stored as parquet
location '/warehouse/gmall/dws/dws_uv_detail_daycount';

2）数据装载
insert overwrite table dws_uv_detail_daycount partition(dt='2020-03-10')
select
mid_id,
concat_ws('|', collect_set(user_id)) user_id,
concat_ws('|', collect_set(version_code)) version_code,
concat_ws('|', collect_set(version_name)) version_name,
concat_ws('|', collect_set(lang))lang,
concat_ws('|', collect_set(source)) source,
concat_ws('|', collect_set(os)) os,
concat_ws('|', collect_set(area)) area,
concat_ws('|', collect_set(model)) model,
concat_ws('|', collect_set(brand)) brand,
concat_ws('|', collect_set(sdk_version)) sdk_version,
concat_ws('|', collect_set(gmail)) gmail,
concat_ws('|', collect_set(height_width)