mysql中创建时间维度

Small-numbers table DROP TABLE IF EXISTS numbers_small; CREATE TABLE numbers_small (number INT); INSERT INTO numbers_small VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); Main-numbers table DROP TABLE IF EXISTS numbers; CREATE TABLE number

• Small-numbers table

DROP TABLE IF EXISTS numbers_small;<br> CREATE TABLE numbers_small (number INT);<br> INSERT INTO numbers_small VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);<br>

• Main-numbers table

DROP TABLE IF EXISTS numbers;<br> CREATE TABLE numbers (number BIGINT);<br> INSERT INTO numbers<br> SELECT thousands.number * 1000 + hundreds.number * 100 + tens.number * 10 + ones.number<br> FROM numbers_small thousands, numbers_small hundreds, numbers_small tens, numbers_small ones<br> LIMIT 1000000;<br>

• Create Date Dimension table

DROP TABLE IF EXISTS Dates_D;<br> CREATE TABLE Dates_D (<br> date_id          BIGINT PRIMARY KEY,<br> date             DATE NOT NULL,<br> day              CHAR(10),<br> day_of_week      INT,<br> day_of_month     INT,<br> day_of_year      INT,<br> previous_day     date NOT NULL default '0000-00-00',<br> next_day         date NOT NULL default '0000-00-00',<br> weekend          CHAR(10) NOT NULL DEFAULT "Weekday",<br> week_of_year     CHAR(2),<br> month            CHAR(10),<br> month_of_year    CHAR(2),<br> quarter_of_year INT,<br> year             INT,<br> UNIQUE KEY `date` (`date`));

• First populate with ids and Date

INSERT INTO Dates_D (date_id, date)<br> SELECT number, DATE_ADD( '2010-01-01', INTERVAL number DAY )<br> FROM numbers<br> WHERE DATE_ADD( '2010-01-01', INTERVAL number DAY ) BETWEEN '2010-01-01' AND '2010-12-31'<br> ORDER BY number;<br>
Change year start and end to match your needs. The above sql creates records for year 2010.

• Update other columns based on the date.

UPDATE Dates_D SET<br> day             = DATE_FORMAT( date, "%W" ),<br> day_of_week     = DAYOFWEEK(date),<br> day_of_month    = DATE_FORMAT( date, "%d" ),<br> day_of_year     = DATE_FORMAT( date, "%j" ),<br> previous_day    = DATE_ADD(date, INTERVAL -1 DAY),<br> next_day        = DATE_ADD(date, INTERVAL 1 DAY),<br> weekend         = IF( DATE_FORMAT( date, "%W" ) IN ('Saturday','Sunday'), 'Weekend', 'Weekday'),<br> week_of_year    = DATE_FORMAT( date, "%V" ),<br> month           = DATE_FORMAT( date, "%M"),<br> month_of_year   = DATE_FORMAT( date, "%m"),<br> quarter_of_year = QUARTER(date),<br> year            = DATE_FORMAT( date, "%Y" );