def _make_query(self): filters = [self.direction.get_filter_clause("A")] if self.exclude_self_calls: filters.append("A.subscriber != A.msisdn_counterpart") on_filters = make_where(filters) sql = f""" SELECT U.subscriber AS subscriber, {self.statistic}(D.value) AS value FROM ( SELECT A.subscriber, A.location_id AS location_id_from, B.location_id AS location_id_to FROM ({self.unioned_from_query.get_query()}) AS A JOIN ({self.unioned_to_query.get_query()}) AS B ON A.id = B.id AND A.outgoing != B.outgoing {on_filters} ) U JOIN ({self.distance_matrix.get_query()}) D USING (location_id_from, location_id_to) GROUP BY U.subscriber """ return sql
def _make_query(self): filters = [self.direction.get_filter_clause()] if self.exclude_self_calls: filters.append("subscriber != msisdn_counterpart") where_clause = make_where(filters) on_clause = f""" ON {'U.subscriber' if self.subscriber_identifier == 'msisdn' else 'U.msisdn'} = R.subscriber AND U.msisdn_counterpart = R.msisdn_counterpart """ sql = f""" SELECT subscriber, AVG(reciprocal::int) AS value FROM ( SELECT U.subscriber, COALESCE(reciprocal, FALSE) AS reciprocal FROM ( SELECT * FROM ({self.unioned_query.get_query()}) U {where_clause} ) U LEFT JOIN ( SELECT subscriber, msisdn_counterpart, reciprocal FROM ({self.contact_reciprocal_query.get_query()}) R ) R {on_clause} ) R GROUP BY subscriber """ return sql
def _make_query(self): where_clause = make_where(self.direction.get_filter_clause()) # Postgres does not support the following three operations with intervals if self.statistic in {"median", "stddev", "variance"}: statistic_clause = ( f"MAKE_INTERVAL(secs => {self.statistic}(EXTRACT(EPOCH FROM delta)))" ) else: statistic_clause = f"{self.statistic}(delta)" sql = f""" SELECT subscriber, {statistic_clause} AS value FROM ( SELECT subscriber, datetime - LAG(datetime, 1, NULL) OVER (PARTITION BY subscriber ORDER BY datetime) AS delta FROM ({self.unioned_query.get_query()}) AS U {where_clause} ) AS U GROUP BY subscriber """ return sql
def _make_query(self): filters = [self.direction.get_filter_clause()] if self.exclude_self_calls: filters.append("subscriber != msisdn_counterpart") where_clause = make_where(filters) sql = f""" SELECT subscriber, COUNT(*) AS value FROM ( SELECT DISTINCT subscriber, msisdn_counterpart FROM ({self.unioned_query.get_query()}) AS U {where_clause} ) AS U GROUP BY subscriber """ return sql
def _make_query(self): filters = [self.direction.get_filter_clause()] if (self.subscriber_identifier in {"msisdn" }) and (self.exclude_self_calls): filters.append("subscriber != msisdn_counterpart") where_clause = make_where(filters) sql = f""" WITH unioned AS ( SELECT * FROM ({self.unioned_query.get_query()}) as U {where_clause} ), total_events AS ( SELECT subscriber, count(*) AS events FROM unioned GROUP BY subscriber ) SELECT U.subscriber, U.msisdn_counterpart, count(*) as events, (count(*)::float / T.events::float) as proportion FROM (SELECT U.subscriber, U.msisdn_counterpart FROM unioned as U) AS U JOIN total_events AS T ON U.subscriber = T.subscriber GROUP BY U.subscriber, U.msisdn_counterpart, T.events ORDER BY proportion DESC """ return sql
def _make_query(self): where_clause = make_where(self.direction.get_filter_clause()) sql = f""" SELECT subscriber, AVG(nocturnal)*100 AS value FROM ( SELECT subscriber, CASE WHEN extract(hour FROM datetime) >= {self.hours[0]} OR extract(hour FROM datetime) < {self.hours[1]} THEN 1 ELSE 0 END AS nocturnal FROM ({self.unioned_query.get_query()}) U {where_clause} ) U GROUP BY subscriber """ return sql
def _make_query(self): loc_cols = ", ".join(self.spatial_unit.location_id_columns) where_clause = make_where(self.direction.get_filter_clause()) return f"""
def _make_query(self): where_clause = make_where(self.direction.get_filter_clause()) return f"""