Создать диапазон дат в MySQL
Лучший способ создания на лету диапазонов дат, для использования с отчетом.
Таким образом, я могу избежать пустых строк в моем отчете, если в данный день нет активности.
Главным образом, чтобы избежать этой проблемы: Каков самый простой способ дополнить пустые даты в результатах sql (либо в mysql, либо в perl end)?
5 ответов
В MySQL нет простого способа сделать это. Лучше всего создать массив диапазонов дат на выбранном вами серверном языке, а затем извлечь данные из базы данных и объединить полученный массив с вашим массивом дат, используя дату в качестве ключа.
Какой язык на стороне сервера вы используете?
Редактировать:
В основном, что бы вы сделали, это (псевдокод):
// Create an array with all dates for a given range
dates = makeRange(startDate, endDate);
getData = mysqlQuery('SELECT date, x, y, z FROM a WHERE a AND b AND c');
while (r = fetchRowArray(getData)) {
dates[ date(r['date']) ] = Array ( x, y, z);
}
В итоге вы получите массив дат, который вы можете просмотреть, с датами, которые имеют или не имеют данных об активности, связанных с ними.
Может быть легко изменен для группировки / фильтрации данных по часам.
Мой совет: не усложняйте свою жизнь, сделайте ее легче. Просто создайте таблицу с одной строкой для каждого календарного дня, имеющую столько строк, сколько вы считаете нужным продлить. В хранилище данных это общее решение, и оно настолько широко реализовано, что у dwh, у которого его нет, есть запах кода.
Многие люди привыкли иметь дело с более традиционными приложениями ввода / вывода данных, чувствуют естественное отвращение к этой идее, потому что считают, что они могут генерировать данные в любом случае, и поэтому они не должны храниться. Но если вы создадите такую таблицу, вы можете украсить ее многими полезными атрибутами, такими как будь то праздник или выходные, и вы можете хранить в ней много общих представлений даты (iso, европейский, американский формат и т. Д.), Которые может сэкономить массу времени при создании отчетов (поскольку вам не нужно разбираться в том, как работает форматирование даты в каждом инструменте отчетности, к которому вы пришли. Или вы можете пойти еще дальше и ежедневно обновлять таблицу дат, чтобы отмечать флаги на текущий день, текущую неделю, текущий месяц, текущий год и т. д. - все виды полезных инструментов, которые значительно упрощают создание отчетов, которые должны работать с определенным диапазоном дат.
Пример кода MySQL согласно запросу в комментарии:
delimiter //
DROP PROCEDURE IF EXISTS p_load_dim_date
//
CREATE PROCEDURE p_load_dim_date (
p_from_date DATE
, p_to_date DATE
)
BEGIN
DECLARE v_date DATE DEFAULT p_from_date;
DECLARE v_month tinyint;
CREATE TABLE IF NOT EXISTS dim_date (
date_key int primary key
, date_value date
, date_iso char(10)
, year smallint
, quarter tinyint
, quarter_name char(2)
, month tinyint
, month_name varchar(10)
, month_abbreviation varchar(10)
, week char(2)
, day_of_month tinyint
, day_of_year smallint
, day_of_week smallint
, day_name varchar(10)
, day_abbreviation varchar(10)
, is_weekend tinyint
, is_weekday tinyint
, is_today tinyint
, is_yesterday tinyint
, is_this_week tinyint
, is_last_week tinyint
, is_this_month tinyint
, is_last_month tinyint
, is_this_year tinyint
, is_last_year tinyint
);
WHILE v_date < p_to_date DO
SET v_month := month(v_date);
INSERT INTO dim_date(
date_key
, date_value
, date_iso
, year
, quarter
, quarter_name
, month
, month_name
, month_abbreviation
, week
, day_of_month
, day_of_year
, day_of_week
, day_name
, day_abbreviation
, is_weekend
, is_weekday
) VALUES (
v_date + 0
, v_date
, DATE_FORMAT(v_date, '%y-%c-%d')
, year(v_date)
, ((v_month - 1) DIV 3) + 1
, CONCAT('Q', ((v_month - 1) DIV 3) + 1)
, v_month
, DATE_FORMAT(v_date, '%M')
, DATE_FORMAT(v_date, '%b')
, DATE_FORMAT(v_date, '%u')
, DATE_FORMAT(v_date, '%d')
, DATE_FORMAT(v_date, '%j')
, DATE_FORMAT(v_date, '%w') + 1
, DATE_FORMAT(v_date, '%W')
, DATE_FORMAT(v_date, '%a')
, IF(DATE_FORMAT(v_date, '%w') IN (0,6), 1, 0)
, IF(DATE_FORMAT(v_date, '%w') IN (0,6), 0, 1)
);
SET v_date := v_date + INTERVAL 1 DAY;
END WHILE;
CALL p_update_dim_date();
END;
//
DROP PROCEDURE IF EXISTS p_update_dim_date;
//
CREATE PROCEDURE p_update_dim_date()
UPDATE dim_date
SET is_today = IF(date_value = current_date, 1, 0)
, is_yesterday = IF(date_value = current_date - INTERVAL 1 DAY, 1, 0)
, is_this_week = IF(year = year(current_date) AND week = DATE_FORMAT(current_date, '%u'), 1, 0)
, is_last_week = IF(year = year(current_date - INTERVAL 7 DAY) AND week = DATE_FORMAT(current_date - INTERVAL 7 DAY, '%u'), 1, 0)
, is_this_month = IF(year = year(current_date) AND month = month(current_date), 1, 0)
, is_last_month = IF(year = year(current_date - INTERVAL 1 MONTH) AND month = month(current_date - INTERVAL 1 MONTH), 1, 0)
, is_this_year = IF(year = year(current_date), 1, 0)
, is_last_year = IF(year = year(current_date - INTERVAL 1 YEAR), 1, 0)
WHERE is_today
OR is_yesterday
OR is_this_week
OR is_last_week
OR is_this_month
OR is_last_month
OR is_this_year
OR is_last_year
OR IF(date_value = current_date, 1, 0)
OR IF(date_value = current_date - INTERVAL 1 DAY, 1, 0)
OR IF(year = year(current_date) AND week = DATE_FORMAT(current_date, '%u'), 1, 0)
OR IF(year = year(current_date - INTERVAL 7 DAY) AND week = DATE_FORMAT(current_date - INTERVAL 7 DAY, '%u'), 1, 0)
OR IF(year = year(current_date) AND month = month(current_date), 1, 0)
OR IF(year = year(current_date - INTERVAL 1 MONTH) AND month = month(current_date - INTERVAL 1 MONTH), 1, 0)
OR IF(year = year(current_date), 1, 0)
OR IF(year = year(current_date - INTERVAL 1 YEAR), 1, 0)
;
//
delimiter ;
С помощью p_load_dim_date вы изначально загружаете dim_date таблица с данными 25 лет. И ежедневно, предпочтительнее, около полуночи вы бегаете p_update_dim_date, Затем вы можете использовать поля флага is_today, is_yesterday, is_this_week, is_last_week и так далее, чтобы выбрать общие диапазоны. Конечно, вы должны изменить этот код в соответствии с вашими потребностями, но это идея. Таким образом, нет генерации диапазонов на лету, вы просто предварительно загружаете в течение достаточно длительного периода времени. Для времени суток может быть настроен аналогичный дизайн - вы сможете сами управлять этим, используя этот код.
Если вам нужны более изящные измерения дат, которые учитывают праздники, и локализованные названия месяцев и дней, вы можете посмотреть по адресу: http://rpbouman.blogspot.com/2007/04/kettle-tip-using-java-locales-for-date.html и http://rpbouman.blogspot.com/2010/01/easter-eggs-for-mysql-and-kettle.html
Недавно я провел некоторое исследование, чтобы найти и оценить возможные варианты. http://www.freeportmetrics.com/devblog/2012/11/02/how-to-quickly-add-date-dimension-to-pentaho-mondrian-olap-cube/.
Ты можешь использовать:
- чайник
- вырожденные размеры
- встроенная функция lucidb
- Предстоящая встроенная функция Мондриана
- ваш собственный скрипт для генерации SQL
- MySQL скрипт, упомянутый ранее
Пожалуйста, проверьте сообщение в блоге для более подробной информации. Он также содержит улучшенную версию sql-скрипта Роланда, который автоматически вычислит диапазон дат для данного столбца и объединит его с измерением даты.
Я понимаю, что это старый пост, но, чтобы держать Stack Overflow немного в курсе, я чувствую необходимость ответить.
С новым движком SEQUENCE в MariaDB это возможно за считанные минуты.SELECTоператор без какой-либо сохраненной процедуры или временной таблицы:
SELECT
DATE_ADD(
CAST('2022-06-01' AS DATE),
INTERVAL `s1`.`seq` DAY
) AS `dates`
FROM `seq_0_to_364` AS `s1`;
Любой интервал будет работать, пока он находится в пределахBIGINT(20) UNSIGNED, так как это предел движка SEQUENCE.
Попробуйте использовать цикл в хранимой подпрограмме MySQL для создания диапазонов дат:
declare iterDate date;
set iterDate = startDate;
DROP TABLE IF EXISTS MyDates;
create temporary table MyDates (
theDate date
);
label1: LOOP
insert into MyDates(theDate) values (iterDate);
SET iterDate = DATE_ADD(iterDate, INTERVAL 1 DAY);
IF iterDate <= endDate THEN
ITERATE label1;
END IF;
LEAVE label1;
END LOOP label1;
select * from MyDates;
DROP TABLE IF EXISTS MyDates;
startDate а также endDate составляют конечные точки диапазона и предоставляются в качестве параметров для процедуры.