-
Notifications
You must be signed in to change notification settings - Fork 8.3k
/
score_calculator.rb
155 lines (125 loc) · 3.85 KB
/
score_calculator.rb
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
class ScoreCalculator
def self.default_score_weights
{
reply_count: 5,
like_score: 15,
incoming_link_count: 5,
bookmark_count: 2,
avg_time: 0.05,
reads: 0.2
}
end
def initialize(weightings = nil)
@weightings = weightings || ScoreCalculator.default_score_weights
end
# Calculate the score for all posts based on the weightings
def calculate(opts = nil)
update_posts_score(opts)
update_posts_rank(opts)
update_topics_rank(opts)
update_topics_percent_rank(opts)
end
private
def update_posts_score(opts)
limit = 20000
components = []
@weightings.each_key { |k| components << "COALESCE(posts.#{k}, 0) * :#{k}" }
components = components.join(" + ")
builder = DB.build <<SQL
UPDATE posts p
SET score = x.score
FROM (
SELECT posts.id, #{components} as score FROM posts
join topics on posts.topic_id = topics.id
/*where*/
limit #{limit}
) AS x
WHERE x.id = p.id
SQL
builder.where("posts.score IS NULL OR posts.score <> #{components}", @weightings)
filter_topics(builder, opts)
while builder.exec == limit
end
end
def update_posts_rank(opts)
limit = 20000
builder = DB.build <<~SQL
UPDATE posts
SET percent_rank = X.percent_rank
FROM (
SELECT posts.id, Y.percent_rank
FROM posts
JOIN (
SELECT id, percent_rank()
OVER (PARTITION BY topic_id ORDER BY SCORE DESC) as percent_rank
FROM posts
) Y ON Y.id = posts.id
JOIN topics ON posts.topic_id = topics.id
/*where*/
LIMIT #{limit}
) AS X
WHERE posts.id = X.id
SQL
builder.where("posts.percent_rank IS NULL OR Y.percent_rank <> posts.percent_rank")
filter_topics(builder, opts)
while builder.exec == limit
end
end
def update_topics_rank(opts)
builder = DB.build <<~SQL
UPDATE topics AS topics
SET has_summary = (topics.like_count >= :likes_required AND
topics.posts_count >= :posts_required AND
x.max_score >= :score_required),
score = x.avg_score
FROM (SELECT p.topic_id,
MAX(p.score) AS max_score,
AVG(p.score) AS avg_score
FROM posts AS p
GROUP BY p.topic_id) AS x
/*where*/
SQL
defaults = {
likes_required: SiteSetting.summary_likes_required,
posts_required: SiteSetting.summary_posts_required,
score_required: SiteSetting.summary_score_threshold
}
builder.where(<<~SQL, defaults)
x.topic_id = topics.id AND
(
(topics.score <> x.avg_score OR topics.score IS NULL) OR
(topics.has_summary IS NULL OR topics.has_summary <> (
topics.like_count >= :likes_required AND
topics.posts_count >= :posts_required AND
x.max_score >= :score_required
))
)
SQL
filter_topics(builder, opts)
builder.exec
end
def update_topics_percent_rank(opts)
builder = DB.build <<~SQL
UPDATE topics SET percent_rank = x.percent_rank
FROM (SELECT id, percent_rank()
OVER (ORDER BY SCORE DESC) as percent_rank
FROM topics) AS x
/*where*/
SQL
builder.where("x.id = topics.id AND (topics.percent_rank <> x.percent_rank OR topics.percent_rank IS NULL)")
filter_topics(builder, opts)
builder.exec
end
def filter_topics(builder, opts)
return builder unless opts
if min_topic_age = opts[:min_topic_age]
builder.where("topics.bumped_at > :bumped_at ",
bumped_at: min_topic_age)
end
if max_topic_length = opts[:max_topic_length]
builder.where("topics.posts_count < :max_topic_length",
max_topic_length: max_topic_length)
end
builder
end
end