带计数的SQL连续记录 [英] SQL Consecutive records with count

问题描述

表名调用

Field 1 - Phone_number
Field 2 - System_outcome

Phone_number      System_outcome  DateTime
--------------------------------------------------
07777778999       Answered        18-12-2016 17:15
07777778123       No Answer       18-12-2016 18:10
07777778999       No Answer       19-12-2016 19:30
07777778999       No Answer       19-12-2016 12:15
07777778999       No Answer       19-12-2016 13:15
07777778999       No Answer       20-12-2016 11:15
07777778124       No Answer       20-12-2016 9:15
07777778128       Answered        20-12-2016 17:15
07777778074       Answered        20-12-2016 17:15

上面是一个示例，我需要找出的是如何找到连续无应答计数大于6的数字?

Above is an example, what i need to find out is how do i find numbers that have a consecutive no answer count of more than 6?

此刻我已经获得了以下内容，但并不是连续的.

at the moment i have been able to get the following but it's not consecutive.

SELECT phone_number,system_outcome,Datetime
FROM calls
WHERE DATE (datetime) BETWEEN '2016-12-23' AND '2016-12-31'
AND system_outcome = 'NO_ANSWER'
GROUP BY phone_number
HAVING count(Phone_number) > 6

推荐答案

您可以使用变量来执行此操作.

You can use variables to do this.

select phone_number from (
select c.*,
@prev_outcome:=@cur_outcome,
@cur_outcome:=system_outcome,
@prev_pnum:=@cur_pnum,
@cur_pnum:=phone_number,
case when @cur_pnum = @prev_pnum and @prev_outcome <> @cur_outcome then @rn:=@rn+1
     when @cur_pnum = @prev_pnum and @prev_outcome = @cur_outcome then @rn:=@rn
else @rn:=1 end as rank
from calls c, 
(select @rn:=0,@prev_pnum:='',@cur_pnum:='',@prev_outcome:='',@cur_outcome:='') r
order by phone_number,dt
    ) x
where system_outcome='No Answer'
group by phone_number,rank
having count(*) > 6

此查询使用4个变量

1)@cur_outcome，它最初设置为空字符串.此后，选择将分配当前行的system_outcome.

1) @cur_outcome which is initially set to an empty string. Thereafter the select assigns the current row's system_outcome.

2)@prev_outcome，它最初设置为空字符串.此后，选择将其设置为@cur_outcome(这是第一次为空字符串，依此类推).

2) @prev_outcome which is initially set to an empty string. Thereafter the select sets it to the @cur_outcome (which is an empty string for the first time and so on).

3)@cur_pnum，它最初设置为空字符串.此后，选择将分配当前行的phone_number.

3) @cur_pnum which is initially set to an empty string. Thereafter the select assigns the current row's phone_number.

4)@prev_pnum，它最初设置为空字符串.此后，选择将其设置为@cur_pnum值(最初为空字符串).

4) @prev_pnum which is initially set to an empty string. Thereafter the select sets it to @cur_pnum value (which is an empty string initially).

order by子句在这里很重要，它可以根据phone_number和date指定当前行和先前行.

order by clause is important here to designate the current and previous rows based on phone_number and date.

首先运行内部查询，以了解变量的设置方式，这将为您澄清事物.

Run the inner query initially to see how the variables are set, which would clarify things for you.

Sample Demo

该演示包含的一些示例数据比问题中显示的要多.

The demo contains some sample data more than what was shown in the question.

这篇关于带计数的SQL连续记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！