Last active
December 19, 2024 23:03
-
-
Save Ghost-chu/10f01aab255bce407327e8e2e21cc8cf to your computer and use it in GitHub Desktop.
Complex SQL
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| WITH LatestSnapshots AS ( | |
| SELECT DISTINCT ON (s.torrent, s.peer_ip, s.user_application) | |
| s.id, | |
| s.torrent, | |
| s.peer_ip, | |
| s.user_application, | |
| s.to_peer_traffic, | |
| s.last_time_seen | |
| FROM | |
| public.peer_history s | |
| WHERE | |
| s.last_time_seen >= ? AND s.to_peer_traffic > 0 | |
| ORDER BY | |
| s.torrent, s.peer_ip, s.user_application, s.last_time_seen DESC | |
| ), | |
| AggregatedUploads AS ( | |
| SELECT | |
| ls.torrent, | |
| ls.peer_ip, | |
| SUM(ls.to_peer_traffic) AS total_uploaded | |
| FROM | |
| LatestSnapshots ls | |
| GROUP BY | |
| ls.torrent, ls.peer_ip | |
| HAVING | |
| SUM(ls.to_peer_traffic) > 0 | |
| ) | |
| SELECT | |
| au.torrent, | |
| au.peer_ip, | |
| au.total_uploaded, | |
| t.size, | |
| (au.total_uploaded / t.size::float) * 100 AS upload_percentage | |
| FROM | |
| AggregatedUploads au | |
| JOIN | |
| public.torrent t ON au.torrent = t.id | |
| WHERE | |
| t.size::float > 0 AND au.total_uploaded > t.size::float * ? | |
| ORDER BY | |
| upload_percentage DESC; |
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| SELECT DISTINCT ban.peerIp | |
| FROM BanHistory ban | |
| WHERE | |
| ban.insertTime >= ?1 AND ban.insertTime <= ?2 AND ban.module LIKE '%ProgressCheatBlocker%' | |
| GROUP BY ban.peerIp, time_bucket(?4, ban.insertTime) | |
| HAVING COUNT(DISTINCT ban.userApplication.appId) >= ?3 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
能否通过
DataGrip之类的工具导出相关表的 DDL 语句