sql - 在 PostgreSQL 中按多个内连接的条件计数
问题描述
我有以下表格:
User
UserPostView
Post
UserPostView
User
是一个联接表,其中包含有关查看帖子后是否赞成、反对或通过的附加信息。
Post
有一列postable_type
指示帖子的类型(TextPost
、ImagePost
等)。
我想计算按postable_type
.
我当前的查询非常慢,我很确定它可以很容易地进行优化。
SELECT
U.id,
count((UP.postable_type = 'text_post' AND UPV.passed = true) OR NULL) as text_posts_pass_count,
count((UP.postable_type = 'text_post' AND UPV.upvote = true) OR NULL) as text_posts_upvote_count,
count((UP.postable_type = 'text_post' AND UPV.downvote = true) OR NULL) as text_posts_downvote_count,
count((UP.postable_type = 'image_post' AND UPV.passed = true) OR NULL) as image_posts_pass_count,
count((UP.postable_type = 'image_post' AND UPV.upvote = true) OR NULL) as image_posts_upvote_count,
count((UP.postable_type = 'image_post' AND UPV.downvote = true) OR NULL) as image_posts_downvote_count
FROM
users U
INNER JOIN(
SELECT
user_id,
post_id,
passed,
upvoted,
downvoted
FROM
user_post_views
) UPV on U.id :: TEXT = UPV.user_id :: TEXT
INNER JOIN(
SELECT
id,
postable_type
FROM
posts
) UP on UPV.post_id :: TEXT = UP.id :: TEXT
GROUP BY
U.id
解决方案
不要对连接进行类型转换!我想你只需要:
SELECT UPV.user_id,
COUNT(*) FILTER (WHERE p.postable_type = 'text_post' AND upv.passed) as text_posts_pass_count,
COUNT(*) FILTER (WHERE p.postable_type = 'text_post' AND upv.upvote) as text_posts_upvote_count,
COUNT(*) FILTER (WHERE p.postable_type = 'text_post' AND upv.downvote ) as text_posts_downvote_count,
COUNT(*) FILTER (WHERE p.postable_type = 'image_post' AND upv.passed) as image_posts_pass_count,
COUNT(*) FILTER (WHERE p.postable_type = 'image_post' AND upv.upvote) as image_posts_upvote_count,
COUNT(*) FILTER (WHERE p.postable_type = 'image_post' AND upv.downvote) as image_posts_downvote_count
FROM user_post_views upv JOIN
posts p
ON upv.post_id = p.id
GROUP BY upv.user_id;
变化:
- 不要对连接进行类型转换!这肯定会阻碍优化器。
- 该
users
表似乎没有必要。 - 子查询不是必需的。
FILTER
比条件聚合略快。更重要的是,意图更加明确。
推荐阅读
- php - 如果数据库中已存在行,则不要导入行
- azure - Azure AD B2C Xamarin.ios 本机
- python - 在 Pandas 中调用多行
- spring - ClassTemplateLoader 不在指定路径中寻找模板
- mysql - 尝试从 sqoop 中选择时出现 MySQLSyntaxErrorException
- scala - Scala更改特征中定义的函数的参数
- artifactory - 将一个 Artifactory 连接到另一个 Artifactory
- typescript - 类型注释 reflectPromise 方法
- css - 如何使 fontawesome 字形周围的边框与视觉效果相匹配
- kubernetes - Prometheus 在节点处于不可调度状态时触发警报