结合回收多个自我联接的mysql查询 [英] Combining mysql queries that recycle multiple self joins

问题描述

我有以下一组MySQL查询，用于跟踪用户通过网站的进度.有什么好方法可以简化它们吗?

I have the following set of MySQL queries, used to track user progress through a website. Is there a good way to simplify them?

#How many people reached stage 2
SELECT COUNT(DISTINCT a.session_id) as "total"
    FROM formation_page_hits a
    WHERE a.progress = 2
    AND DATE(a.datetime) = "2011-03-23";

#How many people reached stage 4 having reached stage 2
SELECT COUNT(DISTINCT a.session_id) as "total"
    FROM formation_page_hits a, (SELECT f.session_id, f.`datetime`
                        FROM formation_page_hits f
                        WHERE f.progress = 2) as b
    WHERE a.progress = 4
    AND a.session_id = b.session_id
    AND DATE(b.datetime) = "2011-03-23"
    AND DATE(a.datetime) = "2011-03-23";


#How many people reached stage 7, having reached stage 4, having reached stage 2
SELECT COUNT(DISTINCT a.session_id) as "total"
    FROM formation_page_hits a, (SELECT f.session_id, f.`datetime`
                        FROM formation_page_hits f
                        WHERE f.progress = 4) as b, (SELECT f.session_id, f.`datetime`
                        FROM formation_page_hits f
                        WHERE f.progress = 2) as c
    WHERE a.progress = 7
    AND a.session_id = b.session_id
    AND a.session_id = c.session_id
    AND DATE(c.datetime) = "2011-03-23"
    AND DATE(b.datetime) = "2011-03-23"
    AND DATE(a.datetime) = "2011-03-23";

如您所见，我很快就会重新查询相同的信息，并且还有另外4或5个遵循相同模式的查询-有没有更好的构造查询的方式，这意味着我没有继续查询有多少人达到了阶段2"?

As you can see, I'm very quickly re-querying the same information and there are an additional 4 or 5 queries that follow the same pattern - is there a better way of constructing the query that means I don't have to keep querying for "how many people reached stage 2"?

编辑:每个页面浏览量都作为一个条目存储在formation_page_hits中-这样就可以完整记录每个会话的页面浏览量

each page view is stored as an entry in formation_page_hits - so that there is a complete record of page views for each session

id_formation_page_hits INT PRIMARY_KEY, session_id VARCHAR(100), datetime DATETIME, progress INT

推荐答案

SELECT  COUNT(*)
FROM    (
        SELECT  session_id
        FROM    formation_page_hits
        WHERE   progress IN (2, 4, 7)
                AND datetime >= '2011-03-23'
                AND datetime < '2011-03-24'
        GROUP BY
                session_id
        HAVING  COUNT(DISTINCT progress) = 3
        ) q

在(session_id, datetime, progress)上创建一个复合索引，以使其快速运行.

Create a composite index on (session_id, datetime, progress) for this to work fast.

这篇关于结合回收多个自我联接的mysql查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！