1

We have two columns in a table customer in which we have customer id and the service id in those. So, every customer using multiple services, we have to find the maximum count of the consecutive services used by the customers. 

create table customer(id int not null auto_increment,customerid varchar(20), serviceid varchar(20),primary key(id));

insert into customer(customerid,serviceid) values('Nitesh','Mobile');
insert into customer(customerid,serviceid) values('Nitesh','Mobile');
insert into customer(customerid,serviceid) values('Nitesh','Landline');
insert into customer(customerid,serviceid) values('Nitesh','Broadband');
insert into customer(customerid,serviceid) values('Nitesh','Mobile');

insert into customer(customerid,serviceid) values('Nishant','Mobile');
insert into customer(customerid,serviceid) values('Nishant','Landline');
insert into customer(customerid,serviceid) values('Nishant','Landline');
insert into customer(customerid,serviceid) values('Nishant','Landline');
insert into customer(customerid,serviceid) values('Nishant','Broadband');

insert into customer(customerid,serviceid) values('Soe','Mobile');
insert into customer(customerid,serviceid) values('Soe','Mobile');
insert into customer(customerid,serviceid) values('Soe','Landline');
insert into customer(customerid,serviceid) values('Soe','Broadband');
insert into customer(customerid,serviceid) values('Soe','Mobile');

I have to count the maximum service used by the customer consecutively.

Output:

Customerid|Serviceid|ServiceidCount
----------------------------------

Nitesh|Mobile|2
Nishant|Landline|3
Soe|Mobile|2
Nitesh Kataria
  • 331
  • 1
  • 4
  • 14

2 Answers2

0

Let me assume that you have a column that specifies the ordering, say id.

Then you can do this using variables:

select customer, max(rn) as serviceidcnt,
       substring_index(group_concat(serviceid order by rn desc)) as serviceid
from (select c.*,
             (@rn := if(@cs = concat_ws(':', customerid, serviceid), @rn + 1,
                        if(@cs := concat_ws(':', customerid, serviceid), 1, 1)
                       )
             ) as rn
      from customer c cross join
           (select @rn := 0, @cs := '') params
      order by c.customer, ??
     ) cs
group by customer;

The ?? is for the column used for ordering.

Note: Getting the service id requires using a MySQL "hack" that depends on group_concat(). By default this is limited to an internal buffer size of about 1000 bytes. This can be changed.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

Updated version

You can try following query. It uses mysql variables to check for consecutive changes in columns customerid and serviceid.

    SELECT customerid, serviceid, MAX(R)+1 AS ServiceIdCount
FROM (SELECT c.customerid, c.serviceid
    , CASE WHEN @s=CONCAT(customerid,'|',serviceid) THEN @r:=@r+1 ELSE @r:=0 END AS R
    , @s:=CONCAT(customerid,'|',serviceid) AS S
    FROM customer c
    CROSS JOIN (SELECT @r:=0, @s:='') d
     ORDER BY id
    ) e 
WHERE R>0
GROUP BY customerid, serviceid;

Added sample data (other than yours):

insert into customer(customerid,serviceid) values('Mark','Mobile');
insert into customer(customerid,serviceid) values('Mark','Mobile');
insert into customer(customerid,serviceid) values('Mark','Mobile');
insert into customer(customerid,serviceid) values('Mark','Landline');
insert into customer(customerid,serviceid) values('Mark','Mobile');
insert into customer(customerid,serviceid) values('Mark','Mobile');

Output:

+------------+-----------+----------------+
| customerid | serviceid | ServiceIdCount |
+------------+-----------+----------------+
| Mark       | Mobile    |              3 |
| Nishant    | Landline  |              3 |
| Nitesh     | Mobile    |              2 |
| Soe        | Mobile    |              2 |
+------------+-----------+----------------+
etsa
  • 5,020
  • 1
  • 7
  • 18