تدفق العمل لدينا هو التعيين للمجموعات ثم (عادةً) تقوم المجموعات بالتعيين للأفراد، وأنا أعمل على إعداد التقارير حول ذلك.
أجد نفسي أمام أشياء كهذه:
من الناحية المثالية، سأتمكن من استخدام COALESCE لتحويل قيم NULL للمستخدمين إلى ''. باستثناء أن هذا نص، وما تم تحديده كان عددًا صحيحًا (user_id)، لذلك أحصل على خطأ. إذا قمت بتحويله إلى 0 باستخدام COALESCE، فإنه يبدو سيئًا بنفس القدر، ولكن بطريقة مختلفة.
هل أتقن أحد هذا؟
هذا استعلام صعب للغاية، أعتقد أنه قد تكون هناك ميزة HTML خفية داخل مستكشف البيانات، لذا ستستخدم شيئًا مثل:
case when group_id IS NOT NULL
then 'http://abc/groups/` || group_id::text
else 'http://abc/user/` || user_id::text
end
ثم قم بإنشاء رابط من ذلك. إذا كنت تريد فقط عرض اسم المجموعة / المستخدم في عمود واحد دون أن يكون رابطًا تشعبيًا، فهذا أسهل قليلاً.
ما هو استعلام SQL الذي لديك الآن؟
أريد عرض كل من المستخدم (عند توفره) والمجموعة. وأنا حقًا أحب وأقدر HMTL النينجا الذي يمنحني اسمًا منسقًا بشكل جيد ومرتبطًا من معرف.
لذلك، فإن بناء ذلك يدويًا لا يستحق العناء بالنسبة لي، خاصة وأنني سأضطر إلى الانضمام إلى جداول إضافية لجعل الأمر يعمل.
ولكن إليك التقرير المعني:
-- الهدف: عرض المهام القديمة،
-- حيث تكون القديمة بعد 7 أيام من التعيين
-- انظر أيضًا: المهام القديمة لمجموعة
WITH
-- البحث عن المستخدمين الذين يعملون بالشركة
SonarSourcers AS (
SELECT u.id AS user_id
FROM groups g
INNER JOIN group_users gu ON g.id=gu.group_id
INNER JOIN users u ON u.id = gu.user_id
WHERE g.name='sonarsourcers'
),
-- البحث عن المجموعات ذات الأهمية
teams AS (
SELECT id as group_id
FROM groups g
WHERE g.id not in (10, 11, 12, 13, 14 -- مجموعات مستوى الثقة
, 1, 2, 3 -- مجموعات مدمجة
, 41 -- SonarSourcers
, 47 -- SonarCloud - نريد الفرق بدلاً من ذلك
, 53 -- .NET Scanner Guild
, 0 -- "الجميع"...؟
, 65, 66, 67 -- مجموعات فرعية CFam
)
),
-- البحث عن الفريق الأساسي لكل SonarSourcer
user_team AS (
SELECT distinct on (user_id) -- لدى بعض المستخدمين مجموعتين. تضييق (اعتباطيًا) إلى 1
ss.user_id, t.group_id
FROM SonarSourcers ss
JOIN group_users gu on gu.user_id=ss.user_id
JOIN teams t on t.group_id = gu.group_id
),
-- البحث عن المواضيع المعينة
-- التي ليست مغلقة ولا محلولة
-- والتي تم تعيينها منذ أكثر من 7 أيام
assigned_topics AS (
SELECT a.topic_id
, a.updated_at
, CASE WHEN assigned_to_type = 'User' THEN assigned_to_id END AS user_id
, CASE WHEN assigned_to_type = 'Group' THEN assigned_to_id ELSE user_team.group_id END AS group_id
FROM assignments a
JOIN topics t ON a.topic_id = t.id
LEFT JOIN user_team ON user_team.user_id=assigned_to_id
-- استبعاد المواضيع المغلقة - الجزء الأول
JOIN posts p on p.topic_id = t.id
LEFT JOIN post_custom_fields pcf ON pcf.post_id=p.id AND pcf.name='is_accepted_answer'
WHERE active = true
AND a.updated_at < current_date - INTEGER '7'
AND t.closed = false -- استبعاد المواضيع المغلقة - الجزء الثاني
AND pcf.id IS NULL -- استبعاد المواضيع المحلولة
GROUP BY a.topic_id, a.updated_at, assigned_to_type, assigned_to_id, user_team.group_id, t.updated_at
ORDER BY t.updated_at asc
),
-- البحث عن آخر منشور عام في كل موضوع معين
-- سنستخدم هذا لاستبعاد المواضيع التي كان فيها عضو المجموعة هو آخر ناشر
last_post AS (
SELECT p.topic_id as topic_id
, max(p.id) as post_id
FROM posts p
JOIN assigned_topics at ON at.topic_id = p.topic_id
WHERE post_type = 1 -- عادي
GROUP BY p.topic_id
),
-- البحث عن آخر منشور عام في كل موضوع من المجموعة المعينة
-- سنستخدم هذا لاستبعاد تلك التي يتم الرد عليها بالفعل
last_group_post AS (
SELECT p.topic_id as topic_id
, max(p.id) as post_id
, max(created_at) as last_public_post
FROM posts p
JOIN assigned_topics at ON at.topic_id = p.topic_id
JOIN user_team on user_team.user_id=p.user_id
WHERE post_type = 1 -- عادي
GROUP BY p.topic_id
),
stale AS (
SELECT COUNT(lp.topic_id) AS stale
, at.user_id
, at.group_id
FROM last_post lp
JOIN assigned_topics at ON at.topic_id = lp.topic_id
JOIN posts p on lp.post_id=p.id
LEFT JOIN last_group_post lgp ON lgp.topic_id = at.topic_id
LEFT JOIN user_team on p.user_id=user_team.user_id
WHERE (user_team.group_id is null -- غير SonarSourcers
OR user_team.group_id != at.group_id)
AND lgp.last_public_post <= current_date - 7 -- آخر منشور قبل 7 أيام أو أكثر من اليوم
AND lgp.last_public_post > current_date - 14 -- آخر منشور قبل أقل من 14 يومًا من اليوم
GROUP BY at.user_id, at.group_id
ORDER BY at.group_id
),
super_stale AS (
SELECT COUNT(lp.topic_id) as super_stale
, at.user_id
, at.group_id
FROM last_post lp
JOIN assigned_topics at ON at.topic_id = lp.topic_id
JOIN posts p on lp.post_id=p.id
LEFT JOIN last_group_post lgp ON lgp.topic_id = at.topic_id
LEFT JOIN user_team on p.user_id=user_team.user_id
WHERE (user_team.group_id is null -- غير SonarSourcers
OR user_team.group_id != at.group_id)
AND (lgp.last_public_post <= current_date - 14
OR lgp.last_public_post IS NULL)
GROUP BY at.user_id, at.group_id
ORDER BY at.group_id
),
aggregated AS (
SELECT COALESCE(s.user_id,ss.user_id) AS user_id
, COALESCE(s.group_id,ss.group_id) AS group_id
, COALESCE(stale,0) AS stale
, COALESCE(super_stale,0) AS super_stale
FROM stale s
FULL JOIN super_stale ss using (user_id)
)
-- في هذه المرحلة، لا يزال هناك صفان لبعض الفرق (صفوف بمعرف مستخدم فارغ).
-- استخدم التجميع والتجميع لدمج الصفوف وإزالة القيم الفارغة
-- أيضًا، أضف الفرق "المفقودة" مرة أخرى
SELECT user_id
, group_id
, COALESCE(MAX(stale),0) AS "قديم (7 أيام)"
, COALESCE(MAX(super_stale),0) AS "قديم جدًا (14 يومًا)"
FROM aggregated
RIGHT JOIN teams using (group_id)
GROUP BY group_id, user_id
ORDER BY group_id, "قديم جدًا (14 يومًا)" desc, "قديم (7 أيام)" desc