3 ms·
I mostly use CROSS JOIN when I need "one of every combination". Suppose we have a log of events, and each event has a user, a date, and an event action associa
by Xcelerate 5y ago
I mostly use CROSS JOIN when I need "one of every combination".
Suppose we have a log of events, and each event has a user, a date, and an event action associated with it. Now I want to transform this log into a tabulation, where I have one record for each combination of user, date, and action type with an associated count. I can do something like:
WITH tabulation AS (
SELECT
user,
action,
date,
COUNT(*) AS num_events
FROM events
GROUP BY
user,
action,
date
),
combinations AS (
SELECT
users.user,
actions.action,
dates.date
FROM (SELECT DISTINCT user FROM tabulation) users
CROSS JOIN (SELECT DISTINCT action FROM tabulation) actions
CROSS JOIN UNNEST(
SEQUENCE(
(SELECT MIN(date) FROM tabulation),
(SELECT MAX(date) FROM tabulation)
)
) AS dates(date)
)
SELECT
user,
action,
date,
COALESCE(num_events, 0) AS num_events
FROM combinations
LEFT JOIN tabulation USING (user, action, date)
- twic 5y ago+1 for being the only comment so far to mention USING!