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

Answer 2

尝试以下操作。

将"lead"one_answers"active"替换为这两种状态的状态ID。

请注意，选择的第一个字段是将created_at时间戳转换为日期值（删除时间）。

提供的计数显示了新创建的具有这些状态的项目的数量。它们不包括已经存在但在给定日期更改为这些状态的项目。这是通过不存在的子查询完成的。

select      date(created_at) as dt
            , sum(case when sh.status_id = 'lead' then 1 else 0 end) as num_lead
            , sum(case when sh.status_id = 'active' then 1 else 0 end) as num_active
            , sum(case when sh.status_id not in ('lead','active') then 1 else 0 end) as num_else
from        status_history sh
where       not exists
                    (   select  1
                        from    status_history x
                        where   x.project_id = sh.project_id
                            and x.created_at < sh.created_at    )
group by    date(created_at)
order by    1

Answer 3

关于：

SELECT to_char(tmp.date, 'YYYY-MM-DD') as date, COUNT(tmp.status = 'lead') as num_lead,  COUNT(tmp.status = 'active') as num_active FROM 
(
    SELECT m.created_at AS date, COUNT(m.id) as messages, s.label as status FROM messages AS m
    INNER JOIN project AS p ON p.id = m.project_id
    INNER JOIN statuses AS s ON s.id = p.current_status_id
    GROUP BY m.created_at, s.id, s.label
) as tmp
GROUP BY tmp.date;

分组应该是100%正确的（因为不清楚一个id是否属于一个文本表示，标签不是primary_key！）

临时表包含"Messages per date and project_status_label"的所有关系，外部选择函数只更改维度。