Можно ли и как составить запрос в БД, чтобы получить статистические данные?

Question

[EvgMul]

У меня следующая задача. Есть две таблицы shops и users в обеих таблицах есть поле create_date типа timestamp. Мне нужно получить информацию о количестве добавленных строк в эти две таблицы по дням. Т.е. в результате я хочу получить таблицу вида:

+---------------+------------+--------+
|     Date      |    Shops   | Users  |
+---------------+------------+--------+
| 01.01.2020    | 123        |    215 |
| 02.01.2020    | 314        |     22 |
| 03.01.2020    | 18         |     45 |
+---------------+------------+--------+

Можно ли это сделать через sql и если можно, подскажите, как?
Заранее благодарен всем отозвавшимся.

Comments

[Akina]

Можно ли это сделать через sql

Да, можно.

как?

1) Укажите точную версию MySQL
2) При отсутствии данных за какую-то дату - должна ли она присутствовать в итоговой таблице с нулевыми количествами?

[EvgMul]

Akina,
1. 8.0
2. Да, должна быть с нулевым значением

Answer 1

[Akina]

WITH RECURSIVE
cte1 AS ( SELECT MIN(DATE(create_date)) mindate, MAX(DATE(create_date)) maxdate
          FROM shops
          UNION ALL
          SELECT MIN(DATE(create_date)) mindate, MAX(DATE(create_date)) maxdate
          FROM users ),
cte2 AS ( SELECT MIN(mindate) mindate, MAX(maxdate) maxdate
          FROM cte1 ),
dates AS ( SELECT mindate thedate, maxdate
           FROM cte2
           UNION ALL
           SELECT thedate + INTERVAL 1 DAY, maxdate
           FROM dates
           WHERE thedate < maxdate ),
shopstat AS ( SELECT DATE(create_date) thedate, COUNT(*) cnt
              FROM shops
              GROUP BY thedate ),
userstat AS ( SELECT DATE(create_date) thedate, COUNT(*) cnt
              FROM users
              GROUP BY thedate )
SELECT thedate `date`, 
       COALESCE(shopstat.cnt, 0) shops, 
       COALESCE(userstat.cnt, 0) users
FROM dates
LEFT JOIN shopstat USING (thedate)
LEFT JOIN userstat USING (thedate)

Если нужны данные за определённый период - убрать cte1 и cte2, использовать границы диапазона в dates (поле maxdate - не нужно, убрать), добавить соотв. WHERE в остальные CTE.