olegbc1
2019 年 9 月 16 日午後 8:44
1
特定の期間の上位検索項目とCTR（クリック率）を表示するSQLクエリを作成したいと考えています。Search Logsレポート（…/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 日午後 3: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.
