如何改写SQL实现按GP、DATEDIFF组重复显示ACTIVE_MOS最大值？
2026-6-30
需求说明
需要改写SQL语句，实现按GP和DATEDIFF分组后，组内每行重复显示ACTIVE_MOS的最大值，效果对应示例中的DESIRED RESULT列。
原SQL语句
LEAST(DENSE_RANK() OVER (PARTITION BY GP, DATEDIFF ORDER BY YRMO ASC), 12) AS ACTIVE_MOS
示例数据
| GP | YRMO | DATEDIFF | ACTIVE_MOS | DESIRED RESULT |
|---|---|---|---|---|
| 54 | 202012 | 0 | 1 | 12 |
| 54 | 202101 | 0 | 2 | 12 |
| 54 | 202102 | 0 | 3 | 12 |
| 54 | 202103 | 0 | 4 | 12 |
| 54 | 202104 | 0 | 5 | 12 |
| 54 | 202105 | 0 | 6 | 12 |
| 54 | 202106 | 0 | 7 | 12 |
| 54 | 202107 | 0 | 8 | 12 |
| 54 | 202108 | 0 | 9 | 12 |
| 54 | 202109 | 0 | 10 | 12 |
| 54 | 202110 | 0 | 11 | 12 |
| 54 | 202111 | 0 | 12 | 12 |
| 54 | 202112 | 0 | 12 | 12 |
| 54 | 202201 | 0 | 12 | 12 |
| 54 | 202202 | 0 | 12 | 12 |
| 54 | 202203 | 0 | 12 | 12 |
| 54 | 202204 | 0 | 12 | 12 |
| 54 | 202205 | 0 | 12 | 12 |
| 54 | 202206 | 0 | 12 | 12 |
| 54 | 202207 | 0 | 12 | 12 |
| 54 | 202208 | 0 | 12 | 12 |
| 54 | 202209 | 0 | 12 | 12 |
| 54 | 202210 | 0 | 12 | 12 |
| 54 | 202211 | 0 | 12 | 12 |
| 54 | 202310 | 1 | 1 | 4 |
| 54 | 202311 | 1 | 2 | 4 |
| 54 | 202312 | 1 | 3 | 4 |
| 54 | 202401 | 1 | 4 | 4 |
解决方案
改写后的SQL语句
MAX(LEAST(DENSE_RANK() OVER (PARTITION BY GP, DATEDIFF ORDER BY YRMO ASC), 12)) OVER (PARTITION BY GP, DATEDIFF) AS DESIRED_RESULT
逻辑解释
- 保留原逻辑计算每行的
ACTIVE_MOS值：通过DENSE_RANK()按GP、DATEDIFF分组排序，再用LEAST()限制最大值为12； - 外层嵌套
MAX()窗口函数，同样以GP和DATEDIFF为分组范围，直接取组内ACTIVE_MOS的最大值； - 窗口函数会将这个最大值填充到组内的每一行，完全匹配需求中的结果列。
内容的提问来源于stack exchange，提问作者SamR
相关产品推荐
相关产品推荐
