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天”的
平均值，而不是几天，几周和几个月的时间。我相信，如果以这种方式进行解释，示例中不会出现性别引用的问题（下降10％）。我相信这种“每一个”方法都是我需要的答案，因此请在此答案上签名。

我建议作为帖子的改进：

• 仅考虑结果中的休息日（不收集当日的用户，也不计算除法中的当日）
• 结果是两个数字。
• 新的研究实际上考虑了每周和每月的数据。

谢谢。

Answer 1

您应该查看汇总函数（最小值，最大值，计数，平均值），它们与并驾齐驱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);

然后，您可以使用以下方法（使用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'而不是一天作为每周计数，依此类推（请参阅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月份出了点问题，实际上并非如此。