使用减法SQL使用分组方式进行聚合

Messi91：

DB有一个表，events用于存储动词= liked的博客帖子的点赞，如果用户不喜欢该帖子，则以动词= likeDeleted存储

**`events`
PostID | Verb | timestamp_gmt**
P121   | liked | 2020-01-05 14:15:23
P157   | liked | 2020-02-07 11:14:12
P121   |likeDeleted| 2020-02-07 11:14:14

查询A：

SELECT 
    YEARWEEK(timestamp_gmt), COUNT(*)
FROM
    `events`
WHERE
    timestamp_gmt > '2020-01-01 00:00:00'
        AND (verb = 'liked')
GROUP BY YEARWEEK(timestamp_gmt)

查询B：

SELECT 
(SELECT 
        COUNT(*)
    FROM
        `events`
    WHERE
        timestamp_gmt > '2020-01-01 00:00:00'
            AND (verb = 'liked')) - (SELECT 
        COUNT(*)
    FROM
        `events`
    WHERE
        timestamp_gmt > '2020-01-01 00:00:00'
            AND (verb = 'likeDeleted')) AS difference

使用查询A，您可以获得按周分组的喜欢数。使用查询B，您会在2020年1月1日之后获得喜欢和已删除喜欢的净计数。

要求是将净计数按周分组。

专家们进行快乐的小脑锻炼:)

法赫米：

您可以尝试使用条件聚合- case expression

select YEARWEEK(timestamp_gmt),count(case when verb='liked' then 1 end)-
       count(case when verb='likeDeleted' then 1 end)
from tablename
WHERE timestamp_gmt>"2020-01-01 00:00:00"
group by YEARWEEK(timestamp_gmt)

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。