26Z10c6

WITH numbers AS (
    SELECT 1 AS pos UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL 
    SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL 
    SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10
),
recursive_prefix AS (
    SELECT 
        o.bucket,
        -- substring_index로 n.pos 번째 '/'까지 문자열 자르기
        substring_index(o.prefix, '/', n.pos) AS prefix,
        o.size_bytes
    FROM object_current_state o
    CROSS JOIN numbers n
    WHERE o.__deleted = 0 
      AND o.prefix IS NOT NULL 
      AND o.prefix != ''
      -- prefix 내 '/' 개수 + 1 만큼만 pos 확장
      AND n.pos <= (length(o.prefix) - length(replace(o.prefix, '/', '')) + 1)
)
SELECT 
    bucket,
    prefix,
    COUNT(1) AS total_object_count,
    SUM(size_bytes) AS total_size_bytes
FROM recursive_prefix
GROUP BY bucket, prefix;