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"""