select year(date), week(date), sum(bottles_sold)
from LIQUORS_SALES
group by year(date), week(date)
order by year(date), week (date).
above one is the query i used to fetch weekly time series for sum of bottles_sold. But i am getting wrong number of bottles sold in every year first week.
what i observed is sum of bottles sold in additional days of every year are adding to first week of that year.
Ex:- take 2012 which is leap year (366 days) where my first week starts from jan 1st. i have a sales of bottles on 2012-01(1-7 days) week are 200, 2012 - 52 (358-364 days) are 500, now sales in last two days (365th and 366th) are 150 which should actually add to 2013-01 week, but it's adding to 2012-01 where my 2012-01 bottles sold become 350.
how can i fix it? is there anything miss in the sql query i used ? or is there anything i need to change the configurations in grid-gain or something wrong with the ignite database itself?
please help me to fix this, it cause a serious problem to do time series analysis. let me know if you need more information about the issue. Thanks in advance.
You need to stop grouping by year(date), week(date) and start instead grouping by iso_year(date), iso_week(date).
select iso_year(date), iso_week(date), sum(bottles_sold) from LIQUORS_SALES group by iso_year(date), iso_week(date)