模块一作业

作业

1. 在会员分析中计算最近七天连续三天活跃会员数。
2. 项目的数据采集过程中，有哪些地方能够优化，如何实现？

题目1

解题思路：

1. 从dws.dws_member_start_day 这张每日会员登录表中已经知道了，每日登录的会员

2. 从求出的时间往前推7天确认数据范围

3. 使用排序函数 row_number,按照device_id分组，dt排序，用dt减去row——number得到分组标识字段gid

4. 再根据gid和device_id进行分组，用连续三天这个条件进行过滤

sql代码：

with tmp as (
    select
        device_id, dt,
        date_sub(dt, row_number() over (partition by device_id order by dt)) gid
    from 
        dws.dws_member_start_day
    where dt between  date_sub("2020-07-28",7) and "2020-07-28"
)
select device_id,count(*) from tmp group by device_id, gid having count(*)>=2;

编写脚本：

drop table if exists dws.dws_member_start_continue_three_day;
create table dws.dws_member_start_continue_three_day(
`device_id` string,
`uid` string,
`app_v` string,
`os_type` string,
`language` string,
`channel` string,
`area` string,
`brand` string,
`date` string,
`count` int
) COMMENT '最近七天连续三天登录会员表'
PARTITIONED BY (`dt` string)
stored as parquet

编写脚本数据：

#！/bin/bash
source /etc/profile
# 可以输入日期；如果未输入日期取昨天的时间
if [ -n "$1" ]
then
do_date=$1
else
do_date=`date -d "-1 day" +%F`
fi
# 定义要执行的SQL

sql="
with tmp as (
    
)
insert overwrite table dws.dws_member_start_continue_three_day
partition(dt='$do_date')
select device_id,
concat_ws('|', collect_set(uid)),
concat_ws('|', collect_set(app_v)),
concat_ws('|', collect_set(os_type)),
concat_ws('|', collect_set(language)),
concat_ws('|', collect_set(channel)),
concat_ws('|', collect_set(area)),
concat_ws('|', collect_set(brand)),
'$do_date',
count(*)
from (
	select
        device_id, dt,uid,app_v,os_type,language,channel,area,brand,
        date_sub(dt, row_number() over (partition by device_id order by dt)) gid
    from 
        dws.dws_member_start_day
    where dt between  date_sub('$do_date',7) and '$do_date' 
) tmp
where dt='$do_date'
group by device_id, gid having count(*)>=3;
"
hive -e "$sql"

题目2

1. 采集数据时，可以进行简单的数据过滤，将无效的数据直接过滤使得ODS层可以减少存储成本和处理效率。

2. 使用 file channel 或者kafka channel 使数据保持可靠性。