Пример проекции HPE Vertica Live Aggregation для удержания пользователя

Question

create table events(
    id char(36) PRIMARY KEY,
    game_id varchar(24) not null, 
    user_device_id char(36) not null, 
    event_name varchar(100) not null, 
    generated_at timestamp with time zone not null
);

SELECT 
    events.generated_at::DATE AS time_stamp, 
    COUNT(DISTINCT ( 
        CASE WHEN 
            events.event_name = 'new_user' THEN events.user_device_id 
        END
        )
    ) as new_users, 
    COUNT(DISTINCT (
        CASE WHEN 
            future_events.event_name <> 'new_user' THEN future_events.user_device_id 
        END
        )
    ) as returned_users, 
    COUNT(DISTINCT (
        CASE WHEN 
            future_events.event_name <> 'new_user' THEN future_events.user_device_id 
        END
    )) / COUNT(DISTINCT (
        CASE WHEN 
            events.event_name = 'new_user' THEN events.user_device_id 
        END
    ))::float as retention 
FROM events 
    LEFT JOIN events AS future_events ON 
        events.user_device_id = future_events.user_device_id AND 
        events.generated_at = future_events.generated_at - interval '1 day' AND 
        events.game_id = future_events.game_id
GROUP BY 
    time_stamp 
ORDER BY 
    time_stamp;

Я пытаюсь получить удержание пользователя Day N ('N' -> любое число от 1 до 7) с помощью вышеуказанного SQL-запроса. Из-за того, что я новичок в HPE vertica, я не могу составить оптимальное утверждение создания агрегированной проекции, поскольку проекция значительно повышает производительность запроса.

Answer 1

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

Вы можете создать регулярную проекцию, сегментированную и отсортированную по столбцам соединения, чтобы добиться улучшения производительности:

CREATE PROJECTION events_p1 (
id,
game_id ENCODING RLE,
user_device_id ENCODING RLE,
event_name,
generated_at ENCODING RLE
) AS
SELECT id,
       game_id,
       user_device_id,
       event_name,
       generated_at
FROM events
ORDER BY generated_at,
         game_id, 
         user_device_id 
SEGMENTED BY hash(generated_at,game_id,user_device_id) ALL NODES KSAFE 1;