我有一张用户表(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％的下降).我相信这个“每一个”的方法都是我需要的答案,所以会签署这个答案.
我建议作为一个改进的职位：
>只考虑结果的闭馆日(不收集当天的用户,不计算当前的分工)
>结果是两个数字.
>新研究考虑到一个数据真的每周和每月.
谢谢.
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％,二月份出现问题,实际情况并非如此.
