bevor ich weitere Abfragen einrichte, möchte ich kurz bestätigen, ob dies das ist, was Sie benötigen:
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
Kein Problem, ich glaube, das sind die Informationen, die du suchst:
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
Ich werde weitere Abfragen nach diesem Muster aufsetzen.
Entschuldigung für die verzögerte Antwort. Hier sind die angeforderten Abfragen. Da einige komplexer als üblich sind, stehe ich gerne für Rückfragen zur Verfügung.
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 IS NULL
AND post_number != 1)
SELECT
p.year,
t.category_id AS id,
c.name AS 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 IS NULL
-- AND t.category_id IS NOT NULL --> *** Aktivieren, um Beiträge ohne Kategorie zu löschen ***
GROUP BY t.category_id, c.name, p.year
ORDER BY p.year DESC, qt DESC
year
id
category
qt
2020
13
Allgemein
14
2020
16
Wissensdatenbank
3
2020
15
Mitarbeiter
3
2020
1
Nicht kategorisiert
2
2020
17
Ideen
1
2019
18
Builds
10
2019
1
Nicht kategorisiert
8
2019
11
CS001x: Einführung in die Informatik
7
2019
13
Allgemein
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 -- *** Rangbegrenzung wählen ***
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 IS NULL)
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 IS NULL
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
