· 8 years ago · Jul 06, 2018, 08:34 PM
11064
2
31064
4
5SELECT MSISDN,Total_counts,TIMESTAMPDIFF(MINUTE, MIN(times), MAX(times)) AS duration
6
7FROM (
8
9SELECT t1.*
10, @groupNumber := IF(@prev_MSISDN != t1.MSISDN, @groupNumber + 1, @groupNumber) AS gn
11, @prev_msisdn := t1.MSISDN
12FROM (
13
14#t1
15#[ itwill returns the msisdn,incoming_timestamp of all msisdn and total count and order them according to their msisdn ,timestamp
16SELECT tb1.a_msisdn AS 'MSISDN',tb1.incoming_timestamp AS 'Times',tb2.total_count AS 'Total_counts'
17FROM cc_info AS tb1
18JOIN
19#{it will count the msisdn whose total_count>=10
20(SELECT COUNT(1) AS 'Total_count',a_msisdn
21FROM cc_info
22GROUP BY a_msisdn
23HAVING Total_count>=10
24ORDER BY incoming_timestamp) AS tb2
25#}
26ON tb1.`a_msisdn`=tb2.a_msisdn
27#WHERE tb1.a_msisdn=420000128321 #it is for testing
28ORDER BY tb1.a_msisdn ,tb1.incoming_timestamp
29#]
30) AS t1
31,(SELECT @groupNumber := 0, @prev_MSISDN := NULL) var_init_subquerysa ORDER BY MSISDN,times
32
33) AS t2
34
35GROUP BY gn,MSISDN
36
37CREATE TABLE IF NOT EXISTS `cc_info` (
38 `a_msisdn` bigint unsigned NOT NULL,
39 `Incoming_timestamp` timestamp NOT NULL
40);
41
42INSERT INTO `cc_info` (`a_msisdn`, `Incoming_timestamp`) VALUES
43 ('420000128321', '2018-06-26 06:26:02'),
44 ('420000128321', '2018-06-26 07:04:44'),
45 ('420000128321', '2018-06-26 07:04:44'),
46 ('420000128321', '2018-06-26 07:04:44'),
47 ('420000128321', '2018-06-26 07:04:44'),
48 ('420000128321', '2018-06-26 07:04:44'),
49 ('420000128321', '2018-06-26 07:04:44'),
50 ('420000128321', '2018-06-26 07:04:44'),
51 ('420000128321', '2018-06-26 07:04:44'),
52 ('420000128321', '2018-06-26 07:04:44'),
53 ('420000128321', '2018-06-26 07:04:44'),
54 ('420000128321', '2018-06-26 07:04:44'),
55 ('420000128321', '2018-06-26 07:04:44'),
56 ('420000128321', '2018-06-26 07:04:44'),
57 ('420000128322', '2018-06-26 06:04:44'),
58 ('420000128322', '2018-06-26 07:04:44'),
59 ('420000128322', '2018-06-26 07:04:44'),
60 ('420000128322', '2018-06-26 07:04:44'),
61 ('420000128322', '2018-06-26 07:04:44'),
62 ('420000128322', '2018-06-26 07:04:44'),
63 ('420000128322', '2018-06-26 07:04:44'),
64 ('420000128322', '2018-06-26 07:04:44'),
65 ('420000128322', '2018-06-26 07:04:44'),
66 ('420000128322', '2018-06-26 07:04:44'),
67 ('420000128322', '2018-06-26 07:04:44');
68
69) AS t2
70
71ORDER BY MSISDN,times