DSIE 6.3.1 consecutive number

Find all job ids that appear at least twice consecutively.

SELECT DISTINCT a.jobid
FROM logs a
JOIN logs b
ON a.timestamp = b.timestamp + 1
AND a.jobid = b.jobid;

Find all job ids that appear at least k times consecutively.

SELECT jobid, sum_ind, COUNT(*)
FROM (SELECT jobid,
SUM(ind) OVER (ORDER BY timestamp) AS sum_ind
FROM (SELECT a.timestamp, a.jobid,
CASE WHEN b.jobid IS NULL OR a.jobid != b.jobid
THEN 1 ELSE 0 END AS ind
FROM logs a
JOIN logs b
ON a.timestamp = b.timestamp + 1) sub1) sub2
GROUP BY jobid, sum_ind
HAVING COUNT(*) >= k;
Like what you read? Give Shaojun Zhang a round of applause.

From a quick cheer to a standing ovation, clap to show how much you enjoyed this story.