Subject | SIGN UDF vs CASE |
---|---|
Author | Rick Debay |
Post date | 2006-02-08T17:45:35Z |
The SIGN version takes over four times longer than the CASE version. I
would expect a small difference, but not that much.
/* FIELD_2 is never zero */
/* query uses one SUM or the other, not both */
SELECT
FIELD_1,
SUM(CASE WHEN FIELD_2 > 0 THEN 1 ELSE -1 END)
SUM( SIGN(FIELD_2) )
FROM
TABLE_1
GROUP BY
FIELD_1
would expect a small difference, but not that much.
/* FIELD_2 is never zero */
/* query uses one SUM or the other, not both */
SELECT
FIELD_1,
SUM(CASE WHEN FIELD_2 > 0 THEN 1 ELSE -1 END)
SUM( SIGN(FIELD_2) )
FROM
TABLE_1
GROUP BY
FIELD_1