Search
Document Center

MaxCompute:SHIFTRIGHT

Last Updated:Jul 24, 2023

Shifts a value right by a specific number of places (>>). This function is an additional function of MaxCompute V2.0.

Syntax

``````int shiftright(tinyint|smallint|int <number1>, int <number2>)
bigint shiftright(bigint <number1>, int <number2>)``````

Parameters

• number1: required. The value is of the TINYINT, SMALLINT, INT, or BIGINT type.

• number2: required. The value is of the INT type.

Return value

A value of the INT or BIGINT type is returned. The return value varies based on the following rules:

• If number1 is not of the TINYINT, SMALLINT, INT, or BIGINT type, an error is returned.
• If number2 is not of the INT type, an error is returned.
• If the value of number1 or number2 is null, null is returned.

Sample data

This section provides sample source data and examples for you to understand how to use the functions. In this topic, a table named mf_math_fun_t is created and data is inserted into the table. Sample statements:

``````create table if not exists mf_math_fun_t(
int_data     int,
bigint_data  bigint,
double_data  double,
decimal_data decimal,
float_data   float,
string_data  string
);
insert into mf_math_fun_t values
(null, -10, 0.525, 0.525BD, cast(0.525 as float), '10'),
(-20, null, -0.1, -0.1BD, cast(-0.1 as float), '-10'),
(0, -1, null, 20.45BD, cast(-1 as float), '30'),
(-40, 4, 0.89, null, cast(0.89 as float), '-30'),
(5, -50, -1, -1BD, null, '50'),
(-60, 6, 1.5, 1.5BD, cast(1.5 as float), '-50'),
(-1, -70, -7.5, -7.5BD, cast(-7.5 as float),null ),
(-80, 1, -10.2, -10.2BD, cast(-10.2 as float), '-1' ),
(9, -90, 2.58, 2.58BD, cast(2.58 as float), '0'),
(-100, 10, -5.8, -5.8BD, cast(-5.8 as float), '-90');``````

Query data from the mf_math_fun_t table. Sample statement:

``````select * from mf_math_fun_t;
-- The following result is returned:
+------------+-------------+-------------+--------------+------------+-------------+
| int_data   | bigint_data | double_data | decimal_data | float_data | string_data |
+------------+-------------+-------------+--------------+------------+-------------+
| NULL       | -10         | 0.525       | 0.525        | 0.525      | 10          |
| -20        | NULL        | -0.1        | -0.1         | -0.1       | -10         |
| 0          | -1          | NULL        | 20.45        | -1.0       | 30          |
| -40        | 4           | 0.89        | NULL         | 0.89       | -30         |
| 5          | -50         | -1.0        | -1           | NULL       | 50          |
| -60        | 6           | 1.5         | 1.5          | 1.5        | -50         |
| -1         | -70         | -7.5        | -7.5         | -7.5       | NULL        |
| -80        | 1           | -10.2       | -10.2        | -10.2      | -1          |
| 9          | -90         | 2.58        | 2.58         | 2.58       | 0           |
| -100       | 10          | -5.8        | -5.8         | -5.8       | -90         |
+------------+-------------+-------------+--------------+------------+-------------+``````

Example: static data

``````-- The return value is 1. The following statement shifts the binary value of 4 two places to the right (4>>2, 0100 shifted to 0001).
select shiftright(4,2);
-- The return value is 4. The following statement shifts the binary value of 32 three places to the right (32>>3, 100000 shifted to 0100).
select shiftright(32,3);
-- The return value is null.
select shiftright(null,3);``````

Example: table data

Shift numbers in the int_data and bigint_data columns right by a specific number of places based on the Sample data. Sample statement:

``````-- Enable the MaxCompute V2.0 data type edition. Commit this command along with SQL statements.
set odps.sql.type.system.odps2=true;
select shiftright(int_data, 1) as int_new, shiftright(bigint_data, 1) as bigint_new from mf_math_fun_t;``````

The following result is returned:

``````+---------+------------+
| int_new | bigint_new |
+---------+------------+
| NULL    | -5         |
| -10     | NULL       |
| 0       | -1         |
| -20     | 2          |
| 2       | -25        |
| -30     | 3          |
| -1      | -35        |
| -40     | 0          |
| 4       | -45        |
| -50     | 5          |
+---------+------------+``````

Related functions

SHIFTRIGHT is a mathematical function. For more information about functions related to data computing and conversion, see Mathematical functions.