mysql - 用两倍GROUP BY计数AVG

以下SQL查询：

SELECT DISTINCT b.browserSessionId, v.visitDay, b.sessionTime
FROM visit AS v
INNER JOIN bsession AS b
ON v.browserSessionId=b.browserSessionId

给我这个结果：

browserSessionId    visitDay    sessionTime
100                 20150914    150
101                 20150914    100
102                 20150914    50
103                 20150915    75
104                 20150915    150
114                 20150915    225

我想得到的结果是按visitDay分组的平均会话时间：

visitDay    sessionTime
20150914    50
20150915    75

此查询给出了错误的结果：

SELECT DISTINCT b.browserSessionId, v.visitDay, AVG(b.sessionTime)
FROM visit AS v
INNER JOIN bsession AS b
ON v.browserSessionId=b.browserSessionId
GROUp BY v.visitDay

browserSessionId    visitDay    AVG(b.sessionTime)
100                 20150914    100.0000
103                 20150915    142.5000

它计算vist表中每次出现browserSession的平均会话时间，而不是每个bowserSessionId的不同值：

SELECT b.browserSessionId, v.visitDay, b.sessionTime
FROM visit AS v
INNER JOIN bsession AS b
ON v.browserSessionId=b.browserSessionId

browserSessionId    visitDay    sessionTime
100                 20150914    150
100                 20150914    150
100                 20150914    150
101                 20150914    100
101                 20150914    100
101                 20150914    100
101                 20150914    100
102                 20150914    50
102                 20150914    50
102                 20150914    50
103                 20150915    75
103                 20150915    75
103                 20150915    75
103                 20150915    75
104                 20150915    150
104                 20150915    150
104                 20150915    150
114                 20150915    225
114                 20150915    225
114                 20150915    225

如何使SQL只计算sessionTime的不同值？

最佳答案

使用子查询：

SELECT t.browserSessionId, t.visitDay, AVG(t.sessionTime)
FROM
(
   SELECT DISTINCT b.browserSessionId, v.visitDay, b.sessionTime
   FROM visit AS v
   INNER JOIN bsession AS b
      ON v.browserSessionId=b.browserSessionId
) AS t
GROUP BY t.browserSessionId, t.visitDay;

或取决于聚合级别

SELECT t.visitDay, AVG(t.sessionTime)
FROM
(
   SELECT DISTINCT b.browserSessionId, v.visitDay, b.sessionTime
   FROM visit AS v
   INNER JOIN bsession AS b
      ON v.browserSessionId=b.browserSessionId
) AS t
GROUP BY t.visitDay;

关于mysql - 用两倍GROUP BY计数AVG，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/32560846/