C, PHP, VB, .NET

Рекурсията е традиционно слаб елемент в езика SQL. Едни от най-трудните задачи обикновено са свързани именно с извеждане на данни, които са наредени в някаква йерархия (например дървовидна структура) и зависят едни от други. В тази статия ще дам много прост пример за начални стъпки в рекурсивните заявки с оператор WITH.

Нека първо решим една наглед проста, но всъщност не съвсем тривиална задача. Нека имаме таблица, в която се отбелязват датите на възникнали покупки на продукти. Искаме да извадим списък с дата и брой покупки за деня от текущата седмица. За улеснение ще направя таблицата максимално проста:

CREATE TABLE test(
	id SERIAL PRIMARY KEY,
	dt DATETIME NOT NULL DEFAULT NOW()
);

INSERT INTO test(dt) 
VALUES ("2020-06-01"),("2020-05-30"),("2020-06-03"),("2020-06-03");

Ако се опитам да изкарам списъка за текущата седмица (денят днес е 2020-06-04), ще се види следния резултат:

SELECT DATE(dt), COUNT(dt)
FROM test
WHERE WEEK(dt) = WEEK(NOW())
GROUP BY dt
ORDER BY dt;
+------------+-----------+
| DATE(dt)   | COUNT(dt) |
+------------+-----------+
| 2020-06-01 |         1 |
| 2020-06-03 |         2 |
+------------+-----------+

Това очевидно не ни удовлетворява, защото за дните, в които не е имало нито една покупка, не се показва нищо. Предполагам на всички е ясно защо се е получило така – когато няма запис, няма какво да бъде преброено. Какво да направим, за да може да се изведат и останалите дни от текущата седмица, а до тях да се изведе 0?

Първо ни трябва заявка, която да изкара датите на дните от текущата седмица. Един много груб начин за съставяне на такава е:

SELECT DATE(SUBDATE(NOW(), weekday(NOW()))) AS day_of_week
UNION
SELECT DATE(SUBDATE(NOW(), weekday(NOW())) + INTERVAL 1 DAY)
UNION
SELECT DATE(SUBDATE(NOW(), weekday(NOW())) + INTERVAL 2 DAY)
UNION
SELECT DATE(SUBDATE(NOW(), weekday(NOW())) + INTERVAL 3 DAY)
UNION
SELECT DATE(SUBDATE(NOW(), weekday(NOW())) + INTERVAL 4 DAY)
UNION
SELECT DATE(SUBDATE(NOW(), weekday(NOW())) + INTERVAL 5 DAY)
UNION
SELECT DATE(SUBDATE(NOW(), weekday(NOW())) + INTERVAL 6 DAY);
+-------------+
| day_of_week |
+-------------+
| 2020-06-01  |
| 2020-06-02  |
| 2020-06-03  |
| 2020-06-04  |
| 2020-06-05  |
| 2020-06-06  |
| 2020-06-07  |
+-------------+

Сега към нея можем да направим LEFT JOIN с предишната заявка и с помощта на оператор IF да сменим NULL с 0:

SELECT all_days_in_this_week.day_of_week, 
       IF(days_of_this_week_with_sales.sales_cnt IS NULL,
	  0,
          days_of_this_week_with_sales.sales_cnt) AS sales_count
FROM
(SELECT DATE(SUBDATE(NOW(), weekday(NOW()))) AS day_of_week
 UNION 
 SELECT DATE(SUBDATE(NOW(), weekday(NOW())) + INTERVAL 1 DAY)
 UNION 
 SELECT DATE(SUBDATE(NOW(), weekday(NOW())) + INTERVAL 2 DAY)
 UNION 
 SELECT DATE(SUBDATE(NOW(), weekday(NOW())) + INTERVAL 3 DAY)
 UNION 
 SELECT DATE(SUBDATE(NOW(), weekday(NOW())) + INTERVAL 4 DAY)
 UNION 
 SELECT DATE(SUBDATE(NOW(), weekday(NOW())) + INTERVAL 5 DAY)
 UNION 
 SELECT DATE(SUBDATE(NOW(), weekday(NOW())) + INTERVAL 6 DAY)
) AS all_days_in_this_week
LEFT JOIN
(SELECT DATE(dt) AS day_of_week, COUNT(dt) AS sales_cnt
 FROM test
 WHERE WEEK(dt) = WEEK(NOW())
 GROUP BY dt
) AS days_of_this_week_with_sales
ON all_days_in_this_week.day_of_week 
     = 
   days_of_this_week_with_sales.day_of_week
ORDER BY all_days_in_this_week.day_of_week;
+-------------+-------------+
| day_of_week | sales_count |
+-------------+-------------+
| 2020-06-01  |           1 |
| 2020-06-02  |           0 |
| 2020-06-03  |           2 |
| 2020-06-04  |           0 |
| 2020-06-05  |           0 |
| 2020-06-06  |           0 |
| 2020-06-07  |           0 |
+-------------+-------------+

Разбира се ще се досетите веднага, че този метод не се скалира добре, защото ако ви трябват не дните от текущата седмица, а текущия месец, година… или изобщо произволен интервал от време, таблицата с генерираните дати ще набъбне и ще стане огромна, а да описваме датите една по една е нереалистичен подход. Можем ли да опростим и автоматизираме нещата?

Един традиционен „хак“ е да се създаде отделна таблица с единствена колона, която с цикъл да се запълни с някакъв огромен интервал от дати. Така вече по нея ще може лесно да се правят интервали с просто WHERE условие. Това решение би било валидно, но определено също не ни удовлетворява – изисква допълнително запазване на информация, която като цяло е ненужна. Друг вариант е да се използват сесийни променливи като вид брояч за дни, които да се използват по хитър начин вътре в заявката, но и това би се приело като нестандартно решение.

Много удобен в случая се явява оператор WITH, който е наличен от MariaDB 10.2.2 нататък. Чрез него е възможно една заявка да извиква сама себе си многократно чрез рекурсия. Ще го обясня директно с пример. Ето как би изглеждал списъка с дати:

WITH RECURSIVE date_range AS (
	SELECT '2020-06-01' AS d
	UNION
	SELECT d + INTERVAL 1 DAY
	FROM date_range
	WHERE d < '2020-06-07'
)
SELECT d FROM date_range;
+------------+
| d          |
+------------+
| 2020-06-01 |
| 2020-06-02 |
| 2020-06-03 |
| 2020-06-04 |
| 2020-06-05 |
| 2020-06-06 |
| 2020-06-07 |
+------------+

Виждате, че заявката date_range има рекурсивно извикване към самата себе си. Може да приемете първия SELECT (в червен цвят) в оператора като „начално условие“, а втория (след UNION, който е в син цвят) като рекурсивно добавяне на допълнителни записи до достигане на крайното условие (неговия WHERE). При всяко ново извикване на синята подзаявка се взима предишното ѝ текущо състояние и резултата от него се натрупва във временна таблица в паметта. Рекурсията приключва тогава, когато синята заявка върне празно множество (то ще се случи само когато въпросната дата е подмината). Резултатът от WITH е резултатът от началното условие с добавени всички междинно натрупани състояния от рекурсивната подзаявка.

За щастие оператор WITH би могъл да се използва и в контекст като вложен SELECT от по-сложна заявка. Така решението на дадената в началото задача би могло да се направи по следния значително по-елегантен (и вече лесно скалируем за произволни интервали от време) начин:

SELECT all_days_in_this_week.day_of_week, 
       IF(days_of_this_week_with_sales.sales_cnt IS NULL,
	  0,
          days_of_this_week_with_sales.sales_cnt) AS sales_count
FROM
(
 WITH RECURSIVE date_range AS (
	SELECT DATE(SUBDATE(NOW(), weekday(NOW()))) AS day_of_week
	UNION
	SELECT day_of_week + INTERVAL 1 DAY
	FROM date_range
	WHERE day_of_week < DATE(SUBDATE(NOW(), weekday(NOW())) + INTERVAL 6 DAY)
 )
 SELECT day_of_week FROM date_range
) AS all_days_in_this_week
LEFT JOIN
(SELECT DATE(dt) AS day_of_week, COUNT(dt) AS sales_cnt
 FROM test
 WHERE WEEK(dt) = WEEK(NOW())
 GROUP BY dt
) AS days_of_this_week_with_sales
ON all_days_in_this_week.day_of_week 
     = 
   days_of_this_week_with_sales.day_of_week
ORDER BY all_days_in_this_week.day_of_week;