JM:I have a dream,期待和你一起去实现!
原文及译文
JM
网友:noob'88

        我正在尝试利用BigQuery的“Over”功能编写一个SQL语句。我本质上是试图做一些复合和平滑的回报,但我有点r。。我的查询如下所示:WITH daily_extract AS(select date_adjusted,...

推荐:想去Google查资料?(你懂的)想建站?想测试?想挂机?想折腾?想要高性价比的服务器?点我!!


BigQuery和分析函数(BigQuery Over and Analytics Functions)

我正在尝试利用BigQuery的“Over”功能编写一个SQL语句。

我本质上是试图做一些复合和平滑的回报,但我有点r。。我的查询如下所示:

WITH daily_extract AS (select date_adjusted,
                              sum(some_number) as 
                              daily_sum_of_some_number,
                              entity_id
                       from `some table`
                       where 1=1 -- no filter  
                       and   1=1 -- no filter  
                       group by date_adjusted, entity_id
                       order by date_adjusted
                       )
      select
      date_adjusted as date_adjusted,
      entity_id as entity_id,
      sum(log(1 + daily_sum_of_some_number)) 
          over (PARTITION by entity_id 
                ORDER     by date_adjusted 
                rows unbounded preceding) as smoothed_number
      from daily_extract
      group by 1,2

但是我得到的是这样的错误:

无法检索数据 - SELECT列表表达式引用列daily_sum_return_selected_nav既没有分组也没有在[16:19]聚合。

我试图说在底部的1,2,3组,但后来我得到的错误:

无法检索数据 - 第3列包含一个分析函数,在[18:20]

我正在阅读下面的链接,但如果有人能帮助我,这将是非常appreicated!

https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#analytic-functions


I am trying to write a SQL leveraging BigQuery's "Over" functionality.

I'm essentially trying to do some compounding and smoother returns but I'm hitting a bit of a rut. My query looks like this:

WITH daily_extract AS (select date_adjusted,
                              sum(some_number) as 
                              daily_sum_of_some_number,
                              entity_id
                       from `some table`
                       where 1=1 -- no filter  
                       and   1=1 -- no filter  
                       group by date_adjusted, entity_id
                       order by date_adjusted
                       )
      select
      date_adjusted as date_adjusted,
      entity_id as entity_id,
      sum(log(1 + daily_sum_of_some_number)) 
          over (PARTITION by entity_id 
                ORDER     by date_adjusted 
                rows unbounded preceding) as smoothed_number
      from daily_extract
      group by 1,2

But what I get is an error such as this:

Failed to retrieve data - SELECT list expression references column daily_sum_return_selected_nav which is neither grouped nor aggregated at [16:19].

I tried to say group by 1,2,3 at the bottom but then I get the error:

Failed to retrieve data - Column 3 contains an analytic function, which is not allowed in GROUP BY at [18:20]

I'm reading the link below, but if anyone could help me out it would be greatly appreicated!

https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#analytic-functions


解决方案(由网友Gordon Linoff提供)

我至少可以看到三个问题。您的CTE正在聚集在错误的列中,窗口框架子句是不必要的,而且您在外部查询中缺少聚合。

您也可能有其他问题,但这可能有所帮助:

with daily_extract as (
      select date_adjusted, sum(some_number) as daily_sum_of_some_number,
             entity_id
      from `some table`
      group by date_adjusted, entity_id
------------------------------^
     )
select date_adjusted, entity_id,
      sum(log(1 + sum(daily_sum_of_some_number))) over
          (partition by entity_id 
           order by date_adjusted 
          ) as smoothed_number
from daily_extract
group by 1, 2;

I can readily see at least three problems. Your CTE is aggregating by the wrong columns, the window frame clause is unnecessary, and you are missing an aggregation in the outer query.

You might have other problems as well, but this might help:

with daily_extract as (
      select date_adjusted, sum(some_number) as daily_sum_of_some_number,
             entity_id
      from `some table`
      group by date_adjusted, entity_id
------------------------------^
     )
select date_adjusted, entity_id,
      sum(log(1 + sum(daily_sum_of_some_number))) over
          (partition by entity_id 
           order by date_adjusted 
          ) as smoothed_number
from daily_extract
group by 1, 2;

推荐:想去Google查资料?(你懂的)想建站?想测试?想挂机?想折腾?想要高性价比的服务器?点我!!


关于站长

JMJavaMethod的缩写,苦逼码农一个,一直想有番作为,奈何人老力衰,只能四处膜拜大佬以获得动力。已经从单机、局域网、互联网、混到了移动互联网,未来希望能在AI世界里继续混下去。这辈子有个终极目标:财务自由,心灵自由。

近期公告

一个人苦逼开发多日,网站终于正式上线,求关注!!! 一个人苦逼多日,终于正式上线,求关注!!!

找他  
猜你喜欢
想建站?想测试?
想挂机?想折腾?
想去Google查资料?(你懂的)
想要高性价比的服务器?
搬瓦工VPS - 性价最高的美国便宜VPS主机
友情链接

冀ICP备17016304号 版权所有 © JavaMethod.com All Rights Reserved,Theme by 拼图