SQL计数和不同和分区

I am trying out some examples with analytical functions and i have created a sql fiddle to understand a count distinct over partition by clause.This is my sqlfiddle.

我正在尝试一些分析函数的例子,我已经创建了一个sql小提琴来理解一个明显超过partition by子句的计数。这是我的sqlfiddle。

create table dummy (value1 varchar2(10),value2 varchar2(10));

insert into dummy values ('abc','abc1');
insert into dummy values ('abc','abc1');
insert into dummy values ('abc','abc2');
insert into dummy values ('def','abc1');
insert into dummy values ('ghi','abc2');
insert into dummy values ('xyz','abc3');
insert into dummy values ('xyz','abc3');

select value1,
       value2,
       count(distinct value2) over (partition by value1) as ValCount
from dummy

If you look at the result set, i would expect valcount as 1 for the third row but instead its 2 and am not sure why that's the case.

如果你看结果集,我会期望第三行的valcount为1,而不是它的2,我不确定为什么会这样。

2 个解决方案

#1

abc (value 1) has only 2 distinct values on the second column (abc1 and abc2), and since you count the distinct values on column2 partitioned over column 1 you should indeed get 2

abc(值1)在第二列(abc1和abc2)上只有2个不同的值,并且由于你计算了在第1列上分区的column2上的不同值,你应该得到2

#2

The valcount should be 2.

valcount应为2。

You've partitioned by value1, so the count executes within that context. That is to say, in the group of results where value1 = "abc", there are 2 distinct values of value2 ("abc1", "abc2").

您已经按value1进行了分区,因此计数在该上下文中执行。也就是说,在value1 =“abc”的结果组中,有2个不同的value2值(“abc1”,“abc2”)。

#1