Mysqlデータ分布統計クラシック専用SQL

1.データベース構築文

CREATE TABLE `start_duration` (
  `id` bigint(13) NOT NULL AUTO_INCREMENT COMMENT '  id',
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '    ',
  `device_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '  id',
  `country` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '  ',
  `start_time` int(11) NOT NULL DEFAULT '0' COMMENT '    ',
  `duration` int(11) NOT NULL DEFAULT '0' COMMENT '    ',
  `create_time` datetime DEFAULT NULL COMMENT '    ',
  `update_time` datetime DEFAULT NULL COMMENT '    ',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4

2.統計durationのデータ分布

SELECT
  case when duration >0 and duration <500 then '1:(0,500)'
        when duration >=500 and duration <1000 then '2:[500,1000)'
        when duration >=1000 and duration <2000 then '3:[1000,2000)'
        when duration >=2000 and duration <3000 then '4:[2000,3000)'
        when duration >=3000 and duration <5000 then '5:[3000,5000)'
        when duration >=5000 and duration <10000 then '6:[5000, 10000)'
        when duration >=10000 and duration <20000 then '7:[10000, 20000)'
        when duration >=20000 then '8:[20000+)'
				ELSE '1:(0,500)' END 'duration(ms)', 
				COUNT(*) 'count'
FROM  start_duration
WHERE create_time > '2019-06-01 00:00:00'
GROUP BY 
  case when duration >0 and duration <500 then '1:(0,500)'
        when duration >=500 and duration <1000 then '2:[500,1000)'
        when duration >=1000 and duration <2000 then '3:[1000,2000)'
        when duration >=2000 and duration <3000 then '4:[2000,3000)'
        when duration >=3000 and duration <5000 then '5:[3000,5000)'
        when duration >=5000 and duration <10000 then '6:[5000, 10000)'
        when duration >=10000 and duration <20000 then '7:[10000, 20000)'
        when duration >=20000 then '8:[20000+)'
				ELSE '1:(0,500)' END

3.統計実行結果

+------------------+-------+
| duration(ms)     | count |
+------------------+-------+
| 1:(0,500)        | 14896 |
| 2:[500,1000)     | 12847 |
| 3:[1000,2000)    | 11132 |
| 4:[2000,3000)    |  4603 |
| 5:[3000,5000)    |  1459 |
| 6:[5000, 10000)  |  449 |
| 7:[10000, 20000) |  204 |
| 8:[20000+)       |  156 |
+------------------+-------+

分析:各分布のフィールドの前で天機の“数字:”のフォーマットは十分に利用することができて、mysqlの自動並べ替えの機能、データを更に美しく整然と見せることができます