如何在2个日期（1年）之间每周生成一个系列
我的数据库中有一个登录历史表，每当有人成功登录我的应用程序时。
我正在尝试创建一个视图，该视图将从730天前返回每周（每7天）到365天之前返回主动用户登录的计数。基本上是在过去365天之前的365天。因此，例如，当前日期是2022年4月21日。我想在2020年4月21日至2021年4月21日至4月20日之间生成一系列整数1-53（53而不是52）每个整数代表周数。因此，仅在“ WW”日期对to_char进行to_char行不通。在这几周中，引进需要执行计数的需求也是一个挑战，我似乎无法弄清楚如何编写视图查询。
登录历史表的一个非常简化的版本基本上是以下
user_id | logged_in_on
---------|---------------------
1 | 2020-04-21 13:22:14
2 | 2020-04-21 17:38:11
1 | 2021-04-28 16:32:21
5 | 2021-04-18 04:33:45
内容
week | login_count
------|-------------
1 | 2
2 | 1
3 | 0
.
.
.
51 | 0
52 | 1
我的 前至365天前。它不一定要包含一周的数字。它也可以是包含每周开始和结束日期的2列。只要视图中的每个记录代表一个星期对我来说都是可以的。
是否有可能创建能够返回此数据的单个查询？
I have a login history table in my database that records every time someone successfully logs in to my app.
I am trying to create a view that will return counts of active user logins by week (Every 7 days) from 730 days ago to 365 days ago. Basically, 365 days before the last 365 days. So for example, the current date is April 21, 2022. I'd like to generate a series of integers 1-53 (53 instead of 52 because of the 1 extra day) between the dates April 21, 2020 and April 20, 2021. Each integer represents the week number. Because of this, simply doing a to_char on the dates with 'WW' won't work. Bringing in the need to perform counts for each of these weeks is also a challenge, and I can't seem to figure out how to write the view query.
A very simplified version of my login history table basically looks like the following
user_id | logged_in_on
---------|---------------------
1 | 2020-04-21 13:22:14
2 | 2020-04-21 17:38:11
1 | 2021-04-28 16:32:21
5 | 2021-04-18 04:33:45
So the results I would like the view to contain should look something like the following
week | login_count
------|-------------
1 | 2
2 | 1
3 | 0
.
.
.
51 | 0
52 | 1
The resulting view should contain a record for each week, or period of 7 days, from 730 days ago to 365 days ago. It doesn't necessarily have to contain week numbers. It can also be 2 date columns that contain the start and end dates of each week. As long as each record in the view represents a week would be fine for me to use.
Is it even possible to create a single query that's able to return this data?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我发现了。以下查询应产生一系列日期，每个日期代表每周（7天）的第一个日期，从730天前开始，并于365天以前结束。
I figured it out. The following query should generate a series of dates, each date representing the first date of each set of a week (7 days) starting from 730 days ago and ending at 365 days ago from the current date.