Guest User

Untitled

a guest
Oct 25th, 2017
137
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 4.45 KB | None | 0 0
  1. #!/usb/bin/env python
  2.  
  3. import psycopg2
  4. import argparse
  5.  
  6. def get_xaily_messages(conn, x):
  7. with conn.cursor() as cur:
  8. cur.execute("""
  9. SELECT COUNT(DISTINCT message_id), date_trunc('%s', date) AS quant
  10. FROM history_messages
  11. GROUP BY 2
  12. ORDER BY 2 DESC
  13. """ % x)
  14. for res in cur:
  15. yield res
  16.  
  17. def get_groups_total(conn):
  18. with conn.cursor() as cur:
  19. cur.execute("SELECT COUNT(id) FROM groups")
  20. return cur.fetchone()[0]
  21.  
  22. def get_avg_msgs_per_user_per_xay(conn, x):
  23. with conn.cursor() as cur:
  24. cur.execute("""
  25. WITH messages AS (
  26. SELECT COUNT(h.message_id) AS avr, h.creator_user_id AS author, date_trunc('%s', date) AS quant
  27. FROM history_messages h
  28. WHERE h.message_id IN (SELECT DISTINCT message_id FROM history_messages)
  29. AND h.message_content_header <> 2
  30. AND h.creator_user_id NOT IN (0, 10)
  31. GROUP BY 3, 2
  32. ORDER BY 3, 2
  33. )
  34. SELECT AVG(ms.avr), ms.quant FROM messages AS ms
  35. GROUP BY 2
  36. ORDER BY 2 DESC
  37. """ % x)
  38. for r in cur:
  39. yield r
  40.  
  41. def get_avg_groups_per_member_xaily(conn, x):
  42. with conn.cursor() as cur:
  43. cur.execute("""
  44. WITH xaily_user_groups AS (
  45. SELECT gu.user_id, COUNT(gu.group_id) AS cnt, date_trunc('%s', gu.joined_at) AS quant
  46. FROM group_users AS gu
  47. GROUP BY gu.user_id, 3
  48. )
  49. SELECT AVG(x.cnt), x.quant FROM xaily_user_groups AS x
  50. GROUP BY 2
  51. ORDER BY 2 DESC
  52.  
  53. """ % x)
  54. for r in cur:
  55. yield r
  56.  
  57. def get_xau(conn, x):
  58. with conn.cursor() as cur:
  59. cur.execute("""
  60. SELECT COUNT(DISTINCT user_id), date_trunc('%s', changed) AS quant
  61. FROM conversation_message_statuses
  62. WHERE action = 'read'
  63. GROUP BY 2
  64. ORDER BY 2 DESC
  65. """ % x)
  66. for r in cur:
  67. yield r
  68.  
  69. def get_groups_by_xau(conn, x):
  70. with conn.cursor() as cur:
  71. cur.execute("""
  72. SELECT COUNT(id), date_trunc('%s', created_at) AS quant
  73. FROM groups
  74. GROUP BY 2
  75. ORDER BY 2 DESC
  76. """ % x)
  77. for r in cur:
  78. yield r
  79.  
  80. if __name__ == '__main__':
  81. parser = argparse.ArgumentParser()
  82. parser.add_argument('host')
  83. parser.add_argument('user')
  84. parser.add_argument('password')
  85. parser.add_argument('dbname')
  86. parser.add_argument('-p','--port', dest='port', type=int, default=5432, action='store')
  87. args = parser.parse_args()
  88.  
  89. conn = psycopg2.connect(dbname=args.dbname, user=args.user, password=args.password, host=args.host, port=args.port)
  90.  
  91. with open('stats_%s.csv' % args.host, 'w') as csv:
  92. csv.write('Daily messages\n')
  93. csv.write('Date, Messages\n')
  94. for r, d in get_xaily_messages(conn, 'day'):
  95. csv.write("%s, %.2f\n" % (d.date().isoformat(), r))
  96.  
  97. csv.write('\nMonthly messages\n')
  98. csv.write('Date, Messages\n')
  99. for r, d in get_xaily_messages(conn, 'month'):
  100. csv.write("%s, %.2f\n" % (d.date().isoformat(), r))
  101.  
  102. csv.write('\nAverage messages per user per day\n')
  103. csv.write('Date, Messages\n')
  104. for r, d in get_avg_msgs_per_user_per_xay(conn, 'day'):
  105. csv.write("%s, %.2f\n" % (d.date().isoformat(), r))
  106.  
  107. csv.write('\nAverage messages per user per month\n')
  108. csv.write('Date, Messages\n')
  109. for r, d in get_avg_msgs_per_user_per_xay(conn, 'month'):
  110. csv.write("%s, %.2f\n" % (d.date().isoformat(), r))
  111.  
  112. csv.write('\nAverage groups per member\n')
  113. csv.write('Date, Groups\n')
  114. for r, d in get_avg_groups_per_member_xaily(conn, 'month'):
  115. csv.write("%s, %.2f\n" % (d.date().isoformat(), r))
  116.  
  117. csv.write('\nGroups created by month\n')
  118. csv.write('Date, Groups\n')
  119. for r, d in get_groups_by_xau(conn, 'month'):
  120. csv.write("%s, %.2f\n" % (d.date().isoformat(), r))
  121.  
  122. csv.write('\nDaily active users\n')
  123. csv.write('Date, Active users\n')
  124. for r, d in get_xau(conn, 'day'):
  125. csv.write("%s, %.2f\n" % (d.date().isoformat(), r))
  126.  
  127. csv.write('\nMonthly active users\n')
  128. csv.write('Date, Active users\n')
  129. for r, d in get_xau(conn, 'month'):
  130. csv.write("%s, %.2f\n" % (d.date().isoformat(), r))
Add Comment
Please, Sign In to add comment