SQL Server Performance Forum – Threads Archive
Help me eliminate an evil cursor !
Hi People Working with a log of visitors to my website I have the following tabelSection = Section of the website denoted as an int. SequeiceId (int), VisitorId (int), SectionId(int) 1,1,2300
2,1,2300
3,1,2700
4,1,2800
5,1,2800
6,1,2300
7,2, 8200
…,…, … I want to feed this log into a transition matrix (marlow chaining). In order to do that I would need to find all (unique) transisions (movements from one section to another), by VisitorId. This means that I would need an output recordset that should look like this: 1,1,2300
3,1,2700
4,1,2800
6,1,2300
7,2, 8200 Where all the visis to the same sectionId, two or more times in a row, are removed. I have already a working solution, using a cursor, but being quite conservative about (evil) cursors (from the Dark Side of the force) my DBA will not allow cursors to run on a production database. The million dollar question: How can I obtain the result, without writing a cursor ? I still need to minimize the use of temporal tables ? Thanks in advance ! regs Mads
(Database developer)
"Follow the join tree" – Dan Tow
Try this Select * from table T
inner join
(
Select SequeiceId , VisitorId , Min(SectionId) as SectionId from table
Group by SequeiceId , VisitorId
) A
on T.SequeiceId =A.SequeiceId and T.VisitorId =A.VisitorId and T.SectionId=A.SectionId Madhivanan Failing to plan is Planning to fail
Thanks for the reply, however, I have found a solution by myself: declare @SequenceOK TABLE
(
tblId int IDENTITY (1,1) NOT null,
UserId int null,
SectionId int null
)
declare @PairSequence TABLE
(
tblId int IDENTITY (1,1) NOT null,
UserId int null,
Item1 int null,
Item2 int null,
Cnt int null
) — Order source table to show ordered sequence for each user.
INSERT INTO @SequenceOK (UserId, SectionId)
SELECT UserId, SectionId
FROM tblSeq
ORDER BY UserId, tblId — Find the sequnece pairs for each user visit
INSERT INTO @PairSequence (UserId, Item1, Item2, Cnt)
SELECT a.UserId, a.SectionId, b.SectionId, count(*) FROM
@SequenceOK a,
@SequenceOK b where a.UserId = b.UserId
AND a.tblId + 1 = b.tblId GROUP BY a.UserId, a.SectionId, b.SectionId — Tuncate the destination cross table
TRUNCATE TABLE tmpCrosstblTest — populate CrossTable with relevant structure, leaving out irrelevant item pairs
INSERT INTO tmpCrosstblTest (Item1, Item2, Cnt)
SELECT a.Item1, b.Item2, 0
from
(Select distinct Item1 from @PairSequence ) a,
(Select distinct Item2 from @PairSequence ) b — Polulate CrossTable with calculated values.
UPDATE tmpCrosstblTest
SET tmpCrosstblTest.Cnt = tmpCrosstblTest.Cnt + ps.cnt
FROM @PairSequence ps
WHERE ps.Item1 <> ps.Item2
AND tmpCrosstblTest.Item1 = ps.Item1
and tmpCrosstblTest.Item2 = ps.Item2
"Follow the join tree" – Dan Tow
Did you get the same result as that of what my query returns? Madhivanan Failing to plan is Planning to fail
No. The problem with your proposal, is that if a user has the following "clickpath" 1 2300
2 2300
3 4300
4 2300 I want to derive
2300 -> 4300
4300 -> 2300 Your result would only give 2300 -> 4300 As far as i can see (I might be wrong). regs,
Mads "Follow the join tree" – Dan Tow
]]>