如果指定了DISTINCT，则不允许BigQuery窗口使用ORDER BY [英] BigQuery Window ORDER BY is not allowed if DISTINCT is specified

问题描述

我正在调查移植一些像这样的窗口化不同计数的bigquery传统sql

I'm investigating porting some bigquery legacy sql containing windowed distinct counts like this

count(distinct brand_id) over (partition by user_id order by order_placed_at range between 7 * 24 * 60 * 60 * 1000000 PRECEDING AND 1 PRECEDING) as last_7_day_buyer_brands

到标准sql ....，但出现此错误....

to Standard sql.... but I get this error....

Window ORDER BY is not allowed if DISTINCT is specified

作为参考，我尝试了APPROX_COUNT_DISTINCT函数，但是没有运气.

For reference I've tried APPROX_COUNT_DISTINCT function with no luck.

除了编写子查询和分组依据之外，还有其他更好的方法可以使它工作吗?

Is there a better way to get this to work other than write the subqueries and group by's?

大多数其他查询只进行了很小的更改就移植到了标准sql.

Most of the other queries have ported to standard sql with only minor changes.

推荐答案

每个

OVER子句要求:

OVER clause requirements:

PARTITION BY:可选.
ORDER BY:可选. 如果存在DISTINCT，则不允许.
window_frame_clause:可选. 如果存在DISTINCT，则不允许.

PARTITION BY: Optional.
ORDER BY: Optional. Disallowed if DISTINCT is present.
window_frame_clause: Optional. Disallowed if DISTINCT is present.

注意:以上内容由我突出显示"，而不是文档中的

如您所见，当使用DISTINCT时，不仅ORDER BY甚至不允许RANGE BETWEEN

As you can see not only ORDER BY but even RANGE BETWEEN is not allowed when DISTINCT is used

我认为，子查询是必经之路.

I think, subquery is the way to go.

如果需要指导，请使用下面的简单示例

In case if you need direction for this, use below simple example

#standardSQL
SELECT
  user_id,
  order_placed_at,
  brand_id,
  (SELECT COUNT(DISTINCT brand) 
      FROM UNNEST(last_7_day_buyer_brands_with_dups) AS brand
  ) AS last_7_day_buyer_brands
FROM (
  SELECT 
    user_id,
    order_placed_at,
    brand_id,
    ARRAY_AGG(brand_id) OVER(
      PARTITION BY user_id ORDER BY order_placed_at 
      RANGE BETWEEN 7 * 24 * 60 * 60 * 1000000 PRECEDING AND 1 PRECEDING
    ) AS last_7_day_buyer_brands_with_dups
  FROM yourTable
)

这篇关于如果指定了DISTINCT，则不允许BigQuery窗口使用ORDER BY的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！