oracle 按每次计数器递增时运行总计重置进行分组

yvgpqqbh  于 2023-08-03  发布在  Oracle
关注(0)|答案(3)|浏览(94)

我有下面的表(我的输入将只是列NIF,文件和FILESIZE)。
我想使用以下规则获取列PARTITION和SUBPARTITION:

  1. PARTITION应以1开始,每次调整SUBPARTITION达到8时递增1。
    1.单个NIF不能位于两个不同的PARTITION中。(这实际上是最严格的条件)。
    1.每次FILESIZE中的运行总数达到10时,SUBPARTITION必须递增1。当SUBPARTITION递增1时,必须重置该运行总计。
    数据来源:
NIF  FILE FILESIZE    PARTITION   SUBPARTITION
  ----------------------------------------------
   A     C1        1            1              1
   A     C2        1            1              1
   A     C3        2            1              1
   A     C4        1            1              1
   B     C5        5            1              2
   B     C6        1            1              2
   C     C7        2            1              2
   C     C8        1            1              2
   D     C9        4            1              3
   D    C10        5            1              3
   D    C11        1            1              3
   D    C12        2            1              4
   D    C13        3            1              4
   D    C14        4            1              4
   D    C15        5            1              5
   E    C16        3            1              6
   E    C17        2            1              6
   E    C18        3            1              6
   E    C19        4            1              7
   F    C20        6            2              1
   F    C20        2            2              1

字符串
它不需要是一个单一的查询,做一个PSLQL游标也可以。
注意:这是创建输入表的代码。

DROP TABLE my_table;
-- Create the table
CREATE TABLE my_table (
  NIF VARCHAR2(10),
  FILE_ VARCHAR2(10),
  FILESIZE NUMBER
);

-- Insert the input data
INSERT INTO my_table (NIF, FILE_, FILESIZE)
    SELECT 'A', 'C1', 1 FROM DUAL UNION ALL
    SELECT 'A', 'C2', 1 FROM DUAL UNION ALL
    SELECT 'A', 'C3', 2 FROM DUAL UNION ALL
    SELECT 'A', 'C4', 1 FROM DUAL UNION ALL
    SELECT 'B', 'C5', 5 FROM DUAL UNION ALL
    SELECT 'B', 'C6', 1 FROM DUAL UNION ALL
    SELECT 'C', 'C7', 2 FROM DUAL UNION ALL
    SELECT 'C', 'C8', 1 FROM DUAL UNION ALL
    SELECT 'D', 'C9', 4 FROM DUAL UNION ALL
    SELECT 'D', 'C10', 5 FROM DUAL UNION ALL
    SELECT 'D', 'C11', 1 FROM DUAL UNION ALL
    SELECT 'D', 'C12', 2 FROM DUAL UNION ALL
    SELECT 'D', 'C13', 3 FROM DUAL UNION ALL
    SELECT 'D', 'C14', 4 FROM DUAL UNION ALL
    SELECT 'D', 'C15', 5 FROM DUAL UNION ALL
    SELECT 'E', 'C16', 3 FROM DUAL UNION ALL
    SELECT 'E', 'C17', 2 FROM DUAL UNION ALL
    SELECT 'E', 'C18', 3 FROM DUAL UNION ALL
    SELECT 'E', 'C19', 4 FROM DUAL UNION ALL
    SELECT 'F', 'C20', 6 FROM DUAL UNION ALL
    SELECT 'F', 'C21', 2 FROM DUAL;
    
COMMIT;

vq8itlhq

vq8itlhq1#

在Oracle 12中,您可以使用分析函数和MATCH_RECOGNIZE的组合来执行逐行处理:

SELECT cto,
       nif,
       file_,
       partition,
       mn - first_mn + 1 AS subpartition,
       total_size
FROM   (
  SELECT t.*,
         ROW_NUMBER() OVER (
           PARTITION BY cto
           ORDER BY SUBSTR(file_, 1, 1), TO_NUMBER(SUBSTR(file_, 2))
         ) AS rn,
         SUM(filesize) OVER (PARTITION BY nif) AS nif_size
  FROM   my_table t
)
MATCH_RECOGNIZE(
  PARTITION BY cto
  ORDER BY rn
  MEASURES
    MATCH_NUMBER() AS mn,
    SUM(filesize) AS total_size
  ALL ROWS PER MATCH
  PATTERN ( (same_nif* nif)* same_nif* end_nif )
  DEFINE
    same_nif AS nif = NEXT(nif),
    nif      AS  (NEXT(nif) IS NULL OR nif != NEXT(nif))
             AND SUM(filesize) + NEXT(nif_size) <= 10,
    end_nif  AS  SUM(filesize) <= 10
)
MATCH_RECOGNIZE(
  PARTITION BY cto
  ORDER BY rn
  MEASURES
    MATCH_NUMBER() AS partition,
    FIRST(mn) AS first_mn
  ALL ROWS PER MATCH
  PATTERN ( (same_nif* nif)+ )
  DEFINE
    same_nif AS nif = NEXT(nif),
    nif      AS (NEXT(nif) IS NULL OR nif != NEXT(nif))
             AND mn - FIRST(mn) < 7
);

字符串
在第一匹配中,对于子分区,模式:

  • same_nif* nif将从当前行匹配到当前NIF分区的末尾,并通过检查当前大小加上下一个NIF分区的总大小是否在限制范围内来确保下一个NIF分区也将完全包含在匹配中;
  • 将其 Package 在()*中允许匹配零次或多次(尽可能多);
  • 最后的same_nif* end_nif允许匹配部分匹配具有相同nif值的一组行(例如文件C9、C10和C11,它们与C12、C13和C14具有相同的nif),但由于它们的文件大小总计超过限制,因此将处于不同的组中。如果没有不同的结束条件,您可能会将C1到C5分组,或者无法拆分Dnif值。

在第二匹配中,对于分区,nif值需要在同一匹配中保持在一起(而对于子分区,nif值可以跨越多个匹配),除此之外,唯一需要的检查是在一个组中存在不超过7个子分区。
对于样本数据(添加了一个CTO列和一个额外的nifG,该组太大,无法与前一个nifF合并在同一个分区中):

CREATE TABLE my_table (
  CTO NUMBER,
  NIF VARCHAR2(10),
  FILE_ VARCHAR2(10),
  FILESIZE NUMBER
);

INSERT INTO my_table (CTO, NIF, FILE_, FILESIZE)
    SELECT 1, 'A', 'C1', 1 FROM DUAL UNION ALL
    SELECT 1, 'A', 'C2', 1 FROM DUAL UNION ALL
    SELECT 1, 'A', 'C3', 2 FROM DUAL UNION ALL
    SELECT 1, 'A', 'C4', 1 FROM DUAL UNION ALL
    SELECT 1, 'B', 'C5', 5 FROM DUAL UNION ALL
    SELECT 1, 'B', 'C6', 1 FROM DUAL UNION ALL
    SELECT 1, 'C', 'C7', 2 FROM DUAL UNION ALL
    SELECT 1, 'C', 'C8', 1 FROM DUAL UNION ALL
    SELECT 1, 'D', 'C9', 4 FROM DUAL UNION ALL
    SELECT 1, 'D', 'C10', 5 FROM DUAL UNION ALL
    SELECT 1, 'D', 'C11', 1 FROM DUAL UNION ALL
    SELECT 1, 'D', 'C12', 2 FROM DUAL UNION ALL
    SELECT 1, 'D', 'C13', 3 FROM DUAL UNION ALL
    SELECT 1, 'D', 'C14', 4 FROM DUAL UNION ALL
    SELECT 1, 'D', 'C15', 5 FROM DUAL UNION ALL
    SELECT 1, 'E', 'C16', 3 FROM DUAL UNION ALL
    SELECT 1, 'E', 'C17', 2 FROM DUAL UNION ALL
    SELECT 1, 'E', 'C18', 3 FROM DUAL UNION ALL
    SELECT 1, 'E', 'C19', 4 FROM DUAL UNION ALL
    SELECT 1, 'F', 'C20', 6 FROM DUAL UNION ALL
    SELECT 1, 'F', 'C21', 2 FROM DUAL UNION ALL
    SELECT 1, 'G', 'C' || (LEVEL + 21), 10 FROM DUAL CONNECT BY LEVEL <= 7;


输出:
| NIF|文件_|分区|细分|总尺寸| TOTAL_SIZE |
| --|--|--|--|--| ------------ |
| 一个|C1|一个|一个|一个| 1 |
| 一个|C2|一个|一个|二个| 2 |
| 一个|C3|一个|一个|四个| 4 |
| 一个|C4|一个|一个|五个| 5 |
| B级|C5|一个|二个|五个| 5 |
| B级|C6|一个|二个|六个| 6 |
| C类|C7|一个|二个|八个| 8 |
| C类|C8|一个|二个|九个| 9 |
| D级|C9|一个|三个|四个| 4 |
| D级|C10|一个|三个|九个| 9 |
| D级|C11|一个|三个|十个| 10 |
| D级|C12|一个|四个|二个| 2 |
| D级|C13|一个|四个|五个| 5 |
| D级|C14|一个|四个|九个| 9 |
| D级|C15|一个|五个|五个| 5 |
| E级|C16|一个|六个|三个| 3 |
| E级|C17|一个|六个|五个| 5 |
| E级|C18|一个|六个|八个| 8 |
| E级|C19|一个|七个|四个| 4 |
| F级|C20|二个|一个|六个| 6 |
| F级|C21|二个|一个|八个| 8 |
| G级|C22|三个|一个|十个| 10 |
| G级|C23|三个|二个|十个| 10 |
| G级|C24|三个|三个|十个| 10 |
| G级|C25|三个|四个|十个| 10 |
| G级|C26|三个|五个|十个| 10 |
| G级|C27|三个|六个|十个| 10 |
| G级|C28|三个|七个|十个| 10 |
fiddle

eoigrqb6

eoigrqb62#

您可以创建一个temporary table或一个真实的的Yable,字段为(NIF,FILE,FILESIZE,PARTITION,SUBPARTITION)。你也可以给它添加一个id,但是为了这个答案的目的,我只描述绝对最小值。
您可以将PARTITION和SUBPARTITION设置为可空,并通过以下命令插入行

insert into yourtable(NIF, FILE, FILESIZE)
values('A', 'C1', 1);

字符串
并实现一个before insert trigger,它将找到max(PARTITION),以找出当前分区是什么,以及该分区的max(SUBPARTITION)沿着该分区中的sum(FILESIZE)。类似于(这是一个不可知查询,您需要保存字段into局部变量)

select PARTITION, SUBPARTITION, sum(FILESIZE) storage_use
from yourtable
group by PARTITION, SUBPARTITION
order by PARTITION desc, SUBPARTITION desc
limit 1


从这里开始,在触发器中,您可以看到要插入的记录的storage_use +实际FILESIZE是否大于10。如果没有,那么将新记录的PARTITIONSUBPARTITION设置为查询中找到的那些。如果不是,那么只要SUBPARTITION小于或等于8,就可以递增SUBPARTITION。如果SUBPARTITION是8,并且storage_use不允许您将新记录放入其中,则将PARTITION + 1SUBPARTITION + 1分别存储为PARTITIONSUBPARTITION

gg0vcinb

gg0vcinb3#

也可以使用MODEL子句:

select nif, file_, filesize, partition, subpartition 
from (
    select 
      row_number() over(order by nif, to_number(substr(file_,2))) as rn, 
      nif, file_, to_number(substr(file_,2)) as file_order,
      filesize 
    from my_table
)
model
    dimension by (rn)
    measures( nif as nif, file_ as file_, file_order as file_order, 
        filesize as filesize,
        0 as partition, 0 as incsubpartition, 1 as subpartition, 0 as running_total )
    rules (
        running_total[any] = 
           case when nvl(running_total[cv()-1],0) + filesize[cv()] >= 10 
           then filesize[cv()]
           else nvl(running_total[cv()-1],0) + filesize[cv()]
           end,
        incsubpartition[any] = 
           case when nvl(running_total[cv()-1],0) + filesize[cv()] >= 10 
           then 1
           else 0
           end,
       -- rule 3
       subpartition[any] = nvl(subpartition[cv()-1],1) + incsubpartition[cv()], 
       partition[any] = 
           case when subpartition[cv()] = 8 and subpartition[cv()-1] <> 8 
           then partition[cv()-1] + 1
           else nvl(partition[cv()-1],1)
           end,
       -- rule 1
       subpartition[any] = 
          case when subpartition[cv()] = 8 
          then 1 
          else subpartition[cv()]
          end,
       -- rule 2
       partition[any] =
          case when nif[cv()] = nif[cv()-1] 
          then partition[cv()-1]
          else partition[cv()]
          end
   )
order by nif, file_order
;

NIF FILE_   FILESIZE    PARTITION   SUBPARTITION
A   C1  1   1   1
A   C2  1   1   1
A   C3  2   1   1
A   C4  1   1   1
B   C5  5   1   2
B   C6  1   1   2
C   C7  2   1   2
C   C8  1   1   2
D   C9  4   1   3
D   C10 5   1   3
D   C11 1   1   4
D   C12 2   1   4
D   C13 3   1   4
D   C14 4   1   5
D   C15 5   1   5
E   C16 3   1   6
E   C17 2   1   6
E   C18 3   1   6
E   C19 4   1   7
F   C20 6   2   1
F   C21 2   2   1

字符串

相关问题