sql - 滚动查询新买家的 SQL 语句

Question

我目前有一个如下所示的订单表：

purchase_date | item_id | item_name | user_id
2/3/2020      | 123     | abc       | 987
3/3/2020      | 123     | abc       | 987
3/3/2020      | 123     | abc       | 876
3/3/2020      | 234     | bcd       | 987
...

我一直在尝试创建一个 SQL 语句，该语句将通过执行 count(distinct user_id) 返回类似的内容：

purchase_date | item_id | item_name | number of buyers new to item
2/3/2020      | 123     | abc       | 1 
3/3/2020      | 123     | abc       | 1
3/3/2020      | 234     | bcd       | 1

当然，订单表中会有多个item_id。

我想要实现的是每天获得之前从未购买过该特定商品的买家的滚动数量。

例如，根据上面的订单表示例，2020 年 2 月 3 日有 1 个买家，2020 年 3 月 3 日有 2 个买家。但是，User_id 987 在 2020 年 2 月 3 日购买了相同的商品，因此我不想计算该用户，因此 2020 年 3 月 3 日的最终结果应该只有 1 个用户。

user_id 987 仍将计入 item_id 234，因为此用户之前未在订单表中购买过此商品。

订单表中的项目将在每周一刷新，因此我正在尝试构建一个查询，该查询将每天向我返回从周一到周日对每个特定项目（即以前没有购买过）的新买家的滚动数量.

这是使用 CROSS JOIN 的概念，还是以某种方式使用临时表，或者这是一个比我预期的更复杂的想法，以 SQL 查询格式执行它？

谢谢！

标签： sqlpresto

Answer 1

使用两个级别的聚合：

select first_pd, item_id, item_name, count(*) as num_first_purchases
from (select user_id, item_id, item_name, min(purchase_date) as first_pd
      from t
      group by user_id, item_id, item_name
     ) ui
group by first_pd, item_id, item_name
order by first_pd, item_id, item_name;