计算分组后组内最大值

【问题】

sql maximum record per group question

CREATE
TABLEDBO.TEST
(
IDINT,RECTYPEINT,SEQINT,MAX0INT,MAX1INT,MAX2INT)
INSERT
INTOdbo.TEST
SELECT
1,1,1,3,2,3
UNION
ALL
SELECT
1,2,2,3,2,3
UNION
ALL
SELECT
1,2,3,3,2,3
UNION
ALL
SELECT
1,1,2,3,2,3
--SELECT * FROM dbo.TEST
how
tofindMAXseqforeach IDandRectype
My result should be
MAX0
ismaximumofseqgroupbyID
MAX1
ismaximumofseqgroupbyIDwhererectype=1
MAX2 id maximum
ofseqgroupbyIDwhererectype=2

ID Rectype SEQ MAX0 MAX1 MAX2
1 1  1  3  2  3
1 2  2  3  2  3
1 2  3  3  2  3
1 1  2  3  2  3

别人的回答：

CREATETABLEDBO.TEST
(
ID INT,
RECTYPE INT,
SEQ INT,
MAX0 INT,
MAX1 INT,
MAX2 INT
)
INSERTINTOdbo.TEST
SELECT1,1,1,NULL,NULL,NULL
UNIONALL
SELECT1,2,2,NULL,NULL,NULL
UNIONALL
SELECT1,2,3,NULL,NULL,NULL
UNIONALL
SELECT1,1,2,NULL,NULL,NULL
--select * from test
;WITHmycte
AS(SELECTID,
RECTYPE,
Max(seq)
OVER(partition BYID)m0,
CASE
WHENrectype =1THENMax(SEQ)OVER(PARTITION BYid,rectype)
ELSENULL
ENDm1,
CASE
WHENrectype =2THENMax(SEQ)OVER(PARTITION BYid,rectype)
ELSENULL
ENDm2
FROMdbo.TEST)
SELECTID,
RECTYPE,
M0,
Max(m1)OVER(partition BYID)m1,
Max(m2)
OVER(partition BYID)m2
FROMmycte
droptabletest

【回答】

这是个比较典型的组内计算，解决思路很清晰：

1. 将数据按 ID 分成多个组，每个组是一个 ID 的全部数据。

2. 进行组内运算，求得本组内 SEQ 的最大值，赋给 MAX0。

3. 组内运算，过滤出本组内 Rectype=1 的记录，再求 SEQ 的最大值，赋给 MAX1。

4. 组内运算，过滤出本组内 Rectype=2 的记录，再求 SEQ 的最大值，赋给 MAX2。

上述思路虽然清晰，但用 SQL 却很难表达组内运算，只能转化成 N 个窗口函数嵌套多级关联。这样的代码复杂难懂，下次遇到类似的问题恐怕还是不会写。如果数据量不是非常大时，建议采用 SPL 来辅助。SPL 可以方便地表达组内运算，可以很容易解决你的问题，代码如下：

代码中的“~”就表示每个分组，类似于循环变量。另外，步骤 2,3,4 可以合为一步：

=A1.run(~.run(MAX0=A1.~.max(SEQ)), 
 ~.run(MAX1=A1.~.select(Rectype==1).max(SEQ)), 
 ~.run(MAX2=A1.~.select(Rectype==2).max(SEQ)) ) 

上述计算结果是 ResultSet 类型，和 JAVA 或报表都很容易集成，可以参看：

【集算器简化 SQL 式计算之组内运算】