php - 使用 SQL 仅计算最近的行

Question

我正在尝试构建一个分析页面，现在我遇到了一个问题；用户根据他们的 session 访问页面，如果该 session 过期，则会为他们提供一个新的页面。

我正在尝试使用此查询确定一种方法来计算上次 session 在特定页面上的用户数量:

SELECT DISTINCT (SELECT MAX(session) FROM analytics b WHERE a.session=b.session) as session,(SELECT MAX(DISTINCT location) FROM analytics c WHERE c.session=a.session) as locale FROM analytics a

该查询将返回如下结果:

session |           location            
------------------------------------------
1       | http://random-page.io/index.html -- Same session, first entry          
1       | http://random-page.io/index.html -- Same session, second entry        
1       | http://random-page.io/index.html -- Same session, last entry   <- What We're trying to Count        
2       | http://random-page.io/index.html -- Same session, first entry        
2       | http://random-page.io/index.html -- Same session, last entry <- What We're trying to Count          
3       | http://random-page.io/index.html -- One session, presumably serves as last and first entry.. but last is what matters  <- What We're trying to Count
4       | http://random-page.io/drafts.html -- One Session  <- What We're trying to Count
5       | http://random-page.io/elements.html -- One session  <- What We're trying to Count

我想要做的是能够只计算 session 结束的行数，并截断所有重复结果(通过使用 GROUP BY 和 COUNT)，以便我的查询返回以下内容:

count   |           location            
------------------------------------------
3       | http://random-page.io/index.html -- The count is 3 and not 5 because there are 3 sessions in which the LAST entry for their assigned session is http://...index.html
1       | http://random-page.io/drafts.html -- You catch my drift
1       | http://random-page.io/elements.html  -- Coolio <3

这有可能吗？

Answer 1

你可以试一试:

SELECT
  COUNT(*) AS count,
  a.lastEntry AS location
FROM (
        SELECT 
          session, 
          SUBSTRING_INDEX(GROUP_CONCAT(location), ',', -1) AS lastEntry
        FROM analytics 
        GROUP BY session
    ) AS a
GROUP BY a.lastEntry;

这是 sqlfiddle .