hive 留存 sql

留存率是指在一定时间范围内，用户在某个时间点后仍然使用产品或服务的比例。计算留存率可以使用Hive SQL或Spark SQL。

假设我们要计算某个应用程序在第一天、第七天和第三十天的留存率。我们可以按照以下步骤进行计算：

1. 首先，我们需要从用户行为日志中提取出每个用户的第一次使用时间。假设我们已经将这些数据存储在一个名为user_first_use的Hive表中，其中包含用户ID和第一次使用时间。

2. 接下来，我们需要计算每个用户在第一天、第七天和第三十天后是否仍然使用了应用程序。假设我们已经将这些数据存储在一个名为user_activity的Hive表中，其中包含用户ID、活动日期和活动类型（例如，打开应用程序、浏览页面等）。

3. 然后，我们可以使用Hive或Spark SQL编写查询来计算留存率。以下是一个使用Hive SQL计算留存率的示例查询：

SELECT
  COUNT(DISTINCT ua.user_id) AS total_users,
  COUNT(DISTINCT CASE WHEN DATEDIFF(ua.activity_date, uf.first_use_date) = 0 THEN ua.user_id END) AS day1_retention,
  COUNT(DISTINCT CASE WHEN DATEDIFF(ua.activity_date, uf.first_use_date) = 6 THEN ua.user_id END) AS day7_retention,
  COUNT(DISTINCT CASE WHEN DATEDIFF(ua.activity_date, uf.first_use_date) = 29 THEN ua.user_id END) AS day30_retention
FROM
  user_first_use uf
  JOIN user_activity ua ON uf.user_id = ua.user_id
WHERE
  ua.activity_date BETWEEN uf.first_use_date AND DATE_ADD(uf.first_use_date, 29)

这个查询将返回四个值：总用户数、第一天留存率、第七天留存率和第三十天留存率。我们使用COUNT(DISTINCT)函数来计算唯一用户的数量，并使用CASE语句来计算在特定日期范围内仍然使用应用程序的用户数量。DATEDIFF函数用于计算用户第一次使用应用程序后的天数。

4. 如果使用Spark SQL，可以使用类似的查询来计算留存率。以下是一个使用Spark SQL计算留存率的示例查询：

SELECT
  COUNT(DISTINCT ua.user_id) AS total_users,
  COUNT(DISTINCT CASE WHEN DATEDIFF(ua.activity_date, uf.first_use_date) = 0 THEN ua.user_id END) AS day1_retention,
  COUNT(DISTINCT CASE WHEN DATEDIFF(ua.activity_date, uf.first_use_date) = 6 THEN ua.user_id END) AS day7_retention,
  COUNT(DISTINCT CASE WHEN DATEDIFF(ua.activity_date, uf.first_use_date) = 29 THEN ua.user_id END) AS day30_retention
FROM
  user_first_use uf
  JOIN user_activity ua ON uf.user_id = ua.user_id
WHERE
  ua.activity_date BETWEEN uf.first_use_date AND DATE_ADD(uf.first_use_date, 29)

这个查询与Hive SQL查询非常相似，只是使用了Spark SQL的语法。