Teradata - Secondary Index

Two challenges for this week:

1. How to create a volatile table with a secondary index
2. How to add an index to an existing table.

While there is no secondary index explicit in TD for VOLATILE tables, I found that I could simply add indices after the PRIMARY INDEX and voila:

CREATE VOLATILE TABLE FOO
(
Id INT
, Date DATE
, Product VARCHAR(20)
)
PRIMARY INDEX (Id)
INDEX (Product)
ON COMMIT PRESERVE ROWS; -- to ensure when you populate the table you can query the data.

Adding a Secondary Index (unique or non-unique) to an existing table:
create index index_name (Field) on database.table;

Extracting the Day of Week to roll up cohorts into Discrete Weeks

Dates seem to be a common theme for me.  I'm sure I'm not alone. Today I'm working on a project where I need to group all people into a calendar week for their event. Thus, I want to take their Event Date, extract AND subtract the DayOfWeek from the Event Date to group everyone by the week's first day.

Also, we calculate our weeks as Monday - Sunday, so you'll see a +2 on the day_of_week field.

SELECT 
StartDt
, EXTRACT(DAY from StartDt) as DayOfMonth
, day_of_week + 2 as DayOfWeek
, StartDt - (day_of_week + 2) as FirstDayOfWeek
from mydb.table
INNER JOIN sys_calendar.calendar
ON StartDt = calendar_date;

This give me:

SubStartDt DayOfMonth DayOfWeek FirstDayOfWeek
 09/25/2009         25         8 09/21/2009
 10/12/2009         12         4 10/12/2009
 09/28/2009         28         4 09/28/2009
 10/17/2009         17         9 10/12/2009
 09/29/2009         29         5 09/28/2009
 10/15/2009         15         7 10/12/2009
 10/07/2009          7         6 10/05/2009
 10/16/2009         16         8 10/12/2009
 10/11/2009         11         3 10/12/2009
 10/04/2009          4         3 10/05/2009

Now back to my weekly assessment.

Renaming a Column in Teradata

Sometimes it's the little stuff where syntax differences between DMBSs get me.  Today I had to look up how to rename a column.  Easy enough stuff:

ALTER TABLE mydb.foo RENAME OldColumnName TO NewColumnName;

Teradata Date Conversion - Getting the First day of the Month

I have a table containing start dates and I want to identify all the records for a given month by their month. This will allow me to identify sets of users by their signup month.

Thanks to a tip from Craig F. I can do this by subtracting from the date the day of the month minus 1. This will ensure that regardless of the date I choose, all my dates are returned as the first of the month.

I created a table (foo) with one column (startdt type DATE) and three records:

select * from foo

== Results ==
startdt
12/31/2009  12:00:00 AM
8/1/2009    12:00:00 AM
7/15/2009  12:00:00 AM

Now I want to get the number of days to subtract from the day in the date. To do this, I use Teradata’s EXTRACT function and get the day of the date:

EXTRACT(Day from StartDt) as DayOfDate 

and I get the number day value : 31, 1, 15

EXTRACT(Month from StartDt) returns the month values 12, 8, 7

EXTRACT(Year from StartDt) returns 2009

Now that I have the numeric day of the month I simply subtract one from it:

SELECT startdt
, startdt - INTERVAL '1' DAY as Start_Minus_1_Day
, EXTRACT(Day from StartDt) -1 as DaySubAmount
, startdt - (EXTRACT(DAY from StartDt) -1) as FirstofMonth
FROM database.foo

You'll notice another function you can do with dates is add or subtract an INTERVAL. There is a lot you can do with INTERVAL so I'll limit my comments here to how I used it.

Above I subtracted 1 day from the date using - INTERVAL '1' DAY. Day could be replaced with Month, Year, Hour, Minute, Second and transformations (like Day to Second). You can also Add an INTERVAL to a date using the same syntax.

My final result set was:

startddt Start_Minus_1_Day DaySubAmount FirstofMonth
8/1/2009 0:00 7/31/2009 0:00  0  8/1/2009 0:00
7/15/2009 0:00 7/14/2009 0:00  14  7/1/2009 0:00
12/31/2009 0:00 12/30/2009 0:00  30  12/1/2009 0:00

Getting Started, my own little “Hello, World!”

I first started this blog as teratara.wordpress.com but somehow got locked out of my account and thus have moved it here.

Most programming lessons start out by teaching you how to get your computer to say “Hello, World!”. This always makes me laugh. I envision a doey-eyed computer-being deep within my maching awakening for the first time, leaning forward with a smile and proclaiming, “Hello, World!”.

Problem solving and language learning has always been fun for me. That said, sometimes my tools for these activities get me to pulling my hair and screwing up my face. The little being deep within my computer then seems to be snickering.

Lately I’ve been working in customer analytics and since last October with Teradata. Teradata is an amazingly power data warehousing solution that is sadly lacking in documentation and online solutions or cookbooks. It’s (almost) ANSI SQL has had me going in the right direction only to hit what seemed a completely illogical and quite nonsensical roadblock. And with no book or good forums to turn to. And so I Google.

Here is where I will post my foibles, syntax conquests, solutions, and tools. As I branch into other DMBSs and visualization tools I will share my code and links to good resources. This will be my online notebook. I hope you’ll contribute.