mysql生成时间周纬度表

-- 创建时间维度表
CREATE TABLE time_dimension (
    time_id INT AUTO_INCREMENT PRIMARY KEY,
    year INT,
    month INT,
    day INT,
    week INT,
    date DATE
);

-- 创建地理位置维度表
CREATE TABLE location_dimension (
    location_id INT AUTO_INCREMENT PRIMARY KEY,
    latitude DECIMAL(9, 6),
    longitude DECIMAL(9, 6)
);

-- 插入时间维度数据
INSERT INTO time_dimension (year, month, day, week, date)
SELECT YEAR(date), MONTH(date), DAY(date), WEEK(date), date
FROM (
    SELECT DATE_SUB(CURDATE(), INTERVAL n DAY) AS date
    FROM (
        SELECT @rownum:=@rownum+1 AS n
        FROM (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6) r,
             (SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10) s,
             (SELECT @rownum:=0) t
    ) d
) t;

-- 插入地理位置维度数据
INSERT INTO location_dimension (latitude, longitude)
VALUES (39.9042, 116.4074), (31.2304, 121.4737), (23.1291, 113.2644);