Postgres上的Date聚合函数

I'm kind of rusty on my SQL, maybe you can help me out on this query.

我的SQL有点生气,也许你可以帮我解决这个问题。

I have these two tables for a tickets system (I'm omitting some fields):

我有这两个表用于票务系统(我省略了一些字段):

table tickets

id - bigint
subject - text
user_id - bigint
closed - boolean
first_message - bigint

(foreign key, for next table's id)

(外键,用于下一个表的id)

last_message - bigint

(same as before)

(和之前一样)

table ticket_messages

creation_date

I need to query the closed tickets, and make an average of the time spent between the first message creation_date and the last message creation_date. This is what I've done so far:

我需要查询已关闭的票证,并平均在第一个消息creation_date和最后一个消息creation_date之间花费的时间。这是我到目前为止所做的:

SELECT t.id, t.subject, tm.creation_date
FROM tickets AS t
INNER JOIN ticket_messages AS tm
ON tm.id = t.first_message
OR tm.id = t.last_message
WHERE t.closed = true

I'm looking for some group by or aggregate function to get all the data from the table, and try to calculate the time spent between last and first, also trying to display the dates for the first and last message.

我正在寻找一些group by或aggregate函数来从表中获取所有数据,并尝试计算在last和first之间花费的时间,同时尝试显示第一条和最后一条消息的日期。

UPDATE I added an inner Join with the second table instead of "OR", now I get both dates, and I can find the sum from my application:

更新我用第二个表而不是“OR”添加了内部Join,现在我得到两个日期,我可以从我的应用程序中找到总和:

SELECT t.id, t.subject, tm.creation_date, tm2.creation_date
FROM tickets AS t
INNER JOIN ticket_messages AS tm
ON tm.id = t.first_message
INNER JOIN ticket_messages as tm2
ON tm2.id = t.last_message
WHERE t.closed = true

I think that did it...

我认为那样做了......

2 个解决方案

#1

Something like this should do for getting the nr of days elapsed. You might need to put this in a subquery to easily pull out more fields from 'tickets'.

这样的事情应该可以让你的日子过去了。您可能需要将其放在子查询中,以便从“票证”中轻松提取更多字段。

SELECT t.id,AVG(tlast.creation_date - tfirst.creation_date)
  FROM tickets AS t
INNER JOIN ticket_messages AS tfirst
  ON tm.id = t.first_message
INNER JOIN ticket_messages AS tlast
  ON tm.id = t.last_message
WHERE t.closed = true
GROUP BY t.id

Which might lead to(not tested..) e.g.

这可能导致(未经测试......),例如

select t.id,t.subject,sub.nr_days 
FROM (
  SELECT t.id,AVG(tlast.creation_date - tfirst.creation_date) as nr_days
    FROM tickets AS t
  INNER JOIN ticket_messages AS tfirst
    ON tm.id = t.first_message
  INNER JOIN ticket_messages AS tlast
    ON tm.id = t.last_message
  WHERE t.closed = true
  GROUP BY t.id ) AS sub
INNER JOIN tickets AS t
  ON sub.id = t.id;

#2

You are trying to combine two queries into one and trying to get the data from three rows of data from two tables. Both need to be fixed.

您正在尝试将两个查询合并为一个,并尝试从两个表中的三行数据中获取数据。两者都需要修复。

First of all, you should not attempt to mix aggregate data (such as averages) with the details for single items - you need separate queries for that. You can do it, but the output is repetitious and therefore wasteful (all the single items in a group will have the same aggregate data).

首先,您不应尝试将汇总数据(例如平均值)与单个项目的详细信息混合在一起 - 您需要单独查询。您可以这样做,但输出是重复的,因此是浪费的(组中的所有单个项目将具有相同的聚合数据)。

Secondly, you need to find the first message and the last message for a given ticket. Hence, that query is:

其次,您需要查找给定故障单的第一条消息和最后一条消息。因此,该查询是:

SELECT t.id, t.subject, tm1.creation_date as start, tm2.creation_date as end,
       tm2.creation_date - tm1.creation_date as close_interval
  FROM            tickets AS t
       INNER JOIN ticket_messages AS tm1 ON t.last_message = tm1.id
       INNER JOIN ticket_messages AS tm2 ON t.last_message = tm2.id
 WHERE t.closed = true

This gives you three rows of data per result row - as required. The computed value should be an interval type - assuming that PostgreSQL actually has that type. (In Informix, the type would effectively be INTERVAL DAY(n) for a suitable n, such as 9.)

这为每个结果行提供了三行数据 - 根据需要。计算出的值应该是一个区间类型 - 假设PostgreSQL实际上有这种类型。 (在Informix中,对于合适的n,类型实际上是INTERVAL DAY(n),例如9.)

You can average those intervals, now. You can't average dates because dates cannot be added together and cannot be divided; averaging involves both summing and dividing. Intervals can be added and divided.

您现在可以平均这些间隔。您无法平均日期,因为日期不能一起添加且无法分割;平均包括求和和除法。间隔可以添加和分割。

#1