# 仪表盘报告 - 点赞

**URL:** <https://meta.discourse.org/t/dashboard-report-likes/291092>\
**Category:** Data & reporting\
**Tags:** sql-query, dashboard-reports, dashboard-sql\
**Created:** [2024年一月9日 23:56 UTC](https://meta.discourse.org/t/dashboard-report-likes/291092 "2024-01-09T23:56:53Z")\
**Posts on this page:** 1\
**Showing post:** 1

<div class="post-metadata">

**Author:** ![SaraDev](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/saradev/32/335139_2.png) [@SaraDev](https://meta.discourse.org/u/SaraDev)\
**Post date:** [2024年一月9日 23:56 UTC](https://meta.discourse.org/t/dashboard-report-likes/291092/1 "2024-01-09T23:56:54Z")

</div>

这是“点赞”仪表板报告的 SQL 版本。

此查询提供了一个报告，其中包含在指定日期范围内，网站上所有帖子的总点赞数，按天统计。

```sql
-- [params]
-- date :start_date = 2023-12-08
-- date :end_date = 2024-01-10

WITH date_range AS (
  SELECT date_trunc('day', series) AS date
  FROM generate_series(
    :start_date::timestamp,
    :end_date::timestamp,
    '1 day'::interval
  ) series
)

SELECT
  dr.date::date,
  COALESCE(pa.likes_count, 0) AS likes_count
FROM
  date_range dr
LEFT JOIN (
  SELECT
    date_trunc('day', pa.created_at) AS action_date,
    COUNT(*) AS likes_count
  FROM post_actions pa
  WHERE pa.post_action_type_id = 2
    AND pa.created_at >= :start_date
    AND pa.created_at <= (:end_date::date + 1)
  GROUP BY action_date
) pa ON dr.date = pa.action_date
ORDER BY dr.date

```

### SQL 查询说明

查询的主要结构建立在一个名为 `date_range` 的 CTE（公共表表达式）之上，该 CTE 用于生成一系列时间戳，每个时间戳代表用户定义期间内的不同一天。

#### 参数

查询接受两个参数：

- `:start_date`：生成报告期间的开始日期。
- `:end_date`：生成报告期间的结束日期。

#### 公共表表达式：`date_range`

- `generate_series` 是一个函数，用于从 `:start_date` 到 `:end_date` 创建一组时间戳，增量为“1 天”间隔。
- `date_trunc('day', series)` 将时间戳截断到当天的开始，有效地将所有时间戳标准化为各自日期的 00:00:00。
- 结果是一组日期，每行一个，涵盖从 `:start_date` 到 `:end_date` 的整个范围。

#### 子查询：计算点赞数

使用子查询通过计算 `post_actions` 表中的行数来计算每天的点赞数。

- 此查询筛选 `post_actions` 中操作类型表示点赞的条目（其中 `post_action_type_id = 2` 表示“点赞”）。
- 它将操作筛选到日期范围，并在结束日期加一天以包含最后一天收到的点赞。
- 它按天对结果进行分组，并计算每天的点赞数。

#### 主查询：合并结果

查询的最后一部分将 `date_range` CTE 中的所有日期集与子查询中的点赞数合并。

- `LEFT JOIN` 确保 `date_range` 中的所有日期都包含在结果中，即使给定日期没有相应的点赞操作（子查询中没有找到连接）。
- `COALESCE` 用于将 `NULL` 计数（来自没有点赞的天数）替换为零，确保报告准确反映没有点赞活动的天数。
- 最终结果集按日期排序，以提供指定期间内点赞的按时间顺序的视图。

### 示例结果

| date | likes\_count |
| --- | --- |
| 2023-12-08 | 123 |
| 2023-12-09 | 156 |
| 2023-12-10 | 278 |
| 2023-12-11 | 134 |
| 2023-12-12 | 89 |

---

_[View the full topic](https://meta.discourse.org/t/dashboard-report-likes/291092)._