-
Notifications
You must be signed in to change notification settings - Fork 3
/
Copy pathmessage_topuser.py
165 lines (144 loc) · 6.7 KB
/
message_topuser.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
import os
from core.slack import SlackMessageRetriever
from core.bigquery import BigqueryProcessor
# 슬랙 메시지 전송을 위한 인스턴스 생성
slack_app = SlackMessageRetriever(env_name="SLACK_TOKEN_BOT")
# 빅쿼리 쿼리를 실행하기 위한 인스턴스 생성
bigquery_client = BigqueryProcessor(
env_name="GOOGLE_APPLICATION_CREDENTIALS", database_id="geultto_9th"
)
# 유저 활동 정보를 가져옴 (이모지, 게시글, 댓글)
total_cnt = bigquery_client.run_query_to_dataframe(
query="""
SELECT SUM(cnt_emoji) AS total_emoji
, SUM(cnt_post) AS total_post
, SUM(cnt_thread) AS total_thread
FROM (
SELECT users.*
, cnt_emoji
, cnt_post
, cnt_thread
FROM `geultto.geultto_9th.users` AS users
LEFT JOIN (
SELECT '이모지수' AS group_name
, user_id
, COUNT(*) cnt_emoji
FROM (
SELECT JSON_EXTRACT_SCALAR(json, '$.name') AS name
, REPLACE(user_id, '"', "") AS user_id
FROM `geultto.geultto_9th.slack_conversation_master`,
UNNEST(JSON_EXTRACT_ARRAY(reactions)) AS json,
UNNEST(JSON_EXTRACT_ARRAY(json, '$.user_id')) AS user_id
WHERE tddate <= '2023-12-24'
) A
GROUP BY user_id
) emoji_cnt
ON users.user_id = emoji_cnt.user_id
LEFT JOIN (
SELECT user_id
, COUNT(*) cnt_post
FROM `geultto.geultto_9th.slack_conversation_master`
WHERE tddate <= '2023-12-24'
AND message_type = 'post'
AND (text not like ('%님이 채널에 참여함%') AND text not like ('%integration to this channel%'))
GROUP BY user_id
) post_cnt
ON users.user_id = post_cnt.user_id
LEFT JOIN (
SELECT user_id
, COUNT(*) cnt_thread
FROM `geultto.geultto_9th.slack_conversation_master`
WHERE tddate <= '2023-12-24'
AND message_type = 'thread'
GROUP BY user_id
) thread_cnt
ON users.user_id = thread_cnt.user_id
) A
"""
)
# 상위 3명의 유저 정보를 가져옴
top_user = bigquery_client.run_query_to_dataframe(
query="""
SELECT *
FROM (
SELECT conversation_cnt.*
, user_db_master.name
, ROW_NUMBER() OVER(PARTITION BY group_name ORDER BY cnt DESC) AS row_nums
FROM (
SELECT '이모지' AS group_name
, user_id
, COUNT(*) cnt
FROM (
SELECT JSON_EXTRACT_SCALAR(json, '$.name') AS name
, REPLACE(user_id, '"', "") AS user_id
FROM `geultto.geultto_9th.slack_conversation_master`,
UNNEST(JSON_EXTRACT_ARRAY(reactions)) AS json,
UNNEST(JSON_EXTRACT_ARRAY(json, '$.user_id')) AS user_id
WHERE tddate <= '2023-12-24'
) A
GROUP BY user_id
UNION ALL
SELECT '게시글' AS group_name
, user_id
, COUNT(*) cnt
FROM `geultto.geultto_9th.slack_conversation_master`
WHERE tddate <= '2023-12-24'
AND message_type = 'post'
AND (text not like ('%님이 채널에 참여함%') AND text not like ('%integration to this channel%'))
GROUP BY user_id
UNION ALL
SELECT '댓글' AS group_name
, user_id
, COUNT(*) cnt
FROM `geultto.geultto_9th.slack_conversation_master`
WHERE tddate <= '2023-12-24'
AND message_type = 'thread'
GROUP BY user_id
) conversation_cnt
LEFT JOIN `geultto.geultto_9th.users` AS users
ON conversation_cnt.user_id = users.user_id
LEFT JOIN `geultto.geultto_9th.user_db_master` AS user_db_master
ON conversation_cnt.user_id = user_db_master.userid
-- 운영진 제외
WHERE real_name NOT IN ('또봇', 'Bamboo Forest', '강나영', '변성윤', '강승현', '김은찬', '김재휘', '김정희', '김준홍', '류지환', '박수민', '배지훈', '성연찬', '송경근', '송민혜', '신해나라', '이찬주', '임정', '임지훈', '정이태', '정종윤', '정현수', '조재우', '지정수', '채정현', '최현구', '조동민')
AND display_name NOT IN ('강나영', '변성윤', '강승현', '김은찬', '김재휘', '김정희', '김준홍', '류지환', '박수민', '배지훈', '성연찬', '송경근', '송민혜', '신해나라', '이찬주', '임정', '임지훈', '정이태', '정종윤',
'정현수', '조재우', '지정수', '채정현', '최현구', '조동민')
AND name NOT IN ('강나영', '변성윤', '강승현', '김은찬', '김재휘', '김정희', '김준홍', '류지환', '박수민', '배지훈', '성연찬', '송경근', '송민혜', '신해나라', '이찬주', '임정', '임지훈', '정이태', '정종윤', '정현수', '조재우', '지정수', '채정현', '최현구', '조동민')
) with_row_nums
WHERE row_nums <= 3
ORDER BY group_name, row_nums
"""
)
# 1짱, 2짱, 3짱, 4짱을 표현하기 위해 사용 - 1: one, 2: two, 3: three, 4: four
def number_to_string(num):
if num == 1:
return "one"
elif num == 2:
return "two"
elif num == 3:
return "three"
else:
return "four"
# 전체 카운트 수
total_emoji = total_cnt["total_emoji"][0]
total_post = total_cnt["total_post"][0]
total_thread = total_cnt["total_thread"][0]
# 상위 3명의 유저 정보를 slack 메시지로 변환
def process_row(row):
return f"""{row['group_name']} :{number_to_string(row['row_nums'])}:짱: :hearts:<@{row['user_id']}>님!:hearts: :amaze:{row['cnt']}회:amaze:"""
slack_messages = [process_row(row) for index, row in top_user.iterrows()]
slack_message = "\n".join(slack_messages)
text = f""":point_right::point_right: 데달부가 1회차, 2회차 활동 기록을 공유드려요!! :point_left::point_left:
*총 게시글 수:* `{total_post}개`, *총 댓글 수:* `{total_thread}개`, *총 이모지 수:* `{total_emoji}개`
어느 정도 수치인지 감이 안오신다구요? :thinking_face:
`8기(1~11회차) 대비, 벌써 게시글/댓글은 절반 수준, 이모지수는 3,000개 가량 더 많습니다..!`
이제 한달가량 지났는데, 벌써 정말 많은 활동들을 해주셨네요 :amaze:
_(혹시나 데이터 수집 & 8기 활동 분석 내용을 보고 싶으신 분들은 해당 <https://github.com/ddongmiin/geultto_genie_bot?tab=readme-ov-file|링크>를 참조해주세요!!!)_
다들 열심히 활동해주셔서 정말정말 감사드립니다 :man-bowing::man-bowing::man-bowing:
가장 많은 활동을 해주신 명예의 전당 7분을 소개해드리며 이만 데달부는 물러가겠습니다 :wave:
올 한해 마무리 잘 하시고, 다들 새해 복 많이 받으세요:pray::pray::pray:
{slack_message}
"""
# 채널 아이디 기입
channel_id = ""
slack_app.message_for_channel(channel_id=channel_id, text=text)