Oracle SQL选择月份-明智的产品统计过去6个月

Question

需求

如何为每个产品选择/获得月年(Month)明智的结果？我试图将一个查询写成：

SELECT * FROM
(
    SELECT  PRODUCT_CODE AS PRODUCT
    ,       EXTRACT (MONTH FROM (UPDATED_DATE))  AS month_num
    ,       PRODUCT_CODE
    FROM    Test_Pivot
)
PIVOT     (    COUNT (PRODUCT_CODE)
          FOR  month_num  IN (  1 AS jan,  2 AS feb,  3 AS mar,  4 As apr,  5 as may,  6 AS jun,
                                7 AS jul,  8 AS aug,  9 AS sep,  10 As oct,  11 as nov,  12 AS dec
                             )                             
          );

我正在取得成绩，但不管年份如何，它都会给出完整的计数。我不知道如何修改SQL以限制从本月开始的最后6个月的计数。我希望将月份列动态显示为'MON-YY‘，而不是像下面示例格式所描述的那样只显示'MON’。

   PRODUCT  JAN-16  FEB-16  MAR-16  APR-16  MAY-16  JUN-16
-------------------------------------------------------------
      D        1       0       1       0       0       2
      A        1       0       2       0       4       1
      B        1       0       2       1       1       0
      C        0       0       1       0       4       1

• 以上样本中的数据仅供描述(6月16日为当前月份)。

样本表

CREATE TABLE Test_Pivot (
  id            NUMBER,
  customer_id   NUMBER,
  product_code  VARCHAR2(5),
  quantity      NUMBER,
  Updated_date          date
);

样本数据

INSERT INTO Test_Pivot VALUES (1, 1, 'A', 10, sysdate);
INSERT INTO Test_Pivot VALUES (2, 1, 'B', 20, sysdate-50);
INSERT INTO Test_Pivot VALUES (3, 1, 'C', 30, sysdate-90);
INSERT INTO Test_Pivot VALUES (4, 2, 'A', 40, sysdate-75);
INSERT INTO Test_Pivot VALUES (5, 2, 'C', 50, sysdate-25);
INSERT INTO Test_Pivot VALUES (6, 3, 'A', 60, sysdate-20);
INSERT INTO Test_Pivot VALUES (7, 3, 'B', 70, sysdate-80);
INSERT INTO Test_Pivot VALUES (8, 3, 'C', 80, sysdate-40);
INSERT INTO Test_Pivot VALUES (9, 3, 'D', 90, sysdate-5);
INSERT INTO Test_Pivot VALUES (10, 4, 'A', 100, sysdate-35);
INSERT INTO Test_Pivot VALUES (11, 2, 'A', 40, sysdate-75);
INSERT INTO Test_Pivot VALUES (12, 2, 'C', 50, sysdate-25);
INSERT INTO Test_Pivot VALUES (13, 3, 'A', 60, sysdate-20);
INSERT INTO Test_Pivot VALUES (14, 3, 'B', 70, sysdate-80);
INSERT INTO Test_Pivot VALUES (15, 3, 'C', 80, sysdate-40);
INSERT INTO Test_Pivot VALUES (16, 3, 'D', 90, sysdate-5);
INSERT INTO Test_Pivot VALUES (17, 4, 'A', 100, sysdate-35);
INSERT INTO Test_Pivot VALUES (18, 3, 'B', 60, sysdate-400);
INSERT INTO Test_Pivot VALUES (19, 3, 'C', 70, sysdate-365);
INSERT INTO Test_Pivot VALUES (20, 3, 'D', 80, sysdate-450);
INSERT INTO Test_Pivot VALUES (21, 3, 'A', 90, sysdate-500);
INSERT INTO Test_Pivot VALUES (22, 4, 'A', 100, sysdate-555);
INSERT INTO Test_Pivot VALUES (23, 2, 'B', 40, sysdate-543);
INSERT INTO Test_Pivot VALUES (24, 2, 'B', 50, sysdate-150);
INSERT INTO Test_Pivot VALUES (25, 3, 'D', 60, sysdate-151);

Answer 1

尝试：

SELECT * FROM
(
    SELECT  PRODUCT_CODE AS PRODUCT
    ,       EXTRACT (YEAR FROM (UPDATED_DATE))  AS year_num
    ,       EXTRACT (MONTH FROM (UPDATED_DATE))  AS month_num
    ,       PRODUCT_CODE
    FROM    Test_Pivot
)
PIVOT     (    
          COUNT (PRODUCT_CODE)
          FOR  (year_num, month_num)  IN (  
               (2016,1) AS jan_16, (2016,2) AS feb_16, (2016,3) AS mar_16,
               (2016,4) AS apr_16, (2016,5) AS may_16, (2016,6) AS jun_16,
               (2016,7) AS jul_16, (2016,8) AS aug_16, (2016,9) AS sep_16,
               (2016,10) AS oct_16, (2016,11) AS nov_16, (2016,12) AS dev_16
          )
);

在静态SQL中，不能使若干年或几个月被“动态显示”和“自动调整为仅显示6个最后的月份”，PIVOT子句不是动态的，您必须列出所有的值：(2014,1) ...... (2027,12)。

然而，您可以进行动态查询(使用动态SQL)，但这是一个独立的主题，超出了这个答案。

======编辑=============

如果您只需要6个上个月的结果，并且不需要确切的月名作为列名(Feb，Apr等)，那么您可以使用如下查询：

SELECT * FROM
(
    SELECT  PRODUCT_CODE AS PRODUCT

    ,       EXTRACT (MONTH FROM (sysdate)) - EXTRACT (MONTH FROM (UPDATED_DATE))  AS month_num
    ,       PRODUCT_CODE
    FROM    Test_Pivot
    WHERE UPDATED_DATE >= trunc( sysdate, 'MM' )  - interval '6' month
)
PIVOT     (    
          COUNT (PRODUCT_CODE)
          FOR  (month_num)  IN (  
             0 as current_month, 
             1 as previous_month, 
             2 as two_months_bef_curr_month, 
             3  as three_months_bef_curr_month,
             4 as four_months_bef_curr_month, 
             5 as five_months_bef_curr_month
                             )                             
          );