oracle - Oracle sql中的rownum与group by

Question

我需要构建一个查询来按成员和到期日期检索信息组，但我需要为每个成员提供一个序列号..

例如:

如果成员“A”有 3 条记录要过期，“B”只有 1 条，“C”有 2 条，我需要这样的结果:

Number    Member  ExpDate 1           A    01/01/2020 2           A    02/01/2020 3           A    03/01/2020 1           B    01/01/2020 1           C    01/01/2020 2           C    02/01/2020

My query now is:

SELECT ROW_NUMBER() OVER(ORDER BY TRUNC(EXPIRATION_DT) ASC) AS SEQUENCE, MEMBER_ID AS MEMBER, SUM(ACCRUALED_VALUE) - SUM(USED_VALUE) AS POINTS, trunc(EXPIRATION_DT) AS EXPDATE
FROM TABLE1
WHERE EXPIRATION_DT > SYSDATE AND  EXPIRATION_DT < SYSDATE + 90
GROUP BY MEMBER_ID, TRUNC(EXPIRATION_DT)
HAVING SUM(ACCRUALED_VALUE) - SUM(USED_VALUE) > 0
ORDER BY 4 ASC;

但我不能“分组”序列号......现在的结果是:

Seq Mem Points Date1   1-O  188   2018-03-01 00:00:002   1-C  472   2018-03-01 00:00:003   1-A  485   2018-03-01 00:00:004   1-1  267   2018-03-01 00:00:005   1-E  500   2018-03-01 00:00:006   1-P  55    2018-03-01 00:00:007   1-E  14    2018-03-01 00:00:00

Answer 1

我认为您需要一个 DENSE_RANK 窗口函数。试试这个 -

 SELECT DENSE_RANK() OVER (PARTITION BY MEMBER ORDER BY TRUNC(EXPIRATION_DT) ASC) AS SEQUENCE
       ,MEMBER_ID AS MEMBER
       ,SUM(ACCRUALED_VALUE) - SUM(USED_VALUE) AS POINTS
       ,trunc(EXPIRATION_DT) AS EXPDATE
FROM TABLE1
WHERE EXPIRATION_DT > SYSDATE AND  EXPIRATION_DT < SYSDATE + 90
GROUP BY MEMBER_ID
        ,TRUNC(EXPIRATION_DT)
HAVING SUM(ACCRUALED_VALUE) - SUM(USED_VALUE) > 0
ORDER BY 4 ASC;