Data Warehouse Time Dimension

The first question is answered by recalling the fundamental reason for wanting dimension tables in a data mart. The dimension tables serve as the source of constraints and as the source of report row headers.


Drop table dim_time;

CREATE TABLE dim_time
( pk_time integer NOT NULL,
time_time time without time zone,
time_hour smallint,
time_minute smallint,
time_second smallint,
time_12hr character(8),
time_24hr character(8),
CONSTRAINT pk_dim_time PRIMARY KEY (pk_time)
)
WITH (
OIDS=FALSE
);
ALTER TABLE dim_time
OWNER TO postgres;

insert into dim_time
(
pk_time
, time_time
, time_hour
, time_minute
, time_second
, time_12hr
, time_24hr
)
select
NULLIF(to_char(time_hour, '00')||
trim(to_char(time_minute, '00'))||
trim(to_char(time_second, '00')), '')::int as pk_time
, time_time
, time_hour
, time_minute
, time_second
, to_char(time_time, 'HH12:MI:SS') as time_12hr
, to_char(time_time, 'HH24:MI:SS') as time_24hr
from
(
select
TIMESTAMP WITHOUT TIME ZONE 'epoch' +
((60*60*time_hour) + (60* time_minute)
+ time_second) * INTERVAL '1 second' as time_time
, time_hour
, time_minute
, time_second

from
generate_series(0,23) as time_hour
, generate_series(0,59) as time_minute
, generate_series(0,59) as time_second
) as t
order by
time_hour,
time_minute,
time_second

Explain Extended: SQL Explained

This series of articles is inspired by multiple questions asked by the site visitors and Stack Overflow users, including Tony, Philip, Rexem and others.

Which method (NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL) is best to select values present in one table but missing in another one?

This:

view sourceprint?
1.SELECT  l.*
2.FROM    t_left l
3.LEFT JOIN
4.t_right r
5.ON      r.value = l.value
6.WHERE   r.value IS NULL

, this:

view sourceprint?
1.SELECT  l.*
2.FROM    t_left l
3.WHERE   l.value NOT IN
4.(
5.SELECT  value
6.FROM    t_right r
7.)

or this:

view sourceprint?
1.SELECT  l.*
2.FROM    t_left l
3.WHERE   NOT EXISTS
4.(
5.SELECT  NULL
6.FROM    t_right r
7.WHERE   r.value = l.value
8.)

 

Modern SQL: Lateral

Lateral is the “for each” loop of SQL

.. Lateral is great for Top-N subqueries

.. In PostgreSQL “With” view are more like materialized views

With deleted_rows AS (

Delete from source_tbl

Returning *

)

Insert into destination_tbl

Select * from deleted_rows;