0

I'm relatively new to SQL and I find my self wanting macros in SQL a lot.

Consider:

SELECT
  session_id,
  user_id,
  hitnumber,
  IF(
    eventinfo.eventcategory = 'search' AND eventinfo.eventaction = 'RunSearch',
    TRUE, FALSE
  ) issued_search,
FROM google_analytics.hits
JOIN (
  SELECT
    session_id
  FROM google_analytics.hits
  WHERE eventinfo.eventcategory = 'search' AND eventinfo.eventaction = 'RunSearch'
  GROUP BY session_id
) sessions_with_search ON hits.session_id = sessions_with_search.session_id

Note that eventinfo.eventcategory = 'search' AND eventinfo.eventaction = 'RunSearch' is repeated twice in the query. This clause represents rows that are search rows. Soon I will want to expand the notion of search row to include a very long list of OR'ed clauses that look like this. I don't want to repeat this in 2 places. Moreover I think it's a good source of bugs to repeat the same SQL in 2 places – I might change one place and not the other without thinking about it.

I want a notion of a macro, but to my knowledge these don't exist. I want to be able to specify this above the query

SEARCH_HIT=`eventinfo.eventcategory = 'search' AND eventinfo.eventaction = 'RunSearch'`

And then the query above would become:

SELECT
  session_id,
  user_id,
  hitnumber,
  IF(@SEARCH_HIT, TRUE, FALSE) issued_search,
FROM google_analytics.hits
JOIN (
  SELECT
    session_id
  FROM google_analytics.hits
  WHERE @SEARCH_HIT
  GROUP BY session_id
) sessions_with_search ON hits.session_id = sessions_with_search.session_id

The query is already cleaner but imagine how much clearer and more maintainable the macro makes the query when @SEARCH_HIT is 20 clauses long.

Questions

  1. Do SQL macros exist in any IDEs?
  2. Do SQL macros exist in any SQL languages?
  3. Do SQL macros exist in any Presto SQL in particular?
  4. If macros do not exist then why not? And what do we replace them with for the sake of query maintainability?
JnBrymn
  • 24,245
  • 28
  • 105
  • 147
  • Possible duplicate of [SQL Scripts - Does the equivalent of a #define exist?](https://stackoverflow.com/q/9238978/11683) – GSerg Nov 30 '18 at 17:01
  • There are two ways this can generally be done: user defined functions and table containing the fields to match against. The first would encapsulate the logic the second would mean joining once on the criteria you are looking at. Either option eliminates the multiple checks on the literal values. Function would probably be the most straight forward; pass in the two fields and a boolean value is returned. Not providing as an answer as I've not used Presto – UnhandledExcepSean Nov 30 '18 at 17:52
  • 1
    @JnBrymn . . . Macros do not exist in SQL, presumably this is part of some WYSIWYG philosophy. SQL can be used in conjunction with most programming languages, and many have macros. For instance, it is often used with Python. – Gordon Linoff Nov 30 '18 at 19:34
  • 3
    Did you check Presto's `PREPARE` statement (https://prestodb.io/docs/current/sql/prepare.html)? – Piotr Findeisen Nov 30 '18 at 20:33

1 Answers1

-1

You are describing dynamic sql. The SQL engine does not interpret variables as executable code. There are a lot of articles on how to construct a query string by string.

Gabriel H
  • 89
  • 6