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

**URL:** https://meta.discourse.org/t/support-to-extract-a-report-using-data-explorer-plugin/338040
**Category:** Support
**Tags:** data-explorer, sql-query
**Created:** [2024年十一月24日 10:57 UTC](https://meta.discourse.org/t/support-to-extract-a-report-using-data-explorer-plugin/338040 "2024-11-24T10:57:08Z")
**Posts on this page:** 4
**Page:** 1

<div class="post-metadata">

### Author: ![Eman\_Ahmed](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/eman_ahmed/32/542047_2.png) [@Eman\_Ahmed](https://meta.discourse.org/u/Eman_Ahmed)
#### Post date: [2024年十一月24日 10:57 UTC](https://meta.discourse.org/t/support-to-extract-a-report-using-data-explorer-plugin/338040/1 "2024-11-24T10:57:08Z")

</div>

各位：

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

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

```sql
-- 用于估算每个类别数据大小（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](https://global.discourse-cdn.com/meta/original/4X/d/7/7/d77c750be1a8e10c95b456bb1a0e59717ee99d1d.png)

---

<div class="post-metadata">

### Author: ![Lilly](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/lilly/32/575047_2.png) [@Lilly](https://meta.discourse.org/u/Lilly)
#### Post date: [2024年十一月24日 18:25 UTC](https://meta.discourse.org/t/support-to-extract-a-report-using-data-explorer-plugin/338040/2 "2024-11-24T18:25:31Z")

</div>

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

```sql
-- 估算每个类别数据大小（以 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

```

---

<div class="post-metadata">

### Author: ![Eman\_Ahmed](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/eman_ahmed/32/542047_2.png) [@Eman\_Ahmed](https://meta.discourse.org/u/Eman_Ahmed)
#### Post date: [2024年十一月25日 11:46 UTC](https://meta.discourse.org/t/support-to-extract-a-report-using-data-explorer-plugin/338040/3 "2024-11-25T11:46:55Z")

</div>

好的，谢谢！

---

<div class="post-metadata">

### Author: ![Jagster](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/jagster/32/192154_2.png) [@Jagster](https://meta.discourse.org/u/Jagster)
#### Post date: [2024年十一月25日 12:53 UTC](https://meta.discourse.org/t/support-to-extract-a-report-using-data-explorer-plugin/338040/4 "2024-11-25T12:53:41Z")

</div>

> [@Lilly](#):
>
> for GB

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