Hive 常用指标分析

姜鸣蝉

已于 2024-05-18 14:24:31 修改

阅读量720

点赞数 23

文章标签： hive hadoop 数据仓库

于 2024-05-18 14:19:09 首次发布

本文链接：https://blog.csdn.net/jiangmingchan/article/details/139024781

版权

常用指标分析

用户留存率
路径分析
7日品牌复购率
7天内连续三天登录
每分钟在线人数

1. 用户留存率

在使用 Hive 统计用户留存率时，我们通常会涉及到两个主要日期：用户注册日期和用户在后续某天的活跃日期。留存率通常是指用户在注册后的某一天仍然活跃的百分比。

假设我们有一个名为 user_activity 的表，这个表包含用户 ID、注册日期和活跃日期。表的结构如下：

CREATE TABLE user_activity (
    user_id STRING,
    registration_date DATE,
    active_date DATE
);

我们将分步骤计算用户留存率。假设我们要计算 D+1 留存率（即用户在注册后的第二天是否仍然活跃）。

步骤一：计算每个用户的注册日期

首先，我们获取每个用户的最早注册日期。

CREATE TABLE user_registration_date AS
SELECT 
    user_id, 
    MIN(registration_date) AS registration_date
FROM 
    user_activity
GROUP BY 
    user_id;

步骤二：计算指定日期的活跃用户数

接着，我们找出在注册后第二天活跃的用户。我们可以使用 Hive 的日期函数来进行日期加减。

CREATE TABLE d1_retained_users AS
SELECT 
    u.user_id, 
    u.registration_date, 
    a.active_date
FROM 
    user_registration_date u
JOIN 
    user_activity a 
ON 
    u.user_id = a.user_id
WHERE 
    a.active_date = DATE_ADD(u.registration_date, 1);

步骤三：计算留存率

现在，我们可以计算 D+1 留存率，即在注册后第二天仍然活跃的用户占总注册用户的比例。

SELECT 
    COUNT(DISTINCT d1.user_id) AS retained_users,
    COUNT(DISTINCT ur.user_id) AS total_users,
    (COUNT(DISTINCT d1.user_id) * 100.0 / COUNT(DISTINCT ur.user_id)) AS retention_rate
FROM 
    user_registration_date ur
LEFT JOIN 
    d1_retained_users d1 
ON 
    ur.user_id = d1.user_id;

步骤四：计算其他天数的留存率

我们可以使用类似的方法计算其他天数（例如 D+7、D+30）的留存率，只需调整 DATE_ADD 的天数即可。例如，计算 D+7 留存率：

CREATE TABLE d7_retained_users AS
SELECT 
    u.user_id, 
    u.registration_date, 
    a.active_date
FROM 
    user_registration_date u
JOIN 
    user_activity a 
ON 
    u.user_id = a.user_id
WHERE 
    a.active_date = DATE_ADD(u.registration_date, 7);

然后计算 D+7 留存率：

SELECT 
    COUNT(DISTINCT d7.user_id) AS retained_users,
    COUNT(DISTINCT ur.user_id) AS total_users,
    (COUNT(DISTINCT d7.user_id) * 100.0 / COUNT(DISTINCT ur.user_id)) AS retention_rate
FROM 
    user_registration_date ur
LEFT JOIN 
    d7_retained_users d7 
ON 
    ur.user_id = d7.user_id;

2. 路径分析

路径分析通常用于分析用户在网站或应用中的行为路径，以了解他们如何到达特定目标或退出。在 Hive 中，可以使用窗口函数和自联接来分析用户路径。假设我们有一个用户行为表 user_activity，包含以下字段：

CREATE TABLE user_activity (
    user_id STRING,
    activity_time TIMESTAMP,
    page STRING
);

步骤一：为每个用户的行为排序

首先，为每个用户的行为按照时间排序，以便我们能够按照用户访问的顺序来分析路径。

CREATE TABLE user_activity_sorted AS
SELECT
    user_id,
    activity_time,
    page,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY activity_time) AS rn
FROM
    user_activity;

步骤二：创建自联接表来获取用户的下一个页面

然后，我们通过自联接将每个行为与下一个行为进行配对，以便分析用户从一个页面到另一个页面的路径。

CREATE TABLE user_paths AS
SELECT
    a.user_id,
    a.page AS current_page,
    b.page AS next_page,
    a.activity_time AS current_time,
    b.activity_time AS next_time
FROM
    user_activity_sorted a
LEFT JOIN
    user_activity_sorted b
ON
    a.user_id = b.user_id
    AND a.rn = b.rn - 1;

步骤三：统计各个路径的频次

接下来，我们统计每条路径出现的次数，以了解用户最常走的路径。

CREATE TABLE path_counts AS
SELECT
    current_page,
    next_page,
    COUNT(*) AS path_count
FROM
    user_paths
GROUP BY
    current_page,
    next_page
ORDER BY
    path_count DESC;

步骤四：分析特定路径

如果我们想分析特定页面路径的细节，比如从首页（home）到购买页面（purchase）的路径，我们可以筛选出相关数据。

SELECT
    user_id,
    current_page,
    next_page,
    current_time,
    next_time
FROM
    user_paths
WHERE
    current_page = 'home'
    AND next_page = 'purchase';

进一步分析

我们还可以进一步细化路径分析，比如计算用户在每个页面的停留时间，或者分析特定用户群体的路径行为。

计算停留时间

CREATE TABLE user_stay_time AS
SELECT
    user_id,
    current_page,
    next_page,
    next_time - current_time AS stay_duration
FROM
    user_paths
WHERE
    next_time IS NOT NULL;

分析特定用户群体

假设我们有一个用户表 user_info 包含用户的详细信息（如用户类别），我们可以结合 user_paths 进行分析。

CREATE TABLE user_info (
    user_id STRING,
    user_category STRING
);

CREATE TABLE category_paths AS
SELECT
    u.user_category,
    p.current_page,
    p.next_page,
    COUNT(*) AS path_count
FROM
    user_paths p
JOIN
    user_info u
ON
    p.user_id = u.user_id
GROUP BY
    u.user_category,
    p.current_page,
    p.next_page
ORDER BY
    path_count DESC;

3. 7日品牌复购率

在 Hive 中计算最近7天的品牌复购率需要几个步骤，包括识别首次购买日期、检查复购行为，并计算复购率。假设我们有一个包含购买记录的表 purchase_log，结构如下：

CREATE TABLE purchase_log (
    user_id STRING,
    purchase_date DATE,
    brand STRING
);

步骤一：定义时间窗口

首先，我们定义一个时间窗口来选择最近7天的记录。假设当前日期为 2024-05-18。

步骤二：识别首次购买

接下来，我们找到每个用户对每个品牌在最近7天的首次购买日期。

CREATE TABLE first_purchase_last_7_days AS
SELECT
    user_id,
    brand,
    MIN(purchase_date) AS first_purchase_date
FROM
    purchase_log
WHERE
    purchase_date BETWEEN DATE_SUB('2024-05-18', 7) AND '2024-05-18'
GROUP BY
    user_id, brand;

步骤三：识别复购行为

然后，我们找出这些用户在首次购买后7天内再次购买同一品牌的记录。

CREATE TABLE repurchase_last_7_days AS
SELECT
    fp.user_id,
    fp.brand
FROM
    first_purchase_last_7_days fp
JOIN
    purchase_log pl
ON
    fp.user_id = pl.user_id
    AND fp.brand = pl.brand
    AND pl.purchase_date > fp.first_purchase_date
    AND pl.purchase_date <= DATE_ADD(fp.first_purchase_date, 7)
WHERE
    fp.first_purchase_date BETWEEN DATE_SUB('2024-05-18', 7) AND '2024-05-18'
GROUP BY
    fp.user_id, fp.brand;

步骤四：计算复购率

最后，我们计算每个品牌在最近7天内的复购率。

-- 计算最近7天首次购买用户数量
CREATE TABLE first_purchase_count_last_7_days AS
SELECT
    brand,
    COUNT(DISTINCT user_id) AS first_purchase_users
FROM
    first_purchase_last_7_dayss
GROUP BY
    brand;

-- 计算最近7天复购用户数量
CREATE TABLE repurchase_count_last_7_days AS
SELECT
    brand,
    COUNT(DISTINCT user_id) AS repurchase_users
FROM
    repurchase_last_7_days
GROUP BY
    brand;

-- 计算最近7天的复购率
SELECT
    f.brand,
    f.first_purchase_users,
    COALESCE(r.repurchase_users, 0) AS repurchase_users,
    (COALESCE(r.repurchase_users, 0) * 100.0 / f.first_purchase_users) AS repurchase_rate
FROM
    first_purchase_count_last_7_days f
LEFT JOIN
    repurchase_count_last_7_days r
ON
    f.brand = r.brand;

4. 7天内连续3天登录

在 Hive 中统计用户在7天内连续3天登录的情况，需要分几步来完成。首先，我们需要有一个包含用户登录记录的表，假设该表名为 login_log，结构如下：

CREATE TABLE login_log (
    user_id STRING,
    login_date DATE
);

步骤一：定义时间窗口

假设当前日期为 2024-05-18，我们会限定分析时间窗口为 2024-05-12 至 2024-05-18 这7天内的登录情况。

步骤二：获取7天内的登录记录

首先，我们获取在最近7天内的用户登录记录。

CREATE TABLE recent_logins AS
SELECT
    user_id,
    login_date
FROM
    login_log
WHERE
    login_date BETWEEN DATE_SUB('2024-05-18', 6) AND '2024-05-18';

步骤三：按用户和日期排序并添加行号

为了识别连续登录的天数，我们按用户和登录日期排序，并使用窗口函数为每个用户的每次登录添加一个行号。

CREATE TABLE user_logins_with_rn AS
SELECT
    user_id,
    login_date,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn
FROM
    recent_logins;

步骤四：自连接查找连续登录天数

接下来，我们将用户的登录记录与自身连接，通过行号差异来识别连续登录天数。

CREATE TABLE consecutive_logins AS
SELECT
    a.user_id,
    a.login_date AS start_date,
    b.login_date AS end_date,
    DATEDIFF(b.login_date, a.login_date) AS days_diff
FROM
    user_logins_with_rn a
JOIN
    user_logins_with_rn b
ON
    a.user_id = b.user_id
    AND b.rn = a.rn + 2
WHERE
    DATEDIFF(b.login_date, a.login_date) = 2;

步骤五：筛选连续3天登录的用户

通过前面的步骤，我们找到了在7天内连续3天登录的用户。

SELECT
    user_id,
    start_date,
    end_date
FROM
    consecutive_logins
WHERE
    days_diff = 2;

5. 每分钟在线人数

统计每分钟同时在线人数可以通过在登录日志表中记录每个用户的登录和退出时间，并计算每分钟的同时在线人数来实现。假设我们有一个登录日志表 login_log，包含用户ID（user_id）和登录时间（login_time）字段。

首先，我们需要对登录日志进行预处理，将每个用户的登录和退出时间按分钟进行划分。然后，根据每个时间段内的登录和退出情况，计算同时在线人数。

以下是一种可能的方法：

步骤一：划分登录和退出时间段

使用 Hive 的时间函数将登录和退出时间按分钟划分。

CREATE TABLE login_minutes AS
SELECT
    user_id,
    from_unixtime(unix_timestamp(login_time, 'yyyy-MM-dd HH:mm'), 'yyyy-MM-dd HH:mm') AS login_minute,
    1 AS login_count
FROM
    login_log;

CREATE TABLE logout_minutes AS
SELECT
    user_id,
    from_unixtime(unix_timestamp(logout_time, 'yyyy-MM-dd HH:mm'), 'yyyy-MM-dd HH:mm') AS logout_minute,
    1 AS logout_count
FROM
    logout_log;

假设 logout_log 是一个包含用户ID（user_id）和退出时间（logout_time）的表，包含了用户的退出记录。

步骤二：计算每分钟的在线人数

然后，我们将登录和退出时间段合并，并根据每分钟的登录和退出情况计算同时在线人数。

CREATE TABLE online_users AS
SELECT
    login_minute AS time_minute,
    SUM(login_count) - SUM(logout_count) AS online_users_count
FROM
    (SELECT * FROM login_minutes UNION ALL SELECT * FROM logout_minutes) t
GROUP BY
    time_minute;