Mysql中的笔试和面试---Mysql进行数据分布统计经典专用SQL

最新推荐文章于 2024-05-30 16:04:32 发布

原创最新推荐文章于 2024-05-30 16:04:32 发布 · 3.9k 阅读

7 ·

CC 4.0 BY-SA版权

Mysql 专栏收录该内容

22 篇文章

订阅专栏

本文详细介绍了数据库中创建表格的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的自动排序功能，使数据展现更美观整齐