连续两次都有三个月以上（不包含三个月）都有购买的险种以及险种总金额

(问题说明): T05_FACT_TRADE 购买记录表 T02_DIM_PRODUCT 产品信息表 关联条件为；PRODUCT_ID统计出连续三个月以上的月份都有的险种，并且需要出现次数大于两次； 如果出现连续9个月，则记录一次

 

SELECT 
      NAME PRODUCT_NAME
      ,SUM(DD) AMOUNT
  FROM (SELECT F1.PRODUCT_ID ID
              ,F2.PRODUCT_NAME NAME
              ,COUNT(JI) A
              ,SUM(DF) DD
          FROM (SELECT DISTINCT PRODUCT_ID
                               ,TO_NUMBER(TO_CHAR(TRADE_DATE, 'yyyymm')) FF
                               ,SUM(AMOUNT)  DF
                               ,DENSE_RANK() OVER(PARTITION BY PRODUCT_ID ORDER BY TO_NUMBER(TO_CHAR(TRADE_DATE, 'yyyymm'))) PAI
                               ,TO_NUMBER(TO_CHAR(TRADE_DATE, 'yyyymm')) - DENSE_RANK() OVER(PARTITION BY PRODUCT_ID ORDER BY TO_NUMBER(TO_CHAR(TRADE_DATE, 'yyyymm'))) JI
                  FROM T05_FACT_TRADE
                  GROUP BY TO_NUMBER(TO_CHAR(TRADE_DATE, 'yyyymm')),PRODUCT_ID
                  ) F1
          JOIN T02_DIM_PRODUCT F2
            ON F1.PRODUCT_ID = F2.PRODUCT_ID
         GROUP BY F1.PRODUCT_ID
                 ,JI
                 ,F2.PRODUCT_NAME
        HAVING COUNT(JI) > 3) SD
 GROUP BY ID
         ,NAME
HAVING COUNT(A) >= 2