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
用 php + 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 numbers (number BIGINT);
INSERT INTO numbers
SELECT thousands.number * 1000 + hundreds.number * 100 + tens.number * 10 + ones.number
FROM numbers_small thousands, numbers_small hundreds, numbers_small tens, numbers_small ones
LIMIT 1000000;
- Create Date Dimension table
DROP
TABLE IF EXISTS Dates_D;
CREATE TABLE Dates_D (
date_id BIGINT PRIMARY KEY,
date DATE NOT NULL,
day CHAR(10),
day_of_week INT,
day_of_month INT,
day_of_year INT,
previous_day date NOT NULL default '0000-00-00',
next_day date NOT NULL default '0000-00-00',
weekend CHAR(10) NOT NULL DEFAULT "Weekday",
week_of_year CHAR(2),
month CHAR(10),
month_of_year CHAR(2),
quarter_of_year INT,
year INT,
UNIQUE KEY `date` (`date`));
- First populate with ids and Date
INSERT
INTO Dates_D (date_id, date)
SELECT number, DATE_ADD( '2010-01-01', INTERVAL number DAY )
FROM numbers
WHERE DATE_ADD( '2010-01-01', INTERVAL number DAY ) BETWEEN '2010-01-01' AND '2010-12-31'
ORDER BY number;
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
day = DATE_FORMAT( date, "%W" ),
day_of_week = DAYOFWEEK(date),
day_of_month = DATE_FORMAT( date, "%d" ),
day_of_year = DATE_FORMAT( date, "%j" ),
previous_day = DATE_ADD(date, INTERVAL -1 DAY),
next_day = DATE_ADD(date, INTERVAL 1 DAY),
weekend = IF( DATE_FORMAT( date, "%W" ) IN ('Saturday','Sunday'), 'Weekend', 'Weekday'),
week_of_year = DATE_FORMAT( date, "%V" ),
month = DATE_FORMAT( date, "%M"),
month_of_year = DATE_FORMAT( date, "%m"),
quarter_of_year = QUARTER(date),
year = DATE_FORMAT( date, "%Y" );
