مرحبًا،
لدي قاعدة بيانات discourse sql. أحتاج إلى إحصائيات المستخدمين لطابع زمني محدد.
على سبيل المثال - أريد معرفة عدد المنشورات التي نشرها المستخدم.
إذا استخرجت هذه البيانات من user_stats، فستعطيني رقمًا منذ انضمامه إلى المنتدى حتى الآن، لكنني أحتاج إلى معرفة ذلك لفترة زمنية محددة.
هل لديك أي فكرة؟
You will need to generate the statistics yourself using a new database query. For example, to get a post count for the last 30 days
SELECT count(*) from posts
WHERE user_id=1
AND created_at > CURRENT_TIMESTAMP - INTERVAL '30 days'
This topic has a lot of example queries you can use as a starting point
Thanks @david 
But what if i want to give a proper time instead of days like if i want to find between 2020-2-2 00:00:00 and 2020-3-1 00:00:00 ?
If i do SELECT count(*) from posts WHERE user_id=16 AND created_at > CURRENT_TIMESTAMP - INTERVAL '30 days'
I got a number but when i look into my stats its less than that number.
If you see this. I got 430 from the database but its actually 308 ?
The count shown in the UI is only for public, undeleted, posts. The database count is every post, including deleted posts, PMs, and private categories.
But what if i want to get that UI stat with in a time period?
You will need to apply similar filters. Something like this:
SELECT count(*) FROM posts p
JOIN topics t ON t.id = p.topic_id
WHERE p.user_id=23968
AND p.deleted_at IS NULL
AND NOT COALESCE(p.hidden, 't')
AND p.post_type = 1
AND t.deleted_at IS NULL
AND COALESCE(t.visible, 't')
AND t.archetype <> 'private_message'
AND p.user_id > 0
AND p.post_number > 1