데이터 분석을 위한 SQL 레시피
Big Query 기준
Ch 3. 데이터 가공을 위한 SQL
x,y 평면 에 있는 두 점의 유클리드 거리 계산하기
SELECT sqrt(power(x1-x2, 2)+power(y1-y2,2)) as distance
FROM `locations`;
# sqrt는 제곱근, power는 제곱 함
cast 함수에 여러 컬럼을 한번에 변환 시키려고 하니 error code 1241이 떴다. cast 함수는 한번에 하나의 컬럼을 사용! 정수 또는 문자열로 IP주소 다루기 inet 은 ip 자료형
select
ip
,cast(split(ip,'.')[safe_ordinal(1)] as int64) as ip_part_1
,cast(split(ip,'.')[safe_ordinal(2)] as int64) as ip_part_2
,cast(split(ip,'.')[safe_ordinal(3)] as int64) as ip_part_3
,cast(split(ip,'.')[safe_ordinal(4)] as int64) as ip_part_4
from (select '192.168.0.1'as ip) as t ;
>>
ip ip_part_1 ip_part_2 ip_part_3 ip_part_4
192.168.0.1 192 168 0 1
집약 함수를 적용한 값과 집약 전의 값을 동시에 다루기 - OVER 구문에 매개 변수를 지정하지 않으면 테이블 전체에 집약 함수를 적용한 값이 리턴 - 매개 변수에 PARTITION BY<컬럼 이름>을 지정하면 해당 컬럼 값을 기반으로 그룹화하고 집약 함수를 적용
select
user_id, product_id, score,
avg(score) over() as avg_score,
avg(score) over(partition by user_id) as user_avg_score,
score-avg(score) over(partition by user_id) as user_avg_score_diff
from
review ;

avg(score) over(partition by user_id) as user_avg_score 가 user_id 별로 score의 평균값이 출력된 것을 알 수 있다. (group by 와 비슷해보이지만, 각 컬럼별로 적용할 수 있는게 다른점으로 보인다) array_agg 는 표현값의 array를 반환한다.
select
product_id,
row_number() over (order by score desc)as R,
array_agg(product_id) over (ORDER BY score desc rows between unbounded proceding and current row) as cum_agg
from `popular_products`
where category ='action'
ORDER BY R ;
>> cum_agg는 해당되는 값들이 배열{ }안에 집약된
string_agg 는 null 아닌값을 연결한다.
SELECT STRING_AGG(fruit, " & ") AS string_agg
FROM UNNEST(["apple", "pear", "banana", "pear"]) AS fruit;
+------------------------------+
| string_agg |
+------------------------------+
| apple & pear & banana & pear |
+------------------------------+
split_part : 문자열을 쉼표 등의 구분자로 분할해 n번째 요소를 추출한다
Select
split_part('A001','A002','A003',','1) AS PART_1
split_part('A001','A002','A003',','2) AS PART_2
split_part('A001','A002','A003',','3) AS PART_3;
>> part_1 part_2 part_3
A001 A002 A003
!!!! 계산한 테이블에 이름 붙여 재사용하기
WITH 구문 : 'WITH <테이블 이름> AS (SELECT~) ' 형태로 사용
WITH
product_sale_ranking AS(
SELECT
category_name, product_id, sales,
ROW_NUMBER() OVER(PARTITION BY category_name ORDER BY sales DESC) AS rank
FROM
`product_sales`
)
SELECT * FROM `product_sale_ranking` ;
GENERATE_ARRAY : 시작 및 종료 값과 단계 값에서 값의 배열 생성
SELECT GENERATE_ARRAY(11, 33, 2) AS odds;
>>
+--------------------------------------------------+
| odds |
+--------------------------------------------------+
| [11, 13, 15, 17, 19, 21, 23, 25, 27, 29, 31, 33] |
+--------------------------------------------------+
Last updated
Was this helpful?