MySQL-正确的进近事件计数 [英] MySQL - Correct approach event counting
问题描述
我想列出具有特定事件计数的用户,但是我对采用哪种方法感到困惑.
I want to list users which have a particular event count but I'm confused on which approach to take.
这是数据库表:
CREATE TABLE `event` (
`event_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`visitor_id` int(11) DEFAULT NULL,
`key` varchar(200) DEFAULT NULL,
`value` text,
`label` varchar(200) DEFAULT '',
`datetime` datetime DEFAULT NULL,
PRIMARY KEY (`event_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
INSERT INTO `event` (`event_id`, `visitor_id`, `key`, `value`, `label`, `datetime`)
VALUES
(1, 1, 'LOGIN', NULL, '', NULL),
(2, 2, 'LOGIN', NULL, '', NULL),
(3, 1, 'VIEW_PAGE', 'HOTEL', '', NULL),
(4, 2, 'VIEW_PAGE', 'HOTEL', '', NULL),
(5, 1, 'PURCHASE_HOTEL', NULL, '', NULL);
CREATE TABLE `visitor` (
`visitor_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`datetime` datetime DEFAULT NULL,
PRIMARY KEY (`visitor_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
INSERT INTO `visitor` (`visitor_id`, `datetime`)
VALUES
(1, NULL),
(2, NULL);
这是我的方法:
SELECT DISTINCT
t1.`visitor_id`
FROM
`visitor` t1
JOIN `event` t2 on t1.visitor_id = t2.visitor_id AND t2.`key` = 'LOGIN'
JOIN `event` t3 on t1.visitor_id = t3.visitor_id AND t3.`key` = 'VIEW_PAGE' AND t3.`value` = 'HOTEL'
WHERE ( SELECT COUNT(*) FROM `event` WHERE `event`.`key` = 'PURCHASE_HOTEL' ) > 0
这应该只列出访问者1,但是实际上也列出没有PURCHASE_HOTEL事件的访问者2.
this should only list visitor 1 but it does actually list visitor 2 too which does not have the PURCHASE_HOTEL event.
您可以想象,对于每个特定案例,将有更多的规则",如所有JOIN事件.我们可以以某种方式纠正和改进吗?
As you can imagine, there will be more "rules" like all the JOIN events for each particular case. Can we correct and improve this somehow?
奖金: 这种方法的名称是什么?
BONUS: What is the name of this approach?
推荐答案
我认为这是集内集"查询.我喜欢对这种类型的查询使用带有having
子句的聚合.以下内容将检查您要查找的三个条件:
I think this is a "set-within-sets" query. I like using aggregation with a having
clause for this type of query. The following checks the three conditions you are looking for:
select visitor_id
from event e
group by visitor_id
having sum(e.key = 'LOGIN') > 0 and
sum(e.key = 'VIEW_PAGE' and e.value = 'HOTEL') > 0 and
sum(e.key = 'PURCHASE_HOTEL') > 0;
having
子句中的第一个条件对LOGIN
记录的数量进行计数,并且在找到至少一个条件时为true. (如果您只想要一个,请将> 0
更改为= 0
.)
The first condition in the having
clause counts the number of LOGIN
records and is true when at least one is found. (If you want exactly one, change > 0
to = 0
.)
第二个条件检查了酒店页面的浏览情况.
The second condition checks the viewing of the hotel page.
第三位计算的是酒店购买次数.
The third counts the number of hotel purchases.
这篇关于MySQL-正确的进近事件计数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!