Secure your code as it's written. Use Snyk Code to scan source code in minutes - no build needed - and fix issues immediately.
)
returning_time_columns = ", ".join(
[x.split(" AS ")[-1] for x in self.redaction_target.time_cols]
)
# We now need to group this table by the relevant columns in order to
# get a count per region
sql = f"""
WITH tne AS (SELECT
{', '.join(self.redaction_target.spatial_unit.location_id_columns)},
{', '.join(self.redaction_target.time_cols)},
count(*) AS value,
count(distinct subscriber) > 15 AS safe_agg
FROM
({self.redaction_target.unioned.get_query()}) unioned
{make_where(self.redaction_target.direction.get_filter_clause())}
GROUP BY
{', '.join(groups)})
SELECT {returning_columns},
{returning_time_columns},
value
FROM tne NATURAL JOIN
(SELECT {returning_columns} FROM tne
GROUP BY {returning_columns}
HAVING every(safe_agg)
) _
"""
return sql
# list of columns that we want to group by, these are all the time
# columns, plus the location columns
groups = [
x.split(" AS ")[0] for x in self.time_cols
] + self.spatial_unit.location_id_columns
# We now need to group this table by the relevant columns in order to
# get a count per region
sql = f"""
SELECT
{', '.join(self.spatial_unit.location_id_columns)},
{', '.join(self.time_cols)},
count(*) AS value
FROM
({self.unioned.get_query()}) unioned
{make_where(self.direction.get_filter_clause())}
GROUP BY
{', '.join(groups)}
"""
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"""
SELECT subscriber, {loc_cols}, {self.statistic}(duration) as value
FROM ({self.unioned_query.get_query()}) u
{where_clause}
GROUP BY subscriber, {loc_cols}
def _make_query(self):
return f"""
SELECT subscriber, COUNT(*) as value FROM
({self.unioned_query.get_query()}) u
{make_where(self.direction.get_filter_clause())}
GROUP BY subscriber
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
def _make_query(self):
loc_cols = ", ".join(self.spatial_unit.location_id_columns)
where_clause = make_where(self.direction.get_filter_clause())
return f"""
SELECT subscriber, {self.statistic}(events) AS value
FROM (
SELECT subscriber, {loc_cols}, COUNT(*) AS events
FROM ({self.unioned_query.get_query()}) U
{where_clause}
GROUP BY subscriber, {loc_cols}
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 (
def _make_query(self):
location_columns = self.spatial_unit.location_id_columns
sql = f"""
WITH unioned_table AS ({self.unioned_query.get_query()})
SELECT {', '.join(location_columns)}, sum(introverted::integer)/count(*)::float as value FROM (
SELECT
{', '.join(f'A.{c} as {c}' for c in location_columns)},
{' AND '.join(f'A.{c} = B.{c}' for c in location_columns)} as introverted
FROM unioned_table as A
INNER JOIN unioned_table AS B
ON A.id = B.id
AND A.outgoing != B.outgoing
{make_where(self.direction.get_filter_clause(prefix="A"))}
) _
GROUP BY {', '.join(location_columns)}
ORDER BY {', '.join(location_columns)}
"""
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}
def _absolute_freq_query(self):
return f"""
SELECT subscriber, COUNT(*) AS absolute_freq FROM
({self.unioned_query.get_query()}) u
{make_where(self.direction.get_filter_clause())}
GROUP BY subscriber, EXTRACT( {self.phase} FROM datetime )
HAVING COUNT(*) > 0