MySql 子查询 : average difference, 按列分组

Question

我有一个带有 visitor_id、country、time_of_visit 的 mysql 表。

我想按国家/地区获取平均访问持续时间。

为了获取持续时间，我获取了每个 visitor_id 的最早和最晚 time_of_visit 之间的差异。

所以这让我得到了所有访问的平均持续时间:

    SELECT AVG(duration)
    FROM 
    (
    SELECT TIMESTAMPDIFF(SECOND, MIN(time_of_visit), MAX(time_of_visit))/60 
as duration 
    FROM tracker 
    GROUP BY visitor_id
    ) as tracker

行得通。但要按国家/地区分组，我失败了。这是我最近尝试按国家/地区获取平均持续时间的尝试:

SELECT country, AVG(duration)
FROM 
(
SELECT TIMESTAMPDIFF(SECOND, MIN(time_of_visit), MAX(time_of_visit))/60 
as duration 
FROM tracker 
GROUP BY visitor_id
) as tracker
GROUP BY country

我得到的错误是:“字段列表”中的未知列“国家/地区”。

我认为这应该很简单，但我是菜鸟。我搜索了很多，尝试了很多想法，但都不好。有帮助吗？

提前致谢。

Answer 1

您必须在子查询中选择国家列，然后必须从派生表 tracker.country 中引用国家字段

SELECT tracker.country, AVG(tracker.duration)
FROM 
(
SELECT TIMESTAMPDIFF(SECOND, MIN(time_of_visit), MAX(time_of_visit))/60     
as duration ,country
FROM tracker 
GROUP BY visitor_id
) as tracker
GROUP BY tracker.country

编辑

Using GROUP BY in subselect visitor_id will gives you the record with duplicate data for countries and when using GROUP BY with both visitor_id,country will group the data of countries within the same visitor id, this will only possible if one visitor will belong to more than one countries , if one visitor will belong to only one country i.e one-to-one relation then just use GROUP BY visitor_id