# 按指定时间范围统计用户每日、每周或总数据

**URL:** https://meta.discourse.org/t/daily-weekly-or-total-stats-by-user-over-a-specified-time-range/275167
**Category:** Data & reporting
**Tags:** sql-query
**Created:** [2019年十二月31日 20:08 UTC](https://meta.discourse.org/t/daily-weekly-or-total-stats-by-user-over-a-specified-time-range/275167 "2019-12-31T20:08:57Z")
**Posts on this page:** 1
**Page:** 1

<div class="post-metadata">

### Author: ![Grayden\_Shand](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/grayden_shand/32/166586_2.png) [@Grayden\_Shand](https://meta.discourse.org/u/Grayden_Shand)
#### Post date: [2019年十二月31日 20:08 UTC](https://meta.discourse.org/t/daily-weekly-or-total-stats-by-user-over-a-specified-time-range/275167/1 "2019-12-31T20:08:57Z")

</div>

```
-- 覆盖范围：'week'（周）、'all'（全部）或 'date'（日期）
-- [参数]
-- date :start_date = 2019-08-27
-- date :end_date = 2019-09-30
-- text :coverage = week

WITH date_range AS (
SELECT date_trunc('day', dd):: date AS "date", EXTRACT(week from date_trunc('day', dd):: date) AS "week"
FROM generate_series
        ( :start_date::timestamp 
        , :end_date::timestamp
        , '1 day'::interval) dd
), likes_given AS (
SELECT u.id, dr.date, dr.week, count(pa.*) AS "likes"
FROM date_range dr
FULL JOIN users u ON (1=1)
LEFT JOIN post_actions pa ON (pa.created_at:: date = dr.date and post_action_type_id=2 AND user_id = u.id)
GROUP BY dr.date, dr.week, u.id
ORDER BY u.id, dr.date
), posts_summary AS (SELECT u.id, u.username, u.created_at, dr.*, count(p.id) - count(t.id) AS replies, count(t.id) AS topics, COALESCE(sum(p.like_count),0) AS likes_received
from date_range dr
FULL OUTER JOIN users u ON (1=1)
LEFT JOIN posts p ON (p.user_id = u.id AND p.created_at::date=dr.date AND p.deleted_at IS NULL)
LEFT JOIN topics t ON (t.user_id = u.id AND t.created_at::date = dr.date AND p.topic_id = t.id AND t.deleted_at IS NULL)
GROUP BY u.id, dr.date, dr.week
ORDER BY u.id, dr.date), 
visits AS (SELECT u.id, dr.*, COALESCE(sum(posts_read),0) AS posts_read, COALESCE(sum(time_read),0) AS time_read, COUNT(uv.*) AS visits
FROM date_range dr
FULL OUTER JOIN users u ON (1=1)
LEFT JOIN user_visits uv ON (uv.user_id = u.id AND visited_at = dr.date)
GROUP BY u.id, dr.date, dr.week
ORDER BY u.id, dr.date
)

SELECT ps.id, ps.username, ps.created_at, CASE
WHEN :coverage::text = 'week' THEN ps.week::text
WHEN :coverage::text = 'all' THEN '-1'
ELSE ps.date::text
END AS period, sum(ps.replies) AS replies, sum(ps.topics) AS topics, sum(ps.likes_received) AS likes_received, sum(lg.likes) AS likes_given, COALESCE(sum(posts_read),0) AS posts_read, COALESCE(sum(time_read),0) AS time_read, SUM(visits) AS visits
FROM posts_summary ps
LEFT JOIN likes_given lg ON (ps.date = lg.date AND ps.id = lg.id)
LEFT JOIN visits v ON (v.id = ps.id AND v.date = ps.date)
GROUP BY ps.id, ps.username, ps.created_at, CASE 
WHEN :coverage::text = 'week' THEN ps.week::text
WHEN :coverage::text = 'all' THEN '-1'
ELSE ps.date::text
END  
ORDER BY ps.id, CASE
WHEN :coverage::text = 'week' THEN ps.week::text
WHEN :coverage::text = 'all' THEN '-1'
ELSE ps.date::text
END

```

您可以使用此查询在指定的时间范围内按用户提取每日、每周或总计的统计数据。  
**输入参数：**

- start\_date（字符串）例如 ‘2019-12-31’
- end\_date（字符串）例如 ‘2019-12-31’
- coverage（枚举）选项包括：1) ‘date’ 表示每日统计，2) ‘week’ 表示每周统计，或 3) ‘all’ 表示总计统计

**输出字段：**

- id（用户 ID）
- username（用户名）
- created\_at（用户创建时间）
- period（日期字符串，用于 ‘date’ 覆盖范围；周数，例如 12 月 26 日 -31 日 = 52，用于 ‘week’ 覆盖范围；或 -1，用于 ‘all’ 覆盖范围）
- replies（非主题首帖的帖子数量）
- topics（主题数量）
- likes\_received（收到的点赞数）
- likes\_given（给出的点赞数）
- posts\_read（已读帖子数）
- time\_read（阅读时长）
- visits（访问次数）

您可能需要使用 API 来访问此查询的完整结果（尤其是长时间跨度的每日数据）。有关如何通过 API 触发查询的更多详情，请参阅 [此帖子](https://meta.discourse.org/t/how-to-run-data-explorer-queries-with-the-discourse-api/120063)。

您可以使用此查询生成每日用户参与度的数据集。