IT评测·应用市场-qidao123.com技术社区

标题: 经典sql题（六）查找用户每月累积访问次数 [打印本页]

---

作者: 傲渊山岳    时间: 2024-9-18 01:02
标题: 经典sql题（六）查找用户每月累积访问次数
利用聚合开窗查找用户每月累积访问次数，首先先容一下利用 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 第一步：提取年代并计算每月访问次数

我们首先提取出每条记载的年代，并计算每个用户每月的访问次数：

1. SELECT
2.     user_id,
3.     DATE_FORMAT(visit_date, '%Y-%m') AS month_id,
4.     SUM(visit_count) AS visit_cnt_lm
5. FROM
6.     test
7. GROUP BY
8.     user_id,
9.     month_id;

复制代码

结果（子查询结果）

user_idmonth_idvisit_cnt_lm12023-013012023-021522023-01522023-021032023-01832023-021242023-01742023-02952023-01652023-0211 第二步：计算访问次数的累计值

然后，我们利用窗口函数为每个用户计算访问次数的累计值：

1. SELECT
2.     user_id,
3.     month_id,
4.     visit_cnt_lm,
5.     SUM(visit_cnt_lm) OVER (PARTITION BY user_id ORDER BY month_id) AS visit_cnt_td
6. FROM (
7.     SELECT
8.         user_id,
9.         DATE_FORMAT(visit_date, '%Y-%m') AS month_id,
10.         SUM(visit_count) AS visit_cnt_lm
11.     FROM
12.         test
13.     GROUP BY
14.         user_id,
15.         month_id
16. ) 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企服之家，中国第一个企服评测及商务社交产业平台。

---

欢迎光临 IT评测·应用市场-qidao123.com技术社区 (https://dis.qidao123.com/)

Powered by Discuz! X3.4