How to get the number of Confluence spaces by month with one query SQL ?
Ok let's see how to do it step by step.
The goal is to build an SQL query to count the number of spaces in an Atlassian server or Atlassian Datacenter instance (*). To do this, we will make a count cummulative total in SQL. We want to obtain this dataset, for example to draw an evolution graph.
|---------|--------------|-------------------|
| period | nb_of_spaces | evol_nb_of_spaces |
|---------|--------------|-------------------|
| 2019 | 10 | 10 |
| 2020 | 20 | 30 |
| 2021 | 20 | 50 |
|---------|--------------|-------------------|
(*) Tested only on Confluence server 7.13 with PostgreSQL. The query is written for PostgreSQL database.
SELECT
EXTRACT(YEAR FROM s.creationdate) as period
FROM spaces s
|--------|
| period |
|--------|
| 2020 |
| 2021 |
|--------|
SELECT
CONCAT(
EXTRACT(YEAR FROM s.creationdate), '_',
LPAD(EXTRACT(MONTH FROM s.creationdate)::TEXT, 2, '0')
) as period
FROM spaces s
GROUP BY period
ORDER BY period
|---------|
| period |
|---------|
| 2020_02 |
| 2020_03 |
|---------|
SELECT
CONCAT(
EXTRACT(YEAR FROM s.creationdate), '_',
LPAD(EXTRACT(MONTH FROM s.creationdate)::TEXT, 2, '0'), '_',
LPAD(EXTRACT(WEEK FROM s.creationdate)::TEXT, 2, '0')
) as period
FROM spaces s
GROUP BY period
ORDER BY period
|------------|
| period |
|------------|
| 2020_09_36 |
| 2020_09_37 |
|------------|
SELECT
CONCAT(
EXTRACT(YEAR FROM s.creationdate), '_',
LPAD(EXTRACT(MONTH FROM s.creationdate)::TEXT, 2, '0'), '_',
LPAD(EXTRACT(WEEK FROM s.creationdate)::TEXT, 2, '0'), '_',
LPAD(EXTRACT(DOY FROM s.creationdate)::TEXT, 3, '0')
) as period
FROM spaces s
GROUP BY period
ORDER BY period
|----------------|
| period |
|----------------|
| 2020_09_36_240 |
| 2020_09_37_241 |
|----------------|
SELECT
CONCAT(
EXTRACT(YEAR FROM s.creationdate), '_',
LPAD(EXTRACT(MONTH FROM s.creationdate)::TEXT, 2, '0')
) as period,
COUNT(s.spaceid) as nb_of_spaces
FROM spaces s
GROUP BY period
ORDER BY period
|---------|--------------|
| period | nb_of_spaces |
|---------|--------------|
| 2020_09 | 10 |
| 2020_10 | 20 |
| 2020_11 | 20 |
|---------|--------------|
Two solutions to do this :
Solution with partition (clause SQL OVER)
SELECT
CONCAT(
EXTRACT(YEAR FROM s.creationdate), '_',
LPAD(EXTRACT(MONTH FROM s.creationdate)::TEXT, 2, '0')
) as period,
SUM(COUNT(s.spaceid)) OVER (
ORDER BY CONCAT(EXTRACT(YEAR FROM s.creationdate), '_', LPAD(EXTRACT(MONTH FROM s.creationdate)::TEXT, 2, '0'))
) as nb_of_spaces
FROM spaces s
GROUP BY period
ORDER BY period
Solution with SQL subqueries
SELECT
t1.period,
MAX(t1.nb_of_spaces) as nb_of_spaces,
COALESCE(SUM(t2.nb_of_spaces), 0) as evol_nb_of_spaces
FROM (
SELECT DISTINCT
r1.period,
MAX(r1.nb_of_spaces) as nb_of_spaces
FROM (
SELECT
CONCAT(
EXTRACT(YEAR FROM s.creationdate), '_',
LPAD(EXTRACT(MONTH FROM s.creationdate)::TEXT, 2, '0')
) as period,
COUNT(s.spaceid) as nb_of_spaces
FROM spaces s
GROUP BY period
) r1
GROUP BY r1.period
) t1
LEFT JOIN(
SELECT DISTINCT
r1.period,
MAX(r1.nb_of_spaces) as nb_of_spaces
FROM (
SELECT
CONCAT(
EXTRACT(YEAR FROM s.creationdate), '_',
LPAD(EXTRACT(MONTH FROM s.creationdate)::TEXT, 2, '0')
) as period,
COUNT(s.spaceid) as nb_of_spaces
FROM spaces s
GROUP BY period
) r1
GROUP BY r1.period
) t2 ON t2.period <= t1.period
GROUP BY t1.period
ORDER BY t1.period
|---------|--------------|-------------------|
| period | nb_of_spaces | evol_nb_of_spaces |
|---------|--------------|-------------------|
| 2020_09 | 10 | 10 |
| 2020_10 | 20 | 30 |
| 2020_11 | 20 | 50 |
|---------|--------------|-------------------|
Here we go !
In this request you can get the evolution of the number of spaces per period, depending on the chosen data granularity : year, month, week or doy. But it may be interesting to retrieve more indicators, like :
To do this, we only need to make a full outer join of each dataset. Look out, TDLR ;) incoming !
See final request with doy data granularity : https://gist.github.com/v20100v/f9ad3f4e6f63990f20fecc45fbb611d5