sql - 按日期和按桶容量的序列号

问题描述

我无法给我的问题一个合适的标题。让我通过例子来解释它。

假设我有下表

输入

我想要的是

首先，我想按日期对交易进行分组（dd-MM-yyyy）
然后我想创建一个最多包含 2 个项目的块/桶。因此，我想为 2 个项目的每个块/桶分配一个 sub_batch_ref_id。在每个块/桶中，事务必须恰好属于一个日期。
SUB_BATCH_REF_ID 的约定是 BATCH_REF{块/桶的全局序列号}
一个块或桶最多可以包含 2 个相同日期的项目

我知道这可以很容易地通过任何高级编程语言（SQL 等面向数据的语言除外）来实现，但我没有这样的规定。该解决方案可以形成以下伪代码以便更好地理解：

伪代码

//I have the following map (assumed)
Map<Date, List<Transaction>> dateWiseTransactions;
BUCKET_CAPACITY = 2
GLOBAL_SERIAL = 0
for each entry in dateWiseTransactions
LOOP
    GLOBAL_SERIAL = GLOBAL_SERIAL + 1;
    for each transaction in entry.value i.e. List<Transaction>
    LOOP
        if loopIndex > BUCKET_CAPACITY //loopIndex starts from 1
            GLOBAL_SERIAL = GLOBAL_SERIAL + 1
        end if;
        transaction.SUB_BATCH_REF_ID = CONCAT(transaction.BATCH_REF, GLOBAL_SERIAL)
    END LOOP;

END LOOP;

预期输出

我试过的

我尝试先按日期对事务数据进行分区，然后分配一个行号，但我无法找到解决方案。

SELECT 
T.*,
ROW_NUMBER() over (PARTITION BY TRUNC(INSERT_DATE) ORDER BY TRANSACTION_ID) rn
FROM TRANSACTION T
WHERE BATCH_REF='XYZ'

任何帮助深表感谢。

SQL 小提琴

标签： sqldatabaseoraclepartition

这不是最佳解决方案。您的实际问题是一个相当复杂的图形问题——因为您希望事务只在所有日期中使用一次。

一种解决方案是为交易分配一个“工作”日期。以下随机执行此操作：

SELECT T.*
FROM (SELECT T.*,
             ROW_NUMBER() over (PARTITION BY TRUNC(INSERT_DATE) ORDER BY TRANSACTION_ID) AS rn
      FROM (SELECT T.*,
                   ROW_NUMBER() OVER (PARTITION BY BATCH_REF, TRANSACTION_ID ORDER BY DBMS_RANDOM.RANDOM) as seqnum
            FROM TRANSACTION T
            ) T
      WHERE BATCH_REF = 'XYZ' AND 
            SEQNUM = 1
     ) T
WHERE rn <= 2;

sql - 按日期和按桶容量的序列号

问题描述

解决方案

推荐阅读