لمن يهمه الأمر، إليك ما يبدو أنه يعمل:
-- [params]
-- null date :start_date
-- null date :end_date
-- null int_list :category_ids
WITH RankedPosts AS (
SELECT
p.topic_id,
p.created_at,
ROW_NUMBER() OVER (PARTITION BY p.topic_id ORDER BY p.post_number DESC) AS rank
FROM
posts p
WHERE
p.created_at BETWEEN :start_date AND :end_date
AND EXISTS (
SELECT 1
FROM topics t
WHERE t.id = p.topic_id
AND t.category_id IN (:category_ids)
)
),
FilteredPosts AS (
SELECT
topic_id,
created_at,
rank
FROM
RankedPosts
WHERE
rank <= 2
),
PostDifferences AS (
SELECT
topic_id,
EXTRACT(days FROM (MAX(created_at) FILTER (WHERE rank = 1) - MAX(created_at) FILTER (WHERE rank = 2)))::numeric(9,2) AS days_difference
FROM
FilteredPosts
GROUP BY
topic_id
)
SELECT
t.category_id,
AVG(pd.days_difference) AS avg_days_difference,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY pd.days_difference) AS median_days_difference
FROM
topics t
JOIN
PostDifferences pd ON t.id = pd.topic_id
GROUP BY
t.category_id
ORDER BY
avg_days_difference DESC
يجب أن يوفر هذا المتوسط والوسيط لأوقات إنشاء الطوابع الزمنية. بدت الطوابع الزمنية للتحديث مشكلة (حصلت على بعض القيم السالبة لسبب ما عند إجراء المتوسطات فقط).
لقد حصلت على بعض المساعدة من الذكاء الاصطناعي في إنشاء الاستعلام، لذلك من الممكن أن تكون هناك أخطاء - وأنا سعيد إذا كان لدى أي شخص اقتراحات حول كيفية تحسينه (أو تصحيح أي أخطاء). بدت النتائج التي حصلت عليها معقولة بناءً على الفحوصات التي تم إجراؤها على مجموعات أصغر، بالإضافة إلى التغييرات الناتجة عن زيادة النطاق الزمني مما أدى إلى اتجاهات تتماشى مع التوقعات التي كانت لدي من استخدام عينة بيانات أكبر.