时间戳

首页 > 解决方案 > MySQL：有没有办法使用窗口函数而不是子查询来查找每个用户执行某项操作的平均次数？

mysql - MySQL：有没有办法使用窗口函数而不是子查询来查找每个用户执行某项操作的平均次数？

问题描述

我有一个名为 的表，log用于记录用户何时采取少数行动之一；假设这些是动作 1 和 2，用户可以选择重复执行这些动作。该表有列username, action_time, action_done。

我想要一个包含操作列的结果集，该操作的平均执行次数（每个用户），以及任何用户执行该操作的最大次数。我能够用一个看起来相当笨拙的子查询来完成这个：

SELECT   
  DISTINCT(s.action) as "Action",  
  AVG(s.times_done) OVER (PARTITION BY s.action) as "Average Times Done",  
  MAX(s.times_done) OVER (PARTITION BY s.action) as "Max Times Done"  
FROM (
SELECT action, COUNT(action)  as times_done
FROM log
GROUP BY action, username 
) s;

我对窗口函数有基本的了解，但我似乎无法让子查询COUNT作为窗口函数正常工作。此外，我觉得我应该能够以更简单的方式完成此任务，完全避免子查询。例如，如果我的数据集是：

------------------------------------------------------
|  username  |      action_time      |  action_done  |
======================================================
| first_user |  2020-01-30 13:01:23  |       1       |
| first_user |  2020-01-30 12:34:40  |       2       |
| first_user |  2020-01-30 12:34:56  |       2       |
| secnd_user |  2020-01-30 15:25:14  |       1       |
| secnd_user |  2020-01-30 15:25:00  |       2       |
| secnd_user |  2020-01-30 15:25:31  |       2       |
| secnd_user |  2020-01-30 15:26:02  |       2       |

那么我想得到以下结果集：

------------------------------------------------------
|  Action  |  Average Times Done  |  Max Times Done  |
======================================================
|    1     |           1          |        1         |
|    2     |          2.5         |        3         |

任何人都可以提出一些建议吗？

编辑：让我稍微解释一下数据和结果集。我包含一个 ISO 日期时间列并不是因为我希望它与最终目标相关，而是因为我发现没有它的数据会更加混乱。用户正在与某个网站进行交互，而数据库正在存储谁在什么时候做什么。

所以 first_user 执行一次动作 1，执行两次动作 2。然后 second_user 执行动作 1 一次，动作 2 他们执行 3 次。

因此，我的两个用户执行操作 1 的平均次数是一次：(1+1)/2。我的用户执行操作二的平均次数是 2.5 次：(2+3)/2。

第二次编辑：也许调用动作 1 和 2 是一个令人困惑的选择。真的，这更像是这个网站上有两个按钮，按钮 A 和按钮 B，用户正在点击它们。我想知道，在所有用户中，人们倾向于点击按钮 A 5 到 6 次，但更多的人点击了 6 次或更多次，因此平均会有 5.72 次点击 A。也许有人喜欢按钮 A比任何人都多，他们点击了 50 次。button_A的结果行将Action, Average Times Done, Max Times Done是：

|  A  |   5.72   |    50   |

标签： mysqlsqlmariadb

解决方案

WITH cte AS ( SELECT action_done,
                     username,
                     COUNT(action_done) cnt
              FROM actions
              GROUP BY action_done, username )
SELECT action_done `Action`,
       AVG(cnt) `Average Times Done`,
       MAX(cnt) `Max Times Done`
FROM cte
GROUP BY action_done;

小提琴

推荐阅读