仪表盘报告 - 热门搜索词

数据与报告
, ,
1

这是趋势搜索词仪表板报告的 SQL 版本。

本报告可深入了解指定日期范围内网站上的搜索词，并有助于了解哪些词被搜索的频率最高以及用户点击搜索结果的频率。

--[params]
-- date :start_date
-- date :end_date

SELECT 
	lower(term) AS search_term,
	count(id) AS term_count,
	(COALESCE(SUM(CASE WHEN search_result_type IS NULL THEN 0 ELSE 1 END), 0) / count(id)::float) * 100 AS click_through_rate,
	SUM(CASE WHEN search_result_type IS NULL THEN 0 ELSE 1 END) as click_count
FROM search_logs sl
WHERE sl.created_at::date BETWEEN :start_date AND :end_date
GROUP BY lower(term)
ORDER BY term_count DESC

SQL 查询说明

以下是查询的细分：

  • 参数：
    • :start_date - 报告日期范围的开始日期。
    • :end_date - 报告日期范围的结束日期。
      • 两个日期参数均接受 YYYY-MM-DD 格式。
  • 数据选择：
    • lower(term) AS search_term：将搜索词转换为小写，以便进行一致的分组，并将结果列命名为 search_term。
    • count(id) AS term_count：计算每个词的总搜索次数，并将此列标记为 term_count。
    • (COALESCE(SUM(CASE WHEN search_result_type IS NULL THEN 0 ELSE 1 END), 0) / count(id)::float) * 100 AS click_through_rate：通过确定导致点击的搜索百分比来计算每个词的点击率 (CTR)。它通过将零分配给 search_result_type 为 NULL（表示未点击）的情况，否则为一，来处理这种情况。然后将结果除以该词的总搜索次数以获得比率，然后乘以 100 以获得百分比。
    • SUM(CASE WHEN search_result_type IS NULL THEN 0 ELSE 1 END) as click_count：对每个词的搜索结果被点击的次数进行求和。
  • 过滤：
    • WHERE sl.created_at::date BETWEEN :start_date AND :end_date：过滤搜索日志，仅包含 created_at 日期在指定开始日期和结束日期之间的条目。
  • 分组和排序：
    • GROUP BY lower(term)：按小写的搜索词对结果进行分组，以汇总每个唯一词的计数和点击次数。
    • ORDER BY term_count DESC：按 term_count 降序对结果进行排序，将搜索频率最高的词显示在顶部。

示例结果

search_term term_count click_through_rate click_count
example search term 756 0 0
#category search term 545 0.3669724770642202 2
in:personal order:latest 70 0 0
test 59 5.084745762711865 3
search 57 14.03508771929824 8
… … … …
5 个赞