def _query_weekly_average(self, table, tables):
# First see if we can find 6 days prior for a full week
idx = tables.index(table)
# For Python list math, 6 has 6 numbers before it as zero index
# based, so 6 or larger is needed
if idx < 6:
return None
# Get our weekly set together
# Note that we add one to idx since list splicing needs one higher than
# the index for right-side inclusive
week_tables = union_all(*[
select([tbl]).where(tbl.c.message == "Ack")
for tbl in tables[idx-6:idx+1]
])
# Calculate channels per user for the past week
chans_per_user = select([
week_tables.c.uaid_hash,
func.count(func.distinct(week_tables.c.channel_id)).label("count")
]).\
group_by(week_tables.c.uaid_hash)
# Rank them into ntiles
ranked = select([
chans_per_user.c.uaid_hash,
chans_per_user.c.count,
func.ntile(100).over(order_by=text("count ASC")).label("rank"),
])
# Remove the bottom/upper 5%, get sum/count for avg
weekly_channels_stats = select([
func.sum(ranked.c.count),
func.count(ranked.c.uaid_hash),
]).\
where(ranked.c.rank > 5).\
where(ranked.c.rank <= 95)
sums, count = self._conn.execute(weekly_channels_stats).fetchone()
weekly_avg = Decimal(sums) / Decimal(count)
return weekly_avg
def _query_daily_average(self, table):
per_user_counts = select([
table.c.uaid_hash,
func.count(table.c.uaid_hash).label("msg_count"),
]).\
where(table.c.message == "Ack").\
group_by(table.c.uaid_hash)
ranked_counts = select([
per_user_counts.c.uaid_hash,
per_user_counts.c.msg_count,
func.ntile(100).over(order_by=text("msg_count ASC")).label("rank"),
])
daily_avg = select([
func.sum(ranked_counts.c.msg_count),
func.count(ranked_counts.c.uaid_hash),
]).\
where(ranked_counts.c.rank > 5).\
where(ranked_counts.c.rank <= 95)
sums, count = self._conn.execute(daily_avg).fetchone()
daily_avg = Decimal(sums) / Decimal(count)
return daily_avg
