Count for consecutive records
I have a table as follows
> RowID SessionID EventID RequestedURL Date > 1 m2jqyc45g 1 Start 24/03/2010 19:52 > 2 m2jqyc45g 1 ProductPage 24/03/2010 19:52 > 3 m2jqyc45g开发者_运维技巧 28 BuyNow 24/03/2010 19:52 > 4 tjmvb55dc4dg 1 ProductPage 24/03/2010 19:52 > 5 tjmvb55dc4dg 1 BuyNow 24/03/2010 19:56 > 6 tjmvb55dc4dg 1 Clicked OK 24/03/2010 19:56 > 7 m2jqyc45g 1 Clicked OK 24/03/2010 19:56 > 8 tjmvb55dc4dg 28 Help 24/03/2010 19:56 > 9 m2jqyc45g 1 Home 24/03/2010 19:56 > 6 m2jqyc45g 1 ProductPage 24/03/2010 19:56 > 7 tjmvb55dc4dg 1 BuyNow 24/03/2010 19:56 > 8 tjmvb55dc4dg 28 Clicked OK 24/03/2010 19:56 > 9 tjmvb55dc4dg 1 Home 24/03/2010 19:56
How do I write a query that does a count whenever the rows BuyNow and Clicked OK have been recorded consecutively in a particular session? For example, the dataset above shows 2 users on the site recording separate sessions. The return count should be 3.
I'm running SQL Server 2008
EDIT Just to clarify What I mean by consecutive rows and why the count should return 3. In the session m2jqyc45g, the BuyNow and Clicked Ok occurs consecutive within the session but when it's entered into the table, there are other concurrent sessions too which breaks the sequence. If you were to order the result set by SessionID and then by Date, you will get them one after the other. Also, there was a typo with RowID7, ProductPage should be BuyNow. Sorry for that.
SELECT COUNT(*)
FROM yourTable y
WHERE RequestedURL = 'BuyNow'
AND Exists (SELECT *
FROM yourTable x
WHERE x.RowID = (SELECT MIN(RowID)
FROM yourTable z
WHERE z.SessionID = y.SessionID
AND z.RowID > y.RowID)
AND RequestedURL = 'Clicked')
This should do it. There might be a faster way but I am not sure what else you can do. Also, you will probably want to but indexes on RowID and SessionID.
Your other option is to use Common Table Expressions to get row IDs that are group by session and time. The below could should do that for you.
WITH temp AS (
SELECT
SessionID,
RequestedURL,
[Date],
RN = ROW_NUMBER() OVER (ORDER BY SessionID,[Date])
FROM yourTable
ORDER BY SessionID,[Date])
SELECT
COUNT(*)
FROM temp x
JOIN temp y ON x.SessionID = y.SessionID
AND x.RN = y.RN - 1
WHERE x.RequestedURL = 'BuyNow'
AND y.RequestedURL = 'Clicked'
精彩评论