4 ms·

I came to this page looking for ways to parameterize and re-use raw SQL. with (and create view for multiple queries) fulfill the case where the unit of reuse is

I came to this page looking for ways to parameterize and re-use raw SQL. with (and create view for multiple queries) fulfill the case where the unit of reuse is a filtered view on one or more tables.

rikkipitt 9y ago
Great timing for me on this, thanks for sharing!
- mshenfield 9y ago
  np!
teej 9y ago
To make progress on this problem, I wrote a light SQL preprocessor to support INCLUDE statements. It enables me to write code like this: WITH frequently_bought_together AS ( INCLUDE frequently_bought_together.sql ) SELECT ... This allows way better isolation and reuse of business logic than before. In Redshift, I combine this with an assert user-defined function to enable writing unit tests in raw SQL. With all that together, I can trust analysts to update complex data assets and I can ask them to take any data issue investigation they've done and turn it into a re-usable test. Tests end up looking like: CREATE TEMPORARY TABLE frequently_bought_together AS INCLUDE frequently_bought_together.sql ; SELECT f_assert(COUNT(*) > 0, 'Table is empty'); SELECT f_assert(COUNT(DISTINCT item_bought || item_recommended) = COUNT(*), 'Table is fanned out'); ... It has made a huge difference in how we write SQL.
- mistermann 9y ago
  It warms my heart to see people in the wild doing smart things like this (after getting laughed at by moron managers who wouldn't authorize any good ideas like this for years).
- mshenfield 9y ago
  It took me a second, but I can see why this would be handy. Whether you just use it in a with, or create a view from it, you have a single definition of the query that anyone can use. And you can test the INCLUDEd query. Neat.
zamalek 9y ago
With SQL Server you can create TVFs (table-valued functions). So long as they are 'inline' (lacking BEGIN and END) they will be inlined into any query that uses them. If your SQL fu is up to scratch they are extremely powerful - e.g. CROSS APPLYing them is one of the best ways I've seen to do reusable row-level filtering.
- Semaphor 9y ago
  Would you happen to have a link that goes into further detail?

I came to this page looking for ways to parameterize and re-use raw SQL. with (and create view for multiple queries) fulfill the case where the unit of reuse is a filtered view on one or more tables.

rikkipitt 9y ago

Great timing for me on this, thanks for sharing!

mshenfield 9y ago
np!

teej 9y ago

To make progress on this problem, I wrote a light SQL preprocessor to support INCLUDE statements. It enables me to write code like this: WITH frequently_bought_together AS ( INCLUDE frequently_bought_together.sql ) SELECT ... This allows way better isolation and reuse of business logic than before. In Redshift, I combine this with an assert user-defined function to enable writing unit tests in raw SQL. With all that together, I can trust analysts to update complex data assets and I can ask them to take any data issue investigation they've done and turn it into a re-usable test. Tests end up looking like: CREATE TEMPORARY TABLE frequently_bought_together AS INCLUDE frequently_bought_together.sql ; SELECT f_assert(COUNT(*) > 0, 'Table is empty'); SELECT f_assert(COUNT(DISTINCT item_bought || item_recommended) = COUNT(*), 'Table is fanned out'); ... It has made a huge difference in how we write SQL.

mistermann 9y ago
It warms my heart to see people in the wild doing smart things like this (after getting laughed at by moron managers who wouldn't authorize any good ideas like this for years).
mshenfield 9y ago
It took me a second, but I can see why this would be handy. Whether you just use it in a with, or create a view from it, you have a single definition of the query that anyone can use. And you can test the INCLUDEd query. Neat.

zamalek 9y ago

With SQL Server you can create TVFs (table-valued functions). So long as they are 'inline' (lacking BEGIN and END) they will be inlined into any query that uses them. If your SQL fu is up to scratch they are extremely powerful - e.g. CROSS APPLYing them is one of the best ways I've seen to do reusable row-level filtering.

Semaphor 9y ago
Would you happen to have a link that goes into further detail?