Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
921 views
in Technique[技术] by (71.8m points)

presto - Counting the number of rows between transitions of values in SQL

I have rows with user_id, timestamps and YES / NO answer. I want to count how many streaks (consecutive rows) of "NO"s each ID has.

Example:

user_id timestamp response no_streak
1 2021-01-20 13:59:26 YES 0
1 2021-01-20 14:01:27 NO 1
1 2021-01-20 14:03:21 NO 2
1 2021-01-20 14:07:29 NO 3
1 2021-01-20 14:09:22 YES 0
1 2021-01-20 14:11:26 YES 0
1 2021-01-20 14:13:30 NO 1
1 2021-01-20 14:17:26 NO 2
1 2021-01-20 14:19:29 YES 0
1 2021-01-20 14:25:30 NO 1
1 2021-01-20 14:27:23 NO 2
1 2021-01-20 14:31:23 NO 3
1 2021-01-20 14:35:27 NO 4
1 2021-01-20 14:39:24 YES 0
2 2021-01-20 14:39:24 NO 1
2 2021-01-20 14:47:28 NO 2
2 2021-01-20 14:49:22 NO 3
2 2021-01-20 14:51:25 NO 4
2 2021-01-20 14:53:29 NO 5
2 2021-01-20 14:55:22 NO 6
2 2021-01-20 14:57:22 YES 0
question from:https://stackoverflow.com/questions/65945841/counting-the-number-of-rows-between-transitions-of-values-in-sql

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

Count the number of "yes"s up to each row so the adjacent NOs have the same grouping value. Then filter and aggregate:

select t.user_id, count(*), min(timestamp), max(timestamp)
from (select t.*,
             sum(case when response = 'YES' then 1 else 0 end) over (partition by user_id order by timestamp) as grp
      from t
     ) t
where response = 'NO'
group by user_id, grp;

Note: This doesn't return streaks of 0 length. I'm not sure of "streak" is the right word for that. But to get them, remove the where filter and use conditional aggregation:

select t.user_id, sum(case when response = 'NO' then 1 else 0 end),
       min(timestamp), max(timestamp)
from (select t.*,
             sum(case when response = 'YES' then 1 else 0 end) over (partition by user_id order by timestamp) as grp
      from t
     ) t
group by user_id, grp;

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...