乾学院 1750 号会员
2 回帖  •  774 浏览  • 
     ...

SQL 如何将同一组的日期合并成区间 *

 
有序计算(43)

有数据库表TBLTEST，数据如下所示：

CODE1

CODE2

CODE3

RATE

VALUE

MONTH

A

B

C

1

1

202001

A

B

C

1

1

202002

A

B

C

1

1

202003

A

B

C

2

1

202004

A

B

C

2

1

202005

A

B

C

1

1

202006

A

B

C

1

1

202007

A

B

C

1

1

202008

A

B

C

1

1

202009

数据按日期有序，要按前5列分组，将组内的日期拼成一个区间，最后一条记录的终止日期写成无限日期99991230，结果如下：

CODE1

CODE2

CODE3

RATE

VALUE

STARTDT

ENDDT

A

B

C

1

1

20200101

20200331

A

B

C

2

1

20200401

20200531

A

B

C

1

1

20200601

99991230

Oracle的SQL：

SELECT

     code1,

     code2,

     code3,

     rate,

     value,

     min(MONTH) start_dt,

     CASE

          WHEN ROW_NUMBER() OVER(PARTITION BY code1, code2, code3 ORDER BYmax(MONTH) DESC) = 1 THEN 99991230

          ELSE max(MONTH)

     END end_dt

FROM

     (

     SELECT

          t.*,

          ROW_NUMBER() OVER(PARTITION BY code1, code2, code3 ORDER BY MONTH) rn1,

          ROW_NUMBER() OVER(PARTITION BY code1, code2, code3, rate, value ORDERBY MONTH) rn2

     FROM

          TBLTEST t

) t

GROUP BY

     code1,

     code2,

     code3,

     rate,

     value,

     rn1 - rn2

ORDER BY

     start_dt

用自然思维去解这道题非常简单，只要对前5列的相邻数据比较，与上一条相同则分到一组，不同时产生新的分组，一直比到最后一条记录即可。但是SQL中的集合是无序的，需要繁琐的办法人为造出两列序号，再用两列序号之间的关系进行分组，光是想到这个办法就不容易。

用开源集算器的SPL就很容易写：

A

1

=connect("oracle")

2

=A1.query@x("SELECT * FROM TBLTEST ORDER BY MONTH")

3

=A2.groups@o(CODE1,CODE2,CODE3,RATE,VALUE;min(MONTH)/"01":STARTDT,string(date((max(MONTH)+1)/"01","yyyyMMdd")-1,"yyyyMMdd"):ENDDT)

4

>A3.m(-1).modify("99991230":ENDDT)

SPL直接支持有序集合，很容易实现相邻数据发生变化时的分组计算。

 

问答搜集

https://stackoverflow.com/questions/64099063/oracle-sql-data-migration-row-to-column-based-in-month


有序计算(43)