CAST written twice, in the SELECT and in the WHERE statement-CodePudding

SELECT CAST(purchase_price AS FLOAT64)
FROM customer_data.customer_purchase
ORDER BY CAST(purchase_price AS FLOAT64) DESC

This is what someone wrote, and it's working fine. Is it necessary to have CAST twice? Why is that so? Thank you!

I tried removing CAST from the SELECT statement, and it produced the same result. So I'm confused as to why someone would write it twice.

CodePudding user response：

Most database support refering to column alias in the ORDER BY clause. So you can just alias that column, so you can then order by it:

SELECT CAST(purchase_price AS FLOAT64) purchase_price_float
FROM customer_data.customer_purchase 
ORDER BY purchase_price_float DESC

One might question why you need to CAST in the first place:

Maybe you are storing this value as a string? If so, fixing your design should be the first goal to pursue
On the other hand, if you already have a numeric value, you probably can use the original value to sort rather than the result of the CAST - which may be more efficient:

SELECT CAST(purchase_price AS FLOAT64) purchase_price_float
FROM customer_data.customer_purchase 
ORDER BY purchase_price DESC

If you are going to need this expression in several spots in your query (including the where clause maybe), then it would be simpler to do the computation in a subquery first:

SELECT *
FROM (
    SELECT p.*, CAST(purchase_price AS FLOAT64) purchase_price_float
    FROM customer_data.customer_purchase p
) p
WHERE purchase_price_float < 10     -- filter against a numeric value
ORDER BY purchase_price_float DESC  -- sort

Again: this is highly inefficient; the database must CAST all values in the string column before it is able to filter or order by it, defeating a lot of potential optimizations (indexing, ...).

CodePudding user response：

If you don't want to cast twice and want using the converted value in the order by and where clause, you can use a subquery :

SELECT 
  purchase_price_float
FROM
(
  SELECT CAST(purchase_price AS FLOAT64) purchase_price_float
  FROM customer_data.customer_purchase 
)
WHERE purchase_price_float = ..
ORDER BY purchase_price_float DESC

CodePudding user response：

Another option you can use when you don't want to repeat casting or defining calculated column is to use UNNEST(). Cross-joinging with a flattened array with just one element will do the trick.

SELECT purchase_price_float
  FROM customer_data.customer_purchase, UNNEST([CAST(purchase_price AS FLOAT64)]) purchase_price_float
 WHERE purchase_price_float > 0
 ORDER BY purchase_price_float;