sql - PostgreSQL - 获取统计数据

Question

我需要在我的应用程序中收集一些统计信息。我有一个用户表(tb_user)每次有新用户访问该应用程序时，它都会在该表中添加一条新记录，即每个用户一行。主要字段是id 和date_hour(用户第一次访问该应用程序的时间戳)。

tb_user

id (bigint) | date_time (timestamp with time zone)
 1          |  2012-01-29 11:29:50.359-03
 2          |  2012-01-31 14:27:10.359-03

我需要得到:

按日、周和月划分的平均用户数量

例子:

按天计算:55.45

按周:XX.XX

月:XX.XX

编辑:

我最好的解决方案是:

WITH daily_count AS (SELECT COUNT(id) AS user_count FROM tb_user)
SELECT user_count, tbaux2.days, (user_count/tbaux2.days) FROM daily_count, 
    (SELECT EXTRACT(DAY FROM (t2.diff) ) + 1 AS days
     FROM
       (with tbaux AS(SELECT  min(date_time) AS min FROM tb_user)
       SELECT (now() - min) AS diff
       FROM tbaux) AS t2) AS tbaux2
GROUP BY user_count, tbaux2.days

但此解决方案仅适用于 EXTRACT (DAY ... 周和月无效

欢迎任何帮助。

或者:

SELECT user_count, tbaux2.days, (user_count/tbaux2.days) AS userPerDay, ((user_count/tbaux2.days) * 7) AS userPerWeek, ((user_count/tbaux2.days) * 30) AS userPerMonth

编辑 2:

根据@Bruno 的回复，有一些注意事项:

当我问这个问题时，实际上我要求的是一种按日、月和年选择数据的方法。我相信我发布的搜索和@Bruno 改进的搜索应该被解释为“一天、每 7 天和每 30 天” 的平均值，而不是按天、周和月。我相信如果这样解释，就不会出现example中引用性别的问题(下降10%)。我相信这种“每个”的方法是我现在需要的答案，所以会在这个答案上签名。

我建议作为帖子的改进:

• 结果只考虑关闭日(不收集当天的用户，划分时不计入当天)
• 结果是两位数字。
• 新研究考虑了每周和每月的数据。

谢谢。

Answer 1

你应该看看aggregate functions (min, max, count, avg)，与 GROUP BY 齐头并进.对于基于日期的聚合，date_trunc也很有用。

例如，这将返回每天的行数:

SELECT date_trunc('day', date_time) AS day_start,
       COUNT(id) AS user_count FROM tb_user
    GROUP BY date_trunc('day', date_time);

然后您可以使用类似这样的方法(使用 a CTE )来计算每日平均值:

WITH daily_count AS (SELECT date_trunc('day', date_time) AS day_start,
       COUNT(id) AS user_count FROM tb_user
    GROUP BY date_trunc('day', date_time))
SELECT AVG(user_count) FROM daily_count;

使用 'week' 而不是 day 进行每周计数等(参见 date_trunc 文档)。

编辑:(以下评论:2012 年 5 月 1 日(含)之前的平均值，即 6 日之前。)

WITH daily_count AS (SELECT date_trunc('day', date_time) AS day_start,
       COUNT(id) AS user_count
    FROM tb_user
       WHERE date_time >= DATE('2012-01-01') AND date_time < DATE('2012-01-06') 
    GROUP BY date_trunc('day', date_time))
SELECT SUM(user_count)/(DATE('2012-01-06') - DATE('2012-01-01')) FROM daily_count;

在这种情况下，上面的内容过于复杂。这应该会给你相同的结果:

SELECT COUNT(id)/(DATE('2012-01-06') - DATE('2012-01-01'))
    FROM tb_user
       WHERE date_time >= DATE('2012-01-01') AND date_time < DATE('2012-01-06');

编辑 2: 在您编辑之后，我猜您想要的只是数据库整个存在期间的单一全局平均值，而不是按月/周/天分组。

这应该为您提供每天的平均行数:

WITH total_min_max AS (SELECT
        COUNT(id) AS total_visits,
        MIN(date_time) AS first_date_time,
        MAX(date_time) AS last_date_time,
    FROM tb_user)
SELECT total_visits/((last_date_time::date-first_date_time::date)+1) AS users_per_day
    FROM total_min_max

(如果最近没有访问，我会将 last_date_time 替换为 NOW() 以计算到现在为止的时间平均值，而不是直到上次访问为止。)

然后，对于每日、每周和“每月”:

WITH daily_avg AS (
    WITH total_min_max AS (SELECT
            COUNT(id) AS total_visits,
            MIN(date_time) AS first_date_time,
            MAX(date_time) AS last_date_time,
        FROM tb_user)
    SELECT total_visits/((last_date_time::date-first_date_time::date)+1) AS users_per_day
        FROM total_min_max)
SELECT
         users_per_day,
         (users_per_day * 7) AS users_per_week,
         (users_per_month * 30) AS users_per_month
    FROM daily_avg

话虽这么说，但您从此类统计数据中得出的结论可能不是很好，尤其是如果您想了解它是如何变化的。

我还会将每天的数据标准化，而不是假设一个月有 30 天(如果不是每小时，因为并非所有的日子都有 24 小时)。假设您在 2011 年 1 月每天有 10 次访问，而在 2011 年 2 月每天有 10 次访问。这使您在 1 月有 310 次访问，在 2 月有 280 次访问。如果您不注意，您可能会认为您几乎访客数量下降了 10%，所以 2 月份出了点问题，但实际上情况并非如此。