需求八：最近七天内连续三天活跃用户数_最近七天连续三天活跃-优快云博客

本文链接：https://blog.youkuaiyun.com/qq_41508919/article/details/125499429

该博客围绕最近七天内连续三天活跃用户数的统计需求展开。介绍了DWS层使用日活明细表dws_uv_detail_day，ADS层的建表、导入数据SQL语句及查询操作。还说明了编写脚本的步骤，包括创建、增加权限、使用等，以及脚本执行时间。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

需求八：最近七天内连续三天活跃用户数

说明：最近7天内连续3天活跃用户数

14.1 DWS层

使用日活明细表dws_uv_detail_day作为DWS层数据

14.2 ADS层

1）建表语句

hive (gmall)>
drop table if exists ads_continuity_uv_count;
create external table ads_continuity_uv_count( 
    `dt` string COMMENT '统计日期',
    `wk_dt` string COMMENT '最近7天日期',
    `continuity_count` bigint
) COMMENT '连续活跃设备数'
row format delimited fields terminated by '\t'
location '/warehouse/gmall/ads/ads_continuity_uv_count';

2）写出导入数据的SQL语句

hive (gmall)>
insert into table ads_continuity_uv_count
select
    '2019-02-12',
    concat(date_add('2019-02-12',-6),'_','2019-02-12'),
    count(*)
from
(
    select mid_id
    from
    (
        select mid_id      
        from
        (
            select 
                mid_id,
                date_sub(dt,rank) date_dif
            from
            (
                select 
                    mid_id,
                    dt,
                    rank() over(partition by mid_id order by dt) rank
                from dws_uv_detail_day
                where dt>=date_add('2019-02-12',-6) and dt<='2019-02-12'
            )t1
        )t2 
        group by mid_id,date_dif
        having count(*)>=3
    )t3 
    group by mid_id
)t4;

(5)查询

hive (gmall)> select * from ads_continuity_uv_count;

14.3 编写脚本

1）在hadoop102的/home/atguigu/bin目录下创建脚本

[atguigu@hadoop102 bin]$ vim ads_continuity_log.sh

在脚本中编写如下内容

#!/bin/bash

if [ -n "$1" ];then
	do_date=$1
else
	do_date=`date -d "-1 day" +%F`
fi

hive=/opt/module/hive/bin/hive
APP=gmall

echo "-----------导入日期$do_date-----------"

sql="
insert into table "$APP".ads_continuity_uv_count
select 
     '$do_date',
     concat(date_add('$do_date',-6),'_','$do_date') dt,
     count(*) 
from 
(
    select mid_id
    from
    (
        select mid_id
        from 
        (
            select
                mid_id,
                date_sub(dt,rank) date_diff
            from 
            (
                select 
                    mid_id,
                    dt,
                    rank() over(partition by mid_id order by dt) rank
                from "$APP".dws_uv_detail_day
                where dt>=date_add('$do_date',-6) and dt<='$do_date'
            )t1
        )t2
        group by mid_id,date_diff
        having count(*)>=3
    )t3 
    group by mid_id
)t4;
"

$hive -e "$sql"

2）增加脚本执行权限