I have BIGINT column in my Redshift table, and I want a query that will:
- Count how many times the value '1' appears in each bit position across the binary value in all the rows of this column
- Will show it in a way that I'll be able to take the x top bits_positions.
For example (I'm already writing the integer values as binary to simplify the example):
column
--------
11011110 = 222
00000000 = 0
11111100 = 252
00011000 = 24
11111100 = 252
00011000 = 24
11000010 = 194
76543210 <- bit_position
will return a table like:
bit_position count
0 0
1 2
2 3
3 5
4 5
5 2
6 4
7 4
In this case I'll be able to get the top five bit_position: (3,4,6,7,2)
Note: I'll might have up to 64 bit_positions for a column.
You can use a bit-wise AND
&
to check for each position.Here's an example going across rows:
To have the results in a single column you need to use union:
http://docs.aws.amazon.com/redshift/latest/dg/r_OPERATOR_SYMBOLS.html
EDIT: If you would like something more dynamic you will need to look into using a UDF. You could start with my
f_bitwise_to_string
UDF as a template and add what you need from there. https://github.com/awslabs/amazon-redshift-udfs/blob/master/scalar-udfs/f_bitwise_to_string.sql