尝试从日期相距不超过 5 分钟的行中排除

发布于 2024-10-21 17:49:12 字数 1115 浏览 1 评论 0原文

我需要计算 MySQL 表中的所有行，忽略具有相同 agent 和 msisnd 列以及彼此相距不超过 5 分钟的日期的记录。例如，

id     type    agent              msisdn     date
2706   jurnal  EC_Catalin.Silvan  745844472  2011-02-18 18:31:54
63535  item    EC_Adelina.Gartan  766699747  2011-02-22 18:42:15
56513  item    EC_Adelina.Gartan  766733667  2011-02-24 17:13:31
56372  item    EC_Adelina.Gartan  766733667  2011-02-24 17:12:01

查询应返回 COUNT = 3，因为 ID 为 56513 和 56372 的行的日期彼此接近。

到目前为止，我已经得到了这个查询，但它不起作用

SELECT `calls_count_pre`.*
    FROM `calls_count_pre`
    LEFT JOIN `calls_count_pre` AS `temp` ON (`calls_count_pre`.`id` = `temp`.`id`)
WHERE
    (MINUTE(TIMEDIFF(`calls_count_pre`.`date`, `temp`.`date`)) <= 5 AND HOUR(TIMEDIFF(`calls_count_pre`.`date`, `temp`.`date`)) = 0)
    AND `calls_count_pre`.`msisdn` = `temp`.`msisdn`
    AND `calls_count_pre`.`agent` = `temp`.`agent`
ORDER BY `calls_count_pre`.`agent`, `calls_count_pre`.`msisdn`, `calls_count_pre`.`date` DESC

，它只是返回所有记录。我知道我没有选择 COUNT，但此时即使是 SELECT * 也将是一个开始。

提前致谢。

原文

I need to count all the rows in a MySQL table omitting the records with same agent and msisnd columns and dates closer than 5 minutes to each other.
For example,

id     type    agent              msisdn     date
2706   jurnal  EC_Catalin.Silvan  745844472  2011-02-18 18:31:54
63535  item    EC_Adelina.Gartan  766699747  2011-02-22 18:42:15
56513  item    EC_Adelina.Gartan  766733667  2011-02-24 17:13:31
56372  item    EC_Adelina.Gartan  766733667  2011-02-24 17:12:01

The query should return COUNT = 3 because the rows with id's 56513 and 56372 have close dates to each other.

So far i've got this query and it's not working

SELECT `calls_count_pre`.*
    FROM `calls_count_pre`
    LEFT JOIN `calls_count_pre` AS `temp` ON (`calls_count_pre`.`id` = `temp`.`id`)
WHERE
    (MINUTE(TIMEDIFF(`calls_count_pre`.`date`, `temp`.`date`)) <= 5 AND HOUR(TIMEDIFF(`calls_count_pre`.`date`, `temp`.`date`)) = 0)
    AND `calls_count_pre`.`msisdn` = `temp`.`msisdn`
    AND `calls_count_pre`.`agent` = `temp`.`agent`
ORDER BY `calls_count_pre`.`agent`, `calls_count_pre`.`msisdn`, `calls_count_pre`.`date` DESC

It's simply returning all records. I know I'm not selecting a COUNT, but even a SELECT * would be a start at this point.

Thanks in advance.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

深空失忆 2024-10-28 17:49:12

您的自加入方向正确，但我认为您可能加入了错误的列。我认为您还需要一个GROUP BY。

尝试使用更像这样的东西：

SELECT calls_count_pre.*
   FROM calls_count_pre
   LEFT JOIN calls_count_pre AS temp
        ON calls_count_pre.msisdn = temp.msisdm AND calls_count_pre.agent = temp.agent
        AND temp.date > calls_count_pre.date
        AND DATESUB(temp.date, INTERVAL 5 MINUTE) < calls_count_pre.date
GROUP BY calls_count_pre.agent

You're on the right track with a self-join, but I think you might be joining on the wrong column. And I think you're going to need a GROUP BY as well.

Try with something more like this:

SELECT calls_count_pre.*
   FROM calls_count_pre
   LEFT JOIN calls_count_pre AS temp
        ON calls_count_pre.msisdn = temp.msisdm AND calls_count_pre.agent = temp.agent
        AND temp.date > calls_count_pre.date
        AND DATESUB(temp.date, INTERVAL 5 MINUTE) < calls_count_pre.date
GROUP BY calls_count_pre.agent

回复收藏 0 原文

~没有更多了~