Блог

Я хочу настроить СУБД для структурированных данных временных рядов ограниченного размера (около 6000 рядов, 50 МБ данных) с различной частотой (ежедневно, ежемесячно, ежеквартально, ежегодно и ежегодно), и я хочу запускать SQL-запросы к базе данных (в основном объединять различные таблицы по времени). База данных обновляется раз в месяц. Имена переменных таблиц в этой базе данных являются скорее техническими и не очень информативными. Исходные данные помечены так, как показано в таблице ниже (пример ежемесячной таблицы).

Я начал настраивать это в MySQL и понял, что простое оснащение таблиц соответствующими временными идентификаторами дает мне нужную функциональность соединения. Однако я не смог выяснить, как правильно хранить метки переменных. Можно ли как-то добавить атрибуты к столбцам? Или я могу связать таблицу с таблицей, сопоставляющей метки с именами столбцов, так что она переносится в объединениях? Или я должен настроить это, используя другой тип базы данных? (база данных должна быть простой в настройке и размещении, и SQL настоятельно предпочтительнее). Я благодарен за любые советы.

Обновление: я подумал, что вы можете добавлять комментарии к столбцам и таблицам MySQL, но, похоже, их нельзя запрашивать стандартным способом или переносить в объединениях. Возможно ли получить информацию в комментариях вместе с запрошенными данными из стандартного соединителя базы данных (например, для языка R: https://github.com/r-dbi/RMySQL )? Ниже приведен пример DDL для таблиц с метками переменных в качестве комментариев.

 
-- Annual FY Table
CREATE TABLE IF NOT EXISTS BOU_MMI_AF (
   FY VARCHAR(7) COMMENT "Fiscal Year (July - June)",
   NFA DOUBLE COMMENT "Net Foreign Assets (NFA) (Shs billion)",
   NDA DOUBLE COMMENT "Net Domestic Assets (NDA) (Shs billion)",
   PRIMARY KEY (FY)
) COMMENT = "Annual FY";

-- Quarterly Table
CREATE TABLE IF NOT EXISTS BOU_FS (
   Year INT CHECK (Year >= 1800 AND Year < 2100) COMMENT "Year",
   Quarter VARCHAR(2) CHECK (Quarter IN ('Q1', 'Q2', 'Q3', 'Q4')) COMMENT "Quarter",
   FY VARCHAR(7) COMMENT "Fiscal Year (July - June)",
   QFY VARCHAR(2) CHECK (QFY IN ('Q1', 'Q2', 'Q3', 'Q4')) COMMENT "Quarter of Fiscal Year",
   KA_RC_RWA DOUBLE COMMENT "Capital Adequacy (%): Regulatory capital to risk-weighted assets",
   AQ_NPL_GL DOUBLE COMMENT "Asset quality (%): NPLs to total gross loans",
   EP_RA DOUBLE COMMENT "Earnings amp; profitability (%): Return on assets",
   L_BFA_TD DOUBLE COMMENT "Liquidity (%): Bank-funded advances to total deposits",
   MS_FX_T1CA DOUBLE COMMENT "Market Sensitivity (%): Forex exposure to regulatory tier 1 capital",
   PRIMARY KEY (Year, Quarter)
) COMMENT = "Quarterly";

-- Daily Table
CREATE TABLE IF NOT EXISTS BOU_I (
   Date DATE CHECK (Date >= '1800-01-01' AND Date < '2100-01-01') COMMENT "Date",
   Year INT CHECK (Year >= 1800 AND Year < 2100) COMMENT "Year",
   Quarter VARCHAR(2) CHECK (Quarter IN ('Q1', 'Q2', 'Q3', 'Q4')) COMMENT "Quarter",
   FY VARCHAR(7) COMMENT "Fiscal Year (July - June)",
   QFY VARCHAR(2) CHECK (QFY IN ('Q1', 'Q2', 'Q3', 'Q4')) COMMENT "Quarter of Fiscal Year",
   Month VARCHAR(9) CHECK (Month IN ('January' , 'February', 'March', 'April', 'May', 'June', 'July', 'August', 'September', 'October', 'November', 'December')) COMMENT "Month",
   Day INT CHECK (Day > 0 AND Day < 32) COMMENT "Day",
   I_Overnight DOUBLE COMMENT "Daily Interbank Money-Market Rates: Overnight  (%)",
   I_7day DOUBLE COMMENT "Daily Interbank Money-Market Rates: 7-day (%)",
   I_Overall DOUBLE COMMENT "Daily Interbank Money-Market Rates: Overall (%)",
   PRIMARY KEY (Date)
) COMMENT = "Daily";


 

Итак, если я выполню запрос, подобный

 SELECT * FROM BOU_I NATURAL JOIN BOU_FS NATURAL JOIN BOU_MMI_AF; 
 

используя статистическую программную среду, такую как R или STATA, подключающуюся к базе данных с помощью соединителя MySQL, я хотел бы видеть таблицу, подобную показанной на рисунке, где я могу получить как имена переменных, так и метки, сохраненные в виде комментариев в DDL.

Я бы структурировал ваши данные по-другому. Я бы поместил все ваши показатели в одну таблицу и получил бы по одному показателю на строку. Затем я бы добавил таблицу ДАТ (чтобы у вас были значения недели / месяца / квартала / года для каждой даты показателя) и таблицу METRIC_TYPE, которая содержит метки для каждого кода показателя.

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

Это только для иллюстрации того, что я имею в виду — это не должно быть окончательным дизайном:

Так что я очень доволен предложением @NickW. Для справки я делюсь своей окончательной схемой ниже. У меня все еще есть некоторые вопросы по этому поводу. Поэтому я в основном запрашиваю таблицу ДАННЫХ напрямую (которая содержит около 700 000 obs) и при необходимости объединяю информацию из таблиц TIME, SERIES и DATASET. Я заметил, что извлечение больших объемов данных может занять некоторое время. Поэтому я задался вопросом: оптимально ли я индексирую это?

Затем есть несколько вычисляемых столбцов: Столбец Ndatasets в DATASOURCE подсчитывает количество идентификаторов DSID по источнику в таблице DATASET, обновленный столбец в DATASET показывает, когда данные в последний раз добавлялись в конкретный набор данных. DS_From, DS_to и S_from, S_to задают максимальный временной диапазон, в котором доступны данные для данного набора данных и серии. В настоящее время я выполняю все эти вычисления в R и вставляю данные. Интересно, можно ли выполнить эти вычисления в MySQL, чтобы иметь самообновляющиеся столбцы?

Благодарен за любые дальнейшие комментарии по этому поводу.

DDL:

 DROP SCHEMA IF EXISTS TSDB;
CREATE SCHEMA IF NOT EXISTS TSDB;
USE TSDB;

CREATE TABLE IF NOT EXISTS DATASOURCE (
   Source VARCHAR(120),
   Source_Url VARCHAR(200),
   NDatasets INT NOT NULL, 
   Desription VARCHAR(3000) NOT NULL,
   Access VARCHAR(3000) NOT NULL,
   PRIMARY KEY (Source)
);

CREATE TABLE IF NOT EXISTS DATASET (
   DSID VARCHAR(30), -- INT
   Dataset VARCHAR(120) NOT NULL,
   Frequency VARCHAR(9) NOT NULL CHECK (Frequency IN ('Daily' , 'Monthly', 'Quarterly', 'Annual CY', 'Annual FY')),
   DS_From DATE CHECK (DS_From >= '1800-01-01' AND DS_From < '2100-01-01'), 
   DS_To DATE CHECK (DS_To >= '1800-01-01' AND DS_To < '2100-01-01'), 
   Updated DATE CHECK (Updated >= '1800-01-01' AND Updated < '2100-01-01'), 
   Desription VARCHAR(3000) NOT NULL,
   Source VARCHAR(120), -- NOT NULL
   DS_Url VARCHAR(200),
   PRIMARY KEY (DSID),
   FOREIGN KEY (Source) REFERENCES DATASOURCE (Source) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE INDEX idx_dataset_source ON DATASOURCE (Source); 

CREATE TABLE IF NOT EXISTS SERIES (
   DSID VARCHAR(30), -- INT
   Series VARCHAR(30) NOT NULL,
   Label VARCHAR(120) NOT NULL,
   S_From DATE CHECK (S_From >= '1800-01-01' AND S_From < '2100-01-01'), 
   S_To DATE CHECK (S_To >= '1800-01-01' AND S_To < '2100-01-01'), 
   S_Source VARCHAR(120),
   S_Url VARCHAR(200),
   PRIMARY KEY (DSID, Series),
   FOREIGN KEY (DSID) REFERENCES DATASET (DSID) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE INDEX idx_series_DSID ON SERIES (DSID); 

CREATE TABLE IF NOT EXISTS TIME (
    Date DATE UNIQUE CHECK (Date >= '1800-01-01' AND Date < '2100-01-01'),
    Year INT NOT NULL CHECK (Year >= 1800 AND Year < 2100),
    Quarter INT NOT NULL CHECK (Quarter >= 1 AND  Quarter <= 4), 
    FY CHAR(7) NOT NULL,
    QFY INT NOT NULL CHECK (QFY >= 1 AND  QFY <= 4), 
    Month INT NOT NULL CHECK (Month >= 1 AND  Month <= 12), 
    Day INT NOT NULL CHECK (Day > 0 AND Day < 32),
    PRIMARY KEY (Date)
);

CREATE TABLE IF NOT EXISTS DATA (
   Date DATE, 
   DSID VARCHAR(30), 
   Series VARCHAR(30),
   Value DOUBLE NOT NULL,
   PRIMARY KEY (Date, DSID, Series),
   FOREIGN KEY (DSID) REFERENCES DATASET (DSID) ON DELETE CASCADE ON UPDATE CASCADE,
   FOREIGN KEY (DSID, Series) REFERENCES SERIES (DSID, Series) ON DELETE CASCADE ON UPDATE CASCADE,
   FOREIGN KEY (Date) REFERENCES TIME (Date) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE INDEX idx_data_DSID ON DATA (DSID); 
CREATE INDEX idx_data_series ON DATA (DSID, Series); 
CREATE INDEX idx_data_date ON DATA (Date); 
 

Диаграмма EER: