def _fast_time_series(self, start, end, interval='days', date_field=None, aggregate=None): ''' Aggregate over time intervals using just 1 sql query ''' date_field = date_field or self.date_field aggregate = aggregate or self.aggregate num, interval = _parse_interval(interval) interval_s = interval.rstrip('s') start, _ = get_bounds(start, interval_s) _, end = get_bounds(end, interval_s) kwargs = {'%s__range' % date_field: (start, end)} # TODO: maybe we could use the tzinfo for the user's location aggregate_data = self.qs.\ filter(**kwargs).\ annotate(d=Trunc(date_field, interval_s, tzinfo=start.tzinfo)).\ order_by().values('d').\ annotate(agg=aggregate) today = _remove_time(compat.now()) def to_dt(d): if isinstance(d, string_types): return parse(d, yearfirst=True, default=today) return d data = dict((to_dt(item['d']), item['agg']) for item in aggregate_data) stat_list = [] dt = start while dt < end: idx = 0 value = 0 for i in range(num): value = value + data.get(dt, 0) if i == 0: stat_list.append(( dt, value, )) idx = len(stat_list) - 1 elif i == num - 1: stat_list[idx] = ( dt, value, ) dt = dt + relativedelta(**{interval: 1}) return stat_list
def _fast_time_series(self, start, end, interval='days', date_field=None, aggregate=None, engine=None): ''' Aggregate over time intervals using just 1 sql query ''' date_field = date_field or self.date_field aggregate = aggregate or self.aggregate engine = engine or self._guess_engine() num, interval = _parse_interval(interval) start, _ = get_bounds(start, interval.rstrip('s')) _, end = get_bounds(end, interval.rstrip('s')) interval_sql = get_interval_sql(date_field, interval, engine) kwargs = {'%s__range' % date_field: (start, end)} aggregate_data = self.qs.extra(select = {'d': interval_sql}).\ filter(**kwargs).order_by().values('d').\ annotate(agg=aggregate) today = _remove_time(compat.now()) def to_dt(d): if isinstance(d, str): return parse(d, yearfirst=True, default=today) return d.replace(tzinfo=None) data = dict((to_dt(item['d']), item['agg']) for item in aggregate_data) stat_list = [] dt = to_dt(start) while dt < to_dt(end): idx = 0 value = 0 for i in range(num): value = value + data.get(dt, 0) if i == 0: stat_list.append(( dt, value, )) idx = len(stat_list) - 1 elif i == num - 1: stat_list[idx] = ( dt, value, ) dt = dt + relativedelta(**{interval: 1}) return stat_list
def _fast_time_series(self, start, end, interval='days', date_field=None, aggregates=None, engine=None): ''' Aggregate over time intervals using just 1 sql query ''' date_field = date_field or self.date_field aggregates = self._get_aggregates(aggregates) engine = engine or self._guess_engine() num, interval = _parse_interval(interval) start, _ = get_bounds(start, interval.rstrip('s')) _, end = get_bounds(end, interval.rstrip('s')) interval_sql = get_interval_sql(date_field, interval, engine) kwargs = {'%s__range' % date_field : (start, end)} aggregate_data = self.qs.extra(select = {'d': interval_sql}).\ filter(**kwargs).order_by().values('d') for i, aggregate in enumerate(aggregates): aggregate_data = aggregate_data.annotate(**{'agg_%d' % i: aggregate}) today = _remove_time(compat.now()) def to_dt(d): if isinstance(d, basestring): return parse(d, yearfirst=True, default=today) return d data = dict((to_dt(item['d']), [item['agg_%d' % i] for i in range(len(aggregates))]) for item in aggregate_data) stat_list = [] dt = start try: try: from django.utils.timezone import utc except ImportError: from django.utils.timezones import utc dt = dt.replace(tzinfo=utc) end = end.replace(tzinfo=utc) except ImportError: pass zeros = [0 for i in range(len(aggregates))] while dt < end: idx = 0 value = [] for i in range(num): value = map(lambda a, b: (a or 0) + (b or 0), value, data.get(dt, zeros[:])) if i == 0: stat_list.append(tuple([dt] + value)) idx = len(stat_list) - 1 elif i == num - 1: stat_list[idx] = tuple([dt] + value) dt = dt + relativedelta(**{interval : 1}) return stat_list
def _fast_time_series(self, start, end, interval='days', date_field=None, aggregate=None, engine=None): ''' Aggregate over time intervals using just 1 sql query ''' date_field = date_field or self.date_field aggregate = aggregate or self.aggregate engine = engine or self._guess_engine() num, interval = _parse_interval(interval) start, _ = get_bounds(start, interval.rstrip('s')) _, end = get_bounds(end, interval.rstrip('s')) interval_sql = get_interval_sql(date_field, interval, engine, timezone=start.tzinfo) kwargs = {'%s__range' % date_field : (start, end)} aggregate_data = self.qs.extra(select = {'d': interval_sql}).\ filter(**kwargs).order_by().values('d').\ annotate(agg=aggregate) today = _remove_time(compat.now()) def to_dt(d): if isinstance(d, str): return parse(d, yearfirst=True, default=today) return d.replace(tzinfo=start.tzinfo) data = dict((to_dt(item['d']), item['agg']) for item in aggregate_data) arithmetic_methods = {Min: min, Max: max, Count: operator.add} aggregate_method = arithmetic_methods[aggregate.__class__] if aggregate.__class__ in arithmetic_methods else operator.add stat_list = [] dt = start if data: default_value = list(data.values())[0].__class__() else: default_value = 0 while dt < end: idx = 0 value = default_value for i in range(num): value = aggregate_method(value, data.get(dt, default_value)) if i == 0: if aggregate_method == min: value = data.get(dt, 0) stat_list.append((dt, value,)) idx = len(stat_list) - 1 elif i == num - 1: stat_list[idx] = (dt, value,) dt = dt + relativedelta(**{interval : 1}) return stat_list
def _fast_time_series(self, start, end, interval='days', date_field=None, aggregate=None, engine=None): ''' Aggregate over time intervals using just 1 sql query ''' date_field = date_field or self.date_field aggregate = aggregate or self.aggregate engine = engine or self._guess_engine() num, interval = _parse_interval(interval) start, _ = get_bounds(start, interval.rstrip('s')) _, end = get_bounds(end, interval.rstrip('s')) interval_sql = get_interval_sql(date_field, interval, engine) kwargs = {'%s__range' % date_field : (start, end)} aggregate_data = self.qs.extra(select = {'d': interval_sql}).\ filter(**kwargs).order_by().values('d').\ annotate(agg=aggregate) today = _remove_time(compat.now()) def to_dt(d): # Fix from https://bitbucket.org/aztrock/django-qsstats-magic try: if isinstance(d, basestring): return parse(d, yearfirst=True, default=today) return d except: if isinstance(d, str): return parse(d, yearfirst=True, default=today) return d data = dict((to_dt(item['d']), item['agg']) for item in aggregate_data) stat_list = [] dt = start while dt < end: idx = 0 value = 0 for i in range(num): value = value + data.get(dt, 0) if i == 0: stat_list.append((dt, value,)) idx = len(stat_list) - 1 elif i == num - 1: stat_list[idx] = (dt, value,) dt = dt + relativedelta(**{interval : 1}) return stat_list
def _fast_time_series(self, start, end, interval="days", date_field=None, aggregate=None, engine=None): """ Aggregate over time intervals using just 1 sql query """ date_field = date_field or self.date_field aggregate = aggregate or self.aggregate engine = engine or self._guess_engine() num, interval = _parse_interval(interval) start, _ = get_bounds(start, interval.rstrip("s")) _, end = get_bounds(end, interval.rstrip("s")) interval_sql = get_interval_sql(date_field, interval, engine) kwargs = {"%s__range" % date_field: (start, end)} aggregate_data = ( self.qs.extra(select={"d": interval_sql}).filter(**kwargs).order_by().values("d").annotate(agg=aggregate) ) def to_dt(d): # leave dates as-is return parse(d, yearfirst=True) if isinstance(d, basestring) else d data = dict((to_dt(item["d"]), item["agg"]) for item in aggregate_data) stat_list = [] dt = start while dt < end: idx = 0 value = 0 for i in range(num): value = value + data.get(dt, 0) if i == 0: stat_list.append((dt, value)) idx = len(stat_list) - 1 elif i == num - 1: stat_list[idx] = (dt, value) dt = dt + relativedelta(**{interval: 1}) return stat_list
def _fast_time_series(self, start, end, interval='days', date_field=None, aggregate=None, engine='mysql'): ''' Aggregate over time intervals using just 1 sql query ''' date_field = date_field or self.date_field aggregate = aggregate or self.aggregate start, _ = get_bounds(start, interval.rstrip('s')) _, end = get_bounds(end, interval.rstrip('s')) interval_sql = get_interval_sql(date_field, interval, engine) kwargs = {'%s__range' % date_field : (start, end)} aggregate_data = self.qs.extra(select = {'d': interval_sql}).\ filter(**kwargs).order_by().values('d').\ annotate(agg=aggregate) data = dict((parse(item['d'], yearfirst=True), item['agg']) for item in aggregate_data) stat_list = [] dt = start while dt < end: value = data.get(dt, 0) stat_list.append([dt, value]) dt = dt + relativedelta(**{interval : 1}) return stat_list
def get_multi_time_series(self, configuration, time_since, time_until, interval, request=None): current_tz = timezone.get_current_timezone() if settings.USE_TZ: time_since = current_tz.localize(time_since) time_until = current_tz.localize(time_until) time_until = time_until.replace(hour=23, minute=59) configuration = configuration.copy() series = {} all_criteria = self.criteriatostatsm2m_set.all( ) # Outside of get_time_series just for performance reasons m2m = self.get_multi_series_criteria(configuration) if m2m and m2m.criteria.dynamic_criteria_field_name: choices = m2m.get_dynamic_choices() serie_map = {} names = [] values = [] for key, name in choices.items(): if key != '': if isinstance(name, (list, tuple)): name = name[1] names.append(name) values.append(key) configuration['select_box_dynamic_' + str(m2m.id)] = values serie_map = self.get_time_series(configuration, all_criteria, request, time_since, time_until, interval) for tv in serie_map: time = tv[0] if time not in series: series[time] = OrderedDict() i = 0 for name in names: i += 1 series[time][name] = tv[i] else: serie = self.get_time_series(configuration, all_criteria, request, time_since, time_until, interval) for time, value in serie: series[time] = {'': value} names = {'': ''} # fill with zeros where the records are missing interval_s = interval.rstrip('s') start, _ = get_bounds(time_since, interval_s) _, end = get_bounds(time_until, interval_s) if self.get_date_field().__class__ == DateField: start = start.date() end = end.date() time = start while time <= end: if time not in series: series[time] = OrderedDict() for key in names: if key not in series[time]: series[time][key] = 0 time = time + relativedelta(**{interval: 1}) return series
def for_interval(self, interval, dt, date_field=None, aggregate=None): start, end = get_bounds(dt, interval) date_field = date_field or self.date_field kwargs = {'%s__range' % date_field : (start, end)} return self._aggregate(date_field, aggregate, kwargs)
def for_interval(self, interval, dt, date_field=None, aggregate=None): start, end = get_bounds(dt, interval) date_field = date_field or self.date_field kwargs = {'%s__range' % date_field: (start, end)} return self._aggregate(date_field, aggregate, kwargs)