如何在 PostgreSQL 中按几天进行分组？

发布于 2024-11-26 17:02:33 字数 635 浏览 3 评论 0原文

以下代码生成日期并按天计算记录。

SELECT ts, COUNT(DISTINCT(user_id)) FROM 
( SELECT current_date + s.ts FROM generate_series(-20,0,1) AS s(ts) )
AS series(ts)
LEFT JOIN messages
ON messages.created_at::date = ts
GROUP BY ts
ORDER BY ts

输出如下所示：

2011-07-07   0
2011-07-08   0
2011-07-09   0
2011-07-10   0
2011-07-11   0
2011-07-12   94
2011-07-13   56
2011-07-14   35
2011-07-15   56
2011-07-16   0
2011-07-17   13

如何将其修改为按 2 天分组，以便结果重叠？它不会计算每天的不同 user_id，而是每 2 天的时间段计算不同的 user_id。

这与对 2 天的计数求和不同，因为 user_id 应该在每 2 天的时间段内仅计数一次。

在 PostgreSQL 8.3 中工作。

谢谢。

原文

The following code generates dates and counts records by day.

SELECT ts, COUNT(DISTINCT(user_id)) FROM 
( SELECT current_date + s.ts FROM generate_series(-20,0,1) AS s(ts) )
AS series(ts)
LEFT JOIN messages
ON messages.created_at::date = ts
GROUP BY ts
ORDER BY ts

The output looks like:

2011-07-07   0
2011-07-08   0
2011-07-09   0
2011-07-10   0
2011-07-11   0
2011-07-12   94
2011-07-13   56
2011-07-14   35
2011-07-15   56
2011-07-16   0
2011-07-17   13

How would you modify it to group by 2 days, so that the results overlap? Instead of counting the distinct user_id's for each day, it would count the distinct user_id's for each 2 day period.

This is different from summing the counts of the 2 days, as the user_id should be counted only once for each 2 day period.

Working in PostgreSQL 8.3.

Thanks.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

心碎无痕… 2024-12-03 17:02:33

SELECT ts, COUNT(DISTINCT(user_id)) FROM 
( SELECT current_date + s.ts FROM generate_series(-20,0,1) AS s(ts) )
AS series(ts)
LEFT JOIN messages
ON messages.created_at::date between ts - 1 and ts -- JOIN on a range
GROUP BY ts
ORDER BY ts

SELECT ts, COUNT(DISTINCT(user_id)) FROM 
( SELECT current_date + s.ts FROM generate_series(-20,0,1) AS s(ts) )
AS series(ts)
LEFT JOIN messages
ON messages.created_at::date between ts - 1 and ts -- JOIN on a range
GROUP BY ts
ORDER BY ts

回复收藏 0 原文

顾挽 2024-12-03 17:02:33

试试这个：

SELECT ts, COUNT(DISTINCT(user_id)) 

FROM 

( SELECT current_date + s.ts 
  FROM generate_series(-20,0,2) AS s(ts) ) AS series(ts)

LEFT JOIN messages
ON messages.created_at::date = ts or messages.created_at::date = ts + 1

GROUP BY ts
ORDER BY ts

Try this:

SELECT ts, COUNT(DISTINCT(user_id)) 

FROM 

( SELECT current_date + s.ts 
  FROM generate_series(-20,0,2) AS s(ts) ) AS series(ts)

LEFT JOIN messages
ON messages.created_at::date = ts or messages.created_at::date = ts + 1

GROUP BY ts
ORDER BY ts

回复收藏 0 原文

~没有更多了~