mysql - 合并 2 个具有相似 where 子句但聚合函数目标不同的查询

Question

我有 2 个单独运行良好的查询。鉴于它们很相似，我想将它们合并到一个高性能查询中。看起来很简单，因为 where 子句类似。但是 sum、count 和 min 函数都适用于不同的行并且会造成妨碍。

上下文:

• 用户可以对某个位置进行评分(或评级)并获得积分
• 当用户 B 首次提交分数时，用户 A 可以推荐用户 B 并获得推荐积分
• 积分在特定日期后过期
• 目标是建立一个用户排行榜及其针对特定位置(地区/国家)进行评分和推荐的总分
• 位置参数填充有“马萨诸塞州”、“美国”和 ScoreDateTime 到期日期的硬值，但不幸的是，这些参数在两个选定子查询中都重复了。

问题:

如何重新组织下面的查询以组合约束？必须有一种方法可以从特定日期之后特定位置的分数列表开始。唯一的复杂之处是获取用户 B 的首次评分日期，并且仅在过期日期之后才向用户 A 提供推荐积分。

select scoring.userId, scoring.points + referring.points as leaderPoints
from (
    select   userId, sum(ratingPoints) as points
    from     scores s, locations l
    where    s.locationId = l.locationId and
             l.locationArea = 'Massachusetts' and
             l.locationCountry = 'United States' and
             s.scoreDateTime > '2016-04-16 18:50:53.154' and
             s.userId != 0
    group by s.userId
) as scoring

join (
    select u1.userId, count(*) * 20 as points
    from users u0
    join users u1 on u0.userId = u1.userId
    join users u2 on u2.referredByEmail = u1.emailAddress
    join scores s on u2.userId = s.userId
    join locations l on s.locationId = l.locationId
    where    l.locationArea = 'Massachusetts' and
             l.locationCountry = 'United States' and
             scoreDateTime = (
                 select min(scoreDateTime)
                 from   scores
                 where  userId = u2.userId
             ) and
             scoreDateTime >= '2016-04-16 18:50:53.154'
    group by u1.userId
) as referring on scoring.userId = referring.userId
order by leaderPoints desc
limit 10;

Answer 1

这是未经测试的代码，但它应该可以解决问题。交叉应用是为了可读性......它会损害性能，但这似乎不是一个特别过程密集的查询，所以我会保留它。

请尝试一下，如果有任何问题请告诉我。

SELECT  U.UserID, 
        ISNULL(SUM(CASE WHEN S.UserID IS NULL THEN 0 ELSE S.ratingPoints END), 0) AS [Rating Points], 
        ISNULL(SUM(CASE WHEN SS.userID IS NULL THEN 0 ELSE 20 END), 0) AS [Referral Points]
FROM Users U
LEFT OUTER JOIN scores S
    ON S.userID = U.userID
    AND S.scoreDateTime >= '2016-04-16 18:50:53.154'
LEFT OUTER JOIN locations L
    ON S.locationID = L.locationID
    AND L.locationArea = 'Massachusetts'
    AND L.LocationCountry = 'United States'
LEFT OUTER JOIN Users U2
    ON U2.referredByEmail = U.emailAddress
LEFT OUTER JOIN scores SS
    ON SS.userID = U2.userID
LEFT OUTER JOIN locations LL
    ON SS.locationID = LL.locationID
    AND LL.locationArea = 'Massachusetts'
    AND LL.locationCountry = 'United States'
    AND SS.scoreDateTime >= '2016-04-16 18:50:53.154'
    AND SS.scoreDateTime = 
        (
            SELECT MIN(scoreDateTime)
            FROM scores
            where userID = U2.userID
        )
GROUP BY U.userID

编辑:

修改答案以删除交叉应用