仪表板报告 - 书签

数据与报告
, ,
1

这是书签仪表板报告的 SQL 版本。

此仪表板报告提供指定日期范围内创建的书签的每日计数。

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

WITH bookmark_counts AS (
  SELECT
    DATE(bookmarks.created_at) AS bookmark_date,
    COUNT(DISTINCT CASE WHEN bookmarkable_type = 'Topic' THEN bookmarks.id END) AS topic_bookmarks,
    COUNT(DISTINCT CASE WHEN bookmarkable_type = 'Post' THEN bookmarks.id END) AS post_bookmarks
  FROM bookmarks
  WHERE bookmarks.created_at::date BETWEEN :start_date AND :end_date
  GROUP BY bookmark_date
)
SELECT
  bookmark_date,
  topic_bookmarks + post_bookmarks AS total_bookmarks,
  topic_bookmarks,
  post_bookmarks
FROM bookmark_counts
ORDER BY bookmark_date

SQL 查询说明

参数

要生成报告，您需要提供两个参数：:start_date 和 :end_date。这些参数定义了您要分析书签活动的日期范围。两个日期参数都接受 YYYY-MM-DD 格式的日期。

Bookmark_Counts (CTE)

使用名为 bookmark_counts 的公共表表达式 (CTE) 来组织数据，然后再进行最终选择。CTE 执行以下操作：

  • **日期过滤：**按创建日期过滤书签，仅包括在提供的开始和结束日期（:start_date和:end_date`）内创建的书签。
  • **日期分组：**按书签的创建日期对结果进行分组，确保计数按天汇总。
  • **计算书签：**分别计算主题和帖子的书签数量，使用 DISTINCT 确保每个书签只计算一次。这是使用带有 CASE 语句的条件聚合完成的：
    • topic_bookmarks 计算 bookmarkable_type 为“Topic”的书签数量。
    • post_bookmarks 计算 bookmarkable_type 为“Post”的书签数量。

主查询

在 CTE 组织好数据后，最终的 SELECT 语句将检索以下列：

  1. bookmark_date：汇总计数的日期。
  2. total_bookmarks：每天主题和帖子书签的总和。
  3. topic_bookmarks：每天主题书签的数量。例如：
    image
    image1249×198 17.3 KB
  4. post_bookmarks：每天帖子书签的数量。例如：
    image
    image1121×125 5.07 KB

然后按 bookmark_date 对结果进行排序，以按时间顺序呈现数据。

示例结果

bookmark_date total_bookmarks topic_bookmarks post_bookmarks
2023-12-01 16 1 15
2023-12-02 4 1 3
2023-12-03 8 0 8
2023-12-04 19 9 10
2023-12-05 18 3 15
… … … …
3 个赞