如何在netezza中创建时间维度?
问题描述:
我发现了一个例子SQL建立时间渔政船,我从这个链接https://gist.github.com/johngrimes/408559如何在netezza中创建时间维度?
一切都在mysql的完美搞定,
但是,当我试图在Netezza公司,代码无法正常工作,有些错误是出现诸如 “期待USING' or
')' '或'', ''” 等
我曾试图修改
CREATE TABLE numbers_small (number INT);
INSERT INTO numbers_small VALUES (0);
INSERT INTO numbers_small VALUES (1);
INSERT INTO numbers_small VALUES (2);
INSERT INTO numbers_small VALUES (3);
INSERT INTO numbers_small VALUES (4);
INSERT INTO numbers_small VALUES (5);
INSERT INTO numbers_small VALUES (6);
INSERT INTO numbers_small VALUES (7);
INSERT INTO numbers_small VALUES (8);
INSERT INTO numbers_small VALUES (9);
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 TABLE D_TIME_DAILY_TES (
TIME_ID BIGINT PRIMARY KEY,
TANGGAL DATE NOT NULL,
TAHUN INT NOT NULL,
BULAN CHAR(10) NOT NULL,
HARI CHAR(10) NOT NULL,
LAST_UPDATE timestamp NOT NULL,
constraint D_TIME_DAILY_TES UNIQUE KEY (TANGGAL)
);
INSERT INTO D_TIME_DAILY_TES (TIME_ID, TANGGAL, LAST_UPDATE)
SELECT number, DATE_ADD('2010-01-01', INTERVAL number DAY), CURRENT_TIMESTAMP
FROM numbers
WHERE DATE_ADD('2010-01-01', INTERVAL number DAY) BETWEEN '2014-01-01' AND '2014-12-31'
ORDER BY number;
UPDATE D_TIME_DAILY_TES SET
HARI = DATE_FORMAT(TANGGAL, "%W"),
BULAN = DATE_FORMAT(TANGGAL, "%M"),
TAHUN = DATE_FORMAT(TANGGAL, "%Y"),
LAST_UPDATE = CURRENT_TIMESTAMP;
,但它仍然没有工作:(
我该如何解决它?
thx :)
答
从语法角度来看,我的代码有两个问题。
第一个是创建表D_TIME_DAILY_TES。
删除TANGGAL上的唯一键约束,它会正常运行。
CREATE TABLE D_TIME_DAILY_TES
(
TIME_ID BIGINT PRIMARY KEY,
TANGGAL DATE NOT NULL,
TAHUN INT NOT NULL,
BULAN CHAR(10) NOT NULL,
HARI CHAR(10) NOT NULL,
LAST_UPDATE TIMESTAMP NOT NULL
);
第二个是在INSERT中填充同一个表。 DATE_ADD不需要将整数添加到日期,并且实际上不存在。您可以改用此表单。请注意,这个插入实际上会失败,因为我们只插入三列,而您为D_TIME_DAILY_TES定义的所有列都不是NULL,所以您还需要修复它。
INSERT INTO D_TIME_DAILY_TES
(
TIME_ID,
TANGGAL,
LAST_UPDATE
)
SELECT NUMBER,
'2010-01-01'::DATE + NUMBER,
CURRENT_TIMESTAMP
FROM numbers
WHERE '2010-01-01'::DATE + NUMBER BETWEEN '2014-01-01'::DATE AND '2014-12-31'::DATE
ORDER BY NUMBER;