代码之家 › 专栏 › 技术社区 › Asik

mysql query-根据日期排除计数中的重复记录

mysql

Asik · 技术社区 · 6 年前

我叫人来吃饭 Post_喜欢 包含以下字段

ID(整数)
邮政编码(国际)
类型(枚举)(FB喜欢或LinkedIn喜欢)
用户哈希(varchar)(FB或LinkedIn唯一标识符)
相似计数(int)
创建日期(时间戳)

创建于 是基于UTC时区的。

我们的业务逻辑是用户可以添加就像每天一次(基于PST时区)的帖子。含义,1 喜欢从FB和1 就像来自LinkedIn。

我有一个条件,在申请方面,检查和限制人民谁试图给予相同的一天。不知怎么的,有些用户添加了重复的喜欢同一天(可能是代码或Web服务器问题)。现在就把这个留下吧)。

我当前的查询如下,以获取每个帖子的计数

SELECT `id`,  
       `title`,  
       Sum(IF(`type` = 'FB_LIKES', like_count, 0)) AS fb_like_counts, 
       Sum(IF(`type` = 'LINKEDIN_LIKES', like_count, 0)) AS linkedin_like_counts, 
       Sum(`like_count`) AS total_like_counts 
FROM   `post_likes` 
GROUP  BY `title`
ORDER  BY `total_like_counts` DESC;

现在,我想得到不同的计数。因为有些帖子在同一天有复制品。同样的用户可以在第二天喜欢。

所以需要添加一个基于pst时区的条件并排除同一天重复的like

注:对不起,我的英语不好,请提前谢谢

3 回复 | 直到 6 年前

Kavindu Wijesuriya 6 年前

您只需对查询进行一些调整,就可以从表中选择不同的日期。对于下面的解决方案,我添加了一个新的select语句来返回相同的 Post_喜欢 但日期不同。这将允许我对筛选后的 Post_喜欢

SELECT `id`,  
       `title`,
       `user_hash` as liked_user,
       Sum(IF(`type` = 'FB_LIKES', like_count, 0)) AS fb_like_counts, 
       Sum(IF(`type` = 'LINKEDIN_LIKES', like_count, 0)) AS linkedin_like_counts, 
       Sum(`like_count`) AS total_like_counts 
FROM   (SELECT * FROM post_likes GROUP BY DATE_FORMAT(`created_date`, '%m %d %Y'), user_hash, type) AS post_likes
GROUP  BY `title`, DATE_FORMAT(`created_date`, '%m %d %Y'), user_hash
ORDER  BY `total_like_counts` DESC;

可以使用以下查询重新创建架构

CREATE TABLE `post_likes` (
  `id` int(11) NOT NULL,
  `title` varchar(255) DEFAULT NULL,
  `post_id` int(11) NOT NULL,
  `type` enum('FB_LIKES','LINKEDIN_LIKES') NOT NULL,
  `user_hash` varchar(30) NOT NULL,
  `like_count` int(11) NOT NULL,
  `created_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP
);

insert into post_likes VALUES (
  1, 'A', 1, 'FB_LIKES', 'ABC', 1, '2018-06-25 05:49:41'
);

insert into post_likes VALUES (
2, 'A', 1, 'FB_LIKES', 'DEF', 1, '2018-06-25 05:50:01'
);

insert into post_likes VALUES (
3, 'A', 1, 'LINKEDIN_LIKES', 'ABC', 1, '2018-06-25 05:50:16'
);

insert into post_likes VALUES (
4, 'A', 1, 'LINKEDIN_LIKES', 'DEF', 1, '2018-06-25 05:50:23'
);

insert into post_likes VALUES (
5,'A', 1, 'LINKEDIN_LIKES', 'ABC', 1, '2018-06-25 05:50:16'
);

insert into post_likes VALUES (
6, 'A', 1, 'FB_LIKES', 'DEF', 1, '2018-06-25 05:50:01'
);

Prasad Wargad 6 年前

假设下面是可能的表结构

CREATE TABLE `post_likes` (
  `id` int(11) NOT NULL,
  `title` varchar(255) DEFAULT NULL,
  `post_id` int(11) NOT NULL,
  `type` enum('FB_LIKES','LINKEDIN_LIKES') NOT NULL,
  `user_hash` varchar(30) NOT NULL,
  `like_count` int(11) NOT NULL,
  `created_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

以下是示例数据:

INSERT INTO `post_likes` (`id`, `title`, `post_id`, `type`, `user_hash`, `like_count`, `created_date`) VALUES
(1, 'A', 1, 'FB_LIKES', 'ABC', 1, '2018-06-25 05:49:41'),
(2, 'A', 1, 'FB_LIKES', 'DEF', 1, '2018-06-25 05:50:01'),
(3, 'A', 1, 'LINKEDIN_LIKES', 'ABC', 1, '2018-06-25 05:50:16'),
(4, 'A', 1, 'LINKEDIN_LIKES', 'DEF', 1, '2018-06-25 05:50:23'),
(5, 'A', 1, 'FB_LIKES', 'ABC', 1, '2018-06-26 05:50:38');

考虑到表中没有任何重复的记录,下面的查询with date字段将在一天内具有唯一的记录:

SELECT `id`, `title`, Sum(IF(`type` = 'FB_LIKES', like_count, 0)) AS fb_like_counts, Sum(IF(`type` = 'LINKEDIN_LIKES', like_count, 0)) AS linkedin_like_counts, Sum(`like_count`) AS total_like_counts, DATE_FORMAT(`created_date`, '%Y-%m-%d') AS `liked_date` 
FROM `post_likes` 
GROUP BY `title`, `liked_date`
ORDER BY `total_like_counts` DESC;

Asik 6 年前

很抱歉更新太晚了。下面是我的最后一个查询,它给出了正确的结果

SELECT `id`, 
   `title`, 
   Sum(IF(`type` = 'FB_LIKES', like_count, 0))       AS fb_like_counts, 
   Sum(IF(`type` = 'LINKEDIN_LIKES', like_count, 0)) AS linkedin_like_counts, 
   Sum(`like_count`)                                 AS 
   total_like_counts  FROM   (SELECT *, 
           Date_format(Convert_tz(created_at, '+00:00', '-8:00'), '%Y-%m-%d' 
   ) AS date_pst 
    FROM   `post_likes` 
    GROUP  BY type, 
              title, 
              date_pst, 
              uid_hash) AS D1 
    GROUP  BY `title` 
    ORDER  BY `total_like_counts` DESC;