时间戳

首页 > 解决方案 > SQL：运行不同值的总数

sql - SQL：运行不同值的总数

问题描述

我正在尝试在窗口中获取滚动数量的唯一值。

这是我的桌子的样子：

SELECT 
   user_id
   , order_date
   , product
FROM example_table 
WHERE user_id = 1 
ORDER BY order_date ASC
用户身份 订购日期 产品
1 2021-01-01 一种
1 2021-01-01 乙
1 2021-01-04 一种
1 2021-01-07 C
1 2021-01-09 C
1 2021-01-20 一种

这是我想要实现的目标：

用户身份 订购日期 产品 cum_dist_count
1 2021-01-01 一种 1
1 2021-01-02 乙 2
1 2021-01-04 一种 2
1 2021-01-07 C 3
1 2021-01-09 C 3
1 2021-01-20 一种 3

换句话说，我希望能够看到客户到目前为止有多少独特的商品，并能够看到特定日期的商品（例如上面的示例：在 2021-01-04，他们购买了 2 件独特的商品而对于 2021-01-07，这个数字是 3)。

我尝试通过在 CTE 中选择 user_id 和 product 和 min(order_date) 进行分组，然后在该 CTE 中对 user_id 和 product 执行 ROW_NUMBER 并且部分工作 - 我能够看到唯一产品计数发生变化的日期（所以对于此示例：2021-01-01、2021-01-02 和 2021-01-07，但随后我松开了我仍然希望能够访问的“中间”行。

with cte as (
   SELECT 
      user_id
      , product
      , min(order_date) as first_order
   FROM example_table 
   GROUP BY 1,2
   ORDER BY order_date ASC
)

SELECT
   user_id
   , first_order
   , product
   , ROW_NUMBER() OVER (PARTITION BY user_id, product ORDER BY first_order) AS number_of_unique_products
WHERE user_id = 1

有了以上内容，我会得到：

用户身份 订购日期 产品 cum_dist_count
1 2021-01-01 一种 1
1 2021-01-02 乙 2
1 2021-01-07 C 3

数据库位于 BigQuery StandardSQL 中。

任何帮助深表感谢！

标签： sqlgoogle-bigqueryaggregate-functionscumulative-sum

解决方案

对于每个项目，您可以记录它出现的最早日期。然后把它们加起来：

select et.* except (seqnum),
       countif(seqnum = 1) over (partition by user_id order by order_date) as running_distinct_count
from (select et.*,
             row_number() over (partition by user_id, product order by order_date) as seqnum
      from example_table et
     ) et

推荐阅读