Dremio环境下每日筛选历史排除账户的营销短信SQL方案问询

核心思路 #

通过窗口函数给账户分配排序序号，结合日期计算当日需排除的账户数量，全程使用视图实现每日自动筛选符合条件的账户，适配Dremio不支持临时表和递归CTE的限制。

分步实现 #

假设原始业务表名为 customer_purchases，包含 PURCHASE_DATE（交易日期）和 ACCOUNT_NUMBER（账户号）两列。

1. 生成去重排序的账户列表
   创建视图存储所有唯一账户，并按账户号排序，作为候选池：

   CREATE VIEW sorted_accounts AS
   SELECT DISTINCT ACCOUNT_NUMBER
   FROM customer_purchases
   ORDER BY ACCOUNT_NUMBER;
   

2. 给账户分配连续索引
   用窗口函数为每个账户分配从0开始的连续索引，方便后续按日期偏移筛选：

   CREATE VIEW numbered_accounts AS
   SELECT 
       ACCOUNT_NUMBER,
       ROW_NUMBER() OVER (ORDER BY ACCOUNT_NUMBER) - 1 AS account_idx
   FROM sorted_accounts;
   

3. 计算当日与首日的间隔天数
   以表中最早的交易日期作为任务首日，计算当日距离首日的天数N（即需排除的账户数量）：

   CREATE VIEW date_calculation AS
   SELECT 
       (CURRENT_DATE - MIN(PURCHASE_DATE)) AS days_since_start
   FROM customer_purchases;
   

   若需指定固定首日（而非表中最早交易日期），可将 MIN(PURCHASE_DATE) 替换为固定日期，例如 DATE '2024-01-01'。

4. 每日筛选目标账户
   结合间隔天数N，选择索引等于N的账户（自动排除前N天已选中的N个账户）；若账户数量不足，通过取模运算实现循环筛选：

   CREATE VIEW daily_marketing_account AS
   SELECT na.ACCOUNT_NUMBER
   FROM numbered_accounts na
   CROSS JOIN date_calculation dc
   CROSS JOIN (SELECT COUNT(*) AS total_accounts FROM sorted_accounts) ta
   WHERE na.account_idx = dc.days_since_start % ta.total_accounts;
   

使用说明 #

每日直接查询 daily_marketing_account 视图，即可获取当日需发送营销短信的账户。

内容的提问来源于stack exchange，提问作者Lasha Dolenjashvili