使用DATE_TRUNC获取上月首次下单用户ID列表无结果，求解决思路

核心问题分析 #

你的SQL未返回数据，主要是日期范围逻辑错误，同时存在NOT IN的潜在陷阱：

1. 日期区间完全错误
   原SQL中BETWEEN CURRENT_DATE() AND DATE_TRUNC(CURRENT_DATE(),month)的参数顺序搞反了——CURRENT_DATE()是当前日期，DATE_TRUNC(CURRENT_DATE(),month)是当月第一天，而BETWEEN要求左值≤右值，这个条件根本匹配不到任何数据。更关键的是，你要筛选的是上个月的订单，而非当月订单。

2. NOT IN的NULL陷阱
   如果子查询返回的user_id包含NULL值，NOT IN会直接导致整个过滤条件失效：因为SQL中NULL与任何值的比较结果都是UNKNOWN，最终会过滤掉所有数据。

修正后的SQL写法 #

提供两种可靠实现方式，规避上述问题：

方式一：用NOT EXISTS（推荐，彻底避免NULL问题）

SELECT DISTINCT user_id
FROM `gcommerce-analytics-prod.grouponi_groupon.tb_orders` o1
WHERE 
  -- 匹配上个月的订单
  DATE_TRUNC(o1.on_date_ts, MONTH) = DATE_TRUNC(DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH), MONTH)
  -- 确保该用户此前无任何订单
  AND NOT EXISTS (
    SELECT 1
    FROM `gcommerce-analytics-prod.grouponi_groupon.tb_orders` o2
    WHERE o2.user_id = o1.user_id
      AND DATE_TRUNC(o2.on_date_ts, MONTH) < DATE_TRUNC(DATE_SUB(CURRENT_DATE(), INTERVAL 1 MONTH), MONTH)
  )

方式二：明确指定上个月的日期范围

SELECT DISTINCT user_id
FROM `gcommerce-analytics-prod.grouponi_groupon.tb_orders`
WHERE 
  -- 限定为上个月第一天到最后一天
  CAST(on_date_ts AS DATE) BETWEEN 
    DATE_SUB(DATE_TRUNC(CURRENT_DATE(), MONTH), INTERVAL 1 MONTH)
    AND DATE_SUB(DATE_TRUNC(CURRENT_DATE(), MONTH), INTERVAL 1 DAY)
  -- 用NOT EXISTS替代NOT IN，规避NULL风险
  AND NOT EXISTS (
    SELECT 1
    FROM `gcommerce-analytics-prod.grouponi_groupon.tb_orders` sub
    WHERE sub.user_id = tb_orders.user_id
      AND CAST(sub.on_date_ts AS DATE) < DATE_SUB(DATE_TRUNC(CURRENT_DATE(), MONTH), INTERVAL 1 MONTH)
  )

额外排查步骤 #

• 先单独验证上个月是否有订单数据：

  SELECT COUNT(*) 
  FROM `gcommerce-analytics-prod.grouponi_groupon.tb_orders`
  WHERE CAST(on_date_ts AS DATE) BETWEEN 
    DATE_SUB(DATE_TRUNC(CURRENT_DATE(), MONTH), INTERVAL 1 MONTH)
    AND DATE_SUB(DATE_TRUNC(CURRENT_DATE(), MONTH), INTERVAL 1 DAY)
  

• 检查表中是否存在NULL的user_id：

  SELECT COUNT(*) 
  FROM `gcommerce-analytics-prod.grouponi_groupon.tb_orders`
  WHERE user_id IS NULL
  

内容的提问来源于stack exchange，提问作者Tzahi Barda