1 背景
现在pg15中有一个商超方面的业务环境：
表tab1 中存储了集团sku状态字段v_status 1为可售、0为不可售。约50万数据，每个sku都是唯一。其他字段是sku的一些附属信息，比如价格、规格、供应商等等；
表tab2 中存储了各个分店sku状态字段v_status 1为可售、0为不可售。约100家店，表总共5000万行数据，每个sku+店铺号是唯一。其他字段是分店对应的sku相关信息；
在分店确定sku是否可售时，需要根据：
1）分店sku 可售、集团sku 可售，则分店sku可售
2）分店sku 可售、集团sku 不可售，则分店sku不可售
3）分店sku 不可售、集团sku 可售，则分店sku不可售
4）分店sku 不可售、集团sku 不可售，则分店sku不可售
根据上面条件创建表tab1 、tab12 ；并使用pg_roaringbitmap、intarray、内置位数组 计算，某个分店不可售的sku有哪些？
2 表结构设计与业务逻辑理解
2.1 集团 SKU 状态表 (tab1)
-- 集团 SKU 状态表
CREATE TABLE tab1 (
sku_id INT PRIMARY KEY, -- SKU 唯一标识
v_status SMALLINT NOT NULL, -- 状态 (1=可售, 0=不可售)
price NUMERIC(10,2), -- 价格
supplier TEXT -- 供应商（其他字段省略）
);
-- 插入示例数据（假设 50 万 SKU，其中 80% 可售）
INSERT INTO tab1 (sku_id, v_status,price ,supplier)
SELECT id, CASE WHEN random() < 0.8 THEN 1 ELSE 0 END,id,'jks ssca 大润发 金客隆 盒马 山姆'
FROM generate_series(1, 500000) AS id;
2.2 分店 SKU 状态表 (tab2)
-- 分店 SKU 状态表
CREATE TABLE tab2 (
store_id INT NOT NULL, -- 分店 ID (共 100 家)
sku_id INT NOT NULL, -- SKU 唯一标识
v_status SMALLINT NOT NULL, -- 状态 (1=可售, 0=不可售)
stock INT, -- 库存（其他字段省略）
PRIMARY KEY (store_id, sku_id)
);
-- 插入示例数据（假设每个分店有 50 万 SKU，其中 70% 可售）
INSERT INTO tab2 (store_id, sku_id, v_status)
SELECT
store,
sku,
CASE WHEN random() < 0.7 THEN 1 ELSE 0 END
FROM generate_series(1, 100) AS store, -- 100 家分店
generate_series(1, 500000) AS sku; -- 每个分店 50 万 SKU
2.3 不可售 SKU 查询逻辑
根据规则，分店 SKU 不可售的条件为以下任意一种：
-
分店 SKU 可售，但集团 SKU 不可售。
-
分店 SKU 不可售，无论集团 SKU 状态如何。
即 不可售条件：(分店状态 = 1 AND 集团状态 = 0) OR (分店状态 = 0)。
3 不同技术方案实现
3.1 使用 pg_roaringbitmap
SELECT extname, extversion FROM pg_extension WHERE extname = 'roaringbitmap';CREATE EXTENSION roaringbitmap;
1. 创建位图表（存储可售 SKU）
-- 集团可售 SKU 位图
CREATE MATERIALIZED VIEW mv_group_roaring AS
SELECT rb_build_agg(sku_id) AS bitmap
FROM tab1
WHERE v_status = 1;
-- 分店可售 SKU 位图（以分店 1 为例）
CREATE MATERIALIZED VIEW mv_store1_roaring AS
SELECT store_id,rb_build_agg(sku_id) AS bitmap
FROM tab2
WHERE v_status = 1
group by store_id;
2. 查询分店 1 不可售的 SKU-- 计算不可售 SKU = (分店可售 SKU 位图 AND 集团不可售位图) OR (分店不可售 SKU)
----不可售的sku SELECT rb_iterate( rb_or( rb_and( (SELECT bitmap FROM mv_store1_roaring where store_id=1), rb_andnot(rb_build(ARRAY(SELECT sku_id FROM tab1)), (SELECT bitmap FROM mv_group_roaring)) ), rb_andnot(rb_build(ARRAY(SELECT sku_id FROM tab2 WHERE store_id = 1)), (SELECT bitmap FROM mv_store1_roaring where store_id=1)) ) ) AS sku_id; -----不可售的商品信息 with skuid as (SELECT rb_iterate( rb_or( rb_and( (SELECT bitmap FROM mv_store1_roaring where store_id=1), rb_andnot(rb_build(ARRAY(SELECT sku_id FROM tab1)), (SELECT bitmap FROM mv_group_roaring)) ), rb_andnot(rb_build(ARRAY(SELECT sku_id FROM tab2 WHERE store_id = 1)), (SELECT bitmap FROM mv_store1_roaring where store_id=1)) ) ) AS sku_id) select b.* from skuid a join tab2 b on a.sku_id = b.sku_id where b.store_id = 1;
3.分步拆解
步骤 1：计算条件 1（分店可售但集团不可售）
rb_and(
(SELECT bitmap FROM mv_store1_roaring), -- 分店可售的 SKU 位图
rb_andnot(
rb_build(ARRAY(SELECT sku_id FROM tab1)), -- 所有集团 SKU 的总位图（包括可售和不可售）
(SELECT bitmap FROM mv_group_roaring) -- 集团可售的 SKU 位图
)
)
-
逻辑：
-
rb_build(ARRAY(SELECT sku_id FROM tab1)：构建集团所有 SKU 的完整位图（假设tab1包含所有 SKU）。 -
rb_andnot(集团总位图, 集团可售位图)：得到 集团不可售的 SKU 位图。 -
rb_and(分店可售位图, 集团不可售位图)：筛选出分店可售但集团不可售的 SKU。
-
步骤 2：计算条件 2（分店不可售）
rb_andnot(
rb_build(ARRAY(SELECT sku_id FROM tab2 WHERE store_id = 1)), -- 分店 1 的所有 SKU 位图
(SELECT bitmap FROM mv_store1_roaring) -- 分店可售的 SKU 位图
)
-
逻辑：
-
rb_build(ARRAY(...))：构建分店 1 的所有 SKU 位图（无论是否可售）。 -
rb_andnot(分店总位图, 分店可售位图)：得到 分店不可售的 SKU 位图。
-
步骤 3：合并条件 1 和条件 2
rb_or(条件1位图, 条件2位图)
-
逻辑：将两个条件的结果合并，得到所有不可售的 SKU 位图。
步骤 4：转换为 SKU ID 列表
rb_iterate(...)
-
逻辑：将最终位图转换为可读的 SKU ID 列表。
4.关键函数说明
| 函数名 | 作用 | 示例 |
|---|---|---|
rb_build(ARRAY) |
将数组转换为 Roaring Bitmap | rb_build(ARRAY[1,3,5]) → 位图包含1,3,5 |
rb_and(a, b) |
返回两个位图的交集（a AND b） | rb_and(位图A, 位图B) |
rb_andnot(a, b) |
返回 a 中存在但 b 中不存在的元素（a AND NOT b） | rb_andnot(位图A, 位图B) |
rb_or(a, b) |
返回两个位图的并集（a OR b） | rb_or(位图A, 位图B) |
rb_iterate(bitmap) |
将位图转换为可读的 SKU ID 列表 | rb_iterate(位图) → 1,3,5,7,... |
3.2 使用 intarray
1. 创建数组表
-- 集团可售 SKU 数组 CREATE MATERIALIZED VIEW mv_group_intarray AS SELECT array_agg(sku_id) AS skus FROM tab1 WHERE v_status = 1; -- 分店可售 SKU 数组（以分店 1 为例） CREATE MATERIALIZED VIEW mv_store1_intarray AS SELECT store_id ,array_agg(sku_id) AS skus
FROM tab2
WHERE v_status = 1
group by store_id ;
2. 查询分店 1 不可售的 SKU
-- 分店不可售 = (分店可售数组 ∩ 集团不可售数组) ∪ (分店不可售数组)
WITH
group_available AS (SELECT skus FROM mv_group_intarray),
store_available AS (SELECT skus FROM mv_store1_intarray where store_id=1),
group_unavailable AS (SELECT array_agg(sku_id) AS skus FROM tab1 WHERE v_status = 0),
store_unavailable AS (SELECT array_agg(sku_id) AS skus FROM tab2 WHERE store_id = 1 AND v_status = 0)
SELECT unnest(
store_available.skus & group_unavailable.skus | store_unavailable.skus
) AS sku_id
FROM store_available, group_unavailable, store_unavailable;
3.3 使用内置位数组
1. 创建位数组表（假设 SKU ID 连续）
-- 集团可售位图（假设最大 SKU ID 为 500000）
CREATE MATERIALIZED VIEW mv_group_bit AS
SELECT
bit_or(bitmask) AS bitmap
FROM (
SELECT set_bit('0'::bit(500000), sku_id - 1, 1) AS bitmask
FROM tab1
WHERE v_status = 1
) t;
-- 分店可售位图
CREATE MATERIALIZED VIEW mv_store1_bit AS
SELECT
store_id,
bit_or( -- 将每个 SKU 的位掩码合并为一个完整位图
set_bit('0'::bit(500000), sku_id - 1, 1)
) AS bitmap
FROM tab2
WHERE v_status = 1
GROUP BY store_id;
2. 查询分店 1 不可售的 SKU
-- 不可售条件：((分店可售 AND 集团不可售) OR 分店不可售)
SELECT
pos + 1 AS sku_id
FROM
generate_series(0, 499999) AS pos
WHERE
get_bit(
(
( (SELECT bitmap FROM mv_store1_bit where store_id=1) & ~(SELECT bitmap FROM mv_group_bit) )
| ~(SELECT bitmap FROM mv_store1_bit where store_id=1)
),
pos
) = 1;
4 对比分析
| 方案 | 存储空间 (分店 1) | 查询时间 (分店 1) | 优点 | 缺点 |
|---|---|---|---|---|
| pg_roaringbitmap | ~1 MB | ~600 ms | 压缩率高，动态扩展，支持海量数据 | 需安装插件 |
| intarray | ~2 MB (50 万元素) | ~50 m | 无需插件，操作简单 | 性能低，内存占用高 |
| 内置位数组 | ~62.5 KB | ~2 min | 原生支持，存储紧凑 | SKU ID 必须连续且范围小 |
5 总结建议
-
推荐方案：使用 pg_roaringbitmap，适合大规模稀疏数据，查询性能最优。
-
替代方案：若 SKU ID 连续且数量较小（如 < 10 万），可使用 内置位数组。
-
避免场景：避免在超大规模数据中使用 intarray，可能引发内存问题。
通过合理选择技术方案，可高效解决商超场景下的 SKU 可售状态计算需求。
posted on
浙公网安备 33010602011771号