reversed result in bigquery-CodePudding

I have the data like this

date    result  reverse
10-sep  1       4
12-sep  2       3
14-sep  3       2
09-sep  4       1

is it possible to have column reversed without order the date?

CodePudding user response：

We could use a ROW_NUMBER trick here:

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY result) rn1,
              ROW_NUMBER() OVER (ORDER BY result DESC) rn2
    FROM yourTable
)

SELECT t1.date, t1.result, t2.result AS reverse
FROM cte t1
INNER JOIN cte t2
    ON t2.rn2 = t1.rn1
ORDER BY t1.result;

CodePudding user response：

Provided real result data are continious integers as in the sample

select *, max(result) over()   1 - result as reverse
from mytable;

CodePudding user response：

Try this

with _data as
(
select '10-sep' as date_,  1 as result union all
select '12-sep' as date_,  2 as result union all
select '14-sep' as date_,  3 as result union all
select '09-sep' as date_,  4 as result 
)
Select  *,
        count(*) over() - result   1  as _reversed
from _data