spark sql实现“平均月活”和“平均周活”及相关函数

一、平均月活：
SELECT date_format(time, 'yyyy-MM') AS current_month,
COUNT(DISTINCT user_id) AS month_active_user_num
FROM table
WHERE time >= trunc(now(), 'YEAR')
GROUP BY date_format(time, 'yyyy-MM');

二、平均周活：
WITH week_user_cnt
(
SELECT date_format(time, 'yyyy') AS current_year,
date_format(date_trunc('WEEK', time), 'MM') AS current_month,
weekofyear(time) AS current_week,
COUNT(DISTINCT user_id) AS active_users
FROM table
GROUP BY date_format(time, 'yyyy'),
date_format(date_trunc('WEEK', time), 'MM'),
weekofyear(time)
)
SELECT CONCAT(current_year, '-', current_month) AS current_month,
CAST(AVG(active_users) AS INT) AS week_active_user_num
FROM week_user_cnt
GROUP BY CONCAT(current_year, '-', current_month);

三、相关函数：
1、字符串类型数据转换为timestamp类型数据：
to_timestamp('2023-01-01')
2、timestamp类型数据转换为字符串类型数据：
date_format(start_date, 'yyyy-MM')
3、将时间差转换为以天为单位：
(unix_timestamp(end_time) - unix_timestamp(start_time))/3600/24