I was thinking of saving only 6 records in kylin instead of splitting them outside in daily avg and adding 365 records for each item. So is there anyway I can achieve using sql level in kylin or have changes to model to accomodate above change? Please advice. Thanks
On Wed, Feb 28, 2018 at 5:51 PM, Alberto Ramón <[email protected]> wrote: > Sounds like: > - your minimum granularity for queries are on Weeks, your fact table need > be on weeks (or less, like days) > - you will need expand you actual fact table to weeks (or more, days) > Example use a hive view > - as extra: Kylin can't use partition format columns on weeks, the > minimum es days > > Alb > > On 28 February 2018 at 21:51, deva namaste <[email protected]> wrote: > >> Hello, >> >> How would I calculate value for a week while I have bi-monthly values. >> >> e.g. Here is my data looks like - >> >> Date - Value >> 01/18/2017 - 100 >> 03/27/2017 - 130 (68 Days) >> 05/17/2017 - 102 (51 Days) >> >> I need average value per week, as below. Lets consider between 03/27 and >> 05/17. So total days between period are 51. so Daily average would be >> 102/51= 2.04 >> >> Week4 (Starting March 26, #days = 4) = (4 x 2.04) = 8.16 >> Week1 (Starting Apr 2, #days = 7) = 14.28 >> Week2 (starting Apr 9, #days = 7)= 14.28 >> Week3 (starting Apr 16, #days = 7)= 14.28 >> Week4 (starting Apr 23, #days = 7)= 14.28 >> week5 (Starting Apr 30, #days =7)= 14.28 >> week1 (starting May 7, #days = 7)= 14.28 >> Week2 (starting May 14, #days = 4)= 8.16 >> >> But as you see that period from 01/18 to 03/27, have 68 days and daily >> average would be 130/68=1.91 >> >> So really to get complete week I need 3 days from 130 value and 4 days >> from 102 value. >> >> So real total for that first week would be - >> Week4 (Starting March 26, #days = 4) = (4x2.04=8.16) + (3x1.91=5.73) = >> 13.89 >> >> How would I achieve this in Kylin? Any function? or other method I can >> use? >> Just for 6 records for year, I dont want to populate daily records. >> Thanks >> Deva >> >> >> >
