PostgreSQL – 获取统计数据

我需要在我的应用程序中收集一些统计信息.
我有一张用户表(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,(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％的下降).我相信这个“每一个”的方法都是我需要的答案,所以会签署这个答案.

我建议作为一个改进的职位：

>只考虑结果的闭馆日(不收集当天的用户,不计算当前的分工)
>结果是两个数字.
>新研究考虑到一个数据真的每周和每月.

谢谢.

您应该研究 aggregate functions(最小,最大,数量,平均),与 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))
SELECT AVG(user_count) FROM daily_count;

使用“周”而不是每周的计数,等等(参见date_trunc文档).

编辑：(以下评论：平均达到并包括5/1/2012,即在6日之前)

WITH daily_count AS (SELECT date_trunc('day',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

(我将以NOW()替换last_date_time,使之达到现在的平均水平,而不是直到最后一次访问,如果没有最近的访问.)

然后,对于每日,每周和“每月”：

WITH daily_avg AS (
    WITH total_min_max AS (SELECT
            COUNT(id) AS total_visits,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次访问.在2月份的1月280次访问中,您可以访问310次访问.如果不注意,可以认为您已经有了几乎一个访客数量下降10％,二月份出现问题,实际情况并非如此.

PostgreSQL – 获取统计数据

猜你在找的Postgre SQL相关文章