postgre常用sql

1. 构造日期序列

2. in条件的写法

where col=any($arr)
--url传过来逗号分隔的字符串转数组
select regexp_split_to_array('1,2,3',',')

1. 月份补足两位

select lpad('1', 2, '0')

2. 取最后一段

select split_part('aa,bb,cc,dd',',',1);
select reverse(split_part(reverse('aa,bb,cc,dd'),',',1));--不知道有几段，倒过来找

3. 递归查询

WITH RECURSIVE cte_name(
    CTE_query_definition -- non-recursive term
    UNION [ALL]
    CTE_query definion  -- recursive term
) SELECT * FROM cte_name;

4. 累计完成度
   8-1是周六，当周只有2天

with tmp as(
select extract(week from sale_date) weekid,extract(month from sale_date) monthid,
sum(sale_real_amt_wtd) sum_real,--当周完成
sum(sale_basic_amt_target_wtd) sum_obj,--当周目标
avg(sale_climb_amt_target_wtd) month_obj--当月目标
from tenant_1800000143_rst.bi_fct_store_day_sales
where sale_date>=(substring(dt,1,8)||'01')::date and sale_date<'2020-09-01'::date 
group by weekid,monthid
)
select a.*,(sum(sum_real) over (partition by monthid order by weekid))/month_obj --月目标比率累计完成度
from tmp a order by weekid

4. 求周一

-- 在public下创建，调用时不用加schema，更方便（周日的dow是0）
create or replace function get_monday(dt varchar)
RETURNS varchar AS $BODY$
begin --select tenant_1800000143_rst.get_monday('2020-12-02')
    return to_char(((-1*mod((6+EXTRACT(DOW FROM dt::date))::int,7) || ' days'):: interval + dt::date),'YYYY-MM-DD');
END
$BODY$
  LANGUAGE plpgsql

-- 调用
select get_monday('2020-12-02')

5. 除0

select a/nullif(b,0)

6. python写存过

CREATE FUNCTION pymax (a integer, b integer)
  RETURNS integer
AS $$ --create extension plpython3u;
  if a > b:
    return a
  return b
$$ LANGUAGE plpython3u;

7. 每月首末的1天

select date_trunc('MONTH', current_date);--当月第一天
SELECT (date_trunc('MONTH', current_date) + INTERVAL '1 MONTH - 1 day')::date;--当月最后一天