使用SQL只计算最近的行 - Using SQL to COUNT the most recent rows only

Using SQL to COUNT the most recent rows only

本文关键字：最近计算 SQL 使用 | 更新日期: 2023-09-26

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

我正在尝试确定一种方法来计算用户的数量，他们的最后一次会话是在一个特定的页面使用这个查询:

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

我想要能够做的是能够计数行，他们的会话只结束，并截断所有重复的结果(通过使用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

这有可能吗?

你可以试试:

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;

试试这个(好吧，这是丑陋的，但我不能想出另一种方法来做)

select
    grouped_analytics.location, count(grouped_analytics.session)
from
    (select 
        analytics.session,analytics.location 
    from 
        analytics 
    group by 
        analytics.session, analytics.location
    ) as grouped_analytics
group by
    grouped_analytics.location

看起来你需要一个子选择…:

SELECT count(session) AS count, location FROM ( 
    SELECT session, location from requests GROUP BY session
) AS I 
GROUP BY location;

这里是一个sql提琴来玩:http://sqlfiddle.com/!9/41f15/20