متوسطات مستكشف البيانات حسب السنة؟

مجتمع البيانات والتقارير
1

أحتاج إلى إنشاء استعلام SQL في Data-Explorer:

أحتاج إلى إجراء الطلب حسب السنة. كمثال، من 2019-01-01 إلى 2019-12-31

عدد الردود حسب الفئة (الموضوع والمشاركة)
أكثر الوسائط شيوعًا في السنة
متوسط عدد المواضيع الجديدة شهريًا (وجدت ذلك في لوحة تحكم التقارير الإدارية)
متوسط عدد الردود شهريًا
متوسط عدد المستخدمين الجدد شهريًا

هل يمكن لأحد المساعدة في هذا؟ أم أن يوجهني إلى بعض التوجيهات؟

مع أطيب التحيات.

2

يمكن أن يساعدك @michebs في هذا الأمر.

5

مرحباً،

دعني أتأكد مما إذا كان هذا هو ما تحتاجه قبل إعداد استعلامات أخرى:

WITH data AS (SELECT 
    id,
    EXTRACT(MONTH FROM created_at) AS month,
    EXTRACT(YEAR FROM created_at) AS year
FROM topics)

SELECT
    ROUND(AVG(qt_topic_month)) AS avg_topic,
    year
FROM
(SELECT COUNT(id) AS qt_topic_month,
        month,
        year
FROM data    
GROUP BY month, year) AS top_m
GROUP BY year
ORDER BY year DESC
avg_topic year
694 2020
1011 2019
284 2018
79 2017
6

@michebs.. نعم بالضبط، لقد فهمت بسرعة كبيرة

إذا لم يكن ذلك كثيراً، فسيكون الأمر على هذا النحو

السنة الشهر الموضوع_المتوسط

2020 01 الرقم
2020 02 الرقم
2020 03 الرقم
إلخ…

وشكراً مقدماً على ذلك

تحياتي.

7

لا مشكلة، أعتقد أن هذه هي المعلومات التي تبحث عنها:

WITH data AS (SELECT 
    id,
    EXTRACT(DAY FROM created_at) AS day,
    EXTRACT(MONTH FROM created_at) AS month,
    EXTRACT(YEAR FROM created_at) AS year
FROM topics)

SELECT
    year,
    month,
    ROUND(AVG(qt_topic_day)) AS avg_topic
FROM
(SELECT COUNT(id) AS qt_topic_day,
        day,
        month,
        year
FROM data    
GROUP BY day, month, year) AS top_day
GROUP BY month, year
ORDER BY year DESC, month ASC
year month avg_topic
2020 1 23
2020 2 26
2020 3 24
2020 4 35
2020 5 31

سأقوم بإعداد استعلامات أخرى بناءً على هذا النموذج. :wink:

8

مرحبًا،

عذرًا على التأخير في الرد، إليك الاستعلامات المطلوبة، وبما أن بعضها أكثر تعقيدًا من المعتاد، فأنا متاحة إذا كان لديك أي أسئلة.

ميشيل

WITH post AS (SELECT 
    id AS post_id,
    topic_id,
    EXTRACT(YEAR FROM created_at) AS year
FROM posts
WHERE post_type = 1
    AND deleted_at ISNULL
    AND post_number != 1)
    
SELECT 
    p.year,
    t.category_id AS id, 
    c.name category,
    COUNT(p.post_id) AS qt
FROM post p
INNER JOIN topics t ON t.id = p.topic_id
LEFT JOIN categories c ON c.id = t.category_id
WHERE t.deleted_at ISNULL
--    AND t.category_id   NOT NULL  --> *** تفعيل لحذف مشاركة بدون فئة ***
GROUP BY t.category_id, c.name, p.year
ORDER BY p.year DESC, qt DESC
year id category qt
2020 13 عام 14
2020 16 قاعدة المعرفة 3
2020 15 الموظفون 3
2020 1 غير مصنفة 2
2020 17 أفكار 1
2019 18 بناء 10
2019 1 غير مصنفة 8
2019 11 CS001x: مقدمة في علوم الحاسوب 7
2019 13 عام 5 
WITH data AS (SELECT 
    tag_id,
    EXTRACT(YEAR FROM created_at) AS year
FROM topic_tags)

SELECT year, rank, name, qt FROM (
    SELECT 
        tag_id,
        COUNT(tag_id) AS qt,
        year,
        rank() OVER (PARTITION BY year ORDER BY COUNT(tag_id) DESC) AS rank    
    FROM
        data
    GROUP BY year, tag_id) as rnk
INNER JOIN tags ON tags.id = rnk.tag_id
WHERE rank <= 5  --   *** اختر حد الترتيب ***
ORDER BY year DESC, qt DESC
year rank tag_name quantity
2020 1 مُبرز 7
2020 2 الموارد البشرية 3
2020 3 تجربة 1
2019 1 تجربة 12
2019 2 الموارد البشرية 4
2019 3 مُبرز 3
2019 3 عميل 3
2019 3 أحداث_2019 3 
WITH data AS (SELECT 
    id,
    EXTRACT(DAY FROM created_at) AS day,
    EXTRACT(MONTH FROM created_at) AS month,
    EXTRACT(YEAR FROM created_at) AS year
FROM topics
WHERE deleted_at ISNULL)

    SELECT
        year,
        month,
        ROUND(AVG(qt_topic_day)) AS avg_topic_by_day
    FROM
    (SELECT COUNT(id) AS qt_topic_day,
            day,
            month,
            year
    FROM data    
    GROUP BY day, month, year) AS top_day
    GROUP BY month, year
    ORDER BY year DESC, month ASC
year month avg_topic_by_day
2020 1 1
2020 2 1
2020 3 2
2020 4 3
2020 5 2
2019 4 9
2019 5 4
2019 6 4
2019 7 1
2019 8 2
2019 9 3
2019 10 1 
WITH data AS (SELECT 
    id,
    EXTRACT(DAY FROM created_at) AS day,
    EXTRACT(MONTH FROM created_at) AS month,
    EXTRACT(YEAR FROM created_at) AS year
FROM posts
WHERE post_type = 1
    AND deleted_at ISNULL
    AND post_number != 1)

SELECT
    year,
    month,
    ROUND(AVG(qt_reply_day)) AS avg_reply_by_day
FROM
(SELECT COUNT(id) AS qt_reply_day,
        day,
        month,
        year
FROM data    
GROUP BY day, month, year) AS top_reply
GROUP BY month, year
ORDER BY year DESC, month ASC
year month avg_reply_by_day
2020 1 7
2020 3 2
2020 4 5
2020 5 6
2019 4 3
2019 5 2
2019 6 4
2019 7 2
2019 8 15
2019 9 3
2019 10 5
2019 12 2 
WITH data AS (SELECT 
    id,
    EXTRACT(DAY FROM created_at) AS day,
    EXTRACT(MONTH FROM created_at) AS month,
    EXTRACT(YEAR FROM created_at) AS year
FROM users)

SELECT
    year,
    month,
    ROUND(AVG(qt_new_user)) AS avg_new_user_by_day
FROM
(SELECT COUNT(id) AS qt_new_user,
        day,
        month,
        year
FROM data    
GROUP BY day, month, year) AS top_new_user
GROUP BY month, year
ORDER BY year DESC, month ASC
year month avg_new_user_by_day
2020 1 1
2020 2 1
2020 3 1
2020 4 3
2020 5 1
2019 4 4
2019 5 2
2019 6 2
2019 7 1
9

يعمل بامتياز…

لقد أنقذت أيامي…

تحياتي…