Количество уникальных клиентов за месяц. SQL

Question

Пишу sql запрос, который должен выводить количество новых уникальных клиентов за, например, месяц. Написал уже два разных запроса, которые выполняют эту задачу, НО они пропускают пустые месяцы. Соответственно нужен запрос, у которого нет этого минуса. Пример моего вывода с пропуском месяца:

Такой вывод требуется:

Вот такие запросы делал:

SELECT  DATE_FORMAT(fisrtV, @sort_by_month) AS DATE,
                                        COUNT(ip) AS count_client
                            FROM
                                (SELECT 
                                    ip,
                                    MIN(AT) AS fisrtV
                                 FROM requests
                                    GROUP BY ip) AS t
                            GROUP BY DATE

Второй запрос:

SELECT DATE_FORMAT(at, @sort_by_month) AS DATE,
         COUNT(ip) AS count_client
FROM requests tb1 
    WHERE DATE_FORMAT(tb1.at, @sort_by_month) = (SELECT MIN(DATE_FORMAT(tb2.at, @sort_by_month)) 
                                                                FROM requests tb2 WHERE tb1.ip = tb2.ip GROUP BY ip)
    GROUP BY DATE_FORMAT(at, @sort_by_month) ORDER BY DATE

Структура таблицы:

CREATE TABLE `requests` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `user_id` INT NULL DEFAULT NULL,
  `ip` VARCHAR(32) NULL DEFAULT NULL,
  `at` DATETIME NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
)

Версия MySQL 8.0.26.

Данные для таблицы (сгенерированы ):

INSERT INTO requests(user_id, ip, at) VALUE 
 (0, '94.52.178.105', '2021-03-20 12:58:07'), 
 (4, '13.60.41.100', '2021-03-11 13:35:49'), 
 (28, '168.166.168.40', '2021-01-3 12:22:09'), 
 (30, '28.249.36.119', '2021-12-11 3:14:45'), 
 (0, '178.26.16.59', '2021-03-23 13:26:21'),
  (0, '133.205.168.233', '2021-09-12 15:03:09'), 
 (8, '129.42.187.115', '2021-04-8 18:57:51'), 
 (11, '175.119.240.52', '2021-03-26 10:31:11'), 
 (18, '97.0.127.186', '2021-04-19 5:25:54'), 
 (18, '178.26.16.59', '2021-12-23 13:41:17'),
  (8, '138.217.106.9', '2021-11-5 20:30:47'), 
  (8, '120.81.241.239', '2021-06-6 2:26:42'), 
  (15, '204.163.242.241', '2021-06-3 14:01:30'), 
  (16, '204.163.242.241', '2021-02-21 23:02:06'),  
  (8, '120.81.241.239', '2021-10-12 8:51:21'),
   (8, '202.129.109.184', '2021-06-20 16:57:20'), 
    (17, '223.196.139.248', '2021-01-19 9:30:17'),
     (0, '94.52.178.105', '2021-04-4 4:14:27'), 
     (0, '225.133.53.175', '2021-11-5 17:36:21'),
      (21, '223.196.139.248', '2021-06-10 19:44:43'), 
      (24, '57.193.133.133', '2021-06-24 20:49:29'),
       (4, '223.196.139.248', '2021-02-17 8:39:11'), 
      (3, '155.202.190.100', '2021-05-25 15:03:13'),
       (21, '94.52.178.105', '2021-09-19 10:04:39'), 
      (26, '194.234.189.126', '2021-05-22 15:14:07'),
       (0, '113.247.142.205', '2021-01-12 17:44:21'), 
      (19, '129.42.187.115', '2021-03-1 3:53:23'), 
      (15, '51.148.255.14', '2021-11-18 19:12:58'),
       (0, '178.26.16.59', '2021-03-18 4:41:48'), 
        (19, '242.132.132.7', '2021-09-19 5:12:44'),
        (0, '205.206.101.128', '2021-10-5 15:44:32'), 
        (17, '175.116.208.152', '2021-03-12 3:10:50'),
         (27, '133.205.168.233', '2021-07-28 7:03:43'), (0, '213.127.38.238', '2021-08-21 22:49:46'),
          (0, '13.60.41.100', '2021-06-9 5:06:51'), (0, '97.0.127.186', '2021-06-28 3:12:11'), 
          (5, '216.179.174.135', '2021-06-2 23:52:44'), (30, '57.193.133.133', '2021-05-20 13:18:31'),
           (12, '246.33.40.57', '2021-04-19 16:33:27'), (9, '129.42.187.115', '2021-11-13 10:36:14'), 
            (20, '148.26.108.236', '2021-04-1 21:23:17'), (6, '175.116.208.152', '2021-06-12 16:44:20'), 
            (3, '246.33.40.57', '2021-05-1 19:35:30'), (14, '234.169.182.103', '2021-03-17 9:11:31'), 
            (9, '173.139.164.88', '2021-05-30 8:52:39'), (10, '51.148.255.14', '2021-08-4 5:56:27'), 
            (24, '133.205.168.233', '2021-05-26 8:32:25'), (1, '52.73.250.35', '2021-06-11 8:12:13'), 
            (3, '13.60.41.100', '2021-08-19 5:56:09'), (0, '179.74.82.88', '2021-01-16 9:06:36'),
             (10, '168.166.168.40', '2021-09-11 14:57:49'), (20, '225.133.53.175', '2021-11-7 13:20:29'),
              (7, '205.206.101.128', '2021-05-1 7:56:15'), (0, '223.196.139.248', '2021-08-4 1:21:48'),
               (9, '223.196.139.248', '2021-05-18 18:40:38'), (9, '205.206.101.128', '2021-11-8 8:33:27'),
(12, '173.139.164.88', '2021-09-26 20:47:31')

Правильный ответ для этих данных:

Answer 1

Ладно, будем считать, что надо за весь год, и пофиг, что он ещё не кончился.

WITH RECURSIVE
cte AS ( SELECT '2021-01-01' m_start, '2021-02-01' m_end
         UNION ALL
         SELECT m_start + INTERVAL 1 MONTH, m_start + INTERVAL 2 MONTH
         FROM cte
         WHERE m_start < '2021-12-01' )
SELECT DATE_FORMAT(m_start, '%Y.%m') `date`,
       COUNT(t1.ip) count_new_users
FROM cte
LEFT JOIN requests t1 ON t1.at >= cte.m_start
                      AND t1.at < m_end
                      AND NOT EXISTS ( SELECT NULL
                                       FROM requests t2
                                       WHERE t1.ip = t2.ip
                                       AND t2.at < t1.at )
GROUP BY 1
ORDER BY 1

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=e118bec35146109169dfacc90f62f4fd

Answer 2

Через несколько часов смог решить проблему другим способом (как мне кажется более гибким) Выкладываю решение для людей, у которых тоже возникли проблемы с этим

SET @sort_by_month = '%Y.%m', @sort_by_day = '%Y.%m.%d', @sort_by_year = '%Y';


WITH sup_table AS (SELECT   DATE_FORMAT(fisrtV, @sort_by_month) AS DATE,
                                        COUNT(ip) AS count_client
                            FROM
                                (SELECT 
                                    ip,
                                    MIN(AT) AS fisrtV
                                 FROM requests
                                    GROUP BY ip) AS t
                            GROUP BY DATE),
        itog AS (SELECT T.DATE AS DATE, 
                         IF(count_client IS NULL , 0, count_client) AS `Новых уникальных клиентов`
                FROM (SELECT DATE_FORMAT(at, @sort_by_month) AS DATE FROM requests GROUP BY DATE ORDER BY DATE) T  
                        LEFT JOIN sup_table ON T.DATE = sup_table.DATE)
    


SELECT DATE, 
       `Новых уникальных клиентов`, 
FROM itog s ORDER BY DATE

P.S Большое спасибо Akina. После просмотра его решения пришла идея запроса выше.

БЛОГ НА HUSL

Количество уникальных клиентов за месяц. SQL

Ответы (2 шт):