gpt-3.5-turbo-0613· openai
Suzhen/CodeChat
有一个doris表,表中有个bitmap字段a,代表认知人群,表中数据按日快照,快照字段为date。需求:统计近15天认知人群每日新增人数和流失人数
- User
有一个doris表,表中有个bitmap字段a,代表认知人群,表中数据按日快照,快照字段为date。需求:统计近15天认知人群每日新增人数和流失人数
- 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字段名。执行该查询语句将返回按日期分组的新增用户数和流失用户数。
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.