# Data Explorer 关于用户下载图片查询

**URL:** https://meta.discourse.org/t/data-explorer-about-user-download-pictures-queries/146244
**Category:** Data & reporting
**Tags:** sql-query
**Created:** [2020年三月31日 08:45 UTC](https://meta.discourse.org/t/data-explorer-about-user-download-pictures-queries/146244 "2020-03-31T08:45:58Z")
**Posts on this page:** 1
**Page:** 1

<div class="post-metadata">

### Author: ![BishopV](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/bishopv/32/185715_2.png) [@BishopV](https://meta.discourse.org/u/BishopV)
#### Post date: [2020年三月31日 08:45 UTC](https://meta.discourse.org/t/data-explorer-about-user-download-pictures-queries/146244/1 "2020-03-31T08:45:58Z")

</div>

我想知道数据探索器是否能检查哪些用户从我的网站大量下载图片？抱歉，我对 MySQL 一窍不通，我尝试从 uploads 中修改它。

```plaintext
WITH heavy_uploads AS ( SELECT 
    ( SUM(downloads.filesize) / 1024) AS sum_kb
  , COUNT(downloads.filesize) AS upload_count
  , ( (SUM(downloads.filesize) / COUNT(downloads.filesize)) / 1024) AS avg_weight_kb
  , downloads.user_id 
  FROM downloads
  GROUP BY downloads.user_id
)
SELECT heavy_downloads.sum_kb
 , heavy_downloads.downloads_count
 , heavy_downloads.avg_weight_kb
 , heavy_downloads.user_id
FROM heavy_downloads 
WHERE heavy_downloads.sum_kb > 100
ORDER BY heavy_downloads.sum_kb DESC

```