08. 에러 해결 방법 Error running query: SELECT list expression references column _PARTITIONTIME which is neither grouped nor aggregated

GCP Bigquery에서 해당 코드를 실행하는 데 아래와 같은 에러가 발생했다.

 

 

에러 코드

SELECT 
		count(*) as count, 
        date(_PARTITIONTIME) as partition_date 

FROM  `데이터 보관 경로`
WHERE 
		DATE(_PARTITIONTIME) in (SELECT distinct DATE(_PARTITIONTIME) as partitiontime
                             	 FROM `goodmorning-4f4f0.diff_datalist.diff_currentStreak` 
                             	 order by partitiontime desc limit 7)
AND 	result = 'false'
GROUP BY date(_PARTITIONTIME) 
ORDER BY partition_date

 

❓❗ 에러 살펴보기

Error running query: SELECT list expression references column _PARTITIONTIME which is neither grouped nor aggregated

 

 

 

문제 원인 및 해결 과정

group by에서 alias 미사용 이슈 때문이다.

해당 코드는, select - group by - order by 순으로 적용되기 때문에 select 안에 있는 alias 값을 group by에도 작성해주면 원하는대로 해결이 된다.

 

 

 

 

해결 코드

SELECT 
		count(*) as count, 
        date(_PARTITIONTIME) as partition_date 

FROM  `데이터 보관 경로`
WHERE 
		DATE(_PARTITIONTIME) in (SELECT distinct DATE(_PARTITIONTIME) as partitiontime
                             	 FROM `goodmorning-4f4f0.diff_datalist.diff_currentStreak` 
                             	 order by partitiontime desc limit 7)
AND 	result = 'false'
GROUP BY partition_date
ORDER BY partition_date