Skip to content
TheChatArchiveThe Chat Archive
gpt-3.5-turbo-0613· openaiHugging Face Suzhen/CodeChat

有一个doris表,表中有个bitmap字段a,代表认知人群,表中数据按日快照,快照字段为date。需求:统计近15天认知人群每日新增人数和流失人数

  1. User

    有一个doris表,表中有个bitmap字段a,代表认知人群,表中数据按日快照,快照字段为date。需求:统计近15天认知人群每日新增人数和流失人数

  2. Assistant

    要统计近15天认知人群每日新增人数和流失人数,可以使用以下SQL查询语句:

    SELECT 
        date,
        COUNT(DISTINCT BITMAP_AND(a, COALESCE(LAG(a) OVER (ORDER BY date), a))) AS new_users,
        COUNT(DISTINCT BITMAP_AND(a, COALESCE(LEAD(a) OVER (ORDER BY date), a))) AS lost_users
    FROM
        doris
    WHERE
        date >= CURDATE() - INTERVAL 14 DAY -- 最近15天的数据
    GROUP BY
        date
    ORDER BY
        date;
    

    请将上述SQL语句中的"doris"替换为你的表名,"date"替换为日期字段名,"a"替换为bitmap字段名。执行该查询语句将返回按日期分组的新增用户数和流失用户数。

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.