编写MySQL/Hive查询聚合文件夹文件数，统计文件最多的文件夹

Hey there! Let's tackle this problem of counting files per folder—including parent folders and their subfolders, then finding which folder has the most files. First, let's assume we have a table file_system that tracks every file's full path (like /mysite/current/report.pdf). I'll cover solutions for both MySQL 8.0+ and Hive since they're common for this kind of task.

1. MySQL 8.0+ Implementation (Recursive CTEs for Hierarchy) #

Split Paths & Count Files for All Folders #

Recursive CTEs let us "climb up" each file's path to extract every parent folder, then we can aggregate the file counts for each level:

WITH RECURSIVE folder_paths AS (
    -- Start with the first level of each file's path
    SELECT
        full_path,
        SUBSTRING_INDEX(full_path, '/', 2) AS folder, -- e.g., '/mysite' from '/mysite/current/file.txt'
        1 AS depth
    FROM file_system
    WHERE full_path != '/' -- Skip root if it's in the table
    UNION ALL
    -- Recursively extract higher parent folders
    SELECT
        fp.full_path,
        SUBSTRING_INDEX(fp.folder, '/', depth + 1) AS folder,
        depth + 1 AS depth
    FROM folder_paths fp
    WHERE SUBSTRING_INDEX(fp.folder, '/', depth + 1) != '' -- Stop at root
)
-- Aggregate stats and label folder type
SELECT
    folder,
    COUNT(DISTINCT full_path) AS total_files,
    -- Mark if it's a parent folder (has subfolders) or leaf subfolder
    CASE WHEN EXISTS (
        SELECT 1 FROM folder_paths fp2 
        WHERE fp2.folder LIKE CONCAT(folder, '/%')
    ) THEN 'Parent Folder (contains subfolders)' ELSE 'Subfolder (leaf level)' END AS folder_type
FROM folder_paths
GROUP BY folder
ORDER BY total_files DESC;

Find the Folder(s) With the Most Files #

If you only care about the top folder(s) by file count, wrap the stats in another CTE to filter for the maximum value:

WITH RECURSIVE folder_paths AS (
    SELECT
        full_path,
        SUBSTRING_INDEX(full_path, '/', 2) AS folder,
        1 AS depth
    FROM file_system
    WHERE full_path != '/'
    UNION ALL
    SELECT
        fp.full_path,
        SUBSTRING_INDEX(fp.folder, '/', depth + 1) AS folder,
        depth + 1 AS depth
    FROM folder_paths fp
    WHERE SUBSTRING_INDEX(fp.folder, '/', depth + 1) != ''
),
folder_stats AS (
    SELECT
        folder,
        COUNT(DISTINCT full_path) AS total_files,
        CASE WHEN EXISTS (
            SELECT 1 FROM folder_paths fp2 
            WHERE fp2.folder LIKE CONCAT(folder, '/%')
        ) THEN 'Parent Folder' ELSE 'Subfolder' END AS folder_type
    FROM folder_paths
    GROUP BY folder
)
SELECT *
FROM folder_stats
WHERE total_files = (SELECT MAX(total_files) FROM folder_stats);

2. Hive Implementation (For Big Data Scenarios) #

Hive doesn't support recursive CTEs, but we can use split and LATERAL VIEW explode to break down paths into their components:

Split Paths & Count Files for All Folders #

WITH folder_paths AS (
    SELECT
        full_path,
        concat_ws('/', collect_list(path_part)) AS folder
    FROM file_system
    -- Strip leading slash, split path into parts, then explode each part with its position
    LATERAL VIEW posexplode(split(regexp_replace(full_path, '^/', ''), '/')) exploded AS pos, path_part
    GROUP BY full_path, pos
)
-- Aggregate and label folder type
SELECT
    folder,
    COUNT(DISTINCT full_path) AS total_files,
    CASE WHEN EXISTS (
        SELECT 1 FROM folder_paths fp2 
        WHERE fp2.folder LIKE concat(folder, '/%')
    ) THEN 'Parent Folder' ELSE 'Subfolder' END AS folder_type
FROM folder_paths
GROUP BY folder
ORDER BY total_files DESC;

Find the Folder(s) With the Most Files #

Use a window function to rank folders by file count and pick the top ones:

WITH folder_paths AS (
    SELECT
        full_path,
        concat_ws('/', collect_list(path_part)) AS folder
    FROM file_system
    LATERAL VIEW posexplode(split(regexp_replace(full_path, '^/', ''), '/')) exploded AS pos, path_part
    GROUP BY full_path, pos
),
folder_stats AS (
    SELECT
        folder,
        COUNT(DISTINCT full_path) AS total_files,
        CASE WHEN EXISTS (
            SELECT 1 FROM folder_paths fp2 
            WHERE fp2.folder LIKE concat(folder, '/%')
        ) THEN 'Parent Folder' ELSE 'Subfolder' END AS folder_type,
        RANK() OVER(ORDER BY COUNT(DISTINCT full_path) DESC) AS rnk
    FROM folder_paths
    GROUP BY folder
)
SELECT folder, total_files, folder_type
FROM folder_stats
WHERE rnk = 1;

Key Notes #

• Parent Folder Counts: For example, /mysite's total includes every file in /mysite/current, /mysite/backup, and any other subfolders—exactly what you requested.
• Folder Type Distinction: We check for existing subfolders by looking for paths that start with the current folder's path plus a slash.
• Duplicate Protection: COUNT(DISTINCT full_path) ensures we don't count the same file multiple times if there are duplicate entries in the table.

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