这样的需求怎样实现，有点难度！

Question

需求是这样的：一个contract_number可能有多个period_id 
(1)如果一个contract_number值对应一个period_id，那么取row_number = 1的那一条
(1)如果一个contract_number值对应多个period_id，那么从dense_rank()=2中取一条(因为dense_rank()=2可能存在多条);以下是我写的代码，大家帮忙优化下！
当然大家若有其他方法实现，那就再好不过了！select contract_number, period_id
 from(select contract_number,
             period_id,  
             rn, 
             cnt,
             row_number() over(partition by dr order by dr) rnb
       from(select contract_number,
                   period_id,
                   dense_rank() over(partition by contract_number order by period_id desc) dr,
                   row_number() over(partition by contract_number order by period_id desc) rn,
                   count(*)     over(partition by contract_number order by period_id desc) cnt 
             from ods_asms_order_detail
            where period_id >= 20100101
              and period_id <  20110101
          )
       )
  where rn = (case when cnt = 1 then 1 else 0 end) or rnb = (case when cnt >=2 then 2 else 0 end);

Answer 1

--这样可以吗？没测试哈
select contract_number, period_id from (
       select contract_number,
              period_id,
              dense_rank() over(partition by contract_number order by period_id desc) dr,
              row_number() over(partition by contract_number order by period_id desc) rn,
              count(*)     over(partition by contract_number order by period_id desc) cnt 
        from ods_asms_order_detail
        where period_id >= 20100101 and period_id <  20110101
)
where (cnt=1 and rn=1) or (cnt!=1 and dr=2 and rn=2)

Answer 2

  不对，你可以考虑rn永远>=dr  所以说：当dr =2时， rn极有可能不等于2.
  故：(cnt!=1 and dr=2 and rn=2)是错误的！
 

Answer 3

没有怎么搞懂你说的，对于分组和排序都是一样的，dr和rn不管怎样都是从1开始的，比如dr=1,2,2,2,3,4,4 而rn肯定就是1，2，3，4，5，6，7
那么对应多个period_id时候，只取dr=2中的一个，那么rn就对应2，3，4啊
不知道我说的对不对，还请你多指示

Answer 4

where rn = (case when cnt = 1 then 1 else 0 end) or rnb = (case when cnt >=2 then 2 else 0 end);
--可以改为
---cnt=1 rn始终是 1  cnt>=2 则含有 rnb=1,2,3...
rn=1 or rnb=2

Answer 5

不知道理解的是否正确with t as (
select contract_number, period_id,
count(*) over (partition by contract_number) cnt,
dense_rank() over (partition by contract_number order by period_id) dr
from ods_asms_order_detail
)
select contract_number,max(period_id)
from t t1
where cnt=1 or dr=2
group by contract_number
/

Answer 6

  呵呵！假设dr=1有2个和更多时，rn就>2了！

Answer 7

我自己解决了，明天去公司验证大家写的：SQL如下：select contract_number, period_id from (
       select contract_number,
              period_id,
              dense_rank() over(partition by contract_number order by period_id desc) dr,
              row_number() over(partition by contract_number order by period_id desc) rn,
              count(*)     over(partition by contract_number order by 1) cnt 
        from ods_asms_order_detail
        where period_id >= 20100101 and period_id <  20110101
)
where (cnt=1 and rn=1) or (cnt>1 and dr=2 or cnr - rn + dr = 1);

Answer 8

--明白了，不过你7楼的好像也不行。但是看了xman_78tom的好像可以
--嗯这个不错
select contract_number, max(period_id) from (
       select contract_number,
              period_id,
              dense_rank() over(partition by contract_number order by period_id desc) dr,
              row_number() over(partition by contract_number order by period_id desc) rn,
              count(*)     over(partition by contract_number order by period_id desc) cnt 
        from ods_asms_order_detail
        where period_id >= 20100101 and period_id <  20110101
)
where cnt=1 or dr=2
group by contract_number

Answer 9

  这样数据少了些，没有考虑同一个contract_number有相同的
period_id的情况。我下面的SQL是正确的，其实和7楼完全一样！select distinct contract_number, period_id
  from (select contract_number,
               period_id,
               dense_rank() over(partition by contract_number order by period_id desc) dr,
               row_number() over(partition by contract_number order by period_id desc) rn,
               count(*) over(partition by contract_number order by 1) cnt
          from ods_asms_order_detail
         where period_id >= 20100101
           and period_id < 20110101
       )
 where cnt = 1
    or (cnt > 1 and (dr = 2 or cnt - rn + dr = 1));

Answer 10

5楼或8楼，稍微改下就对了。
with t as (
select contract_number,
       period_id,
       count(*) over(partition by contract_number order by 1) cnt,
       row_number() over(partition by contract_number order by period_id desc) rn,
       dense_rank() over(partition by contract_number order by period_id) dr
  from ods_asms_order_detail
 where period_id >= 20100101
   and period_id < 20110101
)
select contract_number,max(period_id)
from t t1
where cnt=1 or dr=2 or (cnt > 1 and rn = 1);   

Answer 11

数据太多啦！正确的SQL查询都要执行8-9分钟！