windowFunnel - Hologres - Alibaba Cloud Dokümantasyon Merkezi

Sınırlamalar

windowFunnel işlevini yalnızca Hologres V0.9 ve sonraki sürümler destekler.

Ön Koşullar

Başlamadan önce aşağıdaki koşulları karşıladığınızdan emin olun:

V0.9 veya sonraki bir sürümü çalıştıran bir Hologres örneği
Hedef veritabanına süper kullanıcı erişimi

Herhangi bir huni işlevini çağırmadan önce flow_analysis uzantısını süper kullanıcı olarak yükleyin:

CREATE extension flow_analysis; --Install the extension.

Uzantı veritabanı düzeyinde yüklenir. Her veritabanı için bir kez yüklemeniz yeterlidir.

Varsayılan olarak uzantı public şemasına yüklenir ve başka bir şemaya taşınamaz. İşlevi farklı bir şemadan çağırmak için tam nitelikli adı kullanın; örneğin public.windowFunnel.

Çalışma Mantığı

windowFunnel olay verilerini aşağıdaki şekilde işler:

Zincirdeki ilk koşul eşleştiğinde işlev, olay sayacını 1 olarak ayarlar ve kayan zaman penceresini başlatır.
Sırayla eşleşen her bir sonraki koşul sayacı artırır. Dizi bozulursa sayaç artmayı durdurur.

Dönüş değeri: Kayan zaman penceresi içindeki zincirden ardışık eşleşen maksimum koşul sayısı. Bir tamsayı döndürür.

Tüm olayları kapsayacak kadar uzun bir zaman penceresi için:

Kullanıcı olayları	Belirtilen koşullar	Dönüş değeri
c1, c2, c3, c4	c1, c2, c3	3
c4, c3, c2, c1	c1, c2, c3	1
c4, c3	c1, c2, c3	0

Söz Dizimi

windowFunnel(window, mode, timestamp, cond1, cond2, ..., condN)

Parametreler:

Parametre	Açıklama
`window`	Zaman penceresinin uzunluğu. windowFunnel işlevi, ilk eşleşen olayın gerçekleştiği zamanı zaman penceresinin başlangıç noktası olarak alır ve zaman penceresinin uzunluğuna göre sonraki olayların verilerini çıkarır.
`mode`	Eşleşme modu. Kabul edilen değerler: `default` (varsayılan) ve `strict`. Ayrıntılar için bkz. Eşleşme modları.
`timestamp`	Olay zamanlarını kaydeden sütun. Desteklenen veri türleri: TIMESTAMP, INT ve BIGINT.
`cond`	Huninin bir aşamasını temsil eden boolean koşul. Beklenen olay gerçekleşme sırasına göre koşulları belirtin.

Eşleşme modları

Her iki mod da aynı olay zinciri mantığını kullanır. A→B→C koşulları ve kullanıcı olayları verildiğinde:

default modu — Belirtilen zaman penceresindeki ilk olaydan başlayarak mümkün olduğunca çok olayı eşleştirir.

Kullanıcı olayları	Sonuç
A, B, C	3 — üç koşulun tümü eşleşti
A, B, A, C	3 — tekrarlanan A atlanır; eşleşme C'ye kadar devam eder

strict modu — Tekrarlanan bir olayla karşılaştığında eşleştirmeyi durdurur.

Kullanıcı olayları	Sonuç
A, B, C	3 — üç koşulun tümü eşleşti
A, B, A, C	2 — tekrarlanan A eşleştirmeyi durdurur; yalnızca A ve B sayılır

Örnekler

Aşağıdaki örneklerde GitHub genel olay veri kümesi kullanılmaktadır. Veri kümesini Hologres örneğinize aktarmak için bkz. Genel veri kümelerini birkaç tıklamayla aktarma.

Veri kümesi tablosu şeması:

BEGIN;
CREATE TABLE hologres_dataset_github_event.hologres_github_event (
    id BIGINT,
    actor_id BIGINT,
    actor_login TEXT,
    repo_id BIGINT,
    repo_name TEXT,
    org_id BIGINT,
    org_login TEXT,
    type TEXT,
    created_at TIMESTAMP WITH TIME ZONE NOT NULL,
    action TEXT,
    iss_or_pr_id BIGINT,
    number BIGINT,
    comment_id BIGINT,
    commit_id TEXT,
    member_id BIGINT,
    rev_or_push_or_rel_id BIGINT,
    ref TEXT,
    ref_type TEXT,
    state TEXT,
    author_association TEXT,
    language TEXT,
    merged BOOLEAN,
    merged_at TIMESTAMP WITH TIME ZONE,
    additions BIGINT,
    deletions BIGINT,
    changed_files BIGINT,
    push_size BIGINT,
    push_distinct_size BIGINT,
    hr TEXT,
    month TEXT,
    year TEXT,
    ds TEXT
);

CALL set_table_property('hologres_dataset_github_event.hologres_github_event', 'orientation', 'column');
CALL set_table_property('hologres_dataset_github_event.hologres_github_event', 'bitmap_columns', 'actor_login,repo_name,org_login,type,action,commit_id,ref,ref_type,state,author_association,language,hr,month,year,ds');
CALL set_table_property('hologres_dataset_github_event.hologres_github_event', 'clustering_key', 'created_at:asc');
CALL set_table_property('hologres_dataset_github_event.hologres_github_event', 'dictionary_encoding_columns', 'actor_login:auto,repo_name:auto,org_login:auto,type:auto,action:auto,commit_id:auto,ref:auto,ref_type:auto,state:auto,author_association:auto,language:auto,hr:auto,month:auto,year:auto,ds:auto');
CALL set_table_property('hologres_dataset_github_event.hologres_github_event', 'distribution_key', 'id');
CALL set_table_property('hologres_dataset_github_event.hologres_github_event', 'segment_key', 'created_at');
CALL set_table_property('hologres_dataset_github_event.hologres_github_event', 'time_to_live_in_seconds', '3153600000');

COMMENT ON TABLE hologres_dataset_github_event.hologres_github_event IS NULL;
END;

Kullanıcı başına huni seviyelerini hesaplama

Aşağıdaki sorgu, üç günlük bir dönemde 30 dakikalık bir pencere içinde her kullanıcının CreateEvent → PushEvent → IssuesEvent dönüşüm yolunda ne kadar ilerlediğini belirler.

--Calculate the funnel data of each user.
SELECT
    actor_id,
    windowFunnel (1800, 'default', created_at,  type = 'CreateEvent',type = 'PushEvent',type = 'IssuesEvent') AS level
FROM
    hologres_dataset_github_event.hologres_github_event
WHERE
    created_at >= TIMESTAMP '2024-01-28 10:00:00+08'
    AND created_at < TIMESTAMP '2024-01-31 10:00:00+08'
GROUP BY
    actor_id;

Sonuçtaki level sütunu, her kullanıcının hunide kaç olayı tamamladığını gösterir:

Seviye	Anlamı
0	İlk olayı (CreateEvent) tamamlamadı
1	Yalnızca CreateEvent tamamlandı
2	CreateEvent ve PushEvent tamamlandı
3	Üç olayın tümü tamamlandı

Örnek çıktı:

 actor_id   | level
------------+------
143037332   | 0
38708562    | 0
157624788   | 1
137850795   | 1
69616418    | 2
158019532   | 2
727125      | 3

Kullanıcıları huni aşamasına göre toplama

Kaç kullanıcının her aşamaya ulaştığını saymak için seviye dağılımı üzerinde kümülatif toplam kullanın:

WITH level_detail AS (
    SELECT
        level,
        COUNT(1) AS count_user
    FROM (
        SELECT
            actor_id,
            windowFunnel (1800, 'default', created_at, type = 'CreateEvent', type = 'PushEvent',type = 'IssuesEvent') AS level
        FROM
            hologres_dataset_github_event.hologres_github_event
        WHERE
            created_at >= TIMESTAMP '2024-01-28 10:00:00+08'
            AND created_at < TIMESTAMP '2024-01-31 10:00:00+08'
        GROUP BY
            actor_id) AS basic_table
    GROUP BY
        level
    ORDER BY
        level ASC
)
SELECT  CASE level    WHEN 0 THEN 'total'
                      WHEN 1 THEN 'CreateEvent'
                      WHEN 2 THEN 'PushEvent'
                      WHEN 3 THEN 'IssuesEvent'
                      END AS type
        ,SUM(count_user) over ( ORDER BY level DESC )
FROM
    level_detail
GROUP BY
    level,
    count_user
ORDER BY
    level ASC;

Sonuç:

  type        |  sum
--------------+--------
total         | 1338166
CreateEvent   | 461088
PushEvent     | 202221
IssuesEvent   | 4727

total satırı, analiz edilen tüm kullanıcıların kümülatif sayısını gösterir. Sonraki her satır, hunide en az o aşamaya ulaşan kullanıcı sayısını gösterir.