sql - 基于同一表中存在的列的 Oracle 序列生成

Question

我正在尝试使用 oracle 中的分析函数生成一个序列。但是，我的顺序取决于表 PROD_INFO 中的两列。列名是 PROD_ID 和 BILLING_NO。同一个PROD_ID，可以有多个BILLING_NO。 BILLING_NO 也可以有 NULL 值。我需要根据以下逻辑生成 SEQUENCE。

1. 对于第一组 PROD_ID、BILLING_NO 组合，我需要递增 SEQUENCE，例如 2000、2010、2020 等，直到找到具有相同 PROD_ID 的不同 BILLING_NO。
2. 如果是不同的BILLING_NO，我需要输入3000、3010、3020等序列
3. 当 PROD_ID 发生变化时，我需要将该 BILLING_NO 的顺序更改为 2000、2010、2020 等。输出显示在下表中。

PROD_ID BILLING_NO  SEQUENCE    QUANTITY

1-7OR   AB1     2000        80

1-7OR   AB1     2010        2

1-7OR   AB1     2020        30

1-7OR   NULL    2030        10

1-7OR   AB2     3000        15

1-7OR   AB2     3010        15

1-7OR   AB2     3020        15

1-7OR   AB2     3030        15

1-7OR   NULL    3040        15

1-7OR   NULL    3050        15

1-7OR   AB3     4000        15

1-7OR   AB3     4010        15

1-7OR   AB3     4020        15

1-9ER   UC1     2000        50

1-9ER   UC1     2010        90

1-9ER   UC1     2020        35

1-9ER   UC1     2030        63

1-9ER   NULL    2040        41

1-9ER   UC2     3000        75

1-9ER   UC2     3010        75

1-9ER   UC2     3020        90

1-9ER   UC2     3030        90

P.S:我在上面的输出中用“NULL”描述了 NULL 值。

我目前正在使用以下分析函数。但是，它不会更改为 3000、3010 等，4000、4010 等，而是重复 2000、2010、2020 等。

SELECT   PROD_ID,
    BILLING_NO,
    2000 + ROW_NUMBER() OVER (PARTITION BY PROD_ID, BILLING_NO ORDER BY BILLING_NO) * 10 AS SEQUENCE,
    QUANTITY
FROM    PROD_INFO;

那么，你能帮我实现上面的结果吗。

谢谢!

Answer 1

--The query you need
SELECT PROD_ID,
  BILLING_NO,
  (DENSE_RANK() OVER (PARTITION BY prod_id ORDER BY PROD_ID, BILLING_NO) + 1) * 1000 
  + 
  ROW_NUMBER() OVER (PARTITION BY PROD_ID, BILLING_NO ORDER BY BILLING_NO) * 10 AS sequence,
  QUANTITY
FROM PROD_INFO
/

您需要使用 ANALYTIC 函数 DENSE_RANK 和 ROW_NUMBER。

让我们用一个测试用例来验证一下，

SQL> WITH DATA AS(
  2  SELECT '1-7OR'  PROD_ID,   'AB1'    BILLING_NO FROM DUAL UNION ALL
  3  SELECT '1-7OR'  PROD_ID,   'AB1'        BILLING_NO FROM DUAL UNION ALL
  4  SELECT '1-7OR'  PROD_ID,   'AB1'      BILLING_NO FROM DUAL UNION ALL
  5  SELECT '1-7OR'  PROD_ID,   NULL       BILLING_NO FROM DUAL UNION ALL
  6  SELECT '1-7OR'  PROD_ID,   'AB2'       BILLING_NO FROM DUAL UNION ALL
  7  SELECT '1-7OR'  PROD_ID,   'AB2'        BILLING_NO FROM DUAL UNION ALL
  8  SELECT '1-7OR'  PROD_ID,   'AB2'         BILLING_NO FROM DUAL UNION ALL
  9  SELECT '1-7OR'  PROD_ID,   'AB2'         BILLING_NO FROM DUAL UNION ALL
 10  SELECT '1-7OR'  PROD_ID,   NULL        BILLING_NO FROM DUAL UNION ALL
 11  SELECT '1-7OR'  PROD_ID,   NULL       BILLING_NO FROM DUAL UNION ALL
 12  SELECT '1-7OR'  PROD_ID,   'AB3'         BILLING_NO FROM DUAL UNION ALL
 13  SELECT '1-7OR'  PROD_ID,   'AB3'        BILLING_NO FROM DUAL UNION ALL
 14  SELECT '1-7OR'  PROD_ID,   'AB3'         BILLING_NO FROM DUAL UNION ALL
 15  SELECT '1-9ER'  PROD_ID,   'UC1'         BILLING_NO FROM DUAL UNION ALL
 16  SELECT '1-9ER'  PROD_ID,   'UC1'        BILLING_NO FROM DUAL UNION ALL
 17  SELECT '1-9ER'  PROD_ID,   'UC1'        BILLING_NO FROM DUAL UNION ALL
 18  SELECT '1-9ER'  PROD_ID,   'UC1'        BILLING_NO FROM DUAL UNION ALL
 19  SELECT '1-9ER'  PROD_ID,   NULL       BILLING_NO FROM DUAL UNION ALL
 20  SELECT '1-9ER'  PROD_ID,   'UC2'      BILLING_NO FROM DUAL UNION ALL
 21  SELECT '1-9ER'  PROD_ID,   'UC2'      BILLING_NO FROM DUAL UNION ALL
 22  SELECT '1-9ER'  PROD_ID,   'UC2'        BILLING_NO FROM DUAL UNION ALL
 23  SELECT '1-9ER'  PROD_ID,   'UC2'         BILLING_NO FROM DUAL
 24  )
 25  --The query you need
 26  SELECT PROD_ID,
 27    BILLING_NO,
 28    (DENSE_RANK() OVER (PARTITION BY prod_id ORDER BY PROD_ID, BILLING_NO) + 1) * 1000
 29    +
 30    ROW_NUMBER() OVER (PARTITION BY PROD_ID, BILLING_NO ORDER BY BILLING_NO) * 10 AS sequence
 31  FROM data
 32  /

PROD_ BIL             SEQUENCE
----- --- --------------------
1-7OR AB1                 2010
1-7OR AB1                 2020
1-7OR AB1                 2030
1-7OR AB2                 3010
1-7OR AB2                 3020
1-7OR AB2                 3030
1-7OR AB2                 3040
1-7OR AB3                 4010
1-7OR AB3                 4020
1-7OR AB3                 4030
1-7OR                     5010
1-7OR                     5020
1-7OR                     5030
1-9ER UC1                 2010
1-9ER UC1                 2020
1-9ER UC1                 2030
1-9ER UC1                 2040
1-9ER UC2                 3010
1-9ER UC2                 3020
1-9ER UC2                 3030
1-9ER UC2                 3040
1-9ER                     4010

22 rows selected.

SQL>

注意 窗口函数将所有 NULL 放在一起。