sql - 更改查询以避免 Bigquery 中的 "Aggregations of aggregations are not allowed"

Question

给定用户和订单表，我需要计算在注册日期后的第二天首次下单的用户。

我设法通过以下查询列出了此类用户:

SELECT 
  users.first_name as first_name,
  users.last_name as last_name,
  users.registration_date as registration_date,
  min(orders.order_date) as first_order_date
FROM `users_table` as users
  JOIN `orders_table` as orders
  ON users.id = orders.user_id
GROUP BY
  first_name,
  last_name,
  registration_date
HAVING
  date_diff(first_order_date, registration_date, DAY) = 1
ORDER BY
  registration_date ASC
LIMIT 5

导致:

+------------+-----------+-------------------+------------------+
| first_name | last_name | registration_date | first_order_date |
+------------+-----------+-------------------+------------------+
| Albert     | Ellis     | 2013-04-11        | 2013-04-12       |
| Charles    | Moore     | 2014-04-29        | 2014-04-30       |
| Jimmy      | Payne     | 2014-07-01        | 2014-07-02       |
| Angela     | Stanley   | 2014-10-21        | 2014-10-22       |
| Marie      | Bishop    | 2014-11-15        | 2014-11-16       |
+------------+-----------+-------------------+------------------+

现在，我数不过来。当我尝试类似的事情时:

SELECT 
  count(date_diff(min(orders.order_date), users.registration_date, DAY) = 1)
FROM `users_table` as users
  JOIN `orders_table` as orders
  ON users.id = orders.user_id

我收到“不允许聚合的聚合”错误。我该如何修改查询来解决这个问题？

Answer 1

以下是 BigQuery 标准 SQL

#standardSQL
SELECT COUNT(1) next_day_order_users
FROM `project.dataset.users_table` AS users
JOIN (
  SELECT user_id, MIN(order_date) first_order_date 
  FROM `project.dataset.orders_table`
  GROUP BY user_id
) AS orders
ON users.id = orders.user_id
WHERE DATE_DIFF(first_order_date, registration_date, DAY) = 1