cross join to get all dates and hours and avoid duplicate values

Question

We have 2 tables:

sales
hourt (only 1 field (hourt) of numbers: 0 to 23)

The goal is to list all dates and all 24 hours for each day and group hours that have sales. For hours that do not have sales, zero will be shown.

This query cross joins the sales table with the hourt table and does list all dates and 24 hours. However, there are also many duplicate rows. How can we avoid the duplicates?

We're using Amazon Redshift (based on Postgres 8.0).

with h as (
SELECT
    a.purchase_date,
    CAST(DATE_PART("HOUR", AT_TIME_ZONE(AT_TIME_ZONE(CAST(a.purchase_date AS 
    DATETIME), "0:00"), "PST")) as INTEGER) AS Hour,
    COUNT(a.quantity) AS QtyCount,
    SUM(a.quantity) AS QtyTotal,
    SUM((a.price) AS Price
FROM sales a    
GROUP BY CAST(DATE_PART("HOUR", 
AT_TIME_ZONE(AT_TIME_ZONE(CAST(a.purchase_date AS DATETIME), "0:00"), 
"PST")) as INTEGER), 
DATE_FORMAT(AT_TIME_ZONE(AT_TIME_ZONE(CAST(a.purchase_date AS DATETIME), 
"0:00"), "PST"), "yyyy-MM-dd")
ORDER by a.purchase_date
),
hr as (
     SELECT
          CAST(hourt AS INTEGER) AS hourt
     FROM hourt
),
joined as (
     SELECT
          purchase_date,
          hourt,
          QtyCount,
          QtyTotal,
          Price
     FROM h
     cross JOIN hr
)
SELECT *
     FROM joined
Order by purchase_date,hourt

Sample Tables:

Before the cross join, query returned correct sales and grouped hours, as seen in the below table.

Desired results table:

Joe Harris Joe Harris · Accepted Answer · 2018-09-27T18:15:08

Need to create a series of all the hour values and left join your data back to that. Comments inline explain the logic.

WITH data AS (-- Do the basic aggregation first
    SELECT DATE_TRUNC('hour',a.purchase_date) purchase_hour --Truncate timestamp to the hour is simpler
        ,COUNT(a.quantity) AS QtyCount
        ,SUM(a.quantity)   AS QtyTotal
        ,SUM((a.price)     AS Price
    FROM sales a
    GROUP BY DATE_TRUNC('hour',a.purchase_date)
    ORDER BY DATE_TRUNC('hour',a.purchase_date)
    --           SELECT '2017-01-13 12:00:00'::TIMESTAMP purchase_hour, 1 qty_count, 1 qty_total, 119 price
    -- UNION ALL SELECT '2017-01-13 15:00:00'::TIMESTAMP purchase_hour, 1 qty_count, 1 qty_total, 119 price
    -- UNION ALL SELECT '2017-01-14 21:00:00'::TIMESTAMP purchase_hour, 1 qty_count, 1 qty_total, 119 price
    )
,time_range AS (--Calculate the start and end **date** values
    SELECT DATE_TRUNC('day',MIN(purchase_hour))   start_date
         , DATE_TRUNC('day',MAX(purchase_hour))+1 end_date
     FROM data
    )
,hr AS (--Generate all hours between start and end
    SELECT (SELECT start_date
            FROM time_range
            LIMIT 1) --Limit 1 so the optimizer knows it's not a correlated subquery
           + ((n-1) --Make the series start at zero so we don't miss the starting value
             * INTERVAL '1 hour') AS "hour"
    FROM (SELECT ROW_NUMBER() OVER () n
         FROM stl_query --Can use any table here as long as it enough rows
         LIMIT 100) series
    WHERE "hour" < (SELECT end_date FROM time_range LIMIT 1)
    )
--Use NVL to replace missing values with zeroes
SELECT hr.hour                AS purchase_hour --Timestamp like `2017-01-13 12:00:00`
     , NVL(data.qty_count, 0) AS qty_count
     , NVL(data.qty_total, 0) AS qty_total
     , NVL(data.price, 0)     AS price
FROM hr
LEFT JOIN data
ON hr.hour = data.purchase_hour
ORDER BY hr.hour
;

cross join to get all dates and hours and avoid duplicate values

2 Answers