MySQL AVG(TIMESTAMPDIFF) 与 GROUP BY

发布于 2024-12-02 08:36:08 字数 625 浏览 0 评论 0原文

我有两个表 user (one) 和 transaction (many)，我需要获取从创建用户到进行第一笔交易的平均时间（以天为单位）。我正在使用 AVG(TIMESTAMPDIFF) ，它运行良好，除了 GROUP BY 返回每个用户的平均值，而不是 中所有唯一用户的单个平均值。代码>交易表。如果我删除GROUP BY，我会得到一个平均数字，但它考虑了用户的多个交易，而我只想为每个用户提供一个交易（他们进行的第一个交易）。

这是我的 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 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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

泪眸﹌ 2024-12-09 08:36:08

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

我将 LEFT JOIN 替换为 INNER JOIN，因为我认为这就是您想要的，但它并不 100% 等同于您的 WHERE t.user_id IS NOT NULL。
如果需要，请随意放回 LEFT JOIN。

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.

回复收藏 0 原文

晨与橙与城 2024-12-09 08:36:08

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;

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;

回复收藏 0 原文

~没有更多了~