时间戳

首页 > 解决方案 > 如何根据列值创建 MySQL 直方图类型的桶

mysql - 如何根据列值创建 MySQL 直方图类型的桶

问题描述

我有下表，我正在尝试通过 MySQL 创建直方图样式的存储桶。

| Id       | Values    |
| -------- | --------- |
| 1        | 5         |
| 2        | 7         |
| 3        | 9         |
| 4        | 11        |
| 5        | 15        |
| 6        | 31        |
| 7        | 32        |
| 8        | 43        |

我想要实现的目标如下：

| bucket   | count     |
| -------- | --------- |
| 0-9      | 3         |
| 10-19    | 2         |
| 20-29    | 0         |
| 30-39    | 2         |
| 40-49    | 1         |

有谁知道我们如何以干净的方式得到这个？

标签： mysql

解决方案

一种可能的方法是为存储桶列表创建一个参考表，然后LEFT JOIN将其与您的表一起创建。尝试以下步骤。

  1. 例如创建一个表bucket_list：
CREATE TABLE bucket_list (
 id INT NOT NULL AUTO_INCREMENT,
 startno INT,
 endno INT,
 PRIMARY KEY(id));
  1. 将值插入bucket_list：
 INSERT INTO bucket_list (startno, endno)
VALUES
  (0, 9),
  (10, 19),
  (20, 29),
  (30, 39),
  (40, 49),
  (50, 59),
  (60, 69),
  (70, 79);
  1. 创建查询以返回预期结果：
SELECT CONCAT(a.startno,'-',a.endno) AS bucket,
       SUM(CASE WHEN b.val IS NULL THEN 0 ELSE 1 END) AS COUNT
FROM bucket_list a 
LEFT JOIN mytable b ON b.val BETWEEN a.startno AND a.endno
GROUP BY bucket;

这是一个小提琴：https ://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=7fee426efa2b1f1e39377bb7beb68b62

推荐阅读