每天计数时间范围

发布于 2025-01-28 16:47:22 字数 1211 浏览 2 评论 0原文

现在，我有一个表格

create table widgets
(
   id primary key,
   created_at timestamp,
   -- other fields
)

，现在我想要一个查询，该查询每天在多个时间范围之间显示create_at的小部件计数。例如，create_at之间的小部件计数之间的计数在00:00至11:59:59和12:00:00至23:59:59之间的计数。输出看起来像这样：

date           | morning widgets (before noon) | evening widgets (after noon) |
---------------|-------------------------------|------------------------------|
2022-05-01     |            ##                 |           ##                 |
2022-05-02     |            ##                 |           ##                 |
2022-05-03     |            ##                 |           ##                 |
2022-05-04     |            ##                 |           ##                 |
... etc.

到目前为止，我发现我每天可以获得计数：

select created_at::date as created_at_date, count(*) as total
from widgets
where created_at::date >= '2022-05-01' -- where clause for illustration purposes only and not critical to the central question here
group by created_at::date

我正在学习窗口功能，特别是通过分区。我认为这将帮助我获得想要的东西，但不确定。我该怎么做？

我更喜欢“标准SQL”解决方案。如有必要，我在Postgres上，可以使用其SQL风味的任何特定的东西。

原文

I have a table something like this

create table widgets
(
   id primary key,
   created_at timestamp,
   -- other fields
)

Now I want a query that shows the count of widgets with created_at between multiple time ranges for each day. For example, the count of widgets with created_at between 00:00:00 and 11:59:59 and the count between 12:00:00 and 23:59:59. The output would look something like this:

date           | morning widgets (before noon) | evening widgets (after noon) |
---------------|-------------------------------|------------------------------|
2022-05-01     |            ##                 |           ##                 |
2022-05-02     |            ##                 |           ##                 |
2022-05-03     |            ##                 |           ##                 |
2022-05-04     |            ##                 |           ##                 |
... etc.

So far, I figured out I can get counts per day:

select created_at::date as created_at_date, count(*) as total
from widgets
where created_at::date >= '2022-05-01' -- where clause for illustration purposes only and not critical to the central question here
group by created_at::date

I'm learning about windowing functions, specifically partition by. I think this will help me get what I want, but not sure. How do I do this?

I'd prefer a "standard SQL" solution. If necessary, I'm on postgres and can use anything specific to its flavor of SQL.

分享到QQ

分享到微博

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

发布评论

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

伏妖词 2025-02-04 16:47:22

如果我正确理解，我们可以尝试使用条件窗口函数来制作它。

早上小部件（中午之前）：00:00:00至11:59:59
晚上小部件（中午之后）：12:00:00至23:59之间：59

当表达式时，通过情况将条件汇总函数。

SELECT created_at::date,
       COUNT(CASE WHEN created_at >= created_at::date AND created_at <=  created_at::date + INTERVAL '12 HOUR' THEN 1 END) ,
       COUNT(CASE WHEN created_at >= created_at::date + INTERVAL '12 HOUR' AND  created_at <= created_at::date+ INTERVAL '1 DAY' THEN 1 END)
FROM  widgets w
GROUP BY created_at::date
ORDER BY created_at::date

If I understand correctly, we can try to use the condition window function to make it.

morning widgets (before noon) : between 00:00:00 and 11:59:59
evening widgets (after noon) : between 12:00:00 and 23:59:59

put the condition in aggregate function by CASE WHEN expression.

SELECT created_at::date,
       COUNT(CASE WHEN created_at >= created_at::date AND created_at <=  created_at::date + INTERVAL '12 HOUR' THEN 1 END) ,
       COUNT(CASE WHEN created_at >= created_at::date + INTERVAL '12 HOUR' AND  created_at <= created_at::date+ INTERVAL '1 DAY' THEN 1 END)
FROM  widgets w
GROUP BY created_at::date
ORDER BY created_at::date

sqliddle

回复收藏 0 原文

~没有更多了~