SQL 예제) Multiple Layered WiTH

WITH pm_base AS (

SELECT 'min' as unit -- 기준 시간 : min(선호) / hours / second

, 23 as num_dev

, 35 as num_fac

......

)

, pm AS (

SELECT *

, date_trunc(unit, now_utc) - make_interval(hours => interval_day) as from_1_day   <=== 

FROM pm_base 

)

 

....

With에서 정의한 것을 With 안에서 쓸 때

pm_base처럼 정의를 먼저 한 후에 사용한다.

 

WITH pm_base AS (
  SELECT 'min' as unit	-- 기준 시간 : min(선호) / hours / second
  	, 23 as num_dev
  	, 35 as num_fac
  	, 2 AS interval_hour	-- 바꿀 것> 원하는 시간동안의 % 
  	, 24 AS interval_day	-- interval_hour 보다 크거나 같게 == 
  	, 'factory-%' as like_fac
  	, 'develop-%' as like_dev
  	, (60 / 2) as period_num
  	, NOW() AT TIME ZONE 'UTC' as now_utc
)
, pm AS (
  SELECT *
  	, (100.0 / period_num / interval_hour / num_dev) as percent_dev
  	, (100.0 / period_num / interval_hour / num_fac) as percent_fac
  	, (100.0 / period_num / interval_hour / (num_dev + num_fac)) as percent_all
  	, date_trunc(unit, now_utc) as now_unit_time
  	, date_trunc(unit, now_utc) - INTERVAL '1 hour'*interval_hour as from_time_0
  	, date_trunc(unit, now_utc) - make_interval(hours => interval_hour) as from_time
  	, date_trunc(unit, now_utc) - make_interval(hours => interval_day) as from_1_day
  FROM pm_base
)
select
    ROUND(
    	COUNT(*) FILTER (
    		WHERE received_at < pm.now_unit_time AND received_at >= pm.from_time
    	) ::numeric * MAX(pm.percent_all), 2) AS "%All"	-- MAX : error 회피 : 집계 함수로 감싸줌
    , ROUND(
    	COUNT(*) FILTER ( 
    		WHERE received_at < pm.now_unit_time AND received_at >= pm.from_time AND device_id LIKE pm.like_fac
    	) ::numeric * MAX(pm.percent_fac), 2) AS "%Fac"	-- MAX : error 회피 : 집계 함수로 감싸줌
    , ROUND(
    	COUNT(*) FILTER (
    		WHERE received_at < pm.now_unit_time AND received_at >= pm.from_time AND device_id LIKE pm.like_dev 
    	) ::numeric * MAX(pm.percent_dev), 2) AS "%Dev"	-- MAX : error 회피 : 집계 함수로 감싸줌
    , COUNT(*) FILTER (WHERE received_at < pm.now_unit_time AND received_at >= pm.from_time) AS "#All"
    , COUNT(*) FILTER (WHERE received_at < pm.now_unit_time AND received_at >= pm.from_time AND device_id LIKE pm.like_fac) AS "#Fac"
    , COUNT(*) FILTER (WHERE received_at < pm.now_unit_time AND received_at >= pm.from_time AND device_id LIKE pm.like_dev) AS "#Dev"
FROM 
	pm -- pm을 서브쿼리보다 먼저 JOIN으로 포함시켜야 함	
JOIN LATERAL (
	/*
	 * PostgreSQL은 기본적으로 서브쿼리 안에서 바깥쪽 테이블(pm)을 참조하는 것을 허용하지 않는데, LATERAL 키워드를 쓰면 이를 명시적으로 허용할 수 있습니다.
	 * 
	 * SQL에서는 테이블 이름을 변수처럼 동적으로 참조할 수 없다
	 */
    SELECT device_id, received_at FROM t34955 WHERE received_at >= pm.from_1_day
    UNION all
    	SELECT device_id, received_at FROM t34957 WHERE received_at >= pm.from_1_day
    UNION all
    	SELECT device_id, received_at FROM t34958 WHERE received_at >= pm.from_1_day
) AS logs ON true  -- pm 을 조인으로 옮긴 후 필요해짐
WHERE device_id LIKE pm.like_dev OR device_id LIKE pm.like_fac
;

2시간

24시간