Подсчет количества значений "1" в каждой битовой позиции в столбце Redshift

Question

У меня есть столбец BIGINT в моей таблице Redshift, и я хочу запрос, который будет:

1. Подсчитайте, сколько раз значение "1" появляется в каждой позиции бита в двоичном значении во всех строках этого столбца.
2. Покажу это так, что я смогу принять x top bits_positions.

Например (я уже пишу целочисленные значения в двоичном виде, чтобы упростить пример):

column
--------
11011110  = 222
00000000  = 0
11111100  = 252
00011000  = 24
11111100  = 252
00011000  = 24
11000010  = 194

76543210 <- bit_position

вернет таблицу как:

bit_position   count
0              0
1              2
2              3
3              5
4              5
5              2
6              4
7              4

В этом случае я смогу получить первую пятерку bit_position: (3,4,6,7,2)

Примечание: у меня может быть до 64 bit_positions для столбца.

Answer 1

Вы можете использовать немного И & проверить для каждой позиции.

Вот пример, идущий по строкам:

SELECT SUM(CASE WHEN bit_col & 64 > 0 THEN 1 ELSE 0 END) "1000000"
     , SUM(CASE WHEN bit_col & 32 > 0 THEN 1 ELSE 0 END) "0100000"
     , SUM(CASE WHEN bit_col & 16 > 0 THEN 1 ELSE 0 END) "0010000"
     , SUM(CASE WHEN bit_col & 8 > 0 THEN 1 ELSE 0 END)  "0001000"
     , SUM(CASE WHEN bit_col & 4 > 0 THEN 1 ELSE 0 END)  "0000100"
     , SUM(CASE WHEN bit_col & 2 > 0 THEN 1 ELSE 0 END)  "0000010"
     , SUM(CASE WHEN bit_col & 1 > 0 THEN 1 ELSE 0 END)  "0000001"
FROM my_table
;
 1000000 | 0100000 | 0010000 | 0001000 | 0000100 | 0000010 | 0000001
---------+---------+---------+---------+---------+---------+---------
      11 |       8 |      11 |      13 |      11 |       9 |       8

Чтобы получить результаты в одном столбце, вам нужно использовать объединение:

          SELECT 1 AS "col", SUM(CASE WHEN bit_col & 64 > 0 THEN 1 ELSE 0 END) AS bit_count FROM my_table
UNION ALL SELECT 2 AS "col", SUM(CASE WHEN bit_col & 32 > 0 THEN 1 ELSE 0 END) AS bit_count FROM my_table
UNION ALL SELECT 3 AS "col", SUM(CASE WHEN bit_col & 16 > 0 THEN 1 ELSE 0 END) AS bit_count FROM my_table
UNION ALL SELECT 4 AS "col", SUM(CASE WHEN bit_col &  8 > 0 THEN 1 ELSE 0 END) AS bit_count FROM my_table
UNION ALL SELECT 5 AS "col", SUM(CASE WHEN bit_col &  4 > 0 THEN 1 ELSE 0 END) AS bit_count FROM my_table
UNION ALL SELECT 6 AS "col", SUM(CASE WHEN bit_col &  2 > 0 THEN 1 ELSE 0 END) AS bit_count FROM my_table
UNION ALL SELECT 7 AS "col", SUM(CASE WHEN bit_col &  1 > 0 THEN 1 ELSE 0 END) AS bit_count FROM my_table
ORDER BY bit_count DESC
;
 position | bit_count
----------+-----------
        6 |         6
        7 |         6
        4 |         4
        5 |         4
        2 |         0
        3 |         0
        1 |         0

http://docs.aws.amazon.com/redshift/latest/dg/r_OPERATOR_SYMBOLS.html

РЕДАКТИРОВАТЬ: Если вы хотите что-то более динамичное, вам нужно изучить UDF. Вы могли бы начать с моей f_bitwise_to_string UDF в качестве шаблона и добавьте то, что вам нужно оттуда. https://github.com/awslabs/amazon-redshift-udfs/blob/master/scalar-udfs/f_bitwise_to_string.sql