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

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

新用户是指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);

例如：

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 Users
from       table2 cur
left join  table2 old
on         cur.uuid = old.uuid
           and old.ip <> 2
           and old.id > 10
           and cur.ts - old.ts > 1
where      cur.ip <> 2
           and cur.id > 10
group by   year,month,day,IsNewUser
order by   year,IsNewUser

（编辑：拼字网 - 核心网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!