olegbc1
2019 年9 月 16 日 20:44
1
我想编写一个 SQL 查询，以显示任意给定日期范围内的热门搜索项及其点击率（CTR）。是否有人了解搜索日志报告（位于 …/admin/logs/search_logs?period=weekly）是如何生成的，特别是 CTR 是如何计算的？
我看到有一个 search_logs 表，但不确定 CTR 是基于哪些数据计算的。
谢谢，
loginerror
2019 年10 月 23 日 11:38
2
What an old topic without an answer, let’s bump it
I would like to find out which topics were clicked for particular search term. I found this query, which comes close, but the tables seem to be deprecated since then:
We actually did complete the work of logging the information. You can try out:
SELECT term, count(*) searches,
sum(case when clicked_topic_id is not null then 1 else 0 end)
"continued from search"
from search_logs
where created_at > current_timestamp - interval '30' day
group by term
order by count(*) desc
limit 100
It Data Explorer, it will show you top 100 searches and even counts of who actually clicked through from search. I find the results super interesting.
We will work on exposin…
Is there any way to achieve it on the latest version?
j.jaffeux
2019 年10 月 23 日 15:14
3
https://github.com/discourse/discourse/blob/master/app/models/reports/trending_search.rb
and
}
end
def self.trending(period = :all, search_type = :all)
SearchLog.trending_from(start_of(period), search_type: search_type)
end
def self.trending_from(start_date, options = {})
end_date = options[:end_date]
search_type = options[:search_type] || :all
limit = options[:limit] || 100
select_sql = <<~SQL
lower(term) term,
COUNT(*) AS searches,
SUM(CASE
WHEN search_result_id IS NOT NULL THEN 1
ELSE 0
END) AS click_through
SQL
Should give you all you need to know.
