基于模糊条件分组：能否用SQL实现按价格相似度分组统计？

当然能搞定这种按价格比例分组的需求！这种需要逐行判断相邻记录是否符合条件的分组，用递归CTE是最顺手的方式，它能帮我们给符合条件的行分配同一个组ID，之后再做聚合统计就行。

先给你完整的可运行SQL代码，之后再拆解每部分的作用：

-- 先创建测试表和插入数据（如果已经有表可以跳过这部分）
CREATE TABLE items (name text, price float);
INSERT INTO items VALUES 
('foo', 42.0),
('foo', 42.5),
('foo', 100),
('bar', 42);

-- 核心查询
WITH ranked_items AS (
    -- 第一步：给每个name下的记录按价格排序，生成行号
    SELECT 
        name,
        price,
        ROW_NUMBER() OVER (PARTITION BY name ORDER BY price) AS rn
    FROM items
),
grouped_items AS (
    -- 锚点成员：每个name的第一条记录，初始组ID设为1
    SELECT 
        name,
        price,
        rn,
        1 AS group_id
    FROM ranked_items
    WHERE rn = 1
    
    UNION ALL
    
    -- 递归成员：逐行判断是否加入上一个组
    SELECT 
        ri.name,
        ri.price,
        ri.rn,
        CASE 
            -- 核心条件：当前价格和上一行同name的组内价格比例在0.9-1.1之间
            WHEN ri.price / gi.price BETWEEN 0.9 AND 1.1 THEN gi.group_id
            ELSE gi.group_id + 1
        END AS group_id
    FROM ranked_items ri
    JOIN grouped_items gi ON ri.name = gi.name AND ri.rn = gi.rn + 1
)
-- 最后按name和组ID聚合统计
SELECT 
    name AS item,
    MIN(price) AS min_price,
    MAX(price) AS max_price,
    COUNT(*) AS count
FROM grouped_items
GROUP BY name, group_id
ORDER BY name, min_price;

代码拆解说明 #

咱们一步步看这个查询是怎么工作的：

1. ranked_items CTE
   先把同一个name的记录按price升序排好，给每条记录分配一个行号rn。这样我们就能按顺序逐个处理每个name下的行，确保我们是和前一个同name的记录做比较。

2. grouped_items CTE
   这是递归的核心部分，分成两部分：

   • 锚点成员：取每个name的第一条记录（行号为1），给它分配初始组ID为1，这是每个name分组的起点。
   • 递归成员：把当前行（行号=上一行行号+1）和上一行的组信息关联起来，判断当前行的价格和上一行价格的比例是否在0.9到1.1之间。如果符合条件，就把当前行归到上一行的组里；如果不符合，就新建一个组（组ID加1）。

3. 最终聚合查询
   最后按name和group_id分组，计算每组的最小价格、最大价格和记录数量，就得到了你想要的结果。

运行结果 #

执行上面的代码后，会得到和你期望完全一致的输出：

注意事项 #

如果你的表中存在价格为0的记录，要注意避免除以0的错误，可以在判断条件里加个AND gi.price != 0，或者用NULLIF(gi.price, 0)来处理，比如：

WHEN ri.price / NULLIF(gi.price, 0) BETWEEN 0.9 AND 1.1 THEN gi.group_id

内容的提问来源于stack exchange，提问作者acidnik