Table:
new_table
user_number | diff
2 | 0
1 | 28
2 | 32
1 | 40
1 | 53
1 | 59
1 | 101
1 | 105
2 | 108
2 | 129
2 | 130
1 | 144
|(result)
v
range | number of users
0-20 | 2
21-41 | 3
42-62 | 1
63-83 | 2
84-104 | 1
105-135| 0
136-156| 3
mysql> create table new_table (user_number int, diff int);
Query OK, 0 rows affected (0.14 sec)
mysql> insert into new_table values (2, 0), (1, 28), (2, 32), (1, 40), (1, 53), (1, 59), (1, 101), (1, 105), (2, 108), (2, 129), (2, 130), (1, 144);
Query OK, 12 rows affected (0.01 sec)
Records: 12 Duplicates: 0 Warnings: 0
mysql> select concat(21*floor(diff/21), '-', 21*floor(diff/21) + 20) as `range`, count(*) as `number of users` from new_table group by 1 order by diff;
+---------+-----------------+
| range | number of users |
+---------+-----------------+
| 0-20 | 1 |
| 21-41 | 3 |
| 42-62 | 2 |
| 84-104 | 1 |
| 105-125 | 2 |
| 126-146 | 3 |
+---------+-----------------+
6 rows in set (0.01 sec)
例如下面的代码求通话时长分布的：
每20秒作为一个分段， > 600秒的，汇总为一个分段。
SELECT * FROM (
SELECT
CONCAT(21 * FLOOR((UNIX_TIMESTAMP(endtime) - UNIX_TIMESTAMP(connectedtime)) / 21), "-", 21*FLOOR((UNIX_TIMESTAMP(endtime) - UNIX_TIMESTAMP(connectedtime)) / 21) + 20) AS "通话时长范围",
COUNT(*) AS "样本数"
FROM APP_CALL
WHERE UNIX_TIMESTAMP(endtime) - UNIX_TIMESTAMP(connectedtime) BETWEEN 0 AND 600
GROUP BY `通话时长范围`
ORDER BY UNIX_TIMESTAMP(endtime) - UNIX_TIMESTAMP(connectedtime)
) T1
UNION
SELECT ">600", COUNT(*) AS "样本数"
FROM APP_CALL
WHERE UNIX_TIMESTAMP(endtime) - UNIX_TIMESTAMP(connectedtime) > 600
GROUP BY 1