问题描述
我想查询转换,如:
SELECT BoolA, BoolB, BoolC, BoolD FROM MyTable;
向一个位掩码,其中该位被上述的值所定义
Into a bitmask, where the bits are defined by the values above.
例如,如果 BoolA
和摄入
是真的,我愿意 1001
或 9
。
For example, if BoolA
and BoolD
were true, I'd want 1001
or 9
.
我有东西在头脑的效果:
I have something in mind to the effect of:
SELECT
CASE WHEN BoolD THEN 2^0 ELSE 0 END +
CASE WHEN BoolC THEN 2^1 ELSE 0 END +
CASE WHEN BoolB THEN 2^2 ELSE 0 END +
CASE WHEN BoolA THEN 2^3 ELSE 0 END
FROM MyTable;
但我不知道这是最好的方法,似乎相当冗长。有没有一种简单的方法来做到这一点?
But I'm not sure if this is the best approach and seems rather verbose. Is there an easy way to do this?
推荐答案
有关的位掩码,类型<$c$c>bitstring会是更好的选择。可能看起来像这样那么:
For a bitmask, the type bitstring
would be the better choice. Could look like this then:
SELECT BoolD::int::bit
|| BoolC::int::bit
|| BoolB::int::bit
|| BoolA::int::bit
FROM tbl;
TRUE
转换成 1
, FALSE
到 0
。你可以简单地串联位位串。
TRUE
converts to 1
, FALSE
to 0
. You can simply concatenate bits to a bitstring.
看来你需要一个整数
的结果 - 有一个简单的&安培;快捷方式:
It seems you need an integer
as result - there is a simple & fast way:
SELECT (BoolD::int::bit
|| BoolC::int::bit
|| BoolB::int::bit
|| BoolA::int::bit)::bit(4)::int
FROM tbl;
请务必阅读的章节中印刷精美的位串函数和操作符的手册。
我想出了两个想法,并用10k行组建了一个快速测试/参考总括起来。
I came up with two more ideas and put together a quick test / reference with 10k rows to sum it all up.
测试设置:
CREATE TEMP TABLE t (boola bool, boolb bool, boolc bool, boold bool);
INSERT INTO t
SELECT random()::int::bool
, random()::int::bool
, random()::int::bool
, random()::int::bool
FROM generate_series(1,10000);
演示:
SELECT CASE WHEN boold THEN 1 ELSE 0 END
+ (CASE WHEN boolc THEN 1 ELSE 0 END << 1)
+ (CASE WHEN boolb THEN 1 ELSE 0 END << 2)
+ (CASE WHEN boola THEN 1 ELSE 0 END << 3) AS andriy
, boold::int
+ (boolc::int << 1)
+ (boolb::int << 2)
+ (boola::int << 3) AS mike
, (boola::int::bit
|| boolb::int::bit
|| boolc::int::bit
|| boold::int::bit)::bit(4)::int AS erwin1
, boold::int
| (boolc::int << 1)
| (boolb::int << 2)
| (boola::int << 3) AS erwin2
, (((
boola::int << 1)
| boolb::int << 1)
| boolc::int << 1)
| boold::int AS erwin3
FROM t
LIMIT 15
您也可以用位或 |
而不是 +
运营结果。
个人测试运行显示基本的相同的性能所有五个方法。
You could also use a bitwise OR |
instead of the +
operator.
Individual test runs show basically the same performance for all five methods.
这篇关于我一堆布尔列转换为PostgreSQL的一个位图?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!