sql中每个用户生日当天展开后面N天写法分享

 INSERT OVERWRITE TABLE tableresult

 SELECT /* + mapjoin(v) */ u6.bid AS bid, u6.year AS year, u6.month AS month, u6.day AS day, u6.totaldayreal AS totaldayreal, u6.plantotalday AS plantotalday , v.groupId AS plangroupId

 FROM(

  SELECT u5.bid AS bid, u5.year AS year, u5.month AS month, u5.day AS day, u5.totaldayreal, u5.plantotalday) AS plantotalday

  FROM(

      SELECT u4.bid AS bid, u4.year AS year, u4.month AS month, u4.day AS day, u4.totaldayreal AS totaldayreal, CAST(u4.totaldayreal+add_day AS BIGINT) AS plantotalday

      FROM(

          SELECT u3.bid AS bid, u3.year AS year, u3.month AS month, u3.day AS day, u3.totalday AS totaldayreal, '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61' AS day_grp

         FROM(

              SELECT u2.bid AS bid, u2.year AS year, u2.month AS month, u2.day AS day, u2.totalday AS totalday

              FROM (

                  SELECT DISTINCT u.bid AS bid, u.year AS year, u.month AS month, u.day AS day, u.totaldayreal AS totalday

                  FROM table u

                  )u2

          ) u3

      )u4

      LATERAL VIEW EXPLODE(SPLIT(u4.day_grp, ',')) adtable AS add_day

  )u5

 )u6

 LEFT OUTER JOIN table v

 ON u6.plantotalday = v.totalday;