代码之家 › 专栏 › 技术社区 › Philip Reynolds

SQL-状态机-基于变更集的历史数据报告

postgresql sql

Philip Reynolds · 技术社区 · 16 年前

我想记录用户状态,然后能够根据我们保存的更改记录历史性地报告。我正试图在SQL(使用PostgreSQL)中实现这一点,并且我有一个用于记录用户更改的建议结构,如下所示。

CREATE TABLE users (
  userid SERIAL NOT NULL PRIMARY KEY, 
  name VARCHAR(40), 
  status CHAR NOT NULL
);

CREATE TABLE status_log (
  logid SERIAL, 
  userid INTEGER NOT NULL REFERENCES users(userid), 
  status CHAR NOT NULL, 
  logcreated TIMESTAMP
);

这是我根据数据提出的表结构。

对于状态字段,“a”表示活动用户,“s”表示挂起的用户,

INSERT INTO status_log (userid, status, logcreated) VALUES (1, 's', '2008-01-01'); 
INSERT INTO status_log (userid, status, logcreated) VALUES (1, 'a', '2008-02-01');

因此,该用户在1月1日被暂停,2月1日再次活跃。

如果我想在2008年1月15日得到一个暂停的客户列表,那么userid 1应该会出现。如果我在2008年2月15日得到一个暂停的客户列表,那么userid 1就不应该出现。

1)这是构造此类查询的数据的最佳方法吗?

2)如何查询此结构或您建议的修改结构中的数据,以便我可以简单地确定日期(例如1月15日),并仅在SQL中查找该日期具有活动状态的客户列表?这是SQL的工作吗?

4 回复 | 直到 16 年前

Tony Andrews 16 年前

这是可以做到的,但是如果您存储每个日志的结束日期,效率会更高。对于您的模型,您必须执行以下操作:

select l1.userid
from status_log l1
where l1.status='s'
and l1.logcreated = (select max(l2.logcreated)
                     from status_log l2
                     where l2.userid = l1.userid
                     and   l2.logcreated <= date '2008-02-15'
                    );

在附加的列中,它更像是:

select userid
from status_log
where status='s'
and logcreated <= date '2008-02-15'
and logsuperseded >= date '2008-02-15';

(很抱歉有语法错误,我不知道PostgreSQL。)

为了解决菲尔提出的一些进一步问题:

用户可能会从活动、挂起、取消、再次活动。这是一个简化的版本,实际上有更多的状态,人们可以直接从一个状态移动到另一个状态。

这将出现在表格中,如下所示:

userid  from       to         status
FRED    2008-01-01 2008-01-31 s
FRED    2008-02-01 2008-02-07 c
FRED    2008-02-08            a

我对当前记录的“结束”日期使用了空值。我本可以使用一个未来的日期,比如2999-12-31,但在某些方面,空值更可取。

另外,当前状态也没有“结束日期”,所以我认为这会稍微打断您的查询?

是的,我的查询必须重新编写为

select userid
from status_log
where status='s'
and logcreated <= date '2008-02-15'
and (logsuperseded is null or logsuperseded >= date '2008-02-15');

这种设计的一个缺点是,每当用户的状态发生变化时,您必须结束他们当前状态的日志,并创建一个新的日志。不过,这并不难,我认为查询的优势可能会超过这一点。

David Aldridge 16 年前

Postgres是否支持分析查询?这将使活跃用户在2008-02-15

select userid
from
(
select logid, 
       userid, 
       status, 
       logcreated,
       max(logcreated) over (partition by userid) max_logcreated_by_user
from   status_log
where  logcreated <= date '2008-02-15'
)
where  logcreated = max_logcreated_by_user
  and  status     = 'a'
/

Philip Reynolds 16 年前

@托尼,“结束”日期不一定适用。

用户可能会从活动、挂起、取消、再次活动。这是一个简化的版本,实际上有更多的状态,人们可以直接从一个状态移动到另一个状态。

另外,当前状态也没有“结束日期”,所以我认为这会稍微打断您的查询?

JeremyDWill 16 年前

@ Phil

我喜欢托尼的解决方案。它似乎最恰当地模拟了所描述的情况。任何特定的用户在给定的时间段(一分钟、一小时、一天等)内都有一个状态,但它是一个持续时间,而不是一个即时时间。因为您想知道在某个时间段内谁是活跃的,所以将信息建模为一个持续时间似乎是最好的方法。

我不确定附加状态是否有问题。如果某人处于活动状态,然后被挂起,然后被取消,然后再次处于活动状态,那么每个状态都将适用于给定的持续时间,是不是?这可能是非常短的持续时间,例如几秒钟或一分钟,但它们仍然会持续一段时间。

你是否担心一个人的状态在一天内会发生多次变化,但你想知道在一天内谁是活跃的?如果是这样,那么您只需要更具体地定义在给定的一天中活动意味着什么。如果足够让他们在一天中的任何时候都保持活跃,那么托尼的回答也会很有效。如果它们必须在给定的一天中活动一定的时间,那么可以修改Tony的解决方案,以简单地确定时间长度(小时、分钟或天),并在WHERE子句中添加进一步的限制,以检索处于该状态的正确日期、状态和时间长度。

至于当前状态没有“结束日期”,只要结束日期可以为空,这也没问题。只需使用类似这样的“其中enddate<='2008-08-15'或enddate为空”。