Beispiel #1
0
    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
Beispiel #3
0
    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
Beispiel #8
0
    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)