带有GROUP BY的MySQL AVG（TIMESTAMPDIFF） [英] MySQL AVG(TIMESTAMPDIFF) with GROUP BY

问题描述

我有两个表 user（one）和 transaction（many），我需要得到平均时间从创建用户到第一次交易时间。我使用的是 AVG（TIMESTAMPDIFF），它运行良好，除了 GROUP BY 对每个用户返回一个平均值而不是交易表中所有唯一用户的单一平均值。如果我删除 GROUP BY ，我会得到一个单一的平均数字，但它考虑到了用户的多个事务，而我只想为每个用户创建一个（他们的第一个）。

这是我的SQL：

  SELECT AVG（TIMESTAMPDIFF（ DAY，u.date_created，t.transaction_date））AS平均值
 FROM transaction t 
 LEFT JOIN用户u ON u.id = t.user_id 
 WHERE t.user_id IS NOT NULL AND t。 status = 1 
 GROUP BY t.user_id; 
  

如果有人能够帮助我仅返回唯一用户的平均值，我将不胜感激。把查询分成两份是很好的做法，但表格很大，所以返回大量数据并将其重新放入是不合格的。

选择AVG（TIMESTAMPDIFF（DAY，S.date_created，S .transaction_date））AS平均值
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

我用一个INNER JOIN替换了LEFT JOIN，因为我认为这就是你想要的，但它不是100％等价于你的 WHERE t.user_id不是NULL 。

如果需要的话，可以随意放置LEFT JOIN。

I have two tables user (one) and transaction (many) and I need to get the average time in days from when a user was created to when they made their first transaction. I'm using AVG(TIMESTAMPDIFF) which is working well, except that the GROUP BY returns an average against every user instead of one single average for all unique users in the transaction table. If I remove the GROUP BY, I get a single average figure but it takes into account multiple transactions from users, whereas I just want to have one per user (the first they made).

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.

解决方案

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.

这篇关于带有GROUP BY的MySQL AVG（TIMESTAMPDIFF）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！