每45天显示记录的PostgreSQL查询

Question

我有一个表格，其中包含用户ID和他们加入的时间戳数据。 如果我需要按月份显示数据，我可以使用以下方法：

select 
 count(user_id), 
 date_trunc('month',(to_timestamp(users.timestamp))::timestamp)::date
from 
 users 
group by 2

日期截断代码允许使用“second”、“day”、“week”等。因此，我可以按这些时间段对数据进行分组。如何按“n-day”周期（例如45天）对数据进行分组？基本上，我需要显示每个45天周期的用户数量。欢迎任何建议或指导！目前我得到的是：

Date           Users
2015-03-01      47
2015-04-01      72
2015-05-01      123
2015-06-01      132
2015-07-01      136
2015-08-01      166
2015-09-01      129
2015-10-01      189

我希望数据以45天为间隔传输。就像这样：-

Date           Users
2015-03-01      85
2015-04-15      157
2015-05-30      192
2015-07-14      229
2015-08-28      210
2015-10-12      294

更新：

我使用如下代码获取输出，但仍存在一个问题，即我得到的值有偏差。

with
new_window as (
select
  generate_series as cohort
  , lag(generate_series, 1) over () as cohort_lag

from
  (
    select
      *
    from
      generate_series('2015-03-01'::date, '2016-01-01', '45 day')
  )
  t
)
select
  --cohort
  cohort_lag -- This worked. !!!
  , count(*)
from
  new_window
join users on
  user_timestamp <= cohort
  and user_timestamp > cohort_lag
group by 1
order by 1

但是我得到的输出是：

Date           Users
2015-04-15      85
2015-05-30      157
2015-07-14      193
2015-08-28      225
2015-10-12      210

基本上，2015年3月1日显示的用户应该是2015年3月1日至2015年4月15日之间的用户，依此类推。

但是我似乎获得了截止到某个日期的用户价值。也就是说：截至2015年4月15日，有85个用户。这不是我想要的结果。需要帮助吗？

- gga

---

请问您能否展示一下您想要的结果图片？ - Monty

展示程序相关内容的样例输入及其预期输出。 - Vivek S.

最新的编辑解决了我的问题。感谢大家的帮助。 - gga

Answer 1

Try this query :

SELECT to_char(i::date,'YYYY-MM-DD') as date, 0 as users 
FROM generate_series('2015-03-01', '2015-11-30','45 day'::interval) as i;

OUTPUT :

date        users
2015-03-01    0
2015-04-15    0
2015-05-30    0
2015-07-14    0
2015-08-28    0
2015-10-12    0
2015-11-26    0

- Monty

---

如果我使用这个序列来生成我的序列，我会得到用户的累积总数，而不是在45天范围内的分别总数。 - gga

Answer 2

这看起来有点混乱，最好放在一个函数中包装一下，这样你可以使用一些变量，但是像这样的东西会起作用吗？

with number_of_intervals as (
  select
    min (timestamp)::date as first_date,
    ceiling (extract (days from max (timestamp) - min (timestamp)) / 45)::int as num
  from users
),
intervals as (
  select
    generate_series(0, num - 1, 1) int_start,
    generate_series(1, num, 1) int_end
  from number_of_intervals
),
date_spans as (
  select
    n.first_date + 45 * i.int_start as interval_start,
    n.first_date + 45 * i.int_end as interval_end
  from
    number_of_intervals n
    cross join intervals i    
)
select
  d.interval_start, count (*) as user_count
from
  users u
  join date_spans d on
    u.timestamp >= d.interval_start and
    u.timestamp <  d.interval_end
group by
  d.interval_start
order by
  d.interval_start

使用这个样本数据：

User Id     timestamp       derived range   count
1           3/1/2015        3/1-4/15    
2           3/26/2015       "   
3           4/4/2015        "   
4           4/6/2015        "               (4)
5           5/6/2015        4/16-5/30   
6           5/19/2015       "               (2)
7           6/16/2015       5/31-7/14   
8           6/27/2015       "   
9           7/9/2015        "               (3)
10          7/15/2015       7/15-8/28   
11          8/8/2015        "   
12          8/9/2015        "   
13          8/22/2015       "   
14          8/27/2015       "               (5)

这是输出结果：

2015-03-01      4
2015-04-15      2
2015-05-30      3
2015-07-14      5

- Hambone