如何按每4个连续区域分组求和qty？避免重复使用IIF

给定数据表如下：

需要按每4个连续的substring(area,6,3)值分组求和，得到如下结果，且要求避免重复使用IIF函数：

求创建new_area列并实现分组求和的方案，附带执行逻辑说明。

SQL 代码实现 #

WITH numbered_data AS (
    SELECT 
        CAST(substring(area,6,3) AS INT) AS area_num,
        qty,
        ROW_NUMBER() OVER (ORDER BY CAST(substring(area,6,3) AS INT)) AS rn
    FROM your_table_name
),
grouped_data AS (
    SELECT 
        area_num,
        qty,
        FLOOR((rn - 1) / 4) AS group_id
    FROM numbered_data
)
SELECT 
    CONCAT(MIN(area_num), '-', MAX(area_num)) AS new_area,
    SUM(qty) AS sum_qty
FROM grouped_data
GROUP BY group_id
ORDER BY group_id;

执行逻辑说明 #

1. 数据预处理与编号：
   • 首先将字符串类型的substring(area,6,3)转换为整数area_num，确保能正确排序和计算。
   • 使用ROW_NUMBER()窗口函数，按area_num从小到大排序，为每条记录生成连续的行号rn。
2. 生成分组标识：
   • 通过FLOOR((rn - 1) / 4)计算分组ID：行号减1后除以4取整，这样前4条数据（rn=1_{4）会被分到group_id=0，接下来4条（rn=5}8）分到group_id=1，以此实现每4个连续值为一组的分组规则。
3. 分组聚合计算：
   • 按group_id分组，用MIN(area_num)和MAX(area_num)拼接成范围格式的new_area，同时对每组的qty求和得到sum_qty，最后按分组ID排序输出结果。

注：不同数据库的整数除法语法略有差异，比如MySQL可使用(rn - 1) DIV 4，PostgreSQL可使用(rn - 1) // 4，替代FLOOR((rn - 1)/4)，效果完全一致。

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