mysql avg group,带有GROUP BY的MySQL AVG（TIMESTAMPDIFF）

最新推荐文章于 2024-09-24 06:12:25 发布

不知者无胃口

最新推荐文章于 2024-09-24 06:12:25 发布

阅读量371

点赞数

文章标签： mysql avg group

本文探讨了如何通过SQL查询来获取从用户创建到首次交易的平均天数。作者使用了TIMESTAMPDIFF函数来计算时间差，并通过内连接确保只考虑每个用户的首次交易。最终的目标是得到所有唯一用户首次交易的平均时间。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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.