May 17, 2006 at 10:40 am
I have a table that records page hits, the relevant bits are as follows
create table pagehits(
id varchar(100), // pri key
sessionId varchar(100),
creative varchar(100),
adate datetime)
I’d like to retrieve today’s unique hits by creative. Multiple hits should be filtered out if they are part of the same session. One session may have multiple creative hits.
I started down the road of
select * from pagehits
where adate = (today)
group by sessionid, creative, sessionid, id
but this fails to return all the sessionid/creative rows, so now I think a self join is probably needed.
Any help appreciated.
- jim
May 17, 2006 at 10:59 am
I am still not real clear on what you want your end result to be. Can you post some sample data and an example of how you want your results to look?
May 17, 2006 at 1:44 pm
Some test data might look like this
INSERT INTO pagehits
(id, sessionId, adate, creative)
VALUES
("402881030b2a2b5b010b2a3014ed0002", "934A30C24E5C56AA2223BE821B437554", getdate(), "402881030b2a2a47010b2a2aa8ba0014");
INSERT INTO pagehits
(id, sessionId, adate, creative)
VALUES
("402881030b2a2b5b010b2a302e4f0003", "28A7F824D3F88BF8DDA34A02A2B32467", getdate(), "402881030b2a2a47010b2a2aa8ba0014");
INSERT INTO pagehits
(id, sessionId, adate, creative)
VALUES
("402881030b2a2b5b010b2a367bcc0004", "9A52A524304C663B076A0F7F0A553DCE", getdate(), "402881030b2a2a47010b2a2aa8ba0014");
INSERT INTO pagehits
(id, sessionId, adate, creative)
VALUES
("402881030b2a2b5b010b2a3689870005", "9A52A524304C663B076A0F7F0A553DCE", getdate(), "402881030b2a2a47010b2a2aa8ba0014");
INSERT INTO pagehits
(id, sessionId, adate, creative)
VALUES
("402881030b2a2b5b010b2a368e680008", "9A52A524304C663B076A0F7F0A553DCE", getdate(), "402881030b2a2a47010b2a2aa8ba0014");
For this data we should get one row with creative
402881030b2a2a47010b2a2aa8ba0014 and a count of 2. (Two sessions with creative 402881030b2a2a47010b2a2aa8ba0014 hits.)
thx again, jim
May 17, 2006 at 2:00 pm
May 17, 2006 at 3:10 pm
This appears to be more than just your normal self-join. In fact, I can not find a way to do this one without use of a temp table. Maybe some other forum guru's can give you a better solution, but with the way you are looking to count Creative values and group by SesionID, I can not find a one-DML-statement solution. Anyway, here's what I came up with (you will need to add a where clause to filter on date):
CREATE TABLE #tmpTable (
SessionID varchar(100),
Creative varchar(100)
)
INSERT INTO #tmpTable
SELECT DISTINCT SessionID, Creative
FROM PageHits
GROUP BY SessionID, Creative
SELECT DISTINCT Creative, SessionCount
FROM PageHits
INNER JOIN (SELECT COUNT(*) as SessionCount FROM #tmpTable) tT
ON 1 = 1
May 17, 2006 at 3:16 pm
I think I fell asleep at the wheel on this one. The solution in my previous post has flaws. Back to the drawing board.
May 17, 2006 at 3:57 pm
what is wrong with this:
SELECT SessionID, Creative, Count(*) Cnt
FROM PageHits
where adate = 'Todays'
GROUP BY SessionID, Creative
* Noel
May 17, 2006 at 6:14 pm
Thanks for the input so far.
John you are right, it's three sessions, not two.
Noel, the problem w this solution is that multiple creatives can be accessed in one session (even though my test data snippet didn't include this case), and this query doesn't catch this.
- jim
May 18, 2006 at 1:50 am
Jim,
I believe the following is what you are looking for. Copy the Statements into Query Analyzer and run them against your data. The first SELECT statement is simply a check so that you can validate the data. The second SELECT statement provides the data as I understand your request.
I included logic to strip the time portion of the date off so that all records would be evaluated correctly based on the given day.
Let me know if this helps.
Mark
----------------------------------------------------------------------------------
DECLARE @ADate AS DATETIME
SET @ADate = '2006-05-18'
--This Query is to show you the Unique Creative By Session with a
--count to show how many times a user has chosen a given creative within the same session
SELECT SessionID, Creative, COUNT(*) AS TotalCount
FROM PageHits
WHERE CONVERT(DATETIME, CONVERT(CHAR(10), ADate, 101), 101) = @ADate
GROUP BY Creative, SessionID
--This is the Query you are looking for showing the total count of unique Creative Hits
SELECT SQ.Creative AS Creative, COUNT(SQ.SessionID) AS TotalUniqueHits
FROM (
SELECT SessionID, Creative
FROM PageHits
WHERE CONVERT(DATETIME, CONVERT(CHAR(10), ADate, 101), 101) = @ADate
GROUP BY Creative, SessionID ) SQ
GROUP BY SQ.Creative
------------------------------------------------------------------------------------------------
SAMPLE DATA FROM FIRST SELECT
28A7F824D3F88BF8DDA34A02A2B32467 402881030b2a2a47010b2a2aa8ba0013 1
28A7F824D3F88BF8DDA34A02A2B32467 402881030b2a2a47010b2a2aa8ba0014 1
934A30C24E5C56AA2223BE821B437554 402881030b2a2a47010b2a2aa8ba0013 1
934A30C24E5C56AA2223BE821B437554 402881030b2a2a47010b2a2aa8ba0014 1
9A52A524304C663B076A0F7F0A553DCE 402881030b2a2a47010b2a2aa8ba0013 1
9A52A524304C663B076A0F7F0A553DCE 402881030b2a2a47010b2a2aa8ba0014 3
9A52A524304C663B076A0F7F0A553DCE 402881030b2a2a47010b2a2aa8ba0015 2
SAMPLE DATA FROM SECOND SELECT
402881030b2a2a47010b2a2aa8ba0013 3
402881030b2a2a47010b2a2aa8ba0014 3
402881030b2a2a47010b2a2aa8ba0015 1
May 18, 2006 at 7:38 am
DECLARE @start datetime, @end datetime
SET @start = DATEADD(month,DATEDIFF(month,0,GETDATE()),0)
SET @end = DATEADD(month,1,@start)
SELECT creative,COUNT(DISTINCT sessionid)
FROM [pagehits]
WHERE adate >= @start and adate < @end
GROUP BY creative
Far away is close at hand in the images of elsewhere.
Anon.
May 18, 2006 at 6:28 pm
Brilliant David, that's it. Thanks to all, jim
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply