mysql - SQL查询: How to use sub-query or AVG function to find number of days between a new entry?

Question

我有两个表，一个名为 entities ，其中包含以下相关列:id、company_id 和 integration_id。另一个表是 transactions，其中包含 id、entity_id 和 created_at 列。链接两个表的外键是 integration_id 和 entity_id。

transactions 表显示从 entities 表中从每个公司收到的交易数量。

最终，我想找到交易量最高的日期范围，然后从该范围中找到每家公司的交易之间的平均天数。

为了查找日期范围，我使用了此查询。

SELECT DATE_FORMAT(t.created_at, '%Y/%m/%d'), COUNT(t.id)
FROM entities e
JOIN transactions t
ON ei.id = t.entity_id
GROUP BY t.created_at;

我明白了:

Date_FORMAT(t.created_at, '%Y/%m/%d') | COUNT(t.id)
+-------------------------------------+------------
2015/11/09                             4

等等

据此我确定要使用的范围为 2015/11/09 到 2015/12/27我做了这个查询

SELECT company_id, COUNT(t.id)
FROM entities e
INNER JOIN transactions t
ON e.integration_id = t.entity_id
WHERE tp.created_at BETWEEN '2015/11/09' AND '2015/12/27'
GROUP BY company_id;

我明白了:

company_id  | COUNT(t.id)
+-----------+------------
1234          17

等等

这给出了每个公司在该日期范围内进行的总交易量。现在按公司查询交易之间的平均天数的最佳方法是什么？如何对 WHERE 子句中的日期进行子查询或是否可以使用 AVG 函数？

编辑:

尝试一下查询，我想知道是否有办法可以

选择 company_id, (49/COUNT(t.id))...

49，因为这是该日期范围内的天数，为了得到交易之间的平均天数？

我想可能就是这样，这有道理吗？

Answer 1

我认为这可能有效:

Select z.company_id, 
datediff(max(y.created_at),min(created_at))/count(y.id) as avg_days_between_orders,
max(y.created_at) as latest_order,
min(created_at) as earliest_order, 
count(y.id) as orders
From
(SELECT entity_id, max(t.created_at) latest, min(t.created_at) earliest
FROM   entities e, transactions t
Where  e.id = t.entity_id
group by entity_id
order by COUNT(t.id) desc
limit 1) x,
transactions y,
entities z
where z.id = x.entity_id
and z.integration_id = y.entity_id
and y.created_at between x.earliest and x.latest
group by company_id;

没有数据就很难。我可能在外部查询的子查询/连接中引用了不正确的integration_id。