Table view:

id___user_id___lender_id___status 1 1 2 P 2 1 3 P 3 1 4 P 4 1 5 P 5 1 6 P 6 1 7 S 7 1 6 P 8 2 1 P 9 2 3 P 10 2 1 S

It is necessary to display user_id if he has 5+ consecutive failed statuses (status! = "S") with 4+ different lender_id.

Result of this request: user_id = 1

  • one
    And what is a "contract"? in SQL, it is not defined which record is the first one, but which one goes after it, until sorting is defined in the query. And what did you try to do to solve your problem? And how can there be 3 unsuccessful statuses with 4 different lenders, you should decide how many records there should be, at least 3 or at least 4 - Mike
  • @Mike if the next record has the status S then it does not enter the condition of the sample, we look further and consider again the number of status not equal to S. What I have is not working yet and very little to show. Yes there is a typo, now I will correct it - Barlukov
  • And what is the "next record", how to understand that what is the next record, and not the previous one? In general, it is convenient to use variables in MySQL for processing successive records: ru.stackoverflow.com/questions/568737 - Mike
  • @Mike yes, probably still it will be necessary to use stored procedures - Barlukov
  • Well, then it is definitely not needed. Why did it happen. almost anything can be obtained in one request without procedures - Mike

1 answer 1

On classic SQL: for all records with the status not S, we select such records for which the user_id matches, id is greater than or equal (that is, the same record), and status is not the same for S and there are no records with the status S for which The user matches the id between the two above. After that, the records are grouped by id from the first table (all records going without breaks will be with one id), count and check the necessary quantities. In this case, the user_id can be duplicated if it has several sections that are suitable for the conditions, so we use distinct.

 select distinct A.user_id from Table1 A join Table1 B on B.user_id=A.user_id and B.id>=A.id and B.status!='S' and not exists(select 1 from Table1 C where C.user_id=A.user_id and C.status='S' and C.id>A.id and C.id<B.id ) where A.status!='S' group by A.id, A.user_id having count(1)>=5 and count(distinct B.lender_id)>=4

Example

Using variables, only for MySQL, should work much faster than the first, since in a single pass of the table. Here we introduce the rank variable which is incremented by 1 every time we encounter a record with status S. Thus, with the same rank there can be only one record with status S and several records immediately following it with other statuses. Records S are discarded, we consider the remaining records with the same rank.

 select distinct user_id from ( select user_id, lender_id, status, @rank:=@rank+(status='S') rank from Table1 T, (select @rank:=0) i order by user_id, id ) X where status!='S' group by user_id, rank having count(1)>=5 and count(distinct lender_id)>=4

Example

  • Thank you very much! - Barlukov
  • @Barlukov I made another version with a variable, it turned out shorter;) - Mike
  • Yes, there is noticeably better speed, you're just the best! - Barlukov