如何为数据集添加列统计Block下SubBlock的数量？已尝试ROW_NUMBER()函数未成功

我明白你想要的是给每行新增一列，展示当前BlockRef分组下不同SubBlockRef的总数。你之前尝试的ROW_NUMBER()函数并不适用于这个场景——它的作用是在指定分区内给每行分配一个递增的序号，而不是统计分组内的唯一值数量。

正确的实现方式 #

你可以使用窗口函数COUNT(DISTINCT ...) OVER (...)来实现需求，具体SQL语句如下：

SELECT 
    BlockRef,
    Blocks,
    SubBlockRef,
    SubBlocks,
    -- 统计当前BlockRef下的唯一SubBlockRef数量
    COUNT(DISTINCT SubBlockRef) OVER (PARTITION BY BlockRef) AS SubBlockCount
FROM YourTable;

示例结果 #

针对你提供的数据集，执行上述语句后会得到如下结果：

为什么之前的方法不对？ #

你之前的语句ROW_NUMBER() over (PARTITION BY BlockRef,SubBlockRef ORDER BY BlockRef, SubBlockRef DESC)是按BlockRef+SubBlockRef的组合分区，然后给每个组合内的行排序编号。所以对于49391这个SubBlock，每行的编号会是1到6，49392也是1到6——这显然不是你需要的“每个Block下的SubBlock总数”。

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