构造日期序列
in条件的写法
where col=any($arr)
--url传过来逗号分隔的字符串转数组
select regexp_split_to_array('1,2,3',',')
- 月份补足两位
select lpad('1', 2, '0')
- 取最后一段
select split_part('aa,bb,cc,dd',',',1);
select reverse(split_part(reverse('aa,bb,cc,dd'),',',1));--不知道有几段，倒过来找
- 递归查询
WITH RECURSIVE cte_name(
CTE_query_definition -- non-recursive term
UNION [ALL]
CTE_query definion -- recursive term
) SELECT * FROM cte_name;
- 累计完成度
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
- 求周一
-- 在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')
- 除0
select a/nullif(b,0)
- python写存过
CREATE FUNCTION pymax (a integer, b integer)
RETURNS integer
AS $$ --create extension plpython3u;
if a > b:
return a
return b
$$ LANGUAGE plpython3u;
- 每月首末的1天
select date_trunc('MONTH', current_date);--当月第一天
SELECT (date_trunc('MONTH', current_date) + INTERVAL '1 MONTH - 1 day')::date;--当月最后一天
