如何用oracle 实现统计连续数据的长度?

发布网友发布时间：2022-04-13 19:34

共4个回答

懂视网时间：2022-04-13 23:55

Oracle 按相同数据连续统计 CREATE TABLE TEST(ID NUMBER(20),val NUMBER(20))INSERT INTO TEST VALUES(1,5);INSERT INTO TEST V

Oracle 按相同数据连续统计

CREATE TABLE TEST(
ID NUMBER(20),
val NUMBER(20)
)
INSERT INTO TEST VALUES(1,5);
INSERT INTO TEST VALUES(2,10);
INSERT INTO TEST VALUES(3,10);
INSERT INTO TEST VALUES(4,10);
INSERT INTO TEST VALUES(5,6);
INSERT INTO TEST VALUES(6,6);
INSERT INTO TEST VALUES(7,10);
INSERT INTO TEST VALUES(8,10);

SELECT val,COUNT(*) FROM
(
SELECT ID,val,
row_number() OVER(ORDER BY ID)-row_number() OVER(PARTITION BY val ORDER BY ID) x
FROM TEST
)
GROUP BY val,x

ORDER BY MIN(ID);

可根据自己的情况自由发挥

，

热心网友时间：2022-04-13 21:03

WITH TMP_01 AS
  ( SELECT 101 nbr , 201301 d_month FROM al
  UNION ALL
  SELECT 101 , 201302 FROM al
  UNION ALL
  SELECT 101 , 201303 FROM al
  UNION ALL
  SELECT 101 , 201306 FROM al
  UNION ALL
  SELECT 102 , 201301 FROM al
  UNION ALL
  SELECT 102 , 201303 FROM al
  UNION ALL
  SELECT 102 , 201304 FROM al
  UNION ALL
  SELECT 103 , 201301 FROM al
  UNION ALL
  SELECT 103 , 201304 FROM al
  UNION ALL
  SELECT 103 , 201306 FROM al
  )
SELECT nbr,
  MAX(DECODE(dif,2,2,0)) ls_2 ,
  MAX(DECODE(dif,3,3,0)) ls_3 ,
  MAX(DECODE(dif,4,4,0)) ls_4 ,
  MAX(DECODE(dif,5,5,0)) ls_5 ,
  MAX(DECODE(dif,6,6,0)) ls_6
FROM
  (SELECT nbr,
    MAX(dif)+1 dif
  FROM
    (SELECT b.nbr nbr,
      MAX(b.d_month) - MIN(b.d_month) dif,
      b.cc cc
    FROM
      (SELECT a.*,
        to_number(a.d_month)-rownum cc
      FROM
        ( SELECT nbr,d_month FROM TMP_01 ORDER BY nbr,d_month
        )a
      ) b
    GROUP BY b.nbr,
      b.cc
    )
  GROUP BY nbr
  )
GROUP BY nbr
ORDER BY nbr;

热心网友时间：2022-04-13 22:21

slect nbr ,D_MONTH,count(*) from table6 group by nbr, D_MONTH

热心网友时间：2022-04-13 23:56

select
nbr,
max(case when tot=2 then 1 else 0 end) ls_2,
max(case when tot=3 then 1 else 0 end) ls_3,
max(case when tot=4 then 1 else 0 end) ls_4,
max(case when tot=5 then 1 else 0 end) ls_5,
max(case when tot=6 then 1 else 0 end) ls_6
from
(
select
nbr,
count(1) tot
from
(
select
nbr,
d_month
add_months(d_month,-1*rn+1) inter_val
from(
select
nbr,
d_month,
dense_rank()over(partition by nbr order by d_month) rn
from
tmp_01
) a
) b
group by nbr,inter_val
)c
group by nbr;