Question

我有两个表格<代码>用户(一个)和transaction (many),我需要从创建用户到进行第一次交易的平均时间。使用<代码>AVG(TIMESTAMPDIFF)的Im 运行良好,但<代码>除外。由<<>条/代码>分类的组别,在<条码>中,每个用户平均回报一次,而不是所有独特用户的单一平均数。删除<代码> 按分类,我获得一个单一的平均数字,但考虑到用户的多项交易,而我只希望每个用户有一次(第一次)。

Here s my SQL:

SELECT AVG(TIMESTAMPDIFF(DAY, u.date_created, t.transaction_date)) AS average
FROM transaction t
LEFT JOIN user u ON u.id = t.user_id
WHERE t.user_id IS NOT NULL AND t.status = 1
GROUP BY t.user_id;

I d appreciate it if someone can help me return the average for unique users only. It s fine to break the query down into two, but the tables are large so returning lots of data and putting it back in is a no-go. Thanks in advance.

Answer 1

SELECT AVG(TIMESTAMPDIFF(DAY, S.date_created, S.transaction_date)) AS average 
FROM (
  SELECT u.date_created, t.transaction_date 
  FROM transaction t 
  INNER JOIN user u ON u.id = t.user_id 
  WHERE t.status = 1 
  GROUP BY t.user_id
  HAVING u.date_created = MIN(u.date_created)
) s

I replaced the LEFT JOIN with an INNER JOIN because I think that s what you want, but it s not 100% equivalant to your WHERE t.user_id IS NOT NULL.
Feel free to put the LEFT JOIN back if need be.

Answer 2

select avg( TIMESTAMPDIFF(DAY, u.date_created, min_tdate) ) as average
from user u
inner join 
(select t.user_id, min(t.transaction_date) as min_tdate
 from transaction t
 where t.status=1;
 group by t.user_id
) as min_t
on u.id=min_t.user_id;

友情链接