複雑なユーザーアクティビティレポート要件

コミュニティ データとレポート
21

ありがとうございます！

私が使おうとしているカスタムユーザープロフィールフィールドは「NZRAB number」です。

image
image1202×937 64 KB

以下に試したスクリプトを記載しますが、明らかに間違っていたようで「Undefined function」エラーが発生しました。

フィールド名は大文字やスペースを含まないべきなのかわかりませんでした。少なくともエディタはフィールド名内のスペースを嫌ったようで、アンダースコアに置き換えました。

もしかするとフィールド名とフィールドラベルを混同しているのかもしれませんが、もしそうだとしたら、ラベル「NZRAB number」に対応するフィールド名をどうやって特定すればよいのかわかりません。

-- coverage: 'week', 'all', or 'date'
-- [params]
-- 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, cf.value as NZRAB_number
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)
LEFT JOIN user_custom_fields cf ON (u.id = cf.user_id and cf.name = 'NZRAB number') 
GROUP BY u.id, dr.date, dr.week
ORDER BY u.id, dr.date
)
SELECT ps.id, ps.username, ps.created_at, ps.NZRAB_number, 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

発生したエラー：

PG::UndefinedFunction: ERROR: operator does not exist: integer == integer
LINE 32: LEFT JOIN user_custom_fields cf ON (u.id == cf.user_id and c…
^
HINT: No operator matches the given name and argument types. You might need to add explicit type casts.

これを見て「何をやってるのかさっぱりわからない」と判断されたなら、その通りです！

22

コードから実行可能な例を取得できました。

-- coverage: 'week', 'all', または 'date'
-- [params]
-- 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, cf.value as nzrab_number
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)
LEFT JOIN user_custom_fields cf ON (u.id = cf.user_id and cf.name = CONCAT('user_field_', (select id::text from user_fields where name='NZRAB number'))) 
GROUP BY u.id, dr.date, dr.week, cf.value
ORDER BY u.id, dr.date
)

SELECT ps.id, ps.username, ps.created_at, ps.NZRAB_number, 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
FROM posts_summary ps
LEFT JOIN likes_given lg ON (ps.date = lg.date AND ps.id = lg.id)
GROUP BY ps.id, ps.username, ps.created_at, ps.nzrab_number, 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

注意点として、user_custom_fields に関する結合条件は少し特殊です。もし動作しない場合は、正しい名前を特定するために別のクエリを実行する必要があるかもしれません。

別のクエリで以下を実行してください:

select * from user_custom_fields

結果セットから「NZRAB number」フィールドに一致するレコードを見つけ、name を確認してください。私の場合は「user_field_2」でした。

その値を取得したら、上記クエリの以下の行を置き換えてください:

LEFT JOIN user_custom_fields cf ON (u.id = cf.user_id and cf.name = CONCAT('user_field_', (select id::text from user_fields where name='NZRAB number')))

次のように変更します:

LEFT JOIN user_custom_fields cf ON (u.id = cf.user_id and cf.name = <YOUR FIELD NAME>)

おそらく、この作業は不要で済むでしょう。

23

素晴らしい、そのまま動作しました！

さらにお願いしたいのですが、技術的に「いいね」がついた投稿とついていない投稿をそれぞれ別に集計することは可能でしょうか？

私がこの質問をした理由は、8 いいねを得た人が、そのすべてを1つの投稿で得たのか、それとも複数の投稿に分散して得たのかを、それ以外では判別できないためです。

もう1つの質問は、ユーザーが入力した2つの数値の間の「NZRAB_number」というカスタムフィールドを持つユーザーにのみ出力を制限する方法はあるでしょうか？（このフィールドにテキストを入力したユーザーや、何も入力しなかったユーザーを除外するためです）

また、ユーザーIDの横に実名を表示することは可能でしょうか？