# 去除管理员/版主搜索的热门搜索词？

**URL:** <https://meta.discourse.org/t/trending-search-terms-with-admin-moderator-searches-removed/316002>\
**Category:** Data & reporting\
**Created:** [2024年七月11日 20:03 UTC](https://meta.discourse.org/t/trending-search-terms-with-admin-moderator-searches-removed/316002 "2024-07-11T20:03:36Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![Kieran\_Berryman](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/kieran_berryman/32/347853_2.png) [@Kieran\_Berryman](https://meta.discourse.org/u/Kieran_Berryman)\
**Post date:** [2024年七月11日 20:03 UTC](https://meta.discourse.org/t/trending-search-terms-with-admin-moderator-searches-removed/316002/1 "2024-07-11T20:03:36Z")

</div>

我正处于开始整理网站每周数据报告的早期阶段。目前我正在查看一些我想要积极跟踪的指标。我对热门搜索词报告感兴趣，但当我查看其历史记录时，看起来数据可能会受到管理员/版主搜索的严重影响。

该论坛部分充当了我公司制造产品的支持知识库，因此，我们在回复工单时经常搜索相关的故障排除帖子。有没有一种简单的方法可以从该报告中删除管理员/版主搜索？

---

<div class="post-metadata">

**Author:** ![Lilly](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/lilly/32/575047_2.png) [@Lilly](https://meta.discourse.org/u/Lilly)\
**Post date:** [2024年七月15日 10:51 UTC](https://meta.discourse.org/t/trending-search-terms-with-admin-moderator-searches-removed/316002/2 "2024-07-15T10:51:21Z")

</div>

您好 @Kieran_Berryman 👋

嗯，也许可以试试这个？ 🤔

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

WITH admin_moderator_users AS (
    SELECT 
        gu.user_id
    FROM 
        group_users gu
    JOIN 
        groups g ON gu.group_id = g.id
    WHERE 
        g.name IN ('admins', 'moderators')
)

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

```

* * *

这是“热门搜索词”仪表板报告的 SQL：

> [@Dashboard Report - Trending Search Terms](https://meta.discourse.org/t/dashboard-report-trending-search-terms/288562/1):
>
> This is an SQL version of the Dashboard Report for Trending Search Terms. This report provides insight into the search terms used on a site within a specified date range, and helps understand which terms are most frequently searched for and how often users click on the search results. --[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) \* 10…

---

<div class="post-metadata">

**Author:** ![Kieran\_Berryman](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/kieran_berryman/32/347853_2.png) [@Kieran\_Berryman](https://meta.discourse.org/u/Kieran_Berryman)\
**Post date:** [2024年七月15日 16:04 UTC](https://meta.discourse.org/t/trending-search-terms-with-admin-moderator-searches-removed/316002/3 "2024-07-15T16:04:32Z")

</div>

谢谢！我会去看看的。

---

<div class="post-metadata">

**Author:** ![system](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/system/32/443519_2.png) [@system](https://meta.discourse.org/u/system)\
**Post date:** [2024年八月14日 16:05 UTC](https://meta.discourse.org/t/trending-search-terms-with-admin-moderator-searches-removed/316002/4 "2024-08-14T16:05:05Z")

</div>

This topic was automatically closed 30 days after the last reply. New replies are no longer allowed.