Di meja saya, saya memiliki pesan yang dikelompokkan berdasarkan utas. Menggunakan LEAD, saya bisa mendapatkan pesan berikutnya dalam utas...

[ThreadNextMessageId] = LEAD(gm.GroupMessageId, 1) OVER(PARTITION BY gm.ThreadId ORDER BY gm.GroupMessageId)

But how do I tell how many rows are between the current row and the next row in the thread when ordering by GroupMessageId? The table will likely contain rows from other threads in between and I want to count those as well.

I found one way that seems to do this by enclosing my query as a CTE and then using the ID produced by LEAD to count the number of rows between the current GroupMessageId and ThreadNextMessageId, but it seems like there should be a more efficient way.

WITH cte AS (
    SELECT
        gm.GroupMessageId
        ,gm.ThreadId
        ,[ThreadNextMessageId] = LEAD(gm.GroupMessageId) OVER(PARTITION BY gm.ThreadId ORDER BY gm.GroupMessageId)
        ,[ThreadPrevMessageId] = LAG(gm.GroupMessageId) OVER(PARTITION BY gm.ThreadId ORDER BY gm.GroupMessageId)
    FROM dbo.GroupMessage gm
)
SELECT 
    cte.GroupMessageId
    ,cte.ThreadId
    ,cte.ThreadNextMessageId
    ,cte.ThreadPrevMessageId
    ,[ThreadNextOffset] = (SELECT COUNT(*) FROM cte c1 WHERE c1.GroupMessageId >= cte.GroupMessageId AND c1.GroupMessageId < cte.ThreadNextMessageId)
    ,[ThreadPrevOffset] = (SELECT COUNT(*) FROM cte c1 WHERE c1.GroupMessageId >= cte.ThreadPrevMessageId AND c1.GroupMessageId < cte.GroupMessageId)
FROM cte
ORDER BY cte.GroupMessageId

Is there another method for counting rows between the current one and ones produced by LEAD or LAG that doesn't require a CTE - perhaps using some other SQL window function?

1
adam0101 23 Juli 2020, 20:34

1 menjawab

Jawaban Terbaik

Saya akan menyarankan menghitung semua baris dan menggunakan aritmatika:

WITH gm AS (
      SELECT gm.*,
             ROW_NUMBER() OVER (ORDER BY GroupMessageId) as seqnum
      FROM dbo.GroupMessage gm
     )
SELECT gm.*,
       (LEAD(seqnum) OVER (PARTITION BY gm.ThreadID ORDER BY gm.GroupMessageId) - seqnum - 1) as messages_in_between
FROM gm;
ORDER BY gm.GroupMessageId
1
Gordon Linoff 24 Juli 2020, 00:37