[ Syntax 정리(1) ] LIMIT, OFFSET, Self-Join, JOIN, CAST, LOCATE, POSITION, FUNCTIONS(STRING, DATES, ARRAYS)

 

 

• LIMIT & OFFSET 페이징 

# 1번째 row ~ 3번째 row
SELECT * FROM website_sessions 
LIMIT 3 OFFSET 0;

# 4번째 row ~ 6번째 row
SELECT * FROM website_sessions 
LIMIT 3 OFFSET 3;

LIMIT & OFFSET 결과

• 2nd most & 3rd most..

ex. 봉급이 두번째로 많은 직원 조회

# LIMIT N,1
-- 2nd most 
SELECT * FROM employees ORDER BY salary LIMIT 1,1 ;
-- 3rd most
SELECT * FROM employees ORDER BY salary LIMIT 3,1 ;

 

---

• Self-Join

- Product user behavior를 확인할 때 자주 사용   (사용한 컬럼을 중복으로 불러올 수 있음)

- A self join is useful when a table has the values from one column re-used in another column.

# Self Join

WITH selfjoin AS
(
# 동일한 테이블에서 동일한 컬럼을 불러옴
SELECT 
	a.node a_node,
    a.parent a_parent,
    b.node b_node,
    b.parent b_parent
FROM tree a
LEFT JOIN tree b ON a.parent = b.node
) 
SELECT 
	a_node AS node,
    CASE
		WHEN b_node IS NULL AND b_parent IS NULL THEN 'Root'
        WHEN b_node IS NOT NULL AND b_parent IS NOT NULL THEN 'Leaf'
        ELSE 'Inner'
	END AS label
FROM selfjoin;

 

 

 

---

• JOIN without ON

  # ON is an optional Clause > simply JOIN two tables
  SELECT * FROM test1 JOIN test2;

 

test1, test2

결과

 

---

• JOIN - WHERE, JOIN, USING, ON

# JOIN 
# 1. WHERE 절
SELECT sessions.website_session_id, 
		sessions.user_id,
		pageviews.pageview_url
FROM website_sessions sessions, website_pageviews pageviews
-- WHERE & =
WHERE ( sessions.website_session_id = pageviews.website_session_id )
	AND sessions.utm_campaign='nonbrand';
    
# 2. JOIN & USING
SELECT sessions.website_session_id, 
		sessions.user_id,
		pageviews.pageview_url
FROM website_sessions sessions
-- JOIN & USING 필드명(JOIN KEY)
	JOIN website_pageviews pageviews USING (website_session_id)
WHERE sessions.utm_campaign='nonbrand';

# 3. JOIN & ON
SELECT sessions.website_session_id, 
		sessions.user_id,
		pageviews.pageview_url
FROM website_sessions sessions
-- JOIN & ON 테이블명.필드명(JOIN KEY)
	JOIN website_pageviews pageviews ON pageviews.website_session_id
WHERE sessions.utm_campaign='nonbrand';

 

---

• SELECT * FROM multiple tables (카테시안곱)

- Combine all rows from multiple tables into one table

- table_a의 row 100개, table_b의 row가 200 이면 100*200  20000row의 테이블이 생성 (카테시안곱)

 

---

• CAST 형변환

- INTEGER, DECIMAL, DOUBLE, BOOLEAN, DATETIME, DATE, TIME, GYEAR, GYEARMONTH, DATETIMESTAMP

# 형변환
# CAST( 필드명 AS 변환포맷 )
# INTEGER, DECIMAL, DOUBLE, BOOLEAN, 
# DATETIME, DATE, TIME, GYEAR, GYEARMONTH, DATETIMESTAMP
SELECT created_at original_datetime,
		CAST(created_at AS DATE) date,
        CAST(created_at AS TIME) time,
        price_usd original_price,
        -- DECIMAL(m,0) = DECIMAL(m) 2자리 정수
        CAST(price_usd AS DECIMAL(2,0)) price_integer
FROM orders 
LIMIT 10;

 

---

• Location of a specified substring in a string value

- LOCATE, POSITION

# POSITION, LOCATE : string의 위치
SELECT 
	product_name original,
    # POSITION('' IN 필드)
	POSITION("e" in product_name) position,
    # LOCATE('', 필드)
	LOCATE("e" , product_name) location
FROM products;

 

 

---

• Combining Tables

- UNION, INTERSECT, MINUS

 

---

• String Functions

- CONCAT, LEFT, LOWER, UPPER, SUBSTRING, STRING_SPLIT, LENGTH

 

 

---

• Date Functions

- DATE_DIFF, DATE_ADD, NOW, DATE_PART

 

---

• Array Functions

- ARRAY, ARRAY_AGG, ARRAY_APPEND, ARRAY_CONCAT, ARRAY_JOIN, ARRAY_LENGTH, ARRAY_PREPEND

- ARRAY_CONTAINS, ELEMENT_AT