BigQuery_함수 및 문법(쿼리구문)

1. WITH

• 반복적으로 사용할 (이름을 가진)Subquery 생성

WITH <사용이름> AS

2. UNBOUNDED PRECEDING UNBOUNDED FOLLOWING UNBOUNDED CURRENT

윈도우 함수 종류

• AVG, COUNT, FIRST_VALUE, LAST_VALUE, MAX, MIN, STDDEV, SUM..D

  WINDOW 함수

• 분석함수, 순위함수라 명하기도 함

• 행 그룹의 값을 비교, 연산, 정의하여 각 행마다 하나의 결과 반환

• 행 그룹에 대해 하나의 결과를 반환하는 집계함수와는 다름

• OVER절 포함

  SQL의 윈도우 함수란 행과 행 간을 비교, 연산, 정의하기 위한 함수이다. 분석함수 또는 순위함수라고 하기도 한다. 다른 함수들처럼 중첩해서 사용할 수는 없지만 서브쿼리에서는 사용가능하다.

3. SELECT * REPLACE

WITH orders AS
  (SELECT 5 as order_id,
  "sprocket" as item_name,
  200 as quantity)
SELECT * REPLACE ("widget" AS item_name)
FROM orders;

+----------+-----------+----------+
| order_id | item_name | quantity |
+----------+-----------+----------+
| 5        | widget    | 200      |
+----------+-----------+----------+

WITH orders AS
  (SELECT 5 as order_id,
  "sprocket" as item_name,
  200 as quantity)
SELECT * REPLACE (quantity/2 AS quantity)
FROM orders;

+----------+-----------+----------+
| order_id | item_name | quantity |
+----------+-----------+----------+
| 5        | sprocket  | 100      |
+----------+-----------+----------+

4. PIVOT

• PIVOT_연산자링크

5. FROM 절 FOR

6. JOIN ON / USING

• ON은 여러 ROWS를 반환, USING은 한 ROW를 반환
• ON을 사용하더라도 SELECT에서 한 ROW만 선택하면 해당 ROW만 반환됨
• JOIN은 왼쪽부터 반영되지만, ()를 이용하여 다른 순서로 바인딩 가능
• 여러 View를 JOIN할 경우 ,를 이용한 JOIN이 있으면 , 이후 JOIN에는 ()를 사용해야 함

FROM A JOIN B ON A.x = B.x
FROM A JOIN B USING (x)

Table A   Table B   Result ON     Result USING
+---+     +---+     +-------+     +---+
| x |  *  | x |  =  | x | x |     | x |
+---+     +---+     +-------+     +---+
| 1 |     | 2 |     | 2 | 2 |     | 2 |
| 2 |     | 3 |     | 3 | 3 |     | 3 |
| 3 |     | 4 |     +-------+     +---+
+---+     +---+

FROM ( A JOIN (B JOIN C USING (x)) USING (x) )

7. CROSS JOIN시 왼쪽의 일부행에 대해 오른쪽에 값이 존재하지 않으면 결과에서 최종 행이 삭제됨

• CROSS JOIN 연산 주의사항

8. GROUP BY ROLLUP()

• GROUP BY 와 함께 사용할 수 있는 ROLLUP()구문
• SELECT a, b, SUM(c) FROM Input GROUP BY ROLLUP(a, b);인 경우 (a,b), (b), ()의 GROUP BY 결과가 포함됨

9. HAVING

• GROUP BY또는 집계 함수로 생성된 결과를 필터링 할 때 HAVING이 사용됨

10. ORDER BY

• 기본이 ASC, DESC를 원하면 ORDER BY x DESC 와 같이 넣어야 함
• ASC인 경우 NULL이 맨 앞으로 나오기 때문에 NULL을 뒤로 빼고싶다면 ORDER BY x NULLS LAST를 지정해야 함

11. QUALIFY

• QUALIFY 절은 윈도우 함수의 결과를 필터링
• SELECT문에 WINDOW 함수를 포함할 필요는 없음, QUALIFY 하에 넣어도 됨

SELECT item
FROM Produce
WHERE Produce.category = 'vegetable'
QUALIFY RANK() OVER (PARTITION BY category ORDER BY purchases DESC) <= 3

12. WINDOW

• WINDOW절은 명명된 윈도우 목록을 정의함

13. 집합연산자

• UNION, INTERSECT, EXCEPT
• UNION ALL, UNION DISTINCT, INTERSECT DISTINCT, EXCEPT DISTINCT가 존재
• INTERSECT 연산자는 왼쪽 및 오른쪽 입력 쿼리의 결과 조합에서 발견된 행을 반환
• EXCEPT 연산자는 왼쪽 입력 쿼리에서 오른쪽 입력 쿼리에 없는 행을 반환
• INTERSECT

14. WITH

• WITH절에는 하나 이상의 CTE(공통 테이블 표현식)가 포함됨
• CTE는 재귀적 OR 비재귀적(일반)이 존재
• RECURSIVE CTE는 자기 자신을 참고할 수 있는 애, 다시 말해, 최초 CTE가 반복적으로 실행되어 전체 결과 집합을 얻을 때까지 데이터의 하위 집합을 반환하는 CTE
• 이거 재귀 CTE인데 이해가 잘 안감