navicat mysql 代码块_Navicat for mysql 账期用户分析代码

--账期分析代码SELECT

bp.pid as "pid",

bp.uid as "user_id",

pers.ou as "ou",

bp.oid as "order_id",

o.create_at as "order_time",

o.order_price as "payed_money",

o.type as "payed_type",

o.order_status as "order_status",

o.payed_at as "payed_time",

bp.start_at as "binlling_start_time",

bp.duration as "binlling_duration",

bp.expire_at as "binlling_expire_time",

bp.status as "bill_plan_status",

bp.un_subscribed_at as "un_subscribed_time",

oi.pid as "product_id",

pod.name as "product_name",

bp.created_at as "create_time"

INTO A_20180801_billing_order_product

FROM

"billing_plan" bp ,

"order" o,

"order_item" oi,

"product" pod,

"person" pers

where

bp.start_at >= '2018-08-01'

and bp.oid = o.oid

and o.id = oi.order_id

and pod.pid = oi.pid

and o.uid = pers.uid ;

--添加主键 alter table A_20180801_billing_order_product add PRIMARY KEY( pid);

select apm.id, apm.name ,apm.pid ,apm.product_status, pf."value" as duration into all_month_product from product apm,product_feature pf, product_features pfs

where apm.id = pfs.product_id AND

pf.id = pfs.features_idAND

pf.measurement = 'Duration'AND

pf.measure_unit = 'MONTHS';

select

bop.*,

amp.duration as "product_duration"

INTO "a_20180801_product_billing_order_duration"

from all_month_product amp,a_20180801_billing_order_product bop where amp.pid = bop.product_id and bop.payed_money <500 and bop.payed_type !='OFFLINEPAY';

--有效套餐数create OR REPLACE FUNCTION active_product_users_month(at_month varchar,at_school varchar,at_year varchar default '2018-')

RETURNS integer as $total$

DECLARE

total integer;

time_1 varchar;

time_2 varchar;

BEGIN

time_1 := $3 || $1 || '-01' ;

select date(time_1) + interval '1 month' into time_2;

select count(DISTINCT(user_id)) into total from a_20180801_product_billing_order_duration

where ou = $2 and

binlling_start_time < date(time_2) and

binlling_expire_time >= date(time_1);

RETURN total;

END;

$total$ LANGUAGE plpgsql;

select active_product_users_month('08','12712');

select active_product_users_month('09','12712');

select active_product_users_month('10','12712');

select active_product_users_month('11','12712');

select active_product_users_month('12','12712');

--新增包学期人数create OR REPLACE FUNCTION new_term_users_month(at_month varchar,at_school varchar,at_year varchar default '2018-')

RETURNS integer as $total$

DECLARE

total integer;

time_1 varchar;

time_2 varchar;

time_3 varchar;

BEGIN

time_1 := $3 || $1 || '-01 00:00:00' ;

select date(time_1) + interval '1 month' into time_2;

select date(time_1) + interval '-1 month' into time_3;

select count(DISTINCT(user_id)) into total from a_20180801_product_billing_order_duration

where ou = $2 and

binlling_start_time < date(time_2) and

binlling_expire_time >= date(time_1) and

product_duration >= 4 and

user_id in (

select DISTINCT(user_id) from a_20180801_product_billing_order_duration

where ou = $2 and

binlling_start_time < date(time_2) and

binlling_expire_time >= date(time_1)

EXCEPT

select DISTINCT(user_id) from a_20180801_product_billing_order_duration

where ou = $2 and

binlling_start_time < date(time_1) and

binlling_expire_time >= date(time_3)

);

RETURN total;

END;

$total$ LANGUAGE plpgsql;

select new_term_users_month('09','12712');

select new_term_users_month('10','12712');

select new_term_users_month('11','12712');

select new_term_users_month('12','12712');

--新增非包学期create OR REPLACE FUNCTION new_month_users_month(at_month varchar,at_school varchar,at_year varchar default '2018-')

RETURNS integer as $total$

DECLARE

total integer;

time_1 varchar;

time_2 varchar;

time_3 varchar;

BEGIN

time_1 := $3 || $1 || '-01 00:00:00' ;

select date(time_1) + interval '1 month' into time_2;

select date(time_1) + interval '-1 month' into time_3;

select count(DISTINCT(user_id)) into total from a_20180801_product_billing_order_duration

where ou = $2 and

binlling_start_time < date(time_2) and

binlling_expire_time >= date(time_1) and

product_duration < 4 and

user_id in (

select DISTINCT(user_id) from a_20180801_product_billing_order_duration

where ou = $2 and

binlling_start_time < date(time_2) and

binlling_expire_time >= date(time_1)

EXCEPT

select DISTINCT(user_id) from a_20180801_product_billing_order_duration

where ou = $2 and

binlling_start_time < date(time_1) and

binlling_expire_time >= date(time_3)

);

RETURN total;

END;

$total$ LANGUAGE plpgsql;

select new_month_users_month('09','12712');

select new_month_users_month('10','12712');

select new_month_users_month('11','12712');

select new_month_users_month('12','12712');

--新增用户数create OR REPLACE FUNCTION new_users_month(at_month varchar,at_school varchar,at_year varchar default '2018-')

RETURNS integer as $total$

DECLARE

total integer;

time_1 varchar;

time_2 varchar;

time_3 varchar;

BEGIN

time_1 := $3 || $1 || '-01 00:00:00' ;

select date(time_1) + interval '1 month' into time_2;

select date(time_1) + interval '-1 month' into time_3;

select count(1) into total from (

select DISTINCT(user_id) from a_20180801_product_billing_order_duration

where ou = $2 and

binlling_start_time < date(time_2) and

binlling_expire_time >= date(time_1)

EXCEPT

select DISTINCT(user_id) from a_20180801_product_billing_order_duration

where ou = $2 and

binlling_start_time < date(time_1) and

binlling_expire_time >= date(time_3)

) _a;

RETURN total;

END;

$total$ LANGUAGE plpgsql;

select new_users_month('09','12712');

select new_users_month('10','12712');

select new_users_month('11','12712');

select new_users_month('12','12712');

--用户流失

create OR REPLACE FUNCTION lost_users_month(at_month varchar,at_school varchar,at_year varchar default '2018-')

RETURNS integer as $total$

DECLARE

total integer;

time_1 varchar;

time_2 varchar;

time_3 varchar;

BEGIN

time_1 := $3 || $1 || '-01 00:00:00' ;

select date(time_1) + interval '1 month' into time_2;

select date(time_1) + interval '-1 month' into time_3;

select count(1) into total from (

select DISTINCT(user_id) from a_20180801_product_billing_order_duration

where ou = $2 and

binlling_start_time < date(time_1) and

binlling_expire_time >= date(time_3)

EXCEPT

select DISTINCT(user_id) from a_20180801_product_billing_order_duration

where ou = $2 and

binlling_start_time < date(time_2) and

binlling_expire_time >= date(time_1)

) _a;

RETURN total;

END;

$total$ LANGUAGE plpgsql;

select lost_users_month('09','12712');

select lost_users_month('10','12712');

select lost_users_month('11','12712');

select lost_users_month('12','12712');

--到期用户数create OR REPLACE FUNCTION expire_users_month(at_month varchar,at_school varchar,at_year varchar default '2018-')

RETURNS integer as $total$

DECLARE

total integer;

time_1 varchar;

time_2 varchar;

BEGIN

time_1 := $3 || $1 || '-01 00:00:00' ;

select date(time_1) + interval '1 month' into time_2;

select count(DISTINCT(user_id)) into total from a_20180801_product_billing_order_duration

where ou = $2 and

binlling_start_time < date(time_2)

and binlling_expire_time >= date(time_1)

and binlling_expire_time < date(time_2);

RETURN total;

END;

$total$ LANGUAGE plpgsql;

select expire_users_month('08','12712');

select expire_users_month('09','12712');

select expire_users_month('10','12712');

select expire_users_month('11','12712');

select expire_users_month('12','12712');

--续购用户数create OR REPLACE FUNCTION renew_users_month(at_month varchar,at_school varchar,at_year varchar default '2018-')

RETURNS integer as $total$

DECLARE

total integer;

time_1 varchar;

time_2 varchar;

time_3 varchar;

BEGIN

time_1 := $3 || $1 || '-01 00:00:00' ;

select date(time_1) + interval '1 month' into time_2;

select date(time_1) + interval '-1 month' into time_3;

select count(1) into total from (

select DISTINCT(user_id) from a_20180801_product_billing_order_duration

where ou = $2 and

binlling_start_time < date(time_1)

and binlling_expire_time >= date(time_3)

and binlling_expire_time < date(time_1)

INTERSECT

select DISTINCT(user_id) from a_20180801_product_billing_order_duration

where ou = $2 and

binlling_start_time < date(time_2) and

binlling_expire_time >= date(time_1)

) _a;

RETURN total;

END;

$total$ LANGUAGE plpgsql;

select renew_users_month('09','12712');

select renew_users_month('10','12712');

select renew_users_month('11','12712');

select renew_users_month('12','12712');