SQL 计算项目状态历史记录在日期范围内的项目项

Question

我有 4 个表:

projects: id, title, current_status_id
statuses: id, label
status_history: project_id, status_id, created_at
messages: id, project_id, body, created_at

在应用程序中，当项目更改状态(例如，从“lead”到“active”再到“complete”)时，将插入一个 status_history 行。请注意，created_at 列是记录更改日期的时间戳。在状态更改之间，项目中正在发生事件并创建消息。例如，项目初始化为“领导”状态，项目处于“领导”状态时创建一些消息，项目更改为“事件”状态，项目处于此状态时创建一些消息, 等等。

我想创建显示以下内容的查询:日期、在“主导”项目中创建的消息数、在“事件”项目中创建的消息数以及具有其他状态的项目中的消息数。这可以在一个查询中完成吗？我正在使用 PostgreSQL。

---

这里有一些伪代码，希望能阐明我正在寻找的东西。

* Start at the earliest date
* Find all projects whose status was 'lead' on that date
* Count the number of created messages from these projects with that date
* Find all projects whose status was 'active' on that date
* Count the number of created messages from these projects with that date
* Find all projects whose status was anything else on that date
* Count the number of created messages from these projects with that date
* ... some projects change status, some stay the same, business happens ...
* Go to next date
* Find all projects whose status was 'lead' on that date
* Count the number of created messages from these projects with that date
* Find all projects whose status was 'active' on that date
* Count the number of created messages from these projects with that date
* Find all projects whose status was anything else on that date
* Count the number of created messages from these projects with that date
* ... some projects change status, some stay the same, business happens ...
* keep doing this until the present

虽然项目确实有一个 current_status_id 列，但它是当前状态，不一定是上个月项目的状态。项目的状态并非每天都在变化 - 并非每天都为每个项目创建一个 status_history 行。

Answer 1

您正在寻找这样的查询...这是 MSSQL，但我认为它与 Postgresql 非常相似，或者您可以简单地在线找到正确的语法。

SELECT count(*) AS 'count', messages.created_at, statuses.label
FROM messages
JOIN projects ON projects.id = messages.project_id
JOIN status_history ON projects.id = status_history.project_id
JOIN statuses ON statuses.id ON status_history.status_id
GROUP BY created_at, statues.label