데이터 분석을 위한 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 

!!!! 계산한 테이블에 이름 붙여 재사용하기

계산한 테이블을 다른 테이블, 뷰로 저장해서 사용하면 편하다! Big Query에서는 다른 테이블로 저장하는 것이 용이했다. (하지만 다른 툴에서는 모르겠다. with 절은 쿼리로 결과들을 다른 테이블로 저장한 것 처럼 사용할 수 있다.)

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] |
+--------------------------------------------------+