Sql 应用 GROUP BY 后判断相同的数量

问题:更新DBFiddle

select *
from (
    select 
        v.*
        ,min(cnt_org)over(partition by accountnumber) min_cnt_org
        ,max(cnt_org)over(partition by accountnumber) max_cnt_org
    from (
        select
          accountnumber
          ,org_id
          ,count(org_id)          over(partition by accountnumber) cnt
          ,count(distinct org_id) over(partition by accountnumber) cnt_distinct
          ,count(*)               over(partition by accountnumber,org_id) cnt_org
          ,listagg(org_id,',')within group(order by org_id)
             over(partition by accountnumber)
               as orgs
          ,listagg(distinct org_id,',')within group(order by org_id) 
             over(partition by accountnumber)
               as orgs_distinct
        from mytable
        ) v
    ) v2
where cnt_distinct<>3
or min_cnt_org!=max_cnt_org;

结果:

ACCOUNTNUMBER     ORG_ID        CNT CNT_DISTINCT    CNT_ORG ORGS          ORGS_DISTINCT  MIN_CNT_ORG MAX_CNT_ORG
------------- ---------- ---------- ------------ ---------- ------------- -------------- ----------- -----------
      3333333         81          2            2          1 81,281        81,281                   1           1
      3333333        281          2            2          1 81,281        81,281                   1           1
      4444444         81          4            3          2 81,81,281,404 81,281,404               1           2
      4444444         81          4            3          2 81,81,281,404 81,281,404               1           2
      4444444        281          4            3          1 81,81,281,404 81,281,404               1           2
      4444444        404          4            3          1 81,81,281,404 81,281,404               1           2