Блог

Я анализирую некоторые данные из базы данных SQLite, где мне нужно подсчитать изменение данных за месяц. Мне было интересно, какой наиболее подходящий тип данных для хранения значений данных, подобных этому «2015-11-09» и в другом столбце, подобном этому «Nov-12». Эти значения необходимо собрать и сравнить.

Позже мне нужно использовать python для создания графика с количеством значений за месяц. Спасибо

Тип данных (соответствие типа столбца) из-за гибкости SQLite на самом деле не важен, см..

Скорее, что было бы важным / полезным / удобным, так это формат даты.

Что касается SQLite, то 2015-11-09 — лучший формат из двух, поскольку функции даты и времени можно использовать напрямую, скажем, для выбора диапазона или для извлечения данных в другом формате с помощью функции strftime.

Например, рассмотрим следующий SQL

 DROP TABLE IF EXISTS mytable ;
CREATE TABLE IF NOT EXISTS mytable (col1 TEXT, col2 BLOB, col3 REAL, col4 INTEGER, col5 a_weird_type);
INSERT INTO mytable VALUES 
    ('2015-11-09','2015-11-09','2015-11-09','2015-11-09','2015-11-09'),
    ('NOV 9 2015','NOV 9 2015','NOV 9 2015','NOV 9 2015','NOV 9 2015'),
    ('NOV 09 2015','NOV 09 2015','NOV 09 2015','NOV 09 2015','NOV 09 2015'),
    ('NOV 19 2015','NOV 19 2015','NOV 19 2015','NOV 19 2015','NOV 19 2015')
;
SELECT * FROM mytable;
SELECT * FROM mytable WHERE date(col1) BETWEEN date('2014-01-01') AND date('2016-12-31');
SELECT *,
    CASE
        WHEN substr(col1,4,1) = ' ' AND substr(col1,6,1) = ' ' AND substr(col1,1,3) = 'NOV' THEN
            substr(col1,7,4)||'-11-0'||substr(col1,5,1)
        WHEN substr(col1,4,1) = ' ' AND substr(col1,7,1) = ' ' AND substr(col1,1,3) = 'NOV' THEN
            substr(col1,8,4)||'-11-'||substr(col1,5,2)
        ELSE col1
    END AS colx
    FROM mytable
WHERE date(colx) BETWEEN date('2014-01-01') AND date('2016-12-31')
;
  

Как видно из первого запроса SELECT и вставленных данных, может использоваться любой тип столбца. Результатом запроса является :-

Однако затем рассмотрите второй запрос SELECT, это вернет только одну строку, где дата была сохранена как ГГГГ-ММ-ДД, поскольку ноябрь не будет приравниваться к периоду между 2014 и 2016 годами, т. е. Результат :-

Скорее, чтобы выбрать все строки (все должны быть в этом диапазоне дат), вам пришлось бы использовать что-то вроде третьего запроса SELECT. Это слишком сложно (отметим, что это подходит только для NOV, так что для всех месяцев нужно закодировать еще 22 предложения WHEN). Это приводит к :-