sql - 在 Oracle 中根据 RowCount 对结果进行分组

Question

我有一个需求，我需要根据rowCount输出分组结果。

这是我从 SQL 中得到的结果集:

ID      Date        Count
1     10/01/2013    50
1     10/02/2013    25
1     10/03/2013    100
1     10/04/2013    200 
1     10/05/2013    175
1     10/06/2013     45
2     10/01/2013     85
2     10/02/2013    100 

我可以把它们当作

    id        date    Count
    1     10/03/2013    175
    1     10/04/2013    200
    1     10/05/2013    175
    1     10/06/2013     45
    2     10/02/2013    185

我需要通过对每个 ID 的计数 <= 200 进行分组来减少结果集。例如，10/01、10/02 和 10/03 的总和为 175，因此我需要将它们组合成一行。如果将 10/05 和 10/06 的值相加会 >200，所以不要将它们分组。

是否可以在 Oracle 11g 中使用 PLSQL 或 SQL 分析函数解决此问题？

请求新的结果集有没有办法返回具有附加列的结果？ StartD 列对于每一行，它必须采用该行的上一个结束日期

ID      StartD      EndDate     Count
1       10/01/2013  10/03/2013   175
1       10/03/2013  10/04/2013   200
1       10/04/2013  10/05/2013   250
1       10/05/2013  10/06/2013   190
1       10/06/2013  10/08/2013    45
2       10/01/2013  10/01/2013   185

Answer 1

您可以在 Oracle 12c 中使用 MATCH_RECOGNIZE 执行此操作模式匹配技术。

设置(添加了几行，包括一些计数超过 200 的行，用于测试):

create table stuff (id int, stamp date, num int);
insert into stuff values (1, to_date('10/01/2013', 'MM/DD/RRRR'), 50);
insert into stuff values (1, to_date('10/02/2013', 'MM/DD/RRRR'), 25);
insert into stuff values (1, to_date('10/03/2013', 'MM/DD/RRRR'), 100);
insert into stuff values (1, to_date('10/04/2013', 'MM/DD/RRRR'), 200);
insert into stuff values (1, to_date('10/05/2013', 'MM/DD/RRRR'), 250);
insert into stuff values (1, to_date('10/06/2013', 'MM/DD/RRRR'), 175);
insert into stuff values (1, to_date('10/07/2013', 'MM/DD/RRRR'), 15);
insert into stuff values (1, to_date('10/08/2013', 'MM/DD/RRRR'), 45);
insert into stuff values (2, to_date('10/01/2013', 'MM/DD/RRRR'), 85);
insert into stuff values (2, to_date('10/02/2013', 'MM/DD/RRRR'), 100);
commit;

查询将是:

select id, first_stamp, last_stamp, partial_sum
from stuff
match_recognize (
    partition by id order by stamp
    measures
      first(a.stamp) as first_stamp
    , last(a.stamp)  as last_stamp
    , sum(a.num)     as partial_sum
    pattern (A+)
    define A as (sum(a.num) <= 200 or (count(*) = 1 and a.num > 200))
);

给出:

        ID FIRST_STAMP LAST_STAMP PARTIAL_SUM
---------- ----------- ---------- -----------
         1 01-OCT-13   03-OCT-13          175 
         1 04-OCT-13   04-OCT-13          200 
         1 05-OCT-13   05-OCT-13          250 
         1 06-OCT-13   07-OCT-13          190 
         1 08-OCT-13   08-OCT-13           45 
         2 01-OCT-13   02-OCT-13          185 

 6 rows selected 

这是如何工作的:

• 模式匹配在整个表上完成，按 id 分区并按时间戳排序。
• 模式 A+ 表示我们想要满足条件 A 的连续行组(根据分区和排序子句)。
• 条件A是集合满足:
  • 集合中num的总和小于等于200
  • 或者该集合有单行且 num 大于 200(否则这些行永远不会匹配，并且不会输出)。
• measures 子句指示匹配返回的内容(在分区键之上):
  • 每个组的第一个和最后一个时间戳
  • 每组num的总和

---

这是一种具有表值函数的方法，应该适用于 11g(我认为是 10g)。相当不优雅，但可以完成工作。按顺序遍历表，当它们“满”时输出组。

您也可以为组大小添加一个参数。

create or replace 
type my_row is object (id int, stamp date, num int);

create or replace 
type my_tab as table of my_row;

create or replace
  function custom_stuff_groups
    return my_tab pipelined
  as
    cur_sum number;
    cur_id  number;
    cur_dt  date;
  begin
    cur_sum := null;
    cur_id  := null;
    cur_dt  := null;
    for x in (select id, stamp, num from stuff order by id, stamp)
    loop
      if (cur_sum is null) then
        -- very first row
        cur_id      := x.id;
        cur_sum     := x.num;
      elsif (cur_id != x.id) then
        -- changed ID, so output last line for previous id and reset
        pipe row(my_row(cur_id, cur_dt, cur_sum));
        cur_id              := x.id;
        cur_sum             := x.num;
      elsif (cur_sum + x.num > 200) then
        -- same id, sum overflows.
        pipe row(my_row(cur_id, cur_dt, cur_sum));
        cur_sum := x.num;
      else
        -- same id, sum still below 200
        cur_sum := cur_sum + x.num;
      end if;
      cur_dt := x.stamp;
    end loop;
    if (cur_sum is not null) then
      -- output the last line, if any
      pipe row(my_row(cur_id, cur_dt, cur_sum));
    end if;
  end;

用作:

select * from table(custom_stuff_groups());