它可以与sum((一起工作,但不能与sum((/sum((一起工作
使用sum((:
select *,
case when time_of_day_viewers_time_zone >= '06:00:00 -
06:59:59' and time_of_day_viewers_time_zone <= '10:00:00 - 10:59:59'
then sum(amount_spent_usd) over () else null end as cpm
from t1
不适用于sum((/sum((:
select *,
case when time_of_day_viewers_time_zone >= '06:00:00 - 06:59:59'
and time_of_day_viewers_time_zone <= '10:00:00 - 10:59:59'
then sum(amount_spent_usd)/sum(impressions)*1000
over () else null end as cpm
from t1
edit1:即使我使用sum(amount_spent_usd(/unlif(sum(impression(,0(*1000,它仍然说"语法错误在或接近";超过">
第2版:
sum(amount_spent_usd) over () / (sum(impressions) over () * 1000)
如果我使用这个查询,它会对所有24小时进行求和,这意味着不应用case-when语句。如何解决此问题?
第4版:最后我使用了这个查询,尽管它很长。。
(sum(case when time_of_day_viewers_time_zone >= '06:00:00 - 06:59:59'
and time_of_day_viewers_time_zone <= '10:00:00 - 10:59:59'
then amount_spent_usd else null end) over () /sum(case when time_of_day_viewers_time_zone >= '06:00:00 - 06:59:59'
and time_of_day_viewers_time_zone <= '10:00:00 - 10:59:59'
then impressions else null end) over ())*1000 as new
提前感谢
不能将over()
用于两个窗口函数,需要分别为每个函数提供windowing子句。需要对整个表达式进行乘法运算:
sum(amount_spent_usd) over () / (sum(impressions) over () * 1000)