mysql - 在SQL中每天计算用户匹配项

Closed. This question needs details or clarity。它当前不接受答案。
                            
                        
                    
                
                            
                                
                
                        
                            
                        
                    
                        
                            想改善这个问题吗？添加详细信息并通过editing this post阐明问题。
                        
                        在8个月前关闭。
                                                                                            
                
        
对于像这样的表：

CREATE TABLE IF NOT EXISTS `like` (
 `liking_user_id` int(6) unsigned NOT NULL,
 `liked_user_id` int(6) unsigned NOT NULL,
 `like_time` timestamp NOT NULL,
)

看起来像

liking_user_id                   liked_user_id                 like_time
1                               2               2018-04-18 00:02:07
2                               4               2018-04-19 00:09:07
2                               1               2018-04-22 00:02:07
4                               5               2018-04-23 00:02:07
1                               4               2018-04-24 00:02:07
4                               2               2018-04-25 00:02:07

在上述数据集中，匹配发生在userid = 1和userid = 2之间的4/22以及userid = 2和userid = 4之间的4/25

我想编写一个查询，该查询计算每天的共同点赞次数（按日期（点赞时间）分组）。当用户喜欢另一个之前喜欢过他/她的用户时，就会发生相互喜欢。

预期结果应为：

date(like_time) as Date               count(liking_user_id ...) as Count

2018-04-17                                      0

2018-04-18                                      0

2018-04-19                                      0

2018-04-20                                      0

2018-04-21                                      0

2018-04-22                                      1

2018-04-23                                      0

2018-04-24                                      0

2018-04-25                                      1

最佳答案

没有唯一的约束，并且匹配行的规范为“以前喜欢他/她的人”，

为了避免JOIN中潜在的“重复项”问题，我选择带有相关子查询的EXISTS来测试具有较早时间戳记的对应行的存在

像这样：

  SELECT DATE(t.`like_time`)  AS `date_`
       , COUNT(1)             AS `count_`
    FROM `like` t
   WHERE EXISTS (     -- corresponding row with earlier timestamp
                  SELECT 1
                    FROM `like` f
                   WHERE f.`liked_user_id`  = t.`liking_user_id`
                     AND f.`liking_user_id` = t.`liked_user_id`
                     AND f.`like_time`      < t.`like_time`
                )
   GROUP
      BY DATE(t.`like_time`)

为了验证查询，我选择使用返回明细行的查询（避免使用GROUP BY和聚合函数）



在没有唯一约束的情况下，我们返回的计数可能不是“独特的”相互喜欢的。考虑

t_id  f_id  liked_time
----  ----  ----------
 66    77   2019-06-01
 77    66   2019-06-02
 66    77   2019-06-05
 77    66   2019-06-05
 66    77   2019-06-06

查询将返回一个类似的结果集

 date_       count_
 ----------  ------
 2019-06-02       1
 2019-06-05       2
 2019-06-06       1

跟进：

要返回“零”计数，我们可以这样做。将查询包装在括号内，作为内联视图。并将其外部连接到日历行源，该日历行源返回我们要返回的日期值的不同列表，包括没有任何喜欢/喜欢/喜欢的行要计数的日期

SELECT c.date_
     , IFNULL(s.count_,0) AS `count_`
  FROM ( SELECT DATE(d.`like_time`)  AS `date_`
           FROM `like` d
          GROUP
             BY DATE(d.`like_time`)
       ) c
  LEFT
  JOIN ( SELECT DATE(t.`like_time`)  AS `date_`
              , COUNT(1)             AS `count_`
           FROM `like` t
          WHERE EXISTS (     -- corresponding row with earlier timestamp
                         SELECT 1
                           FROM `like` f
                          WHERE f.`liked_user_id`  = t.`liking_user_id`
                            AND f.`liking_user_id` = t.`liked_user_id`
                            AND f.`like_time`      < t.`like_time`
                       )
          GROUP
             BY DATE(t.`like_time`)
       ) s
    ON s.`date_` = c.`date_`
 ORDER
    BY c.`date_`

好吧，这有点令人困惑。让我们看一下单独的SELECT语句。该内联视图c，我们可以单独运行该查询

         SELECT DATE(d.`like_time`)  AS `date_`
           FROM `like` d
          GROUP
             BY DATE(d.`like_time`)

这就是日期列表，它是“日历”行源。我们可以引用一个“日历”表，以便为我们提供所需的日期。

标记为/别名为s的嵌入式视图是问题顶部的查询。

当我们全神贯注于此时，我们看到的实际上是以下形式的查询：

SELECT c.date_
     , IFNULL(s.count_,0) AS count_
  FROM c
  LEFT
  JOIN s
    ON s.date_ = c.date_
 ORDER
    BY c.date_

（IFNULL让我们用零替换缺失行中的NULL）

还有其他查询模式会返回等效结果，例如

SELECT s.date_
     , SUM(s.has_match_) AS count_
  FROM ( SELECT DATE(t.like_time)  AS date_
              , EXISTS ( SELECT 1
                           FROM `like` f
                          WHERE f.liked_user_id  = t.liking_user_id
                            AND f.liking_user_id = t.liked_user_id
                            AND f.like_time      < t.like_time
                       ) AS has_match_
                  FROM `like` t
       ) s
 GROUP
    BY s.date_
 ORDER
    BY s.date_

关于mysql - 在SQL中每天计算用户匹配项，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/56568225/