基于剩余数量生成篮子ID的SQL实现需求咨询

原SQL的问题 #

你写的SQL完全不符合需求，核心问题如下：

• Partition by id会把每条单独的id划分为一个独立分区，每个分区单独计算排名，和同篮子共享ID的逻辑完全冲突
• Order by rem_material DESC仅按剩余数量降序排序，没有考虑数据的先后顺序以及剩余数量的递增/递减变化，无法生成符合预期的篮子ID
• WHERE条件里的rem_material > 0会过滤掉预期结果中的0值行，和你给出的输出要求矛盾

正确实现思路 #

要实现「剩余数量递减时ID不变，递增时生成新ID」的逻辑，核心是按数据的先后顺序（这里以给定ID排序），对比当前行与上一行的剩余数量，判断是否需要开启新篮子，具体步骤：

1. 用LAG()窗口函数获取上一行的剩余数量
2. 判断当前行剩余数量是否大于上一行：第一行（无前置行）或当前剩余数量大于上一行时，标记为1（代表新篮子），否则标记为0
3. 对标记值做累计求和，得到最终的ExpectedID

正确SQL示例 #

假设你的表名为your_table，给定ID对应列名为given_id，剩余数量对应rem_material，篮子编号对应basket_no，SQL如下：

SELECT
    given_id,
    basket_no,
    rem_material,
    SUM(new_basket_flag) OVER (ORDER BY given_id) AS ExpectedID
FROM (
    SELECT
        given_id,
        basket_no,
        rem_material,
        CASE
            WHEN LAG(rem_material) OVER (ORDER BY given_id) IS NULL THEN 1
            WHEN rem_material > LAG(rem_material) OVER (ORDER BY given_id) THEN 1
            ELSE 0
        END AS new_basket_flag
    FROM your_table
    WHERE source = 'area 5'
) AS sub_query
ORDER BY given_id;

结果验证 #

运行上述SQL后，会得到和你给出的预期结果完全一致的输出：

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