Explorateur de données : moyennes par année ?

Communauté Données et rapports
1

Je dois créer cette requête SQL pour Data-Explorer :

Je dois effectuer la requête par année. En entrée… par exemple du 2019-01-01 au 2019-12-31

Nombre de réponses par catégorie (TOPIC et POST)
Meilleurs tags de l'année
Nombre moyen de nouveaux sujets par mois (je l'ai trouvé dans le rapport admin du panneau d'administration)
Nombre moyen de réponses par mois
Nombre moyen de nouveaux utilisateurs par mois

Quelqu’un peut-il m’aider avec cela ??? ou me donner quelques indications

Cordialement.

2

@michebs peut probablement vous aider ici.

5

Bonjour,

Je souhaite confirmer que c’est bien ce dont vous avez besoin avant de configurer d’autres requêtes :

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.. Oui, exactement, vous l’avez compris très rapidement

si ce n’est pas trop vous demander, ce serait quelque chose comme ça

année mois sujet_moyen

2020 01 nombre
2020 02 nombre
2020 03 nombre
etc…

et Merci d’avance pour cela

Cordialement.,

7

Pas de problème, je pense que voici les informations que vous recherchez :

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

Je vais mettre en place d’autres requêtes basées sur ce paradigme. :wink:

8

Bonjour,

Désolé pour le délai de réponse. Voici les requêtes demandées ; certaines étant plus complexes que d’habitude, je reste à votre disposition si vous avez des questions.

Michelle

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  --> *** Activer pour supprimer un post sans catégorie ***
GROUP BY t.category_id, c.name, p.year
ORDER BY p.year DESC, qt DESC
year id category qt
2020 13 Général 14
2020 16 Base de connaissances 3
2020 15 Équipe 3
2020 1 Non catégorisé 2
2020 17 Idées 1
2019 18 Constructions 10
2019 1 Non catégorisé 8
2019 11 CS001x : Introduction à l’informatique 7
2019 13 Général 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  --   *** Choisir la limite de classement ***
ORDER BY year DESC, qt DESC
year rank tag_name quantity
2020 1 featured 7
2020 2 human-resources 3
2020 3 demo 1
2019 1 demo 12
2019 2 human-resources 4
2019 3 featured 3
2019 3 customer 3
2019 3 milestones-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

Ça marche du tonnerre…

Tu me sauves la mise…

Cordialement…