أرغب في الحصول على قائمة بالمستخدمين الذين عادوا إلى المجتمع بعد فترة 6 أشهر (last_seen_at).
أعلم أنه يمكننا جلب تاريخ last_seen_at من جدول users. يمكنني مقارنة هذا الطابع الزمني والتحقق مما إذا كان أكبر من 6 أشهر والحصول على المستخدمين الذين لم يزوروا منذ الأشهر الستة الماضية، ولكن بمجرد أن يزور شخص ما المجتمع، يتم استبدال هذا الطابع الزمني بالتاريخ والوقت الحاليين مما يجعل تتبع المستخدم صعبًا.
هل من الممكن التحقق مما إذا عاد شخص ما إلى المنتدى بعد فترة زمنية منذ زيارته الأخيرة؟
التفاصيل التي تحتاجها موجودة في جدول user_visits. الجزء الصعب بالنسبة لي هو طرح قيمة الزيارة السابقة من كل إدخال زيارة. يبدو أن هذا يتطلب دالة نافذة. بناءً على الرد على هذا السؤال على StackExchange، يبدو أن دالة LEAD ستعمل لهذا الغرض.
جرب هذا الاستعلام لمعرفة ما إذا كان يعيد البيانات التي تبحث عنها. افتراضيًا، يعيد جميع زيارات المستخدم التي حدثت بعد فترة 180 يومًا بين الزيارات. يمكن تعديل هذه القيمة عن طريق تعيين المعلمة days_away للاستعلام.
--[params]
-- integer :days_away = 180
WITH days_between_visits AS (
SELECT
user_id,
visited_at,
LEAD(visited_at) OVER (PARTITION BY user_id ORDER BY visited_at DESC) AS previous_visit,
visited_at - LEAD(visited_at) OVER (PARTITION BY user_id ORDER BY visited_at DESC) AS time_away
FROM user_visits
)
SELECT * FROM days_between_visits WHERE time_away >= :days_away
ORDER BY visited_at DESC
أخبرني إذا لاحظت أي مشاكل في البيانات التي تم إرجاعها.
آسف لإزعاجك مرة أخرى، هل من الممكن الحصول على username بدلاً من user_id من استعلام SQL أعلاه؟ في صفحة مستكشف البيانات، تظهر نتيجة الاستعلام اسم المستخدم ولكن عندما أقوم بتصدير النتائج كملف CSV، يتم استبدال أسماء المستخدمين بمعرفات المستخدم الخاصة بهم. أفضل أن تكون الأسماء في ملف CSV أيضاً.
--[params]
-- integer :days_away = 180
WITH days_between_visits AS (
SELECT
user_id,
visited_at,
LEAD(visited_at) OVER (PARTITION BY user_id ORDER BY visited_at DESC) AS previous_visit,
visited_at - LEAD(visited_at) OVER (PARTITION BY user_id ORDER BY visited_at DESC) AS time_away
FROM user_visits
)
SELECT
username,
dbv.user_id AS id,
visited_at,
time_away
FROM days_between_visits dbv
JOIN users u ON u.id = dbv.user_id
WHERE time_away >= :days_away
ORDER BY visited_at DESC
أخبرني إذا واجهت أي أخطاء في انتهاء المهلة عند تشغيله. لاحظ أنني ما زلت قد أدرجت معرف المستخدم في النتائج التي تم إرجاعها. هذا العمود مُسمى الآن id. إذا كنت لا تريد إرجاع المعرف، فقم بإزالة السطر dbv.user_id AS id, من عبارة SELECT النهائية.
