请看下，这个SQL能写得出来就厉害啦

Jokeranswer

0019最早的不是最后一条 2016/5/26 ？

huangzhiyun123

bell6248 发表于 2017-5-25 16:46
是不是如下的意思

SQL> with t as

得到的结果是对的，但SQL好复杂哈。

huangzhiyun123

chengccy2010 发表于 2017-5-25 16:58
AGENT_CODE ，RANK 组内的START_DATE为最小的记录？
SELECT *
FROM   (SELECT T.*,

不是，是agent_code分组,取最小的start_date但rank一样的，取较小的start_date

chengccy2010

huangzhiyun123 发表于 2017-5-25 17:15
不是，是agent_code分组,取最小的start_date但rank一样的，取较小的start_date

看懂了，类似连续的起点嘛。
model子句写法：

1. SELECT * FROM
   
2. (SELECT AGENT_CODE, RANK, START_DATE, END_DATE, S_START_DATE, RN1
   
3. FROM   T
   
4. MODEL
   
5. PARTITION BY(AGENT_CODE)
   
6. DIMENSION BY(ROW_NUMBER() OVER(PARTITION BY AGENT_CODE ORDER BY START_DATE) RN)
   
7. MEASURES(RANK, START_DATE, END_DATE, START_DATE AS S_START_DATE, ROW_NUMBER() OVER(PARTITION BY AGENT_CODE ORDER BY START_DATE DESC) RN1)
   
8. RULES(S_START_DATE [ RN ] =CASE WHEN RANK [CV()-1] IS NULL OR RANK [CV() - 1] <> RANK [ CV() ] THEN
   
9.                               START_DATE [CV()]
   
10.                            ELSE
    
11.                               S_START_DATE [CV()-1]
    
12.                            END))
    
13.                            WHERE RN1=1

复制代码

newkid

如果按照置顶帖提问的智慧来发帖，问题早解决了。

with t as
(
   select '142000394' AGENT_CODE, '0119' RANK,to_date('2017/3/1', 'yyyy/mm/dd') START_DATE, null END_DATE from dual
   union all
   select '142000394' AGENT_CODE, '0119' RANK,to_date('2017/1/1', 'yyyy/mm/dd') START_DATE, to_date('2017/2/28 23:59:59', 'yyyy/mm/dd hh24:mi:ss') END_DATE from dual
   union all
   select '142000394' AGENT_CODE, '0122' RANK,to_date('2016/10/1', 'yyyy/mm/dd') START_DATE, to_date('2016/12/31 23:59:59', 'yyyy/mm/dd hh24:mi:ss') END_DATE from dual
   union all
   select '142000394' AGENT_CODE, '0122' RANK,to_date('2016/5/31', 'yyyy/mm/dd') START_DATE, to_date('2016/9/30 23:59:59', 'yyyy/mm/dd hh24:mi:ss') END_DATE from dual
  union all
  select '142000394' AGENT_CODE, '0119' RANK,to_date('2016/5/26', 'yyyy/mm/dd') START_DATE, to_date('2016/5/30 23:59:59', 'yyyy/mm/dd hh24:mi:ss') END_DATE from dual
)
SELECT * FROM (
SELECT t2.*
      ,ROW_NUMBER() OVER(PARTITION BY AGENT_CODE ORDER BY START_DATE) rn
FROM   (SELECT T.*
               ,ROW_NUMBER() OVER(PARTITION BY AGENT_CODE, RANK ORDER BY START_DATE DESC) RN1
               ,ROW_NUMBER() OVER(PARTITION BY AGENT_CODE ORDER BY START_DATE DESC) RN2
         FROM   T
        ) t2
WHERE  RN1=RN2
)
WHERE rn=1

chengccy2010

newkid 发表于 2017-5-25 23:08
如果按照置顶帖提问的智慧来发帖，问题早解决了。

with t as

好思路,
由此想到了,用model的一次排序的写法,赶紧model还是蛮实用的

1. SELECT * FROM
   
2. (SELECT AGENT_CODE, RANK, START_DATE, END_DATE,RN1
   
3. FROM   T
   
4. MODEL
   
5. PARTITION BY(AGENT_CODE)
   
6. DIMENSION BY(ROW_NUMBER() OVER(PARTITION BY AGENT_CODE ORDER BY START_DATE DESC) RN)
   
7. MEASURES(RANK, START_DATE, END_DATE, 0 AS RN1)
   
8. RULES
   
9. (
   
10. RN1[ANY]=CASE WHEN RANK[CV()]=NVL(RANK[CV()-1],RANK[CV()]) AND RANK[CV()]=RANK[1] THEN 1 END,
    
11. RN1[ANY]=CASE WHEN RN1[CV()+1] IS NULL AND RN1[CV()] =1 THEN 1 END
    
12. ))
    
13. WHERE RN1=1

复制代码

chengccy2010

chengccy2010 发表于 2017-5-26 09:41
好思路,
由此想到了,用model的一次排序的写法,赶紧model还是蛮实用的

测了下MODEL效率还没分析函数高

〇〇

问题帖有个模木反就好了

newkid

chengccy2010 发表于 2017-5-26 10:10
测了下MODEL效率还没分析函数高

MODEL的效率比不上纯SQL和PL/SQL。而且ORACLE在10G之后就没有做过任何改进，看来是要让它自生自灭了。

fage090

with t as
(
   select '142000394' AGENT_CODE, '0119' RANK,to_date('2017/3/1', 'yyyy/mm/dd') START_DATE, null END_DATE from dual
   union all
   select '142000394' AGENT_CODE, '0119' RANK,to_date('2017/1/1', 'yyyy/mm/dd') START_DATE, to_date('2017/2/28 23:59:59', 'yyyy/mm/dd hh24:mi:ss') END_DATE from dual
   union all
   select '142000394' AGENT_CODE, '0122' RANK,to_date('2016/10/1', 'yyyy/mm/dd') START_DATE, to_date('2016/12/31 23:59:59', 'yyyy/mm/dd hh24:mi:ss') END_DATE from dual
   union all
   select '142000394' AGENT_CODE, '0122' RANK,to_date('2016/5/31', 'yyyy/mm/dd') START_DATE, to_date('2016/9/30 23:59:59', 'yyyy/mm/dd hh24:mi:ss') END_DATE from dual
  union all
  select '142000394' AGENT_CODE, '0119' RANK,to_date('2016/5/26', 'yyyy/mm/dd') START_DATE, to_date('2016/5/30 23:59:59', 'yyyy/mm/dd hh24:mi:ss') END_DATE from dual
)
SELECT *
  FROM (SELECT t2.*,
               row_number() OVER(PARTITION BY AGENT_CODE ORDER BY START_DATE DESC) rn
          FROM (SELECT t.*,
                       lead(t.RANK) over(PARTITION BY AGENT_CODE ORDER BY START_DATE DESC) lead_rank
                  FROM t) t2
         WHERE t2.rank <> nvl(t2.lead_rank, t2.rank || '*'))
WHERE rn = 1