mysql - 用双 GROUP BY 计算 AVG

Question

以下 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 分组的平均 sessionTimes:

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

它不是为每个 bowserSessionId 计算不同的值，而是计算 vist 表中 browserSession 每次出现的平均 sessionTime:

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 值？

Answer 1

使用子查询:

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;