MySql 查询 - 根据日期排除计数中的重复记录

Question

我有一个名为post_likes的表，其中包含以下字段

• ID (整数(
• post_id(国际(
• 类型 (枚举( (FB_LIKES 或 LINKEDIN_LIKES(
• user_hash(varchar((FB或LinkedIn唯一标识符(
• like_count(整型(
• created_date(时间戳(

Created_at基于 UTC 时区。

我们的业务逻辑是，用户每天可以为帖子添加一次 LIKE(基于 PST 时区(。 意思是，来自 FB 的 1 个LIKE和来自LinkedIn的 1个 LIKE。

我在应用程序方面有一个条件来检查和限制试图在同一天给予的人。不知何故，一些用户在同一天添加了重复的 LIKE(可能是代码或 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时区添加条件并排除同一天的重复喜欢

注意：对不起，我的英语不好，提前感谢

Answer 1

您只需对查询进行一些调整，即可从表中选择不同的日期。对于以下解决方案，我添加了一个新的 select 语句以返回相同的post_likes表，但日期不同。这将允许我在一组过滤的post_likes上运行查询

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'
);

Answer 2

假设下面是可能的表结构

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');

下面是带有日期字段的查询，考虑到表中不存在任何重复记录，请按天具有唯一记录：

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;

Answer 3

抱歉更新晚了。下面是我的最终查询，它给出了正确的结果

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;