基于多记录URL字符串获取各站点根路径最新访问日期

现有包含AccessDate（访问日期，格式为YYYYMMDD）和URL字段的海量访问日志数据，需针对指定的站点根路径集合，获取每个根路径对应的最新（最大）访问日期，输出各根路径及其对应最新访问日期的结果。

数据示例 #

AccessDate  URL
20220523    /sites/brazil/sitepages/home.aspx
20220518    /sites/brazil/sitepages/home.aspx
20220621    /sites/turkey/blog.aspx
20220224    /sites/turkey/dashboards.aspx
20220623    /sites/brazil/sitepages/about.aspx
20210629    /sites/usa/service.aspx
20210728    /sites/usa/windows.aspx
20211117    /sites/turkey/new.aspx
20220513    /sites/brazil/sitepages/home.aspx

指定站点根路径（用于筛选） #

• /sites/brazil/
• /sites/usa/
• /sites/turkey/

期望输出 #

AccessDate  URL
20220623    /sites/brazil/
20210728    /sites/usa/
20220621    /sites/turkey/

解决方案 #

通用分组查询（兼容多数数据库） #

先通过CASE语句映射每条URL到对应的根路径，再按根路径分组取最大访问日期：

SELECT 
    MAX(AccessDate) AS AccessDate,
    root_url AS URL
FROM (
    SELECT 
        AccessDate,
        CASE
            WHEN URL LIKE '/sites/brazil/%' THEN '/sites/brazil/'
            WHEN URL LIKE '/sites/usa/%' THEN '/sites/usa/'
            WHEN URL LIKE '/sites/turkey/%' THEN '/sites/turkey/'
        END AS root_url
    FROM access_logs
    WHERE URL LIKE '/sites/brazil/%' 
       OR URL LIKE '/sites/usa/%' 
       OR URL LIKE '/sites/turkey/%'
) AS sub_query
GROUP BY root_url;

窗口函数写法（适合大数据量，效率更优） #

如果你的数据库支持窗口函数（如MySQL 8+、SQL Server、PostgreSQL），可以用ROW_NUMBER()按根路径分组并按访问日期倒序排序，直接取每组第一条记录：

WITH ranked_logs AS (
    SELECT 
        AccessDate,
        CASE
            WHEN URL LIKE '/sites/brazil/%' THEN '/sites/brazil/'
            WHEN URL LIKE '/sites/usa/%' THEN '/sites/usa/'
            WHEN URL LIKE '/sites/turkey/%' THEN '/sites/turkey/'
        END AS root_url,
        ROW_NUMBER() OVER (
            PARTITION BY 
                CASE
                    WHEN URL LIKE '/sites/brazil/%' THEN '/sites/brazil/'
                    WHEN URL LIKE '/sites/usa/%' THEN '/sites/usa/'
                    WHEN URL LIKE '/sites/turkey/%' THEN '/sites/turkey/'
                END 
            ORDER BY AccessDate DESC
        ) AS rn
    FROM access_logs
    WHERE URL LIKE '/sites/brazil/%' 
       OR URL LIKE '/sites/usa/%' 
       OR URL LIKE '/sites/turkey/%'
)
SELECT AccessDate, root_url AS URL
FROM ranked_logs
WHERE rn = 1;

字符串截取简化写法（根路径格式统一时可用） #

如果所有站点根路径都是/sites/[区域]/的固定格式，可以用字符串截取函数直接提取根路径，避免多个CASE判断：

SQL Server版本

SELECT 
    MAX(AccessDate) AS AccessDate,
    LEFT(URL, CHARINDEX('/', URL, 8)) AS URL
FROM access_logs
WHERE URL LIKE '/sites/%/%'
  AND LEFT(URL, CHARINDEX('/', URL, 8)) IN ('/sites/brazil/', '/sites/usa/', '/sites/turkey/')
GROUP BY LEFT(URL, CHARINDEX('/', URL, 8));

MySQL版本

SELECT 
    MAX(AccessDate) AS AccessDate,
    CONCAT('/sites/', SUBSTRING_INDEX(SUBSTRING_INDEX(URL, '/', 4), '/', -1), '/') AS URL
FROM access_logs
WHERE URL LIKE '/sites/%/%'
  AND SUBSTRING_INDEX(SUBSTRING_INDEX(URL, '/', 4), '/', -1) IN ('brazil', 'usa', 'turkey')
GROUP BY CONCAT('/sites/', SUBSTRING_INDEX(SUBSTRING_INDEX(URL, '/', 4), '/', -1), '/');

内容的提问来源于stack exchange，提问作者SBP