SELECT c, i, y, m, r, nr
FROM book_sales s
     PARTITION BY (s.country, s.isbn)
     RIGHT OUTER JOIN month mo ON (s.month = mo.m AND s.year = mo.y)

WHERE mo.y IN (2002, 2003, 2004)
MODEL
   PARTITION BY (s.country c)
   DIMENSION BY (s.isbn i, mo.y y, mo.m m)
   MEASURES (s.net_revenue r, CAST(NULL AS NUMBER) nr)
   RULES (     
      --生成缺失的月的销售数据（致密化）

      nr[ANY, ANY, ANY]
         = CASE WHEN r[CV(), CV(), CV()] IS NOT NULL
              THEN r[CV(), CV(), CV()]
              ELSE ROUND(AVG(r)[CV(), CV(), m BETWEEN 1 AND 12],2)
           END,
      --Project 2003's Q2 to Q3 trend into Q4
      nr[ANY, 2003, m BETWEEN 10 AND 12]
         = ROUND((((SUM(nr)[CV(), 2003, m BETWEEN 7 and 9]

         - SUM(nr)[CV(), 2003, m BETWEEN 4 and 6])
         / SUM(nr)[CV(), 2003, m BETWEEN 4 and 6])
         * SUM(nr)[CV(), 2003, m BETWEEN 7 and 9]
         + SUM(nr)[CV(), 2003, m BETWEEN 7 and 9])/3,2),
      --Project 2004 sales
      nr[ANY, 2004, ANY] = ROUND(
         ((nr[CV(),2003,CV()] - nr[CV(),2002, CV()])
          / nr[CV(),2002, CV()]) * nr[CV(),2003, CV()]
         + nr[CV(),2003, CV()],2)

)
ORDER BY c, i, y, m;