What’s the best query trick you are using?

exa-MathiasHo
Community Manager
Community Manager

Hi everyone,

We at Exasol often have a good understanding of what best practices are for our users. However, oftentimes, the real value is only seen by you and sometimes only develops over time. This is why we are interested in hearing what your best query trick is.

It could be a query trick, which only has a small impact on one query but performed often is almost invaluable to you. Or it is a practice you use every day or you use it one-time only with great impact. 

What is your best query trick?


Best regards,

Mathias

8 REPLIES 8

mwellbro
Xpert

Hmmm, I guess there´s one thing above most others: using the SQL pre-processor to bend unruly SQLs to the will of the optimizer 😉

exa-Franz
Team Exasol
Team Exasol

Hehe, @mwellbro opening the box of pandora right in the first reply, I like the vibe 😁
You are right, it is so powerful, every advanced Exasol user should know about it.

I am not sure if it is a good idea to do so with every other database technology, but this "best practice" can bring some order into bigger, self-written  statements - and even front ends like Microstrategy can generate it:
I am talking about common table expressions (CTE, with-clause).
Reason: This approach is structuring your query so that someone else can understand it easily, and you don't get lost yourself in your approach to write a query.
Especially if you use multiple of them at the beginning of a statement.
My template:

WITH
temp_01 AS
(
    SELECT ...
),
temp_02 AS
(
    SELECT [...] from temp_01 JOIN [...]
),
temp_03 AS
(
    SELECT [...] from temp_02 GROUP BY [...]
)
SELECT [...];


In the below article from @exa-Nico, last example. Note they are in fact different from "named subselects":
https://community.exasol.com/t5/database-features/multi-path-sql-tables-vs-views-vs-subselects/ta-p/...

exa-Chris
Community Manager
Community Manager
Sounds good, can you share an example?
Connecting Customers, Partners, Prospects and Exasolians is my passion. Apart from that I cycle, listen to music, and try to understand what all those technical discussions really mean...

exa-SebastianB
Team Exasol
Team Exasol

EXPLAIN VIRTUAL uses the VS adapter to rewrite the original query before feeding it into the ExaLoader via IMPORT.

There are other, rarer cases, but let's focus on this one. If EXPLAIN VIRTUAL does not succeed, you know that the problem is either the connection to the remote system (80% of all cases) or the adapter itself. You can pretty much rule out the ExaLoader in this case. Except in rare situations where that has an extra connection problem --but that is a story for another article.

If EXPLAIN VIRTUAL succeeds on the other hand, you get the rewritten query, which you can then review.

If it is fine, you have a problem in the ExaLoader. If the query looks strange, it is the Virtual Schema adapter's fault. So you can do a lot of debugging with that simple trick without having to pull a single log file. Only if all those investigations are inconclusive, you really need to start digging deeper.

ADoerr
SQL-Fighter

It's not really a query trick, since it's actually just one of my favourite exasol features.

But the local  alias is just soooo useful, especially when using it with analytical functions.

Being able to directly use the result of an analytical function or UDF in the same SELECT statement saved me hundreds of

lines of SQL.

 

WITH base AS
(
SELECT 'a' id_column, 112 number_column  UNION ALL
SELECT 'a' id_column, 134 number_column  UNION ALL
SELECT 'b' id_column, 11 number_column  UNION ALL
SELECT 'b' id_column, 232 number_column  UNION ALL
SELECT 'b' id_column, 242 number_column  UNION ALL
SELECT 'b' id_column, 223 number_column 
)
SELECT
	id_column,
	number_column,
	lag(number_column) OVER (PARTITION BY id_column ORDER BY number_column) number_column_before,
	number_column - LOCAL.number_column_before difference2before
FROM 
	base;

 

 

exa-Nico
Community Manager
Community Manager

Maybe not a feature, but you can determine the timestamp that a session was opened by using the following query:

SELECT FROM_POSIX_TIME(<session ID>/1024/1024/1000);

Really helps to determine what day logs might be needed

Sports Enthusiast. Database Guy. Member of Team Exasol.
Having trouble? Just let me know!

Please Give Kudos if you find this post useful and also mark this as Accepted Solution if this is the required solution

PeterK
Xpert

A UDF script that I find quite handy for simplifying some SQL is:

CREATE LUA SET SCRIPT sum_distinct(...) RETURNS DOUBLE AS
-- This aggregation function takes 2 arguments.
-- The first argument is a number to be summed up,
-- but the number is only summed once per unique second argument.
-- Given the following data:
--  ID  VAL
--  --- ----
--   a   1
--   a   1
--   b   2
--
-- then: SELECT sum_distinct(val, id) 
-- would return: 1 + 2 = 3 rather than 1 + 1 + 2 = 4
--
-- This is useful for grouped statements where extra joins
-- may cause underlying duplicate rows which would otherwise
-- mess up regular SUMs. With the right choice of the second
-- argument the SUM will ignore the duplicate rows.
--
-- This avoids having to do multiple levels of grouping via subqueries.
--
function run(ctx)
    local sum
    local rows_seen = {}
    repeat
        if ctx[1] ~= NULL and
           ctx[2] ~= NULL and
           rows_seen[tostring(ctx[2])] ~= 1
        then
            if sum == nil then sum = 0 end
            sum = sum + ctx[1]
            rows_seen[tostring(ctx[2])] = 1
        end
    until not ctx.next()
    return sum
end
/

 As the comment mentions, this is handy in cases where duplicate rows (due to extra one-to-many joins) causes a regular SUM() to be wrong. Usually this requires sub-queries with multiple levels of grouping but with this UDF you can avoid the subqueries and sum at any level of grouping you wish.

Speaking of wishing, I wish Exasol had this as a native function. 😉

exa-Chris
Community Manager
Community Manager
Right place to ask for this
Connecting Customers, Partners, Prospects and Exasolians is my passion. Apart from that I cycle, listen to music, and try to understand what all those technical discussions really mean...