-
Notifications
You must be signed in to change notification settings - Fork 0
/
analytic.go
134 lines (105 loc) · 2.73 KB
/
analytic.go
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
package rating
import (
"time"
"github.com/rs/zerolog"
)
type (
TopMemeView struct {
Link string
MemberFullName string
Score int
}
TopAuthorView struct {
MemberFullName string
Score int
}
TopCriterion struct {
from time.Time
to time.Time
channelID string
limit int
}
TopFetcher interface {
FetchTopMemes(criterion TopCriterion) ([]TopMemeView, error)
FetchTopAuthors(criterion TopCriterion) ([]TopAuthorView, error)
}
SQLiteTopMemeFetcher struct {
connection Connection
logger zerolog.Logger
}
)
func NewTopMemeCriterion(from time.Time, to time.Time, channelID string, limit int) TopCriterion {
return TopCriterion{
from: from,
to: to,
channelID: channelID,
limit: limit,
}
}
func NewSQLiteTopFetcher(connection Connection, logger zerolog.Logger) TopFetcher {
return SQLiteTopMemeFetcher{connection: connection, logger: logger}
}
func (s SQLiteTopMemeFetcher) FetchTopMemes(criterion TopCriterion) ([]TopMemeView, error) {
query := `
SELECT meme.link, COALESCE(member.full_name, 'Неизвестно кто'), meme.score
FROM memes meme LEFT JOIN members member ON meme.member_id = member.id
WHERE meme.timestamp BETWEEN ? AND ? AND meme.channel_id = ?
ORDER BY score DESC
LIMIT ?
`
rows, err := s.connection.Query(
query,
criterion.from.Unix(),
criterion.to.Unix(),
criterion.channelID,
criterion.limit,
)
if err != nil {
return []TopMemeView{}, err
}
defer rows.Close()
result := make([]TopMemeView, 0, criterion.limit)
for rows.Next() {
memeView := TopMemeView{}
scanErr := rows.Scan(&memeView.Link, &memeView.MemberFullName, &memeView.Score)
if scanErr != nil {
s.logger.Err(scanErr).Msg("Can not scan row")
return nil, scanErr
}
result = append(result, memeView)
}
return result, nil
}
func (s SQLiteTopMemeFetcher) FetchTopAuthors(criterion TopCriterion) ([]TopAuthorView, error) {
query := `
SELECT member.full_name, sum(meme.score) AS score FROM memes meme
LEFT JOIN main.members member on member.id = meme.member_id
WHERE meme.timestamp BETWEEN ? AND ?
AND meme.channel_id = ?
GROUP BY member.full_name
ORDER BY score DESC
LIMIT ?
`
rows, err := s.connection.Query(
query,
criterion.from.Unix(),
criterion.to.Unix(),
criterion.channelID,
criterion.limit,
)
if err != nil {
return []TopAuthorView{}, err
}
defer rows.Close()
result := make([]TopAuthorView, 0, criterion.limit)
for rows.Next() {
authorView := TopAuthorView{}
scanErr := rows.Scan(&authorView.MemberFullName, &authorView.Score)
if scanErr != nil {
s.logger.Err(scanErr).Msg("Can not scan row")
return nil, scanErr
}
result = append(result, authorView)
}
return result, nil
}