如何基于建筑列生成用户计数器列？ROW_NUMBER()实现遇阻

我明白你遇到的问题了——想用计数器给同一建筑里的不同用户编号，但用ROW_NUMBER()没得到想要的效果对吧？这大概率是因为ROW_NUMBER()会给每一行都分配唯一序号，哪怕是同一个用户的多条账单，而你需要的是同一建筑下每个不同用户对应唯一的计数器值。下面给你两种靠谱的解决方案：

方案一：用DENSE_RANK()窗口函数（最简洁直接） #

DENSE_RANK()正好匹配你的需求：它会为同一分组（这里是单栋建筑）内的相同用户分配相同的排名，而且排名是连续递增的——每出现一个新用户，计数器就自动+1。

假设你的账单表包含building_id（建筑ID）和user_id（用户ID）字段，直接运行以下SQL即可：

SELECT 
    *,
    DENSE_RANK() OVER (PARTITION BY building_id ORDER BY user_id) AS user_counter
FROM 
    bill_table;

• PARTITION BY building_id：确保我们只在当前建筑的范围内计数，不会跨建筑混排
• ORDER BY user_id：用来区分不同用户，保证同一个用户的所有账单都拿到同一个计数器值

方案二：先去重再关联（适合复杂场景） #

如果你的账单表需要关联其他表（比如从用户表获取建筑信息），或者希望计数器按用户首次在该建筑产生账单的时间排序，可以先提取每个建筑下的唯一用户列表并编号，再关联回原账单表：

-- 第一步：生成每个建筑下的唯一用户计数器
WITH unique_building_users AS (
    SELECT 
        bd.building_id,
        u.user_id,
        ROW_NUMBER() OVER (PARTITION BY bd.building_id ORDER BY MIN(b.bill_date)) AS user_counter
    FROM 
        bill_table b
    JOIN 
        user_table u ON b.user_id = u.user_id
    JOIN 
        building_table bd ON u.building_id = bd.building_id
    GROUP BY 
        bd.building_id, u.user_id
)
-- 第二步：把计数器关联回所有账单
SELECT 
    b.*,
    ubu.user_counter
FROM 
    bill_table b
JOIN 
    unique_building_users ubu ON b.user_id = ubu.user_id 
    AND b.building_id = ubu.building_id

这里用ROW_NUMBER()给去重后的用户编号，ORDER BY MIN(b.bill_date)确保计数器按用户首次产生账单的时间递增，更贴合“新增一位不同用户”的实际场景。

为什么之前的ROW_NUMBER()没生效？ #

如果你直接用ROW_NUMBER() OVER (PARTITION BY building_id ORDER BY user_id)，它会给同一用户的每一条账单都分配不同的序号（比如用户A的两条账单会得到1和2），而你需要的是同一用户对应同一个序号——这就是你没得到预期结果的核心原因。

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