MySQL:计算一个值连续出现的次数 [英] MySQL: Count the number of consecutive times a value appears

问题描述

我做了一些谷歌搜索来尝试找到这个答案，甚至遇到了 SQL Server 的 OVER() 函数和一篇关于如何模拟的文章......但它高于我的工资等级:P

I've done a bit of Googling to try and find this answer and even came across the OVER() function for SQL Server and an article on how to emulate...but it's above my pay grade :P

我会保持简单，假设我有一个学生表和一个操作列.我想创建一个查询来计算他们举手、不举手或不上课的连续次数.没有周末课，所以它必须计算实际记录，而不仅仅是今天-FirstFoundDate.如果学生 A 连续举手 3 周(即 15=5 天 X 3 周)但没有出现在课堂上，则计数为 0.

I'll keep it simple and say I have a table of students and an action column. I want to create a query that will count the number of consecutive times that they either raised their hand, didn't raise their hand, or didn't attend class. There are no weekend classes, so it has to count the actual records, not just today-FirstFoundDate. If student A raises their hand for 3 weeks straight (that count would be 15=5days X 3weeks) but then doesn't show up to class, the count will be 0.

有什么想法吗?

谢谢，

推荐答案

我一直在考虑这个，让我们重新开始...

I've been thinking about this, and let's start over...

怎么样

select *
from student_action maindata
where not exist (select * from student_action action_count
                 where maindata.student_id = action_count.student_id
                   and maindata.action_timestamp < action_count.action_timestamp
                   and maindata.action <> action_count.action)

这应该给我们表中的所有连续动作，每次连续出现一行.然后 group by 将计算它们.

This should give us all the consecutive actions in the table, one row for each consecutive occurrence. Then the group by will count them.

select count(*), maindata.student_id, maindata.action
from student_action maindata
where not exist (select * from student_action action_count
                 where maindata.student_id = action_count.student_id
                   and maindata.action_timestamp < action_count.action_timestamp
                   and maindata.action <> action_count.action)
group by maindata.student_id, maindata.action

这篇关于MySQL:计算一个值连续出现的次数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！