在BigQuery中按日期时间排名

Question

我有以下表模式：

Field name      Type        Mode        Policy tags     Description
order_id        STRING      NULLABLE        
customer_id     INTEGER     NULLABLE        
order_datetime  TIMESTAMP   NULLABLE                    %Y-%m-%d %H:%M:%E*S
item_id         STRING      NULLABLE        
order_quantity  INTEGER     NULLABLE    

客户可以在同一日期下多个订单。我正在尝试编写一个查询，在每个客户下订单的每个日期对order_id进行排名：

SELECT customer_id,
order_datetime as order_date,
order_id,
RANK() OVER(PARTITION BY customer_id, CAST(order_datetime AS DATETIME) ORDER BY 2 DESC) as rank
FROM `SQL_sets.orders`

这将返回以下内容：

customer_id     order_date      order_id    rank
21456       2019-01-12 9:28:35      A-005   1
21456       2019-01-12 12:28:35     A-005   1
21456       2019-01-12 19:28:35     A-005   1
31874       2020-11-15 0:00:00      A-009   1
32483       2020-11-21 0:00:00      A-001   1
32483       2020-11-22 0:00:00      A-001   1
42491       2019-01-16 2:52:07      A-006   1
42491       2019-01-20 2:52:07      A-0101  1
42491       2019-12-01 9:52:07      A-007   1
42491       2019-12-01 15:12:07     A-008   1
42491       2020-11-19 0:00:00      A-006   1
55400       2019-01-11 12:52:07     A-0088  1

但是我期望/想要得到：

customer_id     order_date      order_id    rank
21456       2019-01-12 9:28:35      A-005   1
21456       2019-01-12 12:28:35     A-005   2
21456       2019-01-12 19:28:35     A-005   3
31874       2020-11-15 0:00:00      A-009   1
32483       2020-11-21 0:00:00      A-001   1
32483       2020-11-22 0:00:00      A-001   1
42491       2019-01-16 2:52:07      A-006   1
42491       2019-01-20 2:52:07      A-0101  1
42491       2019-12-01 9:52:07      A-007   1
42491       2019-12-01 15:12:07     A-008   2
42491       2020-11-19 0:00:00      A-006   1
55400       2019-01-11 12:52:07     A-0088  1

我做错了什么？

Answer 1

您没有说，但我假设您使用的是SQL server。这可能更多的是关于你想要什么。在您的示例中，您的分区是按customer和datetime划分的，所以除非客户在完全相同的时间订购了两样东西，否则排名将始终为1。如果您只对同一日期发生的订单感兴趣，则需要将其转换为该类型……

SELECT customer_id,
order_datetime as order_date,
order_id,
RANK() OVER(PARTITION BY customer_id ORDER BY CAST(order_datetime AS DATETIME) DESC) rank
FROM `SQL_sets.orders`