马上注册，结交更多好友，享用更多功能，让你轻松玩转社区。
您需要 登录 才可以下载或查看，没有账号？立即注册
x
利用聚合开窗查找用户每月累积访问次数，首先先容一下利用 GROUP BY和开窗的区别
GROUP BY
- 行数变化：利用 GROUP BY 后，原始数据会按指定列进行分组，结果中每组只保留一行，因此行数通常减少。
- 作用：实用于需要对数据进行汇总的场景，如计算总和、均匀值等。
- 示例：如果有多个用户的访问记载，通过 GROUP BY 可以将每个用户每月的访问次数汇总为一行。
窗口函数
- 行数变化：窗口函数在计算时不改变原始数据的行数，即每条原始记载依然保留。
- 作用：实用于需要在保留详细数据的同时进行累积计算、排名等操纵。
- 示例：在按月汇总用户访问数据的同时，通过窗口函数增长一列显示累计访问次数，每月的详细记载依然存在。
示例数据
假设 test 表有如下数据：
user_idvisit_datevisit_count12023-01-05 10:30:001012023-01-15 15:45:002012023-02-10 12:00:001522023-01-07 09:00:00522023-02-11 14:30:001032023-01-20 11:00:00832023-02-05 16:30:001242023-01-25 14:00:00742023-02-15 09:15:00952023-01-10 13:30:00652023-02-20 16:45:0011 第一步：提取年代并计算每月访问次数
我们首先提取出每条记载的年代，并计算每个用户每月的访问次数：
- SELECT
- user_id,
- DATE_FORMAT(visit_date, '%Y-%m') AS month_id,
- SUM(visit_count) AS visit_cnt_lm
- FROM
- test
- GROUP BY
- user_id,
- month_id;
user_idmonth_idvisit_cnt_lm12023-013012023-021522023-01522023-021032023-01832023-021242023-01742023-02952023-01652023-0211 第二步：计算访问次数的累计值
然后，我们利用窗口函数为每个用户计算访问次数的累计值：
- SELECT
- user_id,
- month_id,
- visit_cnt_lm,
- SUM(visit_cnt_lm) OVER (PARTITION BY user_id ORDER BY month_id) AS visit_cnt_td
- FROM (
- SELECT
- user_id,
- DATE_FORMAT(visit_date, '%Y-%m') AS month_id,
- SUM(visit_count) AS visit_cnt_lm
- FROM
- test
- GROUP BY
- user_id,
- month_id
- ) AS t2;
user_idmonth_idvisit_cnt_lmvisit_cnt_td12023-01303012023-02154522023-015522023-02101532023-018832023-02122042023-017742023-0291652023-016652023-021117 步骤
- 提取年代并计算访问次数：
- 查询：利用 DATE_FORMAT 提取年代，并汇总每个用户每月的访问次数。
- 结果表：显示用户、月份及其访问次数。
- 计算访问次数的累计值：
- 查询：利用窗口函数计算每个用户的访问次数累计值。
- 最终结果：展示每个用户每月的访问次数及其累计值。
解析
- 提取年代：利用 DATE_FORMAT 方法从完整日期中提取年代。
- 汇总访问次数：利用 SUM(visit_count) 按用户和月份分组汇总数据。
- 计算累计值：通过窗口函数 SUM() OVER 按用户分区、按月份排序，计算每个用户的访问次数累计值。
- SUM(visit_cnt_lm) OVER (PARTITION BY user_id ORDER BY month_id) 计算每个用户按照月份的累计访问次数。
- PARTITION BY user_id 按用户分区，ORDER BY month_id 按月份排序。
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。 |
