sql - 有没有办法创建数量为零的营业月

Question

我有一张表格，上面有各种合同编号，包括营业月份，数量不为零。我想实现数量为零的一年中剩余的营业月。表中有多个合同。如您所见，示例 1

和期望的结果，包括我希望在示例 2 中获得的 16110 的记录。

标签： sqloracle

Answer 1

甲骨文

将分区外连接与生成的日历一起使用：

SELECT t.contractid,
       c.month AS bm,
       t.sales_product_number,
       t.currency_tc,
       t.end_customer_no,
       COALESCE( t.billings_quantity, 0 ) AS billings_quantity
FROM   (
         SELECT TO_NUMBER(
                  TO_CHAR(
                    ADD_MONTHS( DATE '2018-01-01', LEVEL-1 ),
                    'YYYYMM'
                  )
                ) AS month
         FROM   DUAL
         CONNECT BY LEVEL <= 12
       ) c
       LEFT OUTER JOIN test_data t
       PARTITION BY ( contractid, sales_product_number, currency_tc, end_customer_no )
       ON ( t.bm = c.month );

因此，对于您的测试数据：

CREATE TABLE test_data ( contractid, bm, sales_product_number, currency_tc, end_customer_no, billings_quantity ) AS
SELECT 15939, 201802, 'SP000095923', 'EUR', 400009, 60 FROM DUAL UNION ALL
SELECT 15939, 201804, 'SP000095923', 'EUR', 400009, 60 FROM DUAL UNION ALL
SELECT 15939, 201808, 'SP000095923', 'EUR', 400009, 40 FROM DUAL UNION ALL
SELECT 15939, 201811, 'SP000095923', 'EUR', 400009, 60 FROM DUAL UNION ALL
SELECT 15939, 201812, 'SP000095923', 'EUR', 400009, 60 FROM DUAL;

输出：

合同编号 | BM | SALES_PRODUCT_NUMBER | CURRENCY_TC | END_CUSTOMER_NO | BILLINGS_QUANTITY
---------: | -----: | :-------------------- | :------------ | --------------: | ----------------:
     15939 | 201801 | SP000095923 | 欧元 | 400009 | 0
     15939 | 201802 | SP000095923 | 欧元 | 400009 | 60
     15939 | 201803 | SP000095923 | 欧元 | 400009 | 0
     15939 | 201804 | SP000095923 | 欧元 | 400009 | 60
     15939 | 201805 | SP000095923 | 欧元 | 400009 | 0
     15939 | 201806 | SP000095923 | 欧元 | 400009 | 0
     15939 | 201807 | SP000095923 | 欧元 | 400009 | 0
     15939 | 201808 | SP000095923 | 欧元 | 400009 | 40
     15939 | 201809 | SP000095923 | 欧元 | 400009 | 0
     15939 | 201810 | SP000095923 | 欧元 | 400009 | 0
     15939 | 201811 | SP000095923 | 欧元 | 400009 | 60
     15939 | 201812 | SP000095923 | 欧元 | 400009 | 60

db<>在这里摆弄

---

更新

这将按年份以及其他列进行分区：

WITH data ( contractid, year, month, sales_product_number, currency_tc, end_customer_no, billings_quantity ) AS (
  SELECT contractid,
         SUBSTR( bm, 1, 4 ),
         TO_NUMBER( SUBSTR( bm, 5, 2 ) ),
         sales_product_number,
         currency_tc,
         end_customer_no,
         billings_quantity
  FROM   test_data
)
SELECT t.contractid,
       TO_NUMBER( t.year || TO_CHAR( c.month, 'FM00') ) AS bm,
       t.sales_product_number,
       t.currency_tc,
       t.end_customer_no,
       COALESCE( t.billings_quantity, 0 ) AS billings_quantity
FROM   (
         SELECT LEVEL AS month
         FROM   DUAL
         CONNECT BY LEVEL <= 12
       ) c
       LEFT OUTER JOIN data t
       PARTITION BY ( contractid, year, sales_product_number, currency_tc, end_customer_no )
       ON ( t.month = c.month );

db<>在这里摆弄