POSTGRE SQL 想使用變數去定義一段日期區間, 但遍尋不到像是在 MSSQL 時常用的 DECLARE @count int, @x int, @y nvarchar(10)...
想到一招利用 CTE 方式去定義一個這樣類似變數的作法來實現...
原來的script
with
cte_order_11month(uuid ,counts)
as (
--前11個月的所有訂購
select
c.uuid, count(1)
FROM ecorder e
LEFT JOIN customer c ON c.id=e.customer_id
WHERE e.ecorder_source NOT IN ('11','12','14','15','16')
and e.ecorder_date >= current_date - interval '1 month' * 12
and e.ecorder_date < to_date(to_char(current_date, 'yyyy/MM')||'/01', 'yyyy/MM/dd')
GROUP BY c.uuid
HAVING count(1)>0
)
....
上面code裡的current_date 是系統定義的當天, 若要想要改變日期, 就要在許多用到 current_date 的地方置換掉, 變為你要的日期....這對我來說真是痛點啊...
試著玩了一下底下的寫法..
with
v_test(start_date)
as (
select to_date('2021-10-1' , 'yyyy-mm-dd')
--select current_date
),
cte_order_11month(uuid ,counts)
as (
--前11個月的所有訂購
select
c.uuid, count(1)
FROM ecorder e
LEFT JOIN customer c ON c.id=e.customer_id
WHERE e.ecorder_source NOT IN ('11','12','14','15','16')
and e.ecorder_date >= (select start_date from v_test) - interval '1 month' * 12
and e.ecorder_date < to_date(to_char((select start_date from v_test), 'yyyy/MM')||'/01', 'yyyy/MM/dd')
GROUP BY c.uuid
HAVING count(1)>0
)
....
以上面片段Script 為例 e.ecorder_date >= (select start_date from v_test), 其中select start_date from v_test 就是去我們自定義的v_test中的 start_date, 取出值來後, 再接著處理..
那這樣不就是變相的可以使用變數, 來完成這段 script 查詢嗎? 帥啦~
