sql-从24小时前存在的表中查找用户

概述我需要编写查询来查找新用户和常规用户.新用户是指uuid在过去的24小时内(从现在开始,减去触发查询的时间)出现在table2中,并且以前没有出现过的用户.常规用户是指uuid在表2的最后一天出现,并且在最近3天内至少出现一次的用户.除此之外,只有ID为> 10和ip！= 2将被考虑.table1是一个包含日期的临时表.我无法弄清楚如何在连接的帮助下实现这一

我需要编写查询来查找新用户和常规用户.

新用户是指uuID在过去的24小时内(从现在开始,减去触发查询的时间)出现在table2中,并且以前没有出现过的用户.

常规用户是指uuID在表2的最后一天出现,并且在最近3天内至少出现一次的用户.

除此之外,只有ID为> 10和ip！= 2将被考虑.

table1是一个包含日期的临时表.我无法弄清楚如何在连接的帮助下实现这一目标.请帮我.

表2

    +----+---------------------+------+------+    | ID | ts                  | uuID | ip   |    +----+---------------------+------+------+    |  1 | 2010-01-10 00:00:00 | uID1 |    5 |    |  2 | 2010-01-10 00:00:00 | uID2 |   14 |    |  3 | 2010-01-10 00:00:00 | uID3 |   11 |    |  4 | 2010-01-11 00:00:00 | uID4 |   16 |    |  5 | 2010-01-11 00:00:00 | uID5 |    4 |    |  6 | 2010-01-13 00:00:00 | uID6 |    2 |    |  7 | 2010-01-10 00:00:00 | uID1 |    1 |    |  8 | 2010-01-11 00:00:00 | uID2 |   10 |    |  9 | 2010-01-12 00:00:00 | uID1 |    1 |    | 10 | 2010-01-13 00:00:00 | uID4 |    1 |    | 11 | 2010-01-09 21:00:00 | uID1 |    1 |    | 12 | 2010-01-09 21:30:00 | uID1 |    2 |    | 13 | 2010-01-10 05:00:00 | uID2 |    3 |    | 14 | 2010-01-10 12:00:00 | uID1 |    1 |    | 15 | 2010-01-10 12:00:00 | uID3 |    1 |    | 16 | 2010-01-10 21:00:01 | uID1 |    7 |    | 17 | 2010-01-11 01:00:00 | uID2 |   14 |    | 18 | 2010-01-11 05:00:00 | uID2 |   11 |    | 19 | 2010-01-11 17:59:00 | uID4 |   13 |    | 20 | 2010-01-11 06:00:00 | uID5 |   12 |    | 21 | 2010-01-11 18:01:00 | uID1 |   14 |    | 22 | 2010-01-12 23:05:00 | uID4 |   17 |    | 23 | 2010-01-13 12:01:23 | uID6 |   13 |    +----+---------------------+------+------+    23 rows in set (0.00 sec)

表格1

    +------------+    | ts         |    +------------+    | 2010-01-10 |    | 2010-01-11 |    | 2010-01-12 |    | 2010-01-13 |    +------------+    4 rows in set (0.00 sec)

新用户在18:00接受时的输出

+------------+-------+| ts         | users |+------------+-------+| 2010-01-10 |     3 || 2010-01-11 |     2 || 2010-01-12 |     0 || 2010-01-13 |     1 |+------------+-------+4 rows in set (0.00 sec)

MysqL表转储

DROP table IF EXISTS `table1`;/*!40101 SET @saved_cs_clIEnt     = @@character_set_clIEnt */;/*!40101 SET character_set_clIEnt = utf8 */;CREATE table `table1` (  `ts` date NOT NulL) ENGINE=MyISAM DEFAulT CHARSET=latin1;/*!40101 SET character_set_clIEnt = @saved_cs_clIEnt */;INSERT INTO `table1` VALUES ('2010-01-10'),('2010-01-11'),('2010-01-12'),('2010-01-13');DROP table IF EXISTS `table2`;CREATE table `table2` (  `ID` int(11) NOT NulL auto_INCREMENT,`ts` datetime DEFAulT NulL,`uuID` varchar(20) DEFAulT NulL,`ip` int(11) DEFAulT NulL,PRIMARY KEY (`ID`)) ENGINE=MyISAM auto_INCREMENT=24 DEFAulT CHARSET=latin1;/*!40101 SET character_set_clIEnt = @saved_cs_clIEnt */;INSERT INTO `table2` VALUES (1,'2010-01-10 00:00:00','uID1',5),(2,'uID2',14),(3,'uID3',11),(4,'2010-01-11 00:00:00','uID4',16),(5,'uID5',4),(6,'2010-01-13 00:00:00','uID6',2),(7,1),(8,10),(9,'2010-01-12 00:00:00',(10,(11,'2010-01-09 21:00:00',(12,'2010-01-09 21:30:00',(13,'2010-01-10 05:00:00',3),(14,'2010-01-10 12:00:00',(15,(16,'2010-01-10 21:00:01',7),(17,'2010-01-11 01:00:00',(18,'2010-01-11 05:00:00',(19,'2010-01-11 17:59:00',13),(20,'2010-01-11 06:00:00',12),(21,'2010-01-11 18:01:00',(22,'2010-01-12 23:05:00',17),(23,'2010-01-13 12:01:23',13);

最佳答案您可以将表本身连接起来,以搜索同一用户超过一天的条目.如果没有一日匹配项,则左侧联接表中的字段将为NulL.

例如：

select       YEAR(cur.ts) as year,MONTH(cur.ts) as month,DAY(cur.ts) as day,case when old.uuID is null then 1 else 0 end as IsNewUser,count(distinct cur.uuID) as Usersfrom       table2 cur@R_403_6823@ join  table2 oldon         cur.uuID = old.uuID           and old.ip <> 2           and old.ID > 10           and cur.ts - old.ts > 1where      cur.ip <> 2           and cur.ID > 10group by   year,month,day,IsNewUserorder by   year,IsNewUser

总结

以上是内存溢出为你收集整理的sql-从24小时前存在的表中查找用户 全部内容，希望文章能够帮你解决sql-从24小时前存在的表中查找用户 所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。