自動生成されたSQLが妙に複雑になる理由

Exploreで自動生成されるSQLが妙に複雑で長くなることがある

COALESCE、CAST、MD5など、見慣れない関数がたくさん・・・

理由

対称集計をおこなうときに、ハッシュジョインを使っているため

code: sample.sql

SELECT

order_items.order_id AS "order_items.order_id",

order_items.sale_price AS "order_items.sale_price",

(COALESCE(CAST( ( SUM(DISTINCT (CAST(FLOOR(COALESCE(users.age ,0)

*(1000000*1.0)) AS DECIMAL(38,0))) +

CAST(STRTOL(LEFT(MD5(CONVERT(VARCHAR,users.id )),15),16) AS DECIMAL(38,0))

* 1.0e8 + CAST(STRTOL(RIGHT(MD5(CONVERT(VARCHAR,users.id )),15),16) AS DECIMAL(38,0)) )

- SUM(DISTINCT CAST(STRTOL(LEFT(MD5(CONVERT(VARCHAR,users.id )),15),16) AS DECIMAL(38,0))

* 1.0e8 + CAST(STRTOL(RIGHT(MD5(CONVERT(VARCHAR,users.id )),15),16) AS DECIMAL(38,0))) ) AS DOUBLE PRECISION)

/ CAST((1000000*1.0) AS DOUBLE PRECISION), 0)

/ NULLIF(COUNT(DISTINCT CASE WHEN users.age IS NOT NULL THEN users.id

ELSE NULL END), 0)) AS "users.average_age

FROM order_items AS order_items

LEFT JOIN users AS users ON order_items.user_id = users.id

GROUP BY 1,2

ORDER BY 3 DESC

LIMIT 500