请教一个分组SQL

〇〇

newkid 发表于 2023-8-23 23:58
再套一层：select t2.*,dense_rank() over(order by min_date)  from (select t.*      ,min(t_date) over( ...

老辣啊

风铃中の鬼

newkid 发表于 2023-8-23 23:58
再套一层：select t2.*,dense_rank() over(order by min_date)  from (select t.*      ,min(t_date) over( ...

这个写法还有点问题。。下面的脚本，我把8月6号的数据改成了10，然后算出来分组的序号，后面就会跟前面重复了，所以如果不是从小到大分按顺序配序号的话，就会有可能出现序号重复的问题

insert into tmp values('2023-08-01',40);
insert into tmp values('2023-08-02',40);
insert into tmp values('2023-08-03',40);
insert into tmp values('2023-08-04',10);
insert into tmp values('2023-08-05',10);
insert into tmp values('2023-08-06',10);
insert into tmp values('2023-08-07',40);
insert into tmp values('2023-08-08',30);
insert into tmp values('2023-08-09',30);
insert into tmp values('2023-08-10',30);

dhhb

SQL> with t as(
  2  select t_date,data1,row_number()over(order by t_date) rn from tmp),
  3  t2 (t_date,data1,rn,grp)as (
  4  select t_date,data1,rn,1 grp from t where rn=1
  5  union all
  6  select t.t_date,t.data1,t.rn, case when  t.data1=t2.data1 then t2.grp else t2.grp+1 end grp from t, t2 where t.rn=t2.rn+1)
  7  select * from t2;

T_DATE                                                            DATA1         RN        GRP
------------------------------------------------------------ ---------- ---------- ----------
2023-08-01                                                           40          1          1
2023-08-02                                                           40          2          1
2023-08-03                                                           40          3          1
2023-08-04                                                           10          4          2
2023-08-05                                                           10          5          2
2023-08-06                                                           10          6          2
2023-08-07                                                           40          7          3
2023-08-08                                                           30          8          4
2023-08-09                                                           30          9          4
2023-08-10                                                           30         10          4

newkid

风铃中の鬼 发表于 2023-8-24 09:00
这个写法还有点问题。。下面的脚本，我把8月6号的数据改成了10，然后算出来分组的序号，后面就会跟前面重复 ...

partition by 写错了，再改一下：

select t2.*,dense_rank() over(order by min_date)
  from (
select t.*
      ,min(t_date) over(partition by data1,grp) min_date
  from (
        select tmp.*
              ,row_number()over(order by t_date) - row_number()over(partition by data1 order by t_date) grp
          from tmp
       ) t
) t2      

〇〇

用http://www.itpub.net/forum.php?m ... ;page=2#pid23733365 的思路

with t as(
select tmp.*,lag(data1)over(order by t_date)data2,row_number()over(order by t_date) a from tmp order by t_date
),
t2 as
(select t.*,case when data1=data2 then NULL else t_date end  x from t order by t_date)
select t2.*,last_value(x ignore nulls)over(order by t_date) from t2;

〇〇

dhhb 发表于 2023-8-24 09:47
SQL> with t as(  2  select t_date,data1,row_number()over(order by t_date) rn from tmp),  3  t2 (t_da ...

递归CTE很巧妙

〇〇

造了一些数据
create or replace table tmp2 as select date'2000-01-01'+ interval (i) day t_date, (random()*10)::int*10 data1 from range(10000)t(i);

--newkid
with ta as(
select t2.*,dense_rank() over(order by min_date) grp
  from (
select t.*
      ,min(t_date) over(partition by data1,grp) min_date
  from (
        select tmp2.*
              ,row_number()over(order by t_date) - row_number()over(partition by data1 order by t_date) grp
          from tmp2
       ) t
) t2
)
select  max(grp) from ta;
┌──────────┐
│ max(grp) │
│  int64   │
├──────────┤
│     9494 │
└──────────┘
Run Time (s): real 0.068 user 0.608404 sys 0.046800
--last_value
with t as(
select tmp2.*,lag(data1)over(order by t_date)data2,row_number()over(order by t_date) a from tmp2 order by t_date
),
t2 as
(select t.*,case when data1=data2 then NULL else t_date end  x from t order by t_date),
t3 as(select t2.*,last_value(x ignore nulls)over(order by t_date)min_date from t2),
tb as(select t3.*,dense_rank() over(order by min_date) grp from t3)
select  max(grp) from tb;
┌──────────┐
│ max(grp) │
│  int64   │
├──────────┤
│     9066 │
└──────────┘
Run Time (s): real 0.024 user 0.015600 sys 0.000000
--dhhb
with recursive t as(
select t_date,data1,row_number()over(order by t_date) rn from tmp2),
t2 (t_date,data1,rn,grp)as (
select t_date,data1,rn,1 grp from t where rn=1
union all
select t.t_date,t.data1,t.rn, case when  t.data1=t2.data1 then t2.grp else t2.grp+1 end grp from t, t2 where t.rn=t2.rn+1)
select max(grp) from t2;
┌──────────┐
│ max(grp) │
│  int32   │
├──────────┤
│     9066 │
└──────────┘
Run Time (s): real 25.711 user 138.528888 sys 1.872012

〇〇

newid的结果是对的，怎么套一个max就不对？
select t2.*,dense_rank() over(order by min_date) grp
   from (
select t.*
       ,min(t_date) over(partition by data1,grp) min_date
   from (
         select tmp2.*
               ,row_number()over(order by t_date) - row_number()over(partition by data1 order by t_date) grp
           from tmp2
        ) t
) t2
;
┌────────────┬───────┬───────┬────────────┬───────┐
│   t_date   │ data1 │  grp  │  min_date  │  grp  │
│    date    │ int32 │ int64 │    date    │ int64 │
├────────────┼───────┼───────┼────────────┼───────┤
│ 2000-01-01 │    30 │     0 │ 2000-01-01 │     1 │
│ 2000-01-02 │   100 │     1 │ 2000-01-02 │     2 │
│ 2000-01-03 │    70 │     2 │ 2000-01-03 │     3 │
│ 2000-01-04 │    50 │     3 │ 2000-01-04 │     4 │
│ 2000-01-05 │    60 │     4 │ 2000-01-05 │     5 │
│ 2000-01-06 │    70 │     4 │ 2000-01-06 │     6 │
│ 2000-01-07 │    30 │     5 │ 2000-01-07 │     7 │
│ 2000-01-08 │    70 │     5 │ 2000-01-08 │     8 │
│ 2000-01-09 │    10 │     8 │ 2000-01-09 │     9 │
│ 2000-01-10 │    30 │     7 │ 2000-01-10 │    10 │
│ 2000-01-11 │    10 │     9 │ 2000-01-11 │    11 │
│ 2000-01-12 │    20 │    11 │ 2000-01-12 │    12 │
│ 2000-01-13 │    30 │     9 │ 2000-01-13 │    13 │
│ 2000-01-14 │    40 │    13 │ 2000-01-14 │    14 │
│ 2000-01-15 │     0 │    14 │ 2000-01-15 │    15 │
│ 2000-01-16 │    80 │    15 │ 2000-01-16 │    16 │
│ 2000-01-17 │    40 │    15 │ 2000-01-17 │    17 │
│ 2000-01-18 │    80 │    16 │ 2000-01-18 │    18 │
│ 2000-01-19 │   100 │    17 │ 2000-01-19 │    19 │
│ 2000-01-20 │    70 │    16 │ 2000-01-20 │    20 │
│ 2027-04-29 │    10 │  8967 │ 2027-04-29 │  9047 │
│ 2027-04-30 │    20 │  9017 │ 2027-04-30 │  9048 │
│ 2027-05-01 │    80 │  8978 │ 2027-05-01 │  9049 │
│ 2027-05-02 │   100 │  9494 │ 2027-05-02 │  9050 │
│ 2027-05-03 │    50 │  8991 │ 2027-05-03 │  9051 │
│ 2027-05-04 │    20 │  9020 │ 2027-05-04 │  9052 │
│ 2027-05-05 │    50 │  8992 │ 2027-05-05 │  9053 │
│ 2027-05-06 │    40 │  8971 │ 2027-05-06 │  9054 │
│ 2027-05-07 │    50 │  8993 │ 2027-05-07 │  9055 │
│ 2027-05-08 │    20 │  9023 │ 2027-05-08 │  9056 │
│ 2027-05-09 │    80 │  8985 │ 2027-05-09 │  9057 │
│ 2027-05-10 │     0 │  9483 │ 2027-05-10 │  9058 │
│ 2027-05-11 │    80 │  8986 │ 2027-05-11 │  9059 │
│ 2027-05-12 │    10 │  8979 │ 2027-05-12 │  9060 │
│ 2027-05-13 │    90 │  9007 │ 2027-05-13 │  9061 │
│ 2027-05-14 │    60 │  8994 │ 2027-05-14 │  9062 │
│ 2027-05-15 │    10 │  8981 │ 2027-05-15 │  9063 │
│ 2027-05-16 │    90 │  9009 │ 2027-05-16 │  9064 │
│ 2027-05-17 │    50 │  9002 │ 2027-05-17 │  9065 │
│ 2027-05-18 │    40 │  8982 │ 2027-05-18 │ 9066 │
├────────────┴───────┴───────┴────────────┴───────┤

〇〇

〇〇 发表于 2023-8-24 13:34
newid的结果是对的，怎么套一个max就不对？ select t2.*,dense_rank() over(order by min_date) grp   from ...

知道了grp列重名，改成grp2就对了
with ta as(
select t2.*,dense_rank() over(order by min_date) grp2
  from (
select t.*
      ,min(t_date) over(partition by data1,grp) min_date
  from (
        select tmp2.*
              ,row_number()over(order by t_date) - row_number()over(partition by data1 order by t_date) grp
          from tmp2
       ) t
) t2
)
select  max(grp2) from ta;
┌───────────┐
│ max(grp2) │
│   int64   │
├───────────┤
│      9066 │
└───────────┘
Run Time (s): real 0.068 user 0.530403 sys 0.031200

chengccy2010

风铃中の鬼 发表于 2023-8-23 20:42
确实不对。。到是不顺序问题。。如果把2023-08-06那条的data1改成10，数字不对了，那还是没完全解决

找变动点，标记1，然后累计求和

1. SELECT A.*, SUM(N) OVER(ORDER BY T_DATE) AS RN
   
2. FROM   (SELECT TMP.*,
   
3.                 CASE
   
4.                   WHEN LAG(DATA1) OVER(ORDER BY T_DATE) = DATA1 THEN
   
5.                    0
   
6.                   ELSE
   
7.                    1
   
8.                 END N
   
9.          FROM   TMP) A
   

复制代码