时间戳

首页 > 解决方案 > 如何编写 SQL 查询来计算每个用户的平均每日广告展示次数？

mysql - 如何编写 SQL 查询来计算每个用户的平均每日广告展示次数？

问题描述

这是一个供参考的示例表：

假设我们正在调查每位用户的平均每日广告展示次数。例如，如果两天前 100 位用户每人有 10 次广告展示，而昨天有 50 位不同的用户每人有 40 次广告展示，那么这两天每位用户的平均每日广告展示次数将为 20：

                       2 days ago Yesterday
Users                      100          50
Ad Impressions per User     10          40
Ad Impressions per Day     1000        2000

每个用户的平均每日广告展示次数 ( (100 * 10 + 50 * 40) / 150) = 20

问题是： 现在假设您有一个展示次数表，其中每一行代表展示次数详细信息。列如下：

国家字符串（用户来自的国家）

日期（展示发生的日期）

user_id int（用户的 id）

展示次数 int（展示次数）

1) 仅美国用户的每位用户的平均每日广告展示次数

我的回答：不工作-我认为公式在我的计算中不正确

SELECT (b.all_users) from(
SELECT (imp_date),(SUM(impressions)*COUNT(user_id))all_users FROM jobs
WHERE country='US'
GROUP BY imp_date
) AS b;

2) 仅英国用户的每周平均展示次数 3) 每位用户的每月平均展示次数

标签： mysqlsql

解决方案

缺少一些信息无法正确回答，但我仍然会尝试回答......

对于您的日常要求，您需要：

  • 按天对结果进行分组GROUP BY imp_date
  • 使用此组，您可以进行总和以获得打印总数SUM(imp_count)
  • 当天完成打印的用户数COUNT(DISTINCT(user_id))

现在您可以将平均每日打印SUM(imp_count) / COUNT(DISTINCT(user_id))限制为美国用户WHERE country = 'US'

剩下的就是根据所需的时间段调整其他请求。

您可以在dbfiddle上显示一个小演示

-- DAILY
SELECT imp_date
    ,SUM(imp_count) AS imp_this_day
    ,COUNT(DISTINCT(user_id)) AS users_this_day
    ,( SUM(imp_count) / COUNT(DISTINCT(user_id)) ) AS imp_per_user
FROM   jobs
WHERE  country = 'US'
GROUP  BY imp_date;  

+-------------+---------------+-----------------+--------------+
|  imp_date   | imp_this_day  | users_this_day  | imp_per_user |
+-------------+---------------+-----------------+--------------+
| 2020-01-03  |            1  |              1  |       1.0000 |
| 2020-01-06  |            5  |              1  |       5.0000 |
| 2020-01-13  |           22  |              3  |       7.3333 |
|        ...  |          ...  |            ...  |          ... |
+-------------+---------------+-----------------+--------------+

-- BETWEEN TWO DATE
SELECT SUM(imp_count) AS imp_this_day
    ,COUNT(DISTINCT(user_id)) AS users_this_period
    ,( SUM(imp_count) / COUNT(DISTINCT(user_id)) ) AS imp_per_user
FROM   jobs
WHERE  country = 'US'
    and imp_date>="2020-01-14"
    and imp_date<="2020-01-15";


+------------------+--------------------+--------------+
| imp_this_period  | users_this_period  | imp_per_user |
+------------------+--------------------+--------------+
|              13  |                 2  |       6.5000 |
+------------------+--------------------+--------------+

-- WEEKLY
SELECT WEEK(ANY_VALUE(imp_date)) as week_num
        ,Sum(imp_count) AS imp_this_week
        ,Count(DISTINCT(user_id)) AS users_this_week
        ,( Sum(imp_count) / Count(DISTINCT(user_id)) ) AS imp_per_user
FROM   jobs
WHERE  country = 'US'
GROUP BY week_num; 


+-----------+----------------+------------------+--------------+
| week_num  | imp_this_week  | users_this_week  | imp_per_user |
+-----------+----------------+------------------+--------------+
|        0  |             1  |               1  |       1.0000 |
|        1  |             5  |               1  |       5.0000 |
|        2  |            40  |               4  |      10.0000 |
+-----------+----------------+------------------+--------------+

-- MONTHLY
SELECT MONTH(ANY_VALUE(imp_date)) as month_num
        ,Sum(imp_count) AS imp_this_month
        ,Count(DISTINCT(user_id)) AS users_this_month
        ,( Sum(imp_count) / Count(DISTINCT(user_id)) ) AS imp_per_user
FROM   jobs
WHERE  country = 'US'
GROUP BY month_num; 


+------------+-----------------+-------------------+--------------+
| month_num  | imp_this_month  | users_this_month  | imp_per_user |
+------------+-----------------+-------------------+--------------+
|         1  |             46  |                4  |      11.5000 |
+------------+-----------------+-------------------+--------------+

推荐阅读