萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 统计商品价格区间分布:CASE WHEN + GROUP BY 一条 SQL 搞定

需求背景

电商后台需统计各价格区间商品数量,Excel 中常用 LOOKUP + 透视表,MySQL 单条 SQL 即可完成。

核心 SQL

SELECT price_range, COUNT(*) AS number
FROM (
    SELECT CASE
        WHEN Price >= 0   AND Price <= 49.99  THEN '0-50'
        WHEN Price >= 50  AND Price <= 99.99  THEN '50-100'
        WHEN Price >= 100 AND Price <= 149.99 THEN '100-150'
        WHEN Price >= 150 AND Price <= 199.99 THEN '150-200'
        WHEN Price >= 200 AND Price <= 249.99 THEN '200-250'
        ELSE '250+'
    END AS price_range
    FROM products
    WHERE 1
) AS price_summaries
GROUP BY price_range
ORDER BY price_range;

结果示例

+------------+--------+
| price_range| number |
+------------+--------+
| 0-50       | 1234   |
| 50-100     | 856    |
| 100-150    | 421    |
| 150-200    | 210    |
| 200-250    | 98     |
| 250+       | 45     |
+------------+--------+

进阶变体

动态区间(参数化)

SET @bins = '0,50,100,150,200,250';
SELECT
    CONCAT(
        COALESCE(NULLIF(SUBSTRING_INDEX(SUBSTRING_INDEX(@bins, ',', n), ',', -1), ''), '0'),
        '-',
        COALESCE(NULLIF(SUBSTRING_INDEX(SUBSTRING_INDEX(@bins, ',', n+1), ',', -1), ''), '+')
    ) AS price_range,
    COUNT(*) AS number
FROM products
JOIN (SELECT 1 n UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6) nums
  ON Price >= SUBSTRING_INDEX(SUBSTRING_INDEX(@bins, ',', n), ',', -1)
 AND Price <  COALESCE(NULLIF(SUBSTRING_INDEX(SUBSTRING_INDEX(@bins, ',', n+1), ',', -1), ''), 999999)
GROUP BY n
ORDER BY n;

直方图函数

-- MySQL 8.0.2+ 近似直方图
SELECT HISTOGRAM(Price, 10) FROM products;
-- 返回 JSON 格式桶分布,适合快速概览

性能提示

  • Price 列加索引可加速范围过滤(若区间选择性高)。
  • 子查询物化开销极小,百万行表通常 < 100ms。
  • 频繁查询可建汇总表 + 定时任务/触发器维护。
copyright © 萬盛學電腦網 all rights reserved