Combining mysql queries that recycle multiple self joins
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";
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"?
EDIT: 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
Create a composite index on (session_id, datetime, progress)
for this to work fast.
精彩评论