在Postgres做了15年的繁重工作之后，现在我第一次发现了一个意想不到的结果，处理一个窗口函数。
下面是一个例子，如果添加order by ...，则count(*) over (partition by ...会中断：

create temp view cc as
select * from
(   values
    (1,1),
    (1,2),
    (2,1),
    (2,2),
    (2,3)
) as t(key1, key2) ;

select *,                       -- correct result
    count(*) over (             -- count(*) works, order unspecified
        partition by key1
    )
from cc order by key1,key2;

select *,                       -- incorrect result
    count(*) over (             -- count(*) fails, order is specified
        partition by key1 order by key2
    )
from cc order by key1,key2;

字符串
上面的例子产生：

key1 | key2 | count      -- correct result
------+------+-------
    1 |    1 |     2      
    1 |    2 |     2
    2 |    1 |     3
    2 |    2 |     3
    2 |    3 |     3
(6 rows)

 key1 | key2 | count      -- incorrect result
------+------+-------
    1 |    1 |     1      -- should be 2
    1 |    2 |     2      
    2 |    1 |     1      -- should be 3
    2 |    2 |     2      -- should be 3
    2 |    3 |     3
(6 rows)

型
我需要额外的“命令”来确定谁是组长。
提前感谢Ariel Tejera