同时使用group by 和集合函数时,遇到
select list expression not produced by aggregation output (missing from GROUP BY clause?):
我的sql如下:
select user_id,percent_rank() over(order by days_cnt asc) from (
select user_id,day_cnts
from table_name where dt_ymd between '20230101' and '20230228'
) a
where days_cnt is not null
group by user_id
在多次和度娘沟通无果的情况下,濒临崩溃,后经多次尝试【抹了一把辛酸泪】,终于找到问题了
select user_id,days_cntpercent_rank() over(order by days_cnt asc) from (
select user_id,day_cnts
from table_name where dt_ymd between '20230101' and '20230228'
) a
where days_cnt is not null
group by user_id,days_cnt
原因是,如果groupby 和聚合函数同时使用,那么groupby后边的字段必须包含聚合函数里用到的字段。扶额~
记录一下,给同样走投无路的小伙伴一点点帮助