sql - 递归 ORDER BY

Question

我有一个USERS表格，它是一个成员矩阵，如下所示。表在 上是唯一的ID，每个都ID属于至少一个组，但可以属于所有 3 个。

SELECT  1 AS ID, 0 AS IS_A, 0 AS IS_B, 1 AS IS_C FROM DUAL UNION ALL
SELECT  2,0,1,0 FROM DUAL UNION ALL
SELECT  3,0,1,1 FROM DUAL UNION ALL
SELECT  4,1,1,0 FROM DUAL UNION ALL
SELECT  5,1,1,0 FROM DUAL UNION ALL
SELECT  6,1,1,1 FROM DUAL UNION ALL
SELECT  7,0,1,1 FROM DUAL UNION ALL
SELECT  8,0,0,1 FROM DUAL UNION ALL
SELECT  9,1,0,0 FROM DUAL UNION ALL
SELECT 10,1,0,1 FROM DUAL UNION ALL
SELECT 11,0,0,1 FROM DUAL UNION ALL
SELECT 12,0,1,1 FROM DUAL

最终目标是SELECT随机抽取来自 A 的至少 4 个用户、来自 B 的 3 个用户和来自 C 的 5 个用户的样本（只是一个例子），但恰好有 10 个不同ID的 s（否则解决方案是微不足道的；只是SELECT *）。

重点不是确定是否有可能，而是更多地尝试尽最大努力最大化会员资格。

输出预计在 上是唯一的ID。

我只能想到一种程序方法来实现这一点：

1. 拿第ID一个MAX(IS_A+IS_B+IS_C)
2. 检查是否达到配额
3. 例如，如果我们已经有来自 A 的 4 个用户，那么我们将继续使用下一个 ID ，完全忽略列中的MAX(IS_B+IS_C)任何进一步贡献IS_A
4. 如果我们已经完成了所有配额，则恢复MAX(IS_A+IS_B+IS_C)为获取“奖励”积分
5. 达到总最大值 10 时停止

本质上，我们优先考虑并逐步采用ID未达到配额的组中成员最多的组

但是，我无法弄清楚如何在 Oracle SQL 中执行此操作，因为这ORDER BY不仅取决于当前行的值，而且还递归地取决于先前的行是否已填满相应的配额。

我试过ROWNUM, ROW_NUMBER(), SUM(IS_A) OVER (ORDER BY ...),RECURSIVE CTE但无济于事。我最好的是

WITH CTE AS (
  SELECT ID, IS_A, IS_B, IS_C
  , ROW_NUMBER() OVER (ORDER BY IS_A+IS_B+IS_C DESC) AS RN
  FROM USERS
)
, CTE2 AS (
  SELECT CTE.*
  , GREATEST(4 - SUM(IS_A) OVER (ORDER BY RN), 0.001) AS QUOTA_A --clip negatives to 0.001
  , GREATEST(3 - SUM(IS_B) OVER (ORDER BY RN), 0.001) AS QUOTA_B --so that when all quotas are exhausted,
  , GREATEST(5 - SUM(IS_C) OVER (ORDER BY RN), 0.001) AS QUOTA_C --we still prioritize those that contribute most number of concurrent memberships
  FROM CTE
)
SELECT ID FROM CTE2
ORDER BY QUOTA_A*IS_A + QUOTA_B*IS_B + QUOTA_C*IS_C DESC
FETCH NEXT 10 ROWS ONLY

但它不起作用，因为它QUOTA_A是基于ORDER BY RN而不是递归计算的。

提前致谢！

标签： sqloracleoracle12c