rownum in Oracle sql with group by

Question

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

所以：

如果成员" a"有3个记录，" B"只有1个和" C"有2个，我需要这样的结果：

数量成员漏斗 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

我现在的查询是：

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点日期1 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;

Answer 2

with g as (
select *
From TABLE1 g
group by MEMBER_ID
        ,TRUNC(EXPIRATION_DT)
HAVING SUM(ACCRUALED_VALUE) - SUM(USED_VALUE) > 0   ---- etc
) 
select rownum, g.* From g

此选择带有序列号码的返回第一列