如何使用 oracle sql 获取自前几个月以来 user_id 的增量
假设我有一个包含“user_id，loaddate”的表，并且许多用户都在 user_id 中，但每个月可能会添加或删除一些用户。我如何比较以找出上个月添加或退出的人员？我想每个月都会比上个月有增量。
Let's say I have a table that has "user_id, loaddate", and many users are in user_id, but some may be added or removed at each month. How can i compare to find out who has been added or dropped out to the previous month´s? I suppose it would be getting a delta over the previous month for each and every month.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
假设 LOADDATE 代表将用户添加到系统的日期，那么新手的增量很容易计算。问题在于找到被删除的用户。默认情况下没有办法做到这一点。
执行此操作的常见方法是维护感兴趣表上的活动的审核或历史记录，通常通过使用由 DML 触发器填充的并行历史记录表来实现。
更昂贵的方法是购买 Oracle 价格适中但非常简洁的 Total Recall 产品（又名闪回数据库）。 了解更多。
Presuming LOADDATE represents the date when a user was added to your system, then the delta of the newbies is easy to calculate. The wrinkle is finding the users who have been removed. There is no way to do this by default.
The common way to do this is to maintain an audit or history of the activity on tables of interest, usually by having a parallel history table which is populated by DML triggers.
The more expensive way of doing this is to splash out for Oracle's moderatley expensive - but highly neat - Total Recall product (AKA Flashback database). Find out more.
要了解已添加的用户，您可以查找加载日期在上个月内的 user_ids。 （或者如果用户可以有多个加载日期，则加载日期都在上个月内的 user_ids ）。 Oracle 数据库没有以前状态的记忆，因此无法查找数据库中曾经存在但现在已不存在的内容。也许您可以通过查找上个月内没有加载日期的用户来获取退出的用户，具体取决于您的表的工作方式。 （用户退出后你们会删除他们吗？）
To find out who has been added, you could look for user_ids whose loaddate is within the last month. (Or user_ids whose loaddates are all within the last month, if a user can have multiple loaddates). Oracle databases have no memory of their previous state, so there is no way to look for things that used to be in the database but aren't anymore. Maybe you could get users who dropped out by looking for users who have no loaddate within the last month, depending on exactly how your table works. (Do you delete users when they drop out?)