I went looking for the gospel, and lost my religion.
Wow. Seems so obvious now, but it never occurred to me. I am saved!
Hey, so like, how would you handle window statements? Example below from mode.com:
SELECT start_terminal,
duration_seconds,
LAG(duration_seconds, 1) OVER
(PARTITION BY start_terminal ORDER BY duration_seconds) AS lag,
LEAD(duration_seconds, 1) OVER
(PARTITION BY start_terminal ORDER BY duration_seconds) AS lead
FROM tutorial.dc_bikeshare_q1_2012
WHERE start_time < '2012-01-08'
ORDER BY start_terminal, duration_seconds
Seems to meet the interpretability requirements, but the partition clause placement hurts my eyes ever so slightly
I'd probably put it all on one line, and then line everything up across the two window functions: https://gist.github.com/bstancil/a1d2d42a6712c4df02cd2acda92a1063
It gets tricky when there are multiple fields in the PARTITION and ORDER clauses, because then the line can get unreasonably long. So I'm not sure what I'd do in those cases, and honestly, I don't think I'd have a consistent answer.
SELECT
start_terminal
,duration_seconds
,LAG(duration_seconds, 1) OVER ( PARTITION BY start_terminal
ORDER BY duration_seconds
) AS lag
,LEAD(duration_seconds, 1) OVER ( PARTITION BY start_terminal
) AS lead
WHERE
start_time < '2012-01-08'
messes up my formatting
The line breaks in the window functions are always what get me. I can never decide what I actually like for that.
Wow. Seems so obvious now, but it never occurred to me. I am saved!
Hey, so like, how would you handle window statements? Example below from mode.com:
SELECT start_terminal,
duration_seconds,
LAG(duration_seconds, 1) OVER
(PARTITION BY start_terminal ORDER BY duration_seconds) AS lag,
LEAD(duration_seconds, 1) OVER
(PARTITION BY start_terminal ORDER BY duration_seconds) AS lead
FROM tutorial.dc_bikeshare_q1_2012
WHERE start_time < '2012-01-08'
ORDER BY start_terminal, duration_seconds
Seems to meet the interpretability requirements, but the partition clause placement hurts my eyes ever so slightly
I'd probably put it all on one line, and then line everything up across the two window functions: https://gist.github.com/bstancil/a1d2d42a6712c4df02cd2acda92a1063
It gets tricky when there are multiple fields in the PARTITION and ORDER clauses, because then the line can get unreasonably long. So I'm not sure what I'd do in those cases, and honestly, I don't think I'd have a consistent answer.
SELECT
start_terminal
,duration_seconds
,LAG(duration_seconds, 1) OVER ( PARTITION BY start_terminal
ORDER BY duration_seconds
) AS lag
,LEAD(duration_seconds, 1) OVER ( PARTITION BY start_terminal
ORDER BY duration_seconds
) AS lead
FROM tutorial.dc_bikeshare_q1_2012
WHERE
start_time < '2012-01-08'
ORDER BY start_terminal, duration_seconds
messes up my formatting
The line breaks in the window functions are always what get me. I can never decide what I actually like for that.