时间戳

首页 > 解决方案 > 识别新卖家（最近3个月没有购买）

google-bigquery - 识别新卖家（最近3个月没有购买）

问题描述

在我的 SQL - BigQuery 中，我有一个包含 3 列的表：月份、日期、关于用户交易记录的 ID。 下面是 我要识别哪个ID是每个月的新卖家的例子，新卖家的定义是最近3个月没有购买的卖家。

我试图按日期，ID 对 ID 顺序进行排序 row_number。我认为不在 (2,3,4) 中的 row_number 是新卖家。但是，ID 可以跳过 1 个月并在下个月重新购买，我的代码不适用于这种情况。

你能帮我解决这个问题吗？非常感谢。

标签： google-bigquery

解决方案

以下是 BigQuery 标准 SQL 

#standardSQL
SELECT *, 
  COUNT(1) OVER(
    PARTITION BY id 
    ORDER BY DATE_DIFF(`date`, '2000-01-01', MONTH) 
    RANGE BETWEEN 4 PRECEDING AND 1 PRECEDING
  ) = 0 AS new_seller
FROM `project.dataset.table`

您可以使用您问题中的示例数据进行测试，使用上面的示例数据，如下例所示 

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 'Mar-19' month, DATE '2019-03-01' `date`, 1 id UNION ALL
  SELECT 'Mar-19', '2019-03-03', 2 UNION ALL
  SELECT 'Mar-19', '2019-03-04', 3 UNION ALL
  SELECT 'Apr-19', '2019-04-05', 3 UNION ALL
  SELECT 'Apr-19', '2019-04-06', 4 UNION ALL
  SELECT 'Apr-19', '2019-04-07', 5 UNION ALL
  SELECT 'May-19', '2019-05-03', 3 UNION ALL
  SELECT 'May-19', '2019-05-04', 6 UNION ALL
  SELECT 'May-19', '2019-05-05', 5 UNION ALL
  SELECT 'Jun-19', '2019-06-06', 1 UNION ALL
  SELECT 'Jun-19', '2019-06-07', 7 UNION ALL
  SELECT 'Jun-19', '2019-06-08', 8 UNION ALL
  SELECT 'Jun-19', '2019-06-09', 9 UNION ALL
  SELECT 'Jul-19', '2019-07-05', 2 UNION ALL
  SELECT 'Jul-19', '2019-07-06', 5 UNION ALL
  SELECT 'Jul-19', '2019-07-07', 9 
)
SELECT *, 
  COUNT(1) OVER(
    PARTITION BY id 
    ORDER BY DATE_DIFF(`date`, '2000-01-01', MONTH) 
    RANGE BETWEEN 4 PRECEDING AND 1 PRECEDING
  ) = 0 AS new_seller
FROM `project.dataset.table`
-- ORDER BY `date`

输出低于 

Row month   date        id  new_seller   
1   Mar-19  2019-03-01  1   true     
2   Mar-19  2019-03-03  2   true     
3   Mar-19  2019-03-04  3   true     
4   Apr-19  2019-04-05  3   false    
5   Apr-19  2019-04-06  4   true     
6   Apr-19  2019-04-07  5   true     
7   May-19  2019-05-03  3   false    
8   May-19  2019-05-04  6   true     
9   May-19  2019-05-05  5   false    
10  Jun-19  2019-06-06  1   false    
11  Jun-19  2019-06-07  7   true     
12  Jun-19  2019-06-08  8   true     
13  Jun-19  2019-06-09  9   true     
14  Jul-19  2019-07-05  2   false    
15  Jul-19  2019-07-06  5   false    
16  Jul-19  2019-07-07  9   false

推荐阅读