sql - 在Oracle中基于7个数字范围的多个组计算计数

没有其他答案可以解决组之间存在间隙的问题（他们只是假设组是连续的）。这将跳过间隙并将其groupid放入7的范围内。

Oracle安装程序：

create table data(amount, customer_number, groupid, seqf) as (
    select '1000+', 5555, 51, 2 from dual union all
    select '1000+', 5555, 52, 3 from dual union all
    select '1000+', 5555, 55, 4 from dual union all
    select '1000+', 5555, 56, 4 from dual union all
    select '1000+', 5555, 57, 4 from dual union all
    select '1000+', 5555, 60, 2 from dual union all
    select '1000+', 5555, 61, 4 from dual union all
    select '1000+', 5555, 65, 2 from dual union all
    select '1000+', 5555, 69, 2 from dual union all
    select '2000+', 6666, 55, 2 from dual );

查询1：

找到组：

SELECT amount,
       customer_number,
       groupid,
       groupid + 6 AS max_groupid
FROM   (
  SELECT d.*,
         MIN( groupid )
           OVER ( PARTITION BY amount, customer_number )
           AS min_groupid,
         MIN( groupid )
           OVER (
             PARTITION BY amount, customer_number
             ORDER BY groupid
             RANGE BETWEEN 7 FOLLOWING AND UNBOUNDED FOLLOWING
           )
           AS next_groupid
  FROM   data d
)
START WITH groupid = min_groupid
CONNECT BY PRIOR amount          = amount
AND        PRIOR customer_number = customer_number
AND        PRIOR next_groupid    = groupid

输出：

金额| CUSTOMER_NUMBER | GROUPID | MAX_GROUPID
：----- | --------------：| ------：| ----------：
1000+ | 5555 | 51 | 57
1000+ | 5555 | 60 | 66
1000+ | 5555 | 69 | 75
2000年以上| 6666 | 55 | 61

查询2：

这计算了组数：

SELECT amount,
       customer_number,
       COUNT(*)
FROM   (
  SELECT d.*,
         MIN( groupid )
           OVER ( PARTITION BY amount, customer_number )
           AS min_groupid,
         MIN( groupid )
           OVER (
             PARTITION BY amount, customer_number
             ORDER BY groupid
             RANGE BETWEEN 7 FOLLOWING AND UNBOUNDED FOLLOWING
           )
           AS next_groupid
  FROM   data d
)
START WITH groupid = min_groupid
CONNECT BY PRIOR amount          = amount
AND        PRIOR customer_number = customer_number
AND        PRIOR next_groupid    = groupid
GROUP BY amount, customer_number

输出：

金额| CUSTOMER_NUMBER | 计数（*）
：----- | --------------：| -------：
1000+ | 5555 | 3
2000年以上| 6666 | 1个

db <> 在这里拨弄