Пишем раздел статистики, используя SQL

В этой статье я расскажу, как можно написать скрипт для вывода раздела статистики, где используется временной период с разбиением его на нужный интервал. Для этого мы будем использовать только возможности SQL, писать все одним запросом, исключая циклы, и я покажу, как можно в данном случае избавиться от «быдло» кода. Будем разбираться на примере: вывести кол-во проданных товаров за последние три месяца, с недельной разбивкой.

Набросаем небольшую схему хранения данных в нашей базе для дальнейших исследований данного вопроса.
У нас есть таблица с товарами product:

id | name 

(очень просто, идентификатор и название товара)

И есть таблица-сборщик данных о продажах sale_product

product_id | count | create_time

(какой продукт, сколько и когда был продан)

на самом деле мне не доводилось на практике работать с такой схемой, но она очень проста и удобна для разбора нашего примера. А если мы разберемся с принципом написания алгоритма по сбору статистических данных по этой схеме, то применить эти же знания в сложной системе будет не намного сложнее.

На самом деле, тут нет ничего сложного в реализации. Ведь можно написать всего один запрос к базе:

SELECT p.name, SUM(sp.count) 
	FROM sale_product sp
		LEFT JOIN product p on p.id = sp.product_id
	WHERE create_time BETWEEN '2015-03-01' AND '2015-06-01'
	GROUP BY p.id;

Получается, мы в нем просто запросили данные из таблицы продаж за нужный нам период времени (на примере, за последние 3 месяца). Но заказчика это не устраивает, он хочет видеть не суммарные показатели за 3 месяца, а чтобы данные выводились неделями на протяжении периода из трех месяцев. Как быть? Писать на «пыхе» (многим известен, как PHP) цикл из вышеприведенного запроса, подставляя даты каждой недели? Ммм.. нет, нужно найти более разумный подход.

Усовершенствуем наш запрос:

SELECT 
	week(sp.create_time,1) as period,
	SUBDATE(sp.create_time, WEEKDAY(db_date)) as start_date,
	ADDDATE(sp.create_time, 6 - WEEKDAY(db_date)) as end_date,
	p.name, SUM(sp.count) 
	FROM sale_product sp
		LEFT JOIN product p on p.id = sp.product_id
	WHERE sp.create_time BETWEEN '2015-03-01' AND '2015-06-01'
	GROUP BY period, p.id

В результате мы получим данные за каждую неделю, по каждому товару. Поля в запросе start_date и end_date выводят начало и конец каждой недели для понимания периода человеком, так как значение поля period — это номер недели в году.
Вот и все готово! Но заказчика снова не устраивает наш скрипт. Скажете, чего еще не хватает? Чего не хватает этому требовательному заказчику? А есть все-таки одна маленькая деталь, которая все портит. Приведу пример: если мы выводим статистику за месяц по недельно, а продажи у нас были только за первую неделю данного месяца, то и результатом от sql запроса будут только строки с датами, соответствующие первой неделе. Больше база данных нам ничего не выведет. Но заказчику нужно видеть таблицу, в которой прописана каждая неделя нужного месяца, и там где продаж не было, ячейки должны быть заполнены нулями. Остается ли нам в таком случае писать в скрипте логику по заполнению оставшихся полей нулями, расчитывать каждый период, который нам не был возвращен базой данных? Нет. Давайте лучше искать другой путь для этого решения.

И все же есть отличный способ для этого, как завести в базе данных таблицу-календарь.
В этой таблице хранится информация о каждом дне за определенный период времени (этот период мы должны заранее прописать в базе).
Я приведу здесь структуру такой таблицы:

CREATE TABLE `time_dimension` (
  `id` int(11) NOT NULL,
  `db_date` date NOT NULL,
  `year` int(11) NOT NULL,
  `month` int(11) NOT NULL,
  `day` int(11) NOT NULL,
  `quarter` int(11) NOT NULL,
  `week` int(11) NOT NULL,
  `day_name` varchar(9) NOT NULL,
  `month_name` varchar(9) NOT NULL,
  `holiday_flag` char(1) DEFAULT 'f',
  `weekend_flag` char(1) DEFAULT 'f',
  `event` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `td_ymd_idx` (`year`,`month`,`day`),
  UNIQUE KEY `td_dbdate_idx` (`db_date`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

и процедуру заполнения этой таблицы данными:

DELIMITER ;;
CREATE PROCEDURE `fill_date_dimension`(IN startdate DATE,IN stopdate DATE)
BEGIN
    DECLARE currentdate DATE;
    SET currentdate = startdate;
    WHILE currentdate < stopdate DO
        INSERT INTO time_dimension VALUES (
                        YEAR(currentdate)*10000+MONTH(currentdate)*100 + DAY(currentdate),
                        currentdate,
                        YEAR(currentdate),
                        MONTH(currentdate),
                        DAY(currentdate),
                        QUARTER(currentdate),
                        WEEKOFYEAR(currentdate),
                        DATE_FORMAT(currentdate,'%W'),
                        DATE_FORMAT(currentdate,'%M'),
                        'f',
                        CASE DAYOFWEEK(currentdate) WHEN 1 THEN 't' WHEN 7 then 't' ELSE 'f' END,
                        NULL);
        SET currentdate = ADDDATE(currentdate,INTERVAL 1 DAY);
    END WHILE;
END;;
DELIMITER ;

После создания таблицы и процедуры в базе, нам нужно заполнить таблицу данными, скажем за период от 2014 по 2017 года. Для этого выполним команду:

call fill_date_dimension("2014-01-01", "2017-12-31");

Отлично, теперь таблица-календарь нам поможет вывести одним запросом все данные по продажам в нужном нам формате, для этого напишем примерно такой скрипт:

SELECT 
	week(td.db_date,1) as period,
	SUBDATE(td.db_date, WEEKDAY(db_date)) as start_date,
	ADDDATE(td.db_date, 6 - WEEKDAY(db_date)) as end_date,
	p.name, SUM(sp.count) 
	FROM time_dimension td
		LEFT JOIN sale_product sp on DATE_FORMAT(sp.create_time, '%Y-%m-%d') = td.db_date
		LEFT JOIN product p on p.id = sp.product_id
	WHERE create_time BETWEEN '2015-03-01' AND '2015-06-01'
	GROUP BY period, p.id

Что мы здесь сделали? Теперь в запросе упор делается не на дату в таблице продаж, а на дату в таблице-календарь. Так как таблице-календарь описан каждый день, то и в выводе за нужный нам период будут выведены все недели без прорех.
Ну теперь то все готово? Да, теперь все готово. Нам останется в самом скрипте теперь только считать таблицу результата и вывести ее в нужном формате на экране пользователя.

Единственное, хочется дать напутствие для написания каких-то условий в нашем запросе. Если мы хотим вывести статистику только по товару с id = 7, то прописывать условия нужно не в WHERE, а месте LEFT JOIN таблицы продаж, пример:

SELECT 
	week(td.db_date,1) as period,
	SUBDATE(td.db_date, WEEKDAY(db_date)) as start_date,
	ADDDATE(td.db_date, 6 - WEEKDAY(db_date)) as end_date,
	p.name, SUM(sp.count) 
	FROM time_dimension td
		LEFT JOIN sale_product sp on DATE_FORMAT(sp.create_time, '%Y-%m-%d') = td.db_date AND sp.product_id = 7
		LEFT JOIN product p on p.id = sp.product_id
	WHERE create_time BETWEEN '2015-03-01' AND '2015-06-01'
	GROUP BY period, p.id

Пишите свои вопросы и замечания, по мере возможности всем отвечу!