User profiling with bit-sliced index - Hologres - Alibaba Cloud Dokümantasyon Merkezi

Roaring bitmap algoritması nitelik etiketi sorgularında iyi sonuç verir ancak GMV, sayfa görüntüleme veya sipariş tutarı gibi büyük kullanıcı gruplarındaki sayısal davranış etiketlerini analiz ederken yetersiz kalır. Ayrıca, tekilleştirme sonrasında depolama alanının önemli ölçüde arttığı yüksek kardinaliteli etiketlerde de zorluk yaşar. Hologres bu sınırlamaları aşmak için bit dilimli dizin (BSI) algoritmasını sunar: BSI sayısal etiket değerlerini önceden hesaplar ve sıkıştırır; böylece ayrıntı tablolarına tekrar katılmanıza gerek kalmadan toplam, dağılım ve en çok kullanılan K değeri (top-K) sorgularını çalıştırabilirsiniz.

BSI ne zaman kullanılır?

Yalnızca roaring bitmaplerin ihtiyaçlarınızı karşılayamadığı durumlarda BSI kullanın:

Sayısal davranış etiketi analizi: GMV, sipariş tutarı veya oynatma süresi gibi sayısal etiketlere yönelik sorgular, yalnızca roaring bitmap kullanıldığında ayrıntı tablolarına katılmayı gerektirir; bu durum sorgu gecikme süresini artırır. BSI bu değerleri önceden hesaplar ve sıkıştırarak birleştirmeyi ortadan kaldırır.
Yüksek kardinaliteli etiket sorguları: Bir etiketin tekilleştirme sonrasında çok sayıda farklı değere sahip olması durumunda roaring bitmapler önemli miktarda depolama tüketir ve sorguları yavaşlatır. BSI tüm kullanıcı etiketi değerlerini en fazla 32 bit dilim olarak saklar; bu sayede depolama alanı kompakt kalır ve sorgular hızlı çalışır.

BSI nasıl çalışır?

BSI sayısal etiket değerlerini ikili formatta kodlar ve kullanıcı kimliklerini (UID) bit dilimleri arasında dağıtır. Her dilim etiket değerinin ikili gösterimindeki bir bit konumuna karşılık gelir.

Bu kodlama sayesinde:

Bir toplam işlemi tüm dilimler arasındaki bitmap kesişimine dönüşür.
Bir en çok kullanılan K değeri (top-K) işlemi en yüksek değerlikli bitlerden başlayarak bitmap kesişimine dönüşür.

BSI roaring bitmap işlemleriyle (AND, OR, NOT) birleşerek nitelik etiketleri ile davranış etiketleri arasında hızlı ilişkilendirme analizi yapmanızı sağlar.

Tam işlev referansı için bkz. BSI işlevleri.

Kullanım notları

UID türü gereksinimi: BSI INT türünde UID değerleri gerektirir. Tamsayı olmayan UID'ler için BSI verilerini oluşturmadan önce UID'leri tamsayı encode_uid değerlerine eşlemek amacıyla bir sözlük kodlama tablosu (örneğin dws_uid_dict) oluşturun.
Kaynak dağıtımı: Paketleme olmadan BSI ve roaring bitmap tabloları belirli düğümlere yerleştirilir ve diğer örnek kaynakları boşta kalır. Büyük veri kümelerine sahip üretim iş yükleri için Gelişmiş uygulamalar: paketleme bölümünde açıklanan paketleme yaklaşımını kullanın.
Paketler arası toplama: bsi_add_agg işlevi bsi_topk veya bsi_stat uygulanmadan önce BSI verilerini birden fazla pakette toplar. Paketlenmiş sorgularda her zaman bu işlevi kullanın.

Temel uygulamalar

Bu örnekte iki kaynak tablo kullanılır:

dws_userbase: kullanıcı nitelik etiketleri (il, cinsiyet)
usershop_behavior: kullanıcı davranış etiketleri (GMV = Brüt Ürün Hacmi)

Tabloları ayarlayın

Tablo	Alanlar	Açıklama
`dws_userbase`	`uid int, province text, gender text`	Kaynak nitelik etiketi tablosu (geniş tablo çözümü ile aynı)
`dws_uid_dict`	`encode_uid serial, uid int`	UID sözlük kodlama tablosu (roaring bitmap çözümü ile aynı)
`usershop_behavior`	`uid int, gmv int`	Kaynak davranış etiketi tablosu
`rb_tag`	`tag_name text, tag_val text, bitmap roaringbitmap`	Roaring bitmap üzerine oluşturulan nitelik etiketi tablosu
`bsi_gmv`	`gmv_bsi bsi`	BSI üzerine oluşturulan GMV ölçüm tablosu

Tabloları aşağıdaki Veri Tanımlama Dili (DDL) deyimleriyle oluşturun:

CREATE TABLE dws_userbase (
    uid int NOT NULL PRIMARY KEY,
    province text,
    gender text
    ... -- Other attribute columns.
)
WITH (
    distribution_key = 'uid'
);

CREATE TABLE dws_uid_dict (
    encode_uid serial,
    uid int PRIMARY KEY
);

CREATE TABLE usershop_behavior (
    uid int NOT NULL,
    gmv int
)
WITH (
    distribution_key = 'uid'
);

CREATE TABLE rb_tag (
    tag_name text,
    tag_val text,
    bitmap roaringbitmap
);

CREATE TABLE bsi_gmv (
    gmv_bsi bsi
);

Verileri yükleyin

Adım 1. dws_userbase ve dws_uid_dict tablolarından nitelik etiketleri (il ve cinsiyet) için roaring bitmapleri oluşturun ve bunları rb_tag tablosuna ekleyin.

INSERT INTO rb_tag
SELECT
    'province',
    province,
    rb_build_agg(b.encode_uid) AS bitmap
FROM
    dws_userbase a
    JOIN dws_uid_dict b ON a.uid = b.uid
GROUP BY
    province;

INSERT INTO rb_tag
SELECT
    'gender',
    gender,
    rb_build_agg(b.encode_uid) AS bitmap
FROM
    dws_userbase a
    JOIN dws_uid_dict b ON a.uid = b.uid
GROUP BY
    gender;

Adım 2. usershop_behavior ve dws_uid_dict tablolarından GMV davranış etiketleri için BSI verilerini oluşturun ve bunları bsi_gmv tablosuna ekleyin.

INSERT INTO bsi_gmv
SELECT
    bsi_build(array_agg(b.encode_uid), array_agg(a.gmv)) AS bitmap
FROM
    usershop_behavior a
    JOIN dws_uid_dict b ON a.uid = b.uid;

Profil analizi çalıştırın

Tanımlanmış kullanıcı grupları için davranış etiketlerini analiz etmek amacıyla BSI'yi roaring bitmap işlemleriyle birlikte kullanın. Aşağıdaki sorgular Guangdong ilinden erkek kullanıcıları hedef alır. Karşılaştırma amacıyla her sorgu BSI yaklaşımını eşdeğer düz SQL ile birlikte gösterir.

Bir kullanıcı grubu için davranış etiketlerini analiz edin

Toplam GMV ve ortalama GMV sorgulayın

BSI yaklaşımı:

SELECT
    sum(kv[1]) AS total_gmv,          -- Total GMV.
    sum(kv[1])/sum(kv[2]) AS avg_gmv  -- Average GMV.
FROM (
    SELECT
        bsi_sum(t1.gmv_bsi, t2.crowd) AS kv
    FROM
        bsi_gmv t1,
        (SELECT rb_and(a.bitmap, b.bitmap) AS crowd FROM
            (SELECT bitmap FROM rb_tag WHERE tag_name = 'gender' AND tag_val = 'Male ') a,  -- Male users.
            (SELECT bitmap FROM rb_tag WHERE tag_name = 'province' AND tag_val = 'Guangdong') b  -- Users from Guangdong province.
        ) t2
    ) t;

Düz SQL:

SELECT
    sum(b.gmv) AS total_gmv,
    avg(b.gmv) AS avg_gmv
FROM
    dws_userbase a
    JOIN usershop_behavior b ON a.uid = b.uid
WHERE
    a.province = 'Guangdong'
    AND a.gender = 'Male';

GMV dağılımını sorgulayın

BSI yaklaşımı — Tek seferde birden fazla aralıktaki dağılımı hesaplamak için sınır değerleri {100, 300, 500} ile bsi_stat işlevini kullanın:

SELECT
    bsi_stat('{100,300,500}', filter_bsi)
FROM (
    SELECT
        bsi_filter(t1.gmv_bsi, t2.crowd) AS filter_bsi
    FROM
        bsi_gmv t1,
        (SELECT rb_and(a.bitmap, b.bitmap) AS crowd FROM
            (SELECT bitmap FROM rb_tag WHERE tag_name = 'gender' AND tag_val = 'Male ') a,  -- Male users.
            (SELECT bitmap FROM rb_tag WHERE tag_name = 'province' AND tag_val = 'Guangdong') b  -- Users from Guangdong province.
        ) t2
    ) t;

Düz SQL — Her aralık için bir CASE WHEN ifadesi gerektirir:

SELECT
    CASE
        WHEN gmv >= 0 AND gmv <= 100 THEN '0-100'
        WHEN gmv > 100 AND gmv <= 300 THEN '100-300'
        WHEN gmv > 300 AND gmv <= 500 THEN '300-500'
        WHEN gmv > 500 THEN '>500'
    END AS gmv_range,
    COUNT(*) AS user_count
FROM
    dws_userbase a
    JOIN usershop_behavior b ON a.uid = b.uid
WHERE a.province = 'Guangdong'
    AND a.gender = 'Male'
GROUP BY gmv_range
ORDER BY gmv_range;

En yüksek K GMV değerini sorgulayın

BSI yaklaşımı:

SELECT
    rb_to_array(bsi_topk(filter_bsi, 10))
FROM (
    SELECT
        bsi_filter(t1.gmv_bsi, t2.crowd) AS filter_bsi
    FROM
        bsi_gmv t1,
        (SELECT rb_and(a.bitmap, b.bitmap) AS crowd FROM
            (SELECT bitmap FROM rb_tag WHERE tag_name = 'gender' AND tag_val = 'Male ') a,  -- Male users.
            (SELECT bitmap FROM rb_tag WHERE tag_name = 'province' AND tag_val = 'Guangdong') b  -- Users from Guangdong province.
        ) t2
    ) t;

Düz SQL:

SELECT
    b.uid,
    b.gmv
FROM
    dws_userbase a
    JOIN usershop_behavior b ON a.uid = b.uid
WHERE
    a.province = 'Guangdong'
    AND a.gender = 'Male'
ORDER BY gmv DESC
LIMIT 10;

Davranış etiketine göre kullanıcıları filtreleyin

GMV'si 1.000'i aşan kullanıcıları belirleyin

BSI yaklaşımı:

SELECT
    rb_to_array(bsi_gt(gmv_bsi, 1000)) AS crowd
FROM
    bsi_gmv;

Düz SQL:

SELECT
    array_agg(uid)
FROM
    usershop_behavior
WHERE
    gmv > 800;

Gelişmiş uygulamalar: paketleme

Paketleme olmadan BSI ve roaring bitmap tabloları belirli düğümlere yerleşir ve örneğin geri kalanı yeterince kullanılmaz. Bu tabloları 65.536 parçaya bölmek verileri tüm düğümlere dağıtarak sorgu eşzamanlılığını ve kaynak verimliliğini artırır.

Paket atama formülü encode_uid / 65536 AS bucket şeklindedir. Hem rb_tag hem de bsi_gmv tabloları her tablodan gelen ilgili parçaların her zaman aynı düğümde birlikte bulunmasını sağlamak için dağıtım anahtarı olarak bucket kullanır.

Tabloları ayarlayın

Temel kurulumla karşılaştırıldığında gelişmiş kurulum rb_tag ve bsi_gmv tablolarına bir bucket sütunu ekler ve bsi_gmv tablosuna zamana göre bölümlenmiş kategori düzeyindeki sorgular için category ve ds sütunlarını ekler.

Tablo	Alanlar	Açıklama
`dws_userbase`	`uid int, province text, gender text`	Temel uygulamalarla aynı
`dws_uid_dict`	`encode_uid serial, uid int`	Temel uygulamalarla aynı
`usershop_behavior`	`uid int, category text, gmv int, ds date`	Temel uygulamalara kıyasla `category` ve `ds` ekler
`rb_tag`	`tag_name text, tag_val text, bucket int, bitmap roaringbitmap`	`bucket` ekler; dağıtım anahtarı `bucket` olur
`bsi_gmv`	`ds date, category text, bucket int, gmv_bsi bsi`	`category`, `ds`, `bucket` ekler; dağıtım anahtarı `bucket` olur

CREATE TABLE rb_tag (
    tag_name text,
    tag_val text,
    bucket int,
    bitmap roaringbitmap
)
WITH (
    distribution_key = 'bucket'  -- Use the bucket ID as the distribution key.
);

CREATE TABLE bsi_gmv (
    category text,
    bucket int,
    gmv_bsi bsi,
    ds date
)
WITH (
    distribution_key = 'bucket'  -- Use the bucket ID as the distribution key.
);

Verileri yükleyin

Adım 1. Nitelik etiketleri için paketlere bölünmüş roaring bitmapleri oluşturun.

INSERT INTO rb_tag
SELECT
    'province',
    province,
    encode_uid / 65536 AS "bucket",
    rb_build_agg(b.encode_uid) AS bitmap
FROM
    dws_userbase a
    JOIN dws_uid_dict b ON a.uid = b.uid
GROUP BY
    province,
    "bucket";

INSERT INTO rb_tag
SELECT
    'gender',
    gender,
    encode_uid / 65536 AS "bucket",
    rb_build_agg(b.encode_uid) AS bitmap
FROM
    dws_userbase a
    JOIN dws_uid_dict b ON a.uid = b.uid
GROUP BY
    gender,
    "bucket";

Adım 2. GMV için paketlere bölünmüş BSI verilerini oluşturun. Önceki günün verilerini yüklemek için bu adımı günlük olarak çalıştırın.

INSERT INTO bsi_gmv
SELECT
    a.category,
    b.encode_uid / 65536 AS "bucket",
    bsi_build(array_agg(b.encode_uid), array_agg(a.gmv)) AS bitmap,
    a.ds
FROM
    usershop_behavior a
    JOIN dws_uid_dict b ON a.uid = b.uid
WHERE
    ds = CURRENT_DATE - interval '1 day'
GROUP BY
    category,
    "bucket",
    ds;

Profil analizi çalıştırın

Aşağıdaki tüm sorgular altyapının her düğümde eşleşen parçaları birlikte işlemesini sağlamak amacıyla bsi_gmv ve rb_tag tablolarını bucket üzerinden birleştirir.

Bir kullanıcı grubu için davranış etiketlerini analiz edin

3C kategorisindeki Guangdong ilinden erkek kullanıcıların (önceki gün) toplam GMV ve ortalama GMV değerlerini sorgulayın

SELECT
    sum(kv[1]) AS total_gmv,          -- Total GMV.
    sum(kv[1])/sum(kv[2]) AS avg_gmv  -- Average GMV.
FROM (
    SELECT
        bsi_sum(t1.gmv_bsi, t2.crowd) AS kv,
        t1.bucket
    FROM
        (SELECT gmv_bsi, bucket FROM bsi_gmv WHERE category = '3C' AND ds = CURRENT_DATE - interval '1 day') t1
    JOIN
        (SELECT rb_and(a.bitmap, b.bitmap) AS crowd, a.bucket FROM
            (SELECT bitmap, bucket FROM rb_tag WHERE tag_name = 'gender' AND tag_val = 'Male ') a  -- Male users.
         JOIN
            (SELECT bitmap, bucket FROM rb_tag WHERE tag_name = 'province' AND tag_val = 'Guangdong') b  -- Users from Guangdong province.
         ON a.bucket = b.bucket
        ) t2
    ON t1.bucket = t2.bucket
    ) t;

3C kategorisindeki Guangdong ilinden erkek kullanıcıların (önceki gün) GMV dağılımını sorgulayın

SELECT
    bsi_stat('{100,300,500}', bsi_add_agg(filter_bsi))
FROM (
    SELECT
        bsi_filter(t1.gmv_bsi, t2.crowd) AS filter_bsi,
        t1.bucket
    FROM
        (SELECT gmv_bsi, bucket FROM bsi_gmv WHERE category = '3C' AND ds = CURRENT_DATE - interval '1 day') t1
    JOIN
        (SELECT rb_and(a.bitmap, b.bitmap) AS crowd, a.bucket FROM
            (SELECT bitmap, bucket FROM rb_tag WHERE tag_name = 'gender' AND tag_val = 'Male ') a  -- Male users.
         JOIN
            (SELECT bitmap, bucket FROM rb_tag WHERE tag_name = 'province' AND tag_val = 'Guangdong') b  -- Users from Guangdong province.
         ON a.bucket = b.bucket
        ) t2
    ON t1.bucket = t2.bucket
    ) t;

Guangdong ilinden erkek kullanıcıların (önceki gün, tüm kategoriler) en yüksek 10 GMV değerini sorgulayın

SELECT
    bsi_topk(bsi_add_agg(filter_bsi), 10)
FROM (
    SELECT
        bsi_filter(t1.gmv_bsi, t2.crowd) AS filter_bsi,
        t1.bucket
    FROM
        (SELECT bsi_add_agg(gmv_bsi) AS gmv_bsi, bucket FROM bsi_gmv WHERE ds = CURRENT_DATE - interval '1 day' GROUP BY bucket) t1
    JOIN
        (SELECT rb_and(a.bitmap, b.bitmap) AS crowd, a.bucket FROM
            (SELECT bitmap, bucket FROM rb_tag WHERE tag_name = 'gender' AND tag_val = 'Male ') a  -- Male users.
         JOIN
            (SELECT bitmap, bucket FROM rb_tag WHERE tag_name = 'province' AND tag_val = 'Guangdong') b  -- Users from Guangdong province.
         ON a.bucket = b.bucket
        ) t2
    ON t1.bucket = t2.bucket
    ) t;

Davranış etiketine göre kullanıcıları filtreleyin

Önceki 30 gün içinde 3C kategorisinde GMV'si 1.000'i aşan kullanıcıları belirleyin

SELECT
    rb_to_array(bsi_gt(bsi_add_agg(gmv_bsi), 1000)) AS crowd
FROM
    bsi_gmv
WHERE
    category = '3C'
    AND ds BETWEEN CURRENT_DATE - interval '30 day' AND CURRENT_DATE - interval '1 day';

Sonraki adımlar

BSI işlevleri — Söz dizimi, bağımsız değişkenler ve dönüş türleri dahil olmak üzere tüm BSI işlevleri için tam referans.