指定された期間内のユーザー別の日次、週次、または合計統計

コミュニティ データとレポート
1 
-- 対象期間: '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（user_id）
  • username
  • created_at（ユーザー作成日時）
  • period（‘date’ 指定の場合は日付文字列、‘week’ 指定の場合は週番号（例：12月26日〜31日 = 52）、‘all’ 指定の場合は -1）
  • replies（トピック内の最初の投稿ではない投稿数）
  • topics
  • likes_received
  • likes_given
  • posts_read
  • time_read
  • visits

このクエリの結果（特に長期にわたる日次データ）をすべて取得するには、API を使用する必要があるでしょう。API を通じてクエリを実行する方法の詳細については、この投稿 を参照してください。

このクエリを使用して、ユーザーのエンゲージメントに関する日次データセットを生成できます。

「いいね！」 7