使用Data Explorer插件提取报告的支持

支持
,
1

各位：

我需要你们的支持来执行一段 SQL 代码，以查看每个类别的规模，以便定期跟进，确保类别没有超出版主商定的规模。

我们执行了以下代码，但结果以字节显示，我们需要以 GB 或 MB 显示。

-- 用于估算每个类别数据大小（MB 和 GB）的查询
SELECT 
    c.id AS category_id,
    c.name AS category_name,
    COUNT(DISTINCT t.id) AS topics_count,
    COUNT(p.id) AS posts_count,
    ROUND(SUM(LENGTH(p.raw)) / 1024 / 1024, 2) AS total_text_size_mb,
    ROUND(SUM(LENGTH(p.raw)) / 1024 / 1024 / 1024, 4) AS total_text_size_gb,
    ROUND(COALESCE(SUM(u.filesize), 0) / 1024 / 1024, 2) AS total_uploads_size_mb,
    ROUND(COALESCE(SUM(u.filesize), 0) / 1024 / 1024 / 1024, 4) AS total_uploads_size_gb,
    ROUND((COALESCE(SUM(u.filesize), 0) + SUM(LENGTH(p.raw))) / 1024 / 1024, 2) AS total_category_size_mb,
    ROUND((COALESCE(SUM(u.filesize), 0) + SUM(LENGTH(p.raw))) / 1024 / 1024 / 1024, 4) AS total_category_size_gb
FROM 
    categories c
LEFT JOIN 
    topics t ON t.category_id = c.id
LEFT JOIN 
    posts p ON p.topic_id = t.id
LEFT JOIN 
    post_custom_fields pcf ON pcf.post_id = p.id AND pcf.name = 'uploads'
LEFT JOIN 
    uploads u ON u.id = pcf.value::int
WHERE 
    c.id > 0 -- 排除系统类别
    AND t.deleted_at IS NULL -- 排除已删除的主题
    AND p.deleted_at IS NULL -- 排除已删除的帖子
GROUP BY 
    c.id, c.name
ORDER BY 
    total_category_size_mb DESC

需要你们支持将其显示为以下格式：

image
image1892×296 5.58 KB

2

我认为您只需要更改计算方式以反映您的单位。例如，对于 GB：

-- 估算每个类别数据大小（以 GB 为单位）的查询
SELECT 
    c.id AS category_id,
    c.name AS category_name,
    COUNT(DISTINCT t.id) AS topics_count,
    COUNT(p.id) AS posts_count,
    ROUND(SUM(LENGTH(p.raw)) / 1024 / 1024 / 1024, 4) AS total_text_size_gb,
    ROUND(COALESCE(SUM(u.filesize), 0) / 1024 / 1024 / 1024, 4) AS total_uploads_size_gb,
    ROUND((COALESCE(SUM(u.filesize), 0) + SUM(LENGTH(p.raw))) / 1024 / 1024 / 1024, 4) AS total_category_size_gb
FROM 
    categories c
LEFT JOIN 
    topics t ON t.category_id = c.id
LEFT JOIN 
    posts p ON p.topic_id = t.id
LEFT JOIN 
    post_custom_fields pcf ON pcf.post_id = p.id AND pcf.name = 'uploads'
LEFT JOIN 
    uploads u ON u.id = pcf.value::int
WHERE 
    c.id > 0 -- 排除系统类别
    AND t.deleted_at IS NULL -- 排除已删除的主题
    AND p.deleted_at IS NULL -- 排除已删除的帖子
GROUP BY 
    c.id, c.name
ORDER BY 
    total_category_size_gb DESC
1 个赞
3

好的，谢谢！

4

非常基础的问题……我的显示的大小几乎为零。这是来自 S3 使用还是图片不计为上传？当然，那些相对较小，一些 PDF 和电子书也是如此，但它们应该比 0.001 GB 大。