添加日期参数

数据与报告
1

通常，您会想提取特定时间段的数据。您可以通过在查询本身中硬编码时间段来完成此操作，但为了使您的报告更具前瞻性，您可以添加日期参数：

此查询旨在计算在特定日期范围内创建的每个类别的帖子总数（不包括已删除的帖子和主题、私信以及没有类别的帖子），然后按帖子总数降序对类别进行排序。

-- [params]
-- date :start_date -- 这是范围开始日期的参数
-- date :end_date -- 这是范围结束日期的参数

SELECT
   t.category_id,
   COUNT(p.id) AS "Total Posts" -- 计算每个类别的帖子数量
FROM posts p
  JOIN topics t ON t.id = p.topic_id
WHERE p.created_at::date BETWEEN :start_date AND :end_date -- 筛选在开始日期和结束日期之间创建的帖子（::date 将 created_at 时间戳转换为日期）
  AND t.deleted_at IS NULL
  AND p.deleted_at IS NULL
  AND t.archetype <> 'private message'
  AND p.user_id > 0
  AND t.category_id IS NOT NULL
GROUP BY 1
ORDER BY 2 DESC

使用 date 而不是 string 风格的参数，可以使输入参数值更加直观，尤其是在与他人共享时。

此类型查询的另一个值得注意的技巧是将 created_at 转换为日期。由于该值在数据库中存储为时间戳，如果您不将其转换为日期，查询将无法获取 :end_date 当天的数据。

您可以使用 Explorer 树检查哪些值存储为时间戳：

explorer tree - value type
explorer tree - value type278×281 3.18 KB

此查询主要是用于演示原理的示例。希望您觉得它有用，但如果您有任何问题，请在下方提问。 :slight_smile:

9 个赞
2 
PG::SyntaxError: ERROR:  syntax error at or near \":\"
LINE 16: WHERE p.created_at::date BETWEEN :start_date AND :end_date -…

:tired_face:

4 个赞
3

是的，它不喜欢参数旁边的那些内联注释。

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

SELECT
   t.category_id,
   COUNT(p.id) AS "Total Posts" -- 计算每个类别中的帖子数量
FROM posts p
  JOIN topics t ON t.id = p.topic_id
WHERE p.created_at::date BETWEEN :start_date AND :end_date -- 筛选在开始日期和结束日期之间创建的帖子 (::date 将 created_at 时间戳转换为日期)
  AND t.deleted_at IS NULL
  AND p.deleted_at IS NULL
  AND t.archetype <> 'private message'
  AND p.user_id > 0
  AND t.category_id IS NOT NULL
GROUP BY 1
ORDER BY 2 DESC

这个似乎效果更好。:+1: (您可能需要第一次刷新页面才能看到参数输入框)

感谢您帮助我调试指南。:slight_smile: :trophy:

7 个赞