Hi All, I would very much appreciate if someone could help me speed up my
database table insertion process as what I have in the followings took a
long time to complete even when using sqlite3 shell command.
Thank you in advance for your help.
Cheers,
Rick
CREATE TABLE [BuyPattern] (
[ID] INTEGER NOT NULL,
[Year] INTEGER NOT NULL,
[Item] TEXT NOT NULL,
[Skips] INTEGER NOT NULL,
[Frequency] INTEGER NOT NULL,
[LastBuyDate] DATE NOT NULL,
[NextBuyDate] DATE NOT NULL,
[Match] TEXT NOT NULL
)
CREATE TABLE [Purchases] (
[RecNumber] INTEGER NOT NULL,
[BuyDate] DATE NOT NULL,
[Item] TEXT NOT NULL
)
CREATE INDEX [IDX_Purchases_1] ON [Purchases](
[RecNumber] ASC,
[Item] ASC
)
CREATE INDEX [IDX_BuyPattern_1] ON [SkipnHit](
[Item] ASC,
[Skips] ASC,
[Frequency] ASC,
[NextBuyDate] ASC,
[Hit] ASC,
[Year] ASC,
[LastBuyDate] ASC
)
begin;
insert into BuyPattern select 1 AS 'ID',
2004 AS 'Year',
x.Item AS 'ITEM',
CAST((CAST(strftime('%J', y.BuyDate) AS INTEGER) - CAST(strftime('%J',
x.BuyDate) AS INTEGER))/7 AS INTEGER) AS 'SKIPS',
COUNT(x.Item) AS 'FREQUENCY',
strftime('%Y-%m-%d', (select max(BuyDate) from Purchases where
strftime('%Y', BuyDate) = '2004' and Item = x.Item group by Item)) AS
'LastBuyDate',
strftime('%Y-%m-%d', CAST(strftime('%J', y.BuyDate) AS INTEGER) -
CAST(strftime('%J', x.BuyDate) AS INTEGER) + 1 + CAST(strftime('%J',
(select max(BuyDate) from Item where strftime('%Y', BuyDate) = '2004'
and Item = x.Item group by Item)) AS INTEGER)) AS 'NextBuyDate' ,
Case when (SELECT count(*) from Item where Item = x.Item and
strftime('%Y-%m-%d', BuyDate) = strftime('%Y-%m-%d', CAST(strftime('%J',
y.BuyDate) AS INTEGER) - CAST(strftime('%J', x.BuyDate) AS INTEGER) + 1 +
CAST(strftime('%J', (select max(BuyDate) from Purchases where
strftime('%Y', BuyDate) = '2004' and Item = x.Item group by Item)) AS
INTEGER))) <> 0 THEN 'Match' Else 'Miss' End AS 'HIT/MISS'
FROM Purchases x, Purchases y where x.Item = y.Item and
(CAST(strftime('%J', y.BuyDate) AS INTEGER) - CAST(strftime('%J',
x.BuyDate) AS INTEGER)) > 0 and CAST((CAST(strftime('%J', y.BuyDate) AS
INTEGER) - CAST(strftime('%J', x.BuyDate) AS INTEGER))/7 AS INTEGER) <=
52 and strftime('%Y', x.BuyDate) = '2004' GROUP BY x.Item,
CAST((CAST(strftime('%J', y.BuyDate) AS INTEGER) - CAST(strftime('%J',
x.BuyDate) AS INTEGER))/7 AS INTEGER);
end;
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
