Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- #!/usb/bin/env python
- import psycopg2
- import argparse
- def get_xaily_messages(conn, x):
- with conn.cursor() as cur:
- cur.execute("""
- SELECT COUNT(DISTINCT message_id), date_trunc('%s', date) AS quant
- FROM history_messages
- GROUP BY 2
- ORDER BY 2 DESC
- """ % x)
- for res in cur:
- yield res
- def get_groups_total(conn):
- with conn.cursor() as cur:
- cur.execute("SELECT COUNT(id) FROM groups")
- return cur.fetchone()[0]
- def get_avg_msgs_per_user_per_xay(conn, x):
- with conn.cursor() as cur:
- cur.execute("""
- WITH messages AS (
- SELECT COUNT(h.message_id) AS avr, h.creator_user_id AS author, date_trunc('%s', date) AS quant
- FROM history_messages h
- WHERE h.message_id IN (SELECT DISTINCT message_id FROM history_messages)
- AND h.message_content_header <> 2
- AND h.creator_user_id NOT IN (0, 10)
- GROUP BY 3, 2
- ORDER BY 3, 2
- )
- SELECT AVG(ms.avr), ms.quant FROM messages AS ms
- GROUP BY 2
- ORDER BY 2 DESC
- """ % x)
- for r in cur:
- yield r
- def get_avg_groups_per_member_xaily(conn, x):
- with conn.cursor() as cur:
- cur.execute("""
- WITH xaily_user_groups AS (
- SELECT gu.user_id, COUNT(gu.group_id) AS cnt, date_trunc('%s', gu.joined_at) AS quant
- FROM group_users AS gu
- GROUP BY gu.user_id, 3
- )
- SELECT AVG(x.cnt), x.quant FROM xaily_user_groups AS x
- GROUP BY 2
- ORDER BY 2 DESC
- """ % x)
- for r in cur:
- yield r
- def get_xau(conn, x):
- with conn.cursor() as cur:
- cur.execute("""
- SELECT COUNT(DISTINCT user_id), date_trunc('%s', changed) AS quant
- FROM conversation_message_statuses
- WHERE action = 'read'
- GROUP BY 2
- ORDER BY 2 DESC
- """ % x)
- for r in cur:
- yield r
- def get_groups_by_xau(conn, x):
- with conn.cursor() as cur:
- cur.execute("""
- SELECT COUNT(id), date_trunc('%s', created_at) AS quant
- FROM groups
- GROUP BY 2
- ORDER BY 2 DESC
- """ % x)
- for r in cur:
- yield r
- if __name__ == '__main__':
- parser = argparse.ArgumentParser()
- parser.add_argument('host')
- parser.add_argument('user')
- parser.add_argument('password')
- parser.add_argument('dbname')
- parser.add_argument('-p','--port', dest='port', type=int, default=5432, action='store')
- args = parser.parse_args()
- conn = psycopg2.connect(dbname=args.dbname, user=args.user, password=args.password, host=args.host, port=args.port)
- with open('stats_%s.csv' % args.host, 'w') as csv:
- csv.write('Daily messages\n')
- csv.write('Date, Messages\n')
- for r, d in get_xaily_messages(conn, 'day'):
- csv.write("%s, %.2f\n" % (d.date().isoformat(), r))
- csv.write('\nMonthly messages\n')
- csv.write('Date, Messages\n')
- for r, d in get_xaily_messages(conn, 'month'):
- csv.write("%s, %.2f\n" % (d.date().isoformat(), r))
- csv.write('\nAverage messages per user per day\n')
- csv.write('Date, Messages\n')
- for r, d in get_avg_msgs_per_user_per_xay(conn, 'day'):
- csv.write("%s, %.2f\n" % (d.date().isoformat(), r))
- csv.write('\nAverage messages per user per month\n')
- csv.write('Date, Messages\n')
- for r, d in get_avg_msgs_per_user_per_xay(conn, 'month'):
- csv.write("%s, %.2f\n" % (d.date().isoformat(), r))
- csv.write('\nAverage groups per member\n')
- csv.write('Date, Groups\n')
- for r, d in get_avg_groups_per_member_xaily(conn, 'month'):
- csv.write("%s, %.2f\n" % (d.date().isoformat(), r))
- csv.write('\nGroups created by month\n')
- csv.write('Date, Groups\n')
- for r, d in get_groups_by_xau(conn, 'month'):
- csv.write("%s, %.2f\n" % (d.date().isoformat(), r))
- csv.write('\nDaily active users\n')
- csv.write('Date, Active users\n')
- for r, d in get_xau(conn, 'day'):
- csv.write("%s, %.2f\n" % (d.date().isoformat(), r))
- csv.write('\nMonthly active users\n')
- csv.write('Date, Active users\n')
- for r, d in get_xau(conn, 'month'):
- csv.write("%s, %.2f\n" % (d.date().isoformat(), r))
Add Comment
Please, Sign In to add comment